Data warehousing: Making use of synchronized seq scans

Why Concurrent Analytics Slow Down

When many users run analytical queries against the same large table at the same time, PostgreSQL can end up doing a lot of redundant work. Consider a simple scenario: ten users all need to scan a 1 TB table. If the storage system can deliver 1 GB/second, a single scan would finish in roughly 17 minutes with no caching.

Add concurrent users, and the picture changes. With no shared cache, each additional session forces the storage to read the same data again from scratch.

The key detail is that throughput usually doesn't scale perfectly with concurrency, especially on spinning disks.

The net effect is that every new user makes the query take longer for everyone already running.

How PostgreSQL Syncs Scans

PostgreSQL has had a solution to this for over 15 years, though it often goes unnoticed outside data warehouse circles. The feature is called synchronized sequential scans, and it works by aligning concurrent scans of the same table.

The idea is simple. When a second query needs to do a full sequential scan while a first one is already in progress, the new query doesn't start from the beginning of the table. Instead, it joins the first scan at its current position. Both scans then read the remaining data together, sharing the same I/O requests.

Once they hit the end of the table, the second scan loops back to the start and reads the portion it missed initially. This works with more than two scans as well. The scans proceed in lockstep until they finish, effectively turning many independent full-table reads into roughly a single pass over the data. This can reduce I/O dramatically when the data volume is large.

The synchronization, however, is not mandatory. PostgreSQL only aligns the scans at the start. If one scan falls behind because it has more work to do, the scans are free to separate so the slower one isn't forced to crawl. That keeps the optimization from being a bottleneck in mixed workloads.

Since data warehousing and analytics are frequently I/O-bound, keeping scans together usually gives a noticeable performance win.

Controlling the Behavior

The behavior is controlled by a single configuration variable:

By default, synchronize_seqscans is set to on, which is the recommended setting for most systems. If you ever need to alter it, the value can be changed in postgresql.conf.