PostgreSQL 18: Thirteen Small Measurements That Really Change Performance

The author of the Explain, Measured blog published, on November 8, 2026, a list of thirteen measurements taken in a single test session on PostgreSQL 18.6. No marketing benchmarks here: these are targeted comparisons, using EXPLAIN (ANALYZE, BUFFERS, TIMING OFF), the median of five runs after a warm-up, running on a single machine (MacBook Air M5, 10 cores, 24GB RAM, default configuration with work_mem at 4MB and shared_buffers at 128MB).

Each one is small. A few of them surprised me anyway.

The author warns that these numbers aren't meant for comparison with his previous posts, done on a different machine. What they do serve for is understanding the direction and order of magnitude of each design decision. That's the angle that matters to anyone deciding on indexes, transaction isolation, and work_mem size in production.

Indexes: what each type really does to the page

The test table has 2 million orders, with 5% in open status. A partial index WHERE status = 'open' took up 0.7MB against 14.2MB for the full index on customer_id, and needed 2 buffers versus 43 to fetch a customer's open orders. The savings only show up when the query's WHERE clause exactly matches the partial index's condition.

The BRIN index on created_at showed the other side of the coin: since rows were inserted in increasing date order, a summary by block range did the job with 24kB against 44.9MB for a B-tree, with nearly identical query time (0.63ms versus 0.55ms) to count a full day. This result depends entirely on the physical order of the data; the author did not test the same index on a shuffled table, where BRIN would have nothing useful to summarize.

The INCLUDE clause revealed a trade-off that the documentation glosses over:

The index gets faster because it skips the trip to the table to fetch amount, but it nearly triples in size. The cause isn't amount itself: since PostgreSQL 13, the B-tree deduplicates repeated keys, keeping a list of pointers to the same key. INCLUDE columns break this deduplication, because the included value varies row by row even when the key repeats. With deduplication manually disabled, an index on status (only two possible values) jumps from 13.9MB to 45.0MB, and customer_id (50,000 values) from 14.2MB to 45.0MB; on a primary key, where every value is already unique, the size doesn't even change (44.9MB).

Another point that usually surprises: even an Index Only Scan in the plan doesn't guarantee the table won't be touched. On a freshly loaded table with autovacuum disabled, scanning 200,001 rows generated 200,001 "heap fetches" and 1,917 buffers in 7.1ms, because the visibility map didn't know which pages were fully visible. After a VACUUM, heap fetches dropped to zero, buffers to 553, and time to 4.4ms, with the plan showing exactly the same "Index Only Scan" in both runs. The plan's label, by itself, doesn't give away the problem.

The measurement that should most catch the attention of anyone writing a migration is the one about a foreign key without an index. PostgreSQL automatically creates an index for the primary key, but not for the foreign key column. With 10,000 parent tables, 1 million child rows, and ON DELETE CASCADE, deleting 200 parents (cascading to 20,000 children) took 3.6 seconds without an index on child.parent_id and 0.013 second with the index in place: a difference of about 277 times.

Writing: COPY, UNLOGGED, and the cost of synchronous commit

Loading 200,000 rows through psql's local socket, one transaction per run, made the cost of inserting row by row clear:

Row-by-row INSERT was about 54 times slower than \copy. For seed scripts, initial loads, or data migrations, that's a direct argument for swapping INSERT loops for COPY or, at the very least, batches of hundreds of rows.

UNLOGGED tables showed another measurable gain: inserting 2 million rows took 1.78s and generated 192.6MB of WAL on a normal table, versus 1.00s and 0MB of WAL on an UNLOGGED one. The price is well known, but it's worth repeating: an UNLOGGED table is wiped after a crash and isn't replicated, so it only works for reconstructible data, like staging tables in ETL or caches that can be recalculated.

With synchronous_commit = off, 10,000 single-row INSERTs, each in its own transaction, delivered 64,478 transactions per second versus 28,101 with the parameter on, more than double. The author is honest about the test's limit: on this Mac, fsync is cheap (wal_sync_method resolved as open_datasync), so on server disks that actually write to disk the difference could be different. Turning off synchronous_commit doesn't corrupt data, but it can lose the last commits in a crash, which rules out the option for financial transaction tables and frees it up for metrics, logs, and low-risk queues.

The bloat measurement from UPDATE sums up why high-write tables need attention to VACUUM:

Each UPDATE writes a new version of the row; VACUUM marks the old space as reusable, but doesn't return anything to the operating system, which is why the size doesn't shrink back. The gain only shows up in the next round: the second full UPDATE reused the freed space instead of requesting more disk. Anyone who needs to actually shrink the table back needs VACUUM FULL or a tool like pg_repack, with the locking cost that implies.

Sorting and parallelism: where work_mem stops helping

An ORDER BY note LIMIT 10 over the 2 million rows, without parallelism, took 119ms using a top-N heapsort of just 26kB. Swapping LIMIT 10 for a full sort of the same rows took 571ms: the plan with LIMIT also reads every row, but keeps only the top ten in memory instead of materializing the entire sort.

The same full sort, tested at three work_mem values, shows where the gain stops:

Once the sort fits entirely in memory, giving it more work_mem doesn't speed anything up: the jump from 64MB to 256MB barely changed the time. Since work_mem applies to each sort or hash in each query, not to the server as a whole, a global value that's too high multiplies RAM usage under concurrent load without bringing the same benefit.

Finally, a LIKE '%ab%' filter with GROUP BY over the 2 million rows measured the effect of max_parallel_workers_per_gather: 151ms without parallelism, 54ms with the default 2 workers (nearly 3 times faster), and 44ms with 4 workers. The jump from 0 to 2 workers delivered most of the gain; from 2 to 4, the return is already much smaller, which matters for anyone thinking about raising this parameter without measuring it on their own machine first.

What the test doesn't cover

All the measurements came from a single machine, with no concurrent connections, no replica, and no server disk with genuinely expensive fsync. The author made this explicit when commenting on the synchronous_commit result and by not testing BRIN on shuffled data. For anyone who wants to reproduce or extend the tests, the post publishes the run.py and brin_size.py scripts and the results.json file, with the raw output of each individual run.

None of these thirteen numbers, on its own, justifies a production configuration change without measuring your application's actual case. But together, they form a concrete checklist: a partial index when the filter is selective, BRIN when the table already grows in order, caution with INCLUDE on high-cardinality columns, a manual index on every foreign key used in ON DELETE CASCADE, \copy or batches for bulk loading, UNLOGGED only for disposable data, and work_mem sized to the actual sort, not guesswork.