Explain, Measured

PostgreSQL performance, measured. Every number comes from a run you can repeat.

  1. Thirteen small measurements on PostgreSQL 18

    Partial and BRIN indexes, INCLUDE, deduplication, index-only scans before VACUUM, an unindexed foreign key, COPY against INSERT, synchronous_commit, a full-table UPDATE, top-N sorts, work_mem and parallel workers. One run, one laptop, all the numbers.

  2. Four PostgreSQL arguments that came out even

    IN against ANY, text against enum for a status column, empty columns, and count(DISTINCT). I measured each one expecting a winner. Three were a tie and the fourth went the other way.

  3. What deleting rows does to a PostgreSQL table

    I deleted half of a 2M-row table and the file stayed at 137 MB, through DELETE and through VACUUM. Only VACUUM FULL brought it to 68 MB. Plus DELETE against TRUNCATE, and what fillfactor does to updates.

  4. Five PostgreSQL queries that did more work than I asked for

    UNION, a MATERIALIZED CTE, ORDER BY random(), SELECT * and a function in WHERE. Each one quietly read far more than the answer needed. UNION took 13.6 s where UNION ALL took 0.68 s.

  5. UUIDs stored as text: what it costs in PostgreSQL 18

    Same 5 million UUIDs, stored as uuid and as text. The text index was 88% bigger, one level deeper, and every lookup read one more page.

  6. UUIDv4 or UUIDv7 for a primary key? 5 million rows on PostgreSQL 18

    UUIDv7 inserted 5 million rows in 27 seconds, UUIDv4 in 46. The v4 index was 29% bigger. Random lookups cost the same.