8 November 2026 · postgresql

Thirteen small measurements on PostgreSQL 18

These are thirteen things I measured in one sitting. Each one is small. A few of them surprised me anyway.

Setup

PostgreSQL 18.6, built from source, default settings (work_mem 4MB, shared_buffers 128MB, 2 parallel workers per gather). MacBook Air M5, 10 cores, 24 GB RAM, internal SSD. This is a different machine from my earlier posts, so don't compare times across posts.

Most tests use one table of 2,000,000 orders: 50,000 customers, 5% of rows with status open, created_at increasing every 10 seconds, and an md5 string in note. Query times are EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) execution time, median of 5 runs after one warm-up run (3 runs for the slower ones). Buffers are from the top plan node. Sizes are decimal MB. The script and the raw results are linked at the bottom.

Indexes

A partial index only holds the rows its WHERE clause allows.

It only gets used when the query's WHERE matches the index condition.

BRIN stores a summary per block range instead of one entry per row. Rows were inserted in created_at order, which is the case it is built for.

The table itself was 202.3 MB. I didn't test BRIN on shuffled data, where it would have nothing useful to summarize.

INCLUDE lets an index carry a column it isn't sorted by. For sum(amount) over one customer's 40 rows:

The size jump is deduplication. Since PostgreSQL 13 a B-tree stores a repeated key once with a list of row pointers, and INCLUDE indexes can't do that. With deduplication turned off by hand:

An index-only scan still has to check the table for any page VACUUM hasn't marked all-visible. On a freshly loaded table with autovacuum off, scanning 200,001 rows:

The plan said Index Only Scan both times.

PostgreSQL creates an index for a primary key, not for a foreign key column. With 10,000 parents, 1,000,000 children and ON DELETE CASCADE, deleting 200 parents (20,000 children) took 3.6 s without an index on child.parent_id and 0.013 s with one.

Writing

Loading 200,000 rows from psql over the local socket, one transaction each time, median of 3:

Inserting 2,000,000 rows into an empty table:

An unlogged table is emptied after a crash and isn't replicated.

For commits, I ran 10,000 single-row INSERTs with pgbench, one client, each statement its own transaction. With synchronous_commit = on it did 28,101 transactions per second. With off, 64,478. Off can lose the last few commits in a crash but doesn't corrupt anything. This Mac's fsync is cheap (wal_sync_method was open_datasync), so on a server disk that really flushes, the gap could be different. I didn't test one.

Then an UPDATE of every row in a copy of the table with no indexes:

Every update writes a new row version. VACUUM didn't return the space to the operating system, but it let the second UPDATE reuse it.

Sorting and parallel workers

Sorting the 2M rows by note with parallelism off. ORDER BY note LIMIT 10 took 119 ms and used a 26 kB top-N heapsort. The full sort took 571 ms. Both read every row.

The same full sort at three work_mem settings:

Past what the sort needed, more memory did nothing. work_mem applies to each sort or hash in each query, so a big global value multiplies.

Last, a LIKE '%ab%' filter with a GROUP BY over all 2M rows:

Every run read the same pages. Two workers gave most of the gain.

Files

• run.py: every test above, in order
• brin_size.py: the exact BRIN size in bytes (the main run rounded it to 0.0 MB)
• results.json: raw output, including each individual run