Hollis Varden: 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.

Index on customer_id	Size	Buffers for one customer's open orders
all rows	14.2 MB	43
`WHERE status = 'open'`	0.7 MB	2

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.

Index on created_at	Size	Count one day (8,640 rows)
B-tree	44.9 MB	0.55 ms, 134 buffers
BRIN	24 kB	0.63 ms, 137 buffers

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:

Index	Size	Buffers	Time
`(customer_id)`	14.2 MB	43	0.073 ms
`(customer_id) INCLUDE (amount)`	45.0 MB	4	0.038 ms

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:

Index	deduplicate_items on	off
status (2 values)	13.9 MB	45.0 MB
customer_id (50,000 values)	14.2 MB	45.0 MB
primary key (all unique)	44.9 MB

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:

	Heap fetches	Buffers	Time
before VACUUM	200,001	1,917	7.1 ms
after VACUUM	0	553	4.4 ms

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:

Method	Time
`\copy`	0.039 s
INSERT, 1,000 rows per statement	0.133 s
INSERT, one row per statement	2.1 s

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

Table	Time	WAL
normal	1.78 s	192.6 MB
UNLOGGED	1.00 s	0 MB

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:

Step	Table size
before	202.5 MB
after UPDATE	404.7 MB
after VACUUM	404.7 MB
after a second full UPDATE	404.7 MB

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: