week 46, 2021

Slow UPDATEs in PostgreSQL

I found that some query was dealing with a relatively small fraction of rows in the table, but the query execution was based on some regular index. Postgres indexes come with a lot of various flavors: besides the type of the index you may choose, there are multicolumn, functional, partial, and – since Postgres 11 – covering indexes. For the SELECT queries, I was oxptimizing, using a partial index was a natural choice. It improved the SELECT indeed, as expected. But it also made UPDATEs to the same table significantly slower!

This was a big surprise. I know that this will be a surprise to many of my readers too. Let's buckle up and travel thru a series of short experiments to explore how this may happen: we'll check various index options looking at the performance of both SELECT and UPDATE queries, and in the end, we'll try to draw some useful conclusions, as well as discuss how we can build an optimization approach that would follow the "first, do no harm" principle.

Source: How partial, covering, and multicolumn indexes may slow down UPDATEs in PostgreSQL, an article by Nikolay Samokhvalov.

The secret of the macOS Monterey network quality tool

It seems that Apple has quietly added a new tool in macOS Monterey for measuring your device’s Internet connectivity quality. You can simply call the executable networkQuality, which executes the following tests:

  • Upload/download capacity (your Tx/Rx bandwidth essentially)
  • Upload/download flows, this seems to be the number of test packets used for the responsiveness tests
  • Upload/download responsiveness measured in Roundtrips Per Minute (RPM), which according to Apple, is the number of sequential round-trips, or transactions, a network can do in one minute under normal working conditions

Source: The secret of the macOS Monterey network quality tool, an article by Dan Petrov.