PostgreSQL 18.6 benchmark suite tests partial indexes, BRIN, deduplication, and parallel query

Database engineer Hollis Varden published a systematic benchmark of thirteen PostgreSQL 18.6 behaviors, built from source with default settings on an Apple Silicon MacBook Air M5. The tests cover index strategies, write performance, and query execution using a 2-million-row orders table with realistic cardinality patterns. Key findings include a 20x buffer reduction using a partial index over a full B-tree, nearly identical query times between BRIN and B-tree for time-ordered data at vastly different sizes, and the dramatic 276x speedup from adding an index on a foreign key column with ON DELETE CASCADE.

The write-path tests compare bulk loading methods, measure the 1.78x speed and complete WAL elimination of UNLOGGED tables, and show synchronous_commit=off more than doubling transaction throughput from 28,101 to 64,478 TPS. The analysis also demonstrates that INCLUDE indexes cannot use PostgreSQL 13's B-tree deduplication, causing significant size inflation, and that VACUUM reclaims space for reuse without returning it to the operating system. Parallel worker scaling and work_mem thresholds for sort methods are also characterized with concrete numbers.

Index and storage engine behavior in PostgreSQL 18.6

• Partial indexes: A partial index on customer_id WHERE status = 'open' shrank from 14.2 MB to 0.7 MB and reduced buffer reads from 43 to 2 for targeted queries, but requires exact WHERE clause matching to be selected by the planner.
• BRIN vs. B-tree: For time-sequenced data, a BRIN index on created_at occupied 24 kB versus 44.9 MB for B-tree, with query times of 0.63 ms versus 0.55 ms and comparable buffer usage, confirming BRIN's niche for append-only ordered data.
• INCLUDE and deduplication: An INCLUDE (amount) index halved query time from 0.073 ms to 0.038 ms and cut buffers from 43 to 4, but ballooned from 14.2 MB to 45.0 MB because PostgreSQL 13's B-tree deduplication cannot compress repeated keys in INCLUDE indexes.
• Index-only scan visibility: On a fresh table with autovacuum disabled, an index-only scan performed 200,001 heap fetches using 1,917 buffers; after VACUUM marked pages all-visible, heap fetches dropped to zero and buffers fell to 553, improving time from 7.1 ms to 4.4 ms despite identical plan labels.
• Foreign key deletion performance: Deleting 200 parent rows with ON DELETE CASCADE cascaded to 20,000 child rows, taking 3.6 seconds without a child.parent_id index versus 0.013 seconds with one, a 276x difference demonstrating PostgreSQL's lack of automatic foreign key indexes.

Why it matters

These measurements provide reproducible baselines for database administrators tuning PostgreSQL 18 in production, particularly for decisions around index type selection, bulk loading strategy, and the operational implications of UNLOGGED tables and synchronous_commit settings. The explicit quantification of INCLUDE index overhead and foreign key deletion penalties addresses common schema design questions that affect application performance at scale across Linux-hosted PostgreSQL deployments.