Postgres 19: How Our Advice Has Changed Since We Wrote It

Over the years we have written a lot about how data gets into Postgres, how it sits on disk, and how indexes help you find it again. Some of that advice was written against Postgres 10 or 11. A surprising amount of it is still exactly what we would tell you for the upcoming Postgres 19 release. Functionality described here is based on current betas; minor details may still change before GA.

This post revisits Crunchy posts in the “load, storage, indexes, and partitioning” bucket: what we wrote, which version moved the needle, and what we would tell you to do now. Along the way: async I/O, more resilient COPY, LZ4 by default, richer BRIN shapes, skip scan, and smoother partition operations.

Async I/O: faster scans and vacuum on modern storage

In 2019 we benchmarked a BRIN index against a B-tree and a parallel sequential scan on the same time-series table. Sometimes BRIN won. Sometimes the parallel seq scan won: four workers chewing through the heap beat a clever index. That was the right lesson for Postgres 11: indexes are a tradeoff against what the executor can already do in parallel.

Postgres 18 made those heap reads substantially faster.

Async I/O lets backends queue multiple disk reads instead of waiting on each one. Sequential scans, bitmap heap scans (the path BRIN and many bitmap index plans finish with), and vacuum all benefit. Community benchmarks have shown up to ~3× on cold, latency-bound storage, a big deal for cloud disks. Defaults matter here: io_method = worker is on out of the box; on Linux 5.1+ you can try io_method = io_uring.

Postgres 19 builds on that: I/O workers can autoscale (io_min_workers / io_max_workers), read-ahead scheduling improved, and EXPLAIN (ANALYZE, IO) can show what the async subsystem is doing. Parallel query is still there; each worker can queue several reads and keep making progress while some of them are still in flight, so you get more useful work between waits. Parallel autovacuum workers also landed in 19 (autovacuum_max_parallel_workers and per-table autovacuum_parallel_workers), so maintenance can fan out, but the defaults are conservative: tune them when vacuum is falling behind on large tables.

One related default flip: JIT is off by default in Postgres 19 (it had been on since 12). The old cost model was unreliable, so large analytical or parallel scans that used to compile at runtime no longer will unless you turn jit back on. If those workloads matter to you, re-enable explicitly and re-check plans after upgrade.

Postgres 19 advice: Keep choosing indexes for selectivity, and re-test BRIN-vs-parallel plans on your storage with EXPLAIN (ANALYZE, BUFFERS, IO) after you upgrade. Tune effective_io_concurrency / maintenance_io_concurrency with the new defaults in mind (they rose to 16 in 18). Treat async I/O as more headroom for the heap paths, alongside good COPY, TOAST modeling, BRIN, and covering indexes.

Loading data: COPY is still king, and now more resilient

What we wrote: In Fast CSV and JSON Ingestion in PostgreSQL with COPY (2018, Postgres 10), Jonathan Katz showed the classic pattern: generate CSV or newline-delimited JSON, pipe it into psql, and let COPY ... FROM STDIN do the heavy lifting. Prefer COPY over row-by-row INSERT. Store JSON as jsonb. Add a GIN index when you need containment queries.

That advice was right then and is still right now. The load path itself did not need reinventing; it got more capable.

What changed:

FREEZE on an initial load into a freshly created or truncated table is still the right performance trick when you want to skip a later freeze vacuum.

Postgres 19 advice: Keep using COPY for bulk ingest, including imperfect feeds. A practical 19-era load looks like this:

COPY events (event_id, occurred_at, payload)
FROM STDIN
WITH (
  FORMAT csv,
  HEADER 2,
  DEFAULT '__DEFAULT__',
  ON_ERROR set_null,
  LOG_VERBOSITY verbose
);

HEADER 2 skips two lead-in lines. ON_ERROR set_null keeps the row and nulls only the bad field instead of discarding the whole line. Prefer ON_ERROR ignore when a bad cell should drop the row entirely; pair that with REJECT_LIMIT when you want a hard cap on how many skips you will tolerate.

Prefer FREEZE for empty staging tables that you load once and then promote. And when you need to export partitioned data or hand JSON to another system, COPY TO handles the parent directly:

COPY events TO STDOUT WITH (FORMAT json);
COPY events TO STDOUT WITH (FORMAT json, FORCE_ARRAY);

Plain FORMAT json streams newline-delimited JSON (NDJSON/JSONL: one object per line), which is handy for batch pipelines. FORCE_ARRAY wraps the whole result in a single JSON array, which some web APIs expect.

TOAST: same mechanism, faster default compression

What we wrote: In Postgres TOAST: The Greatest Thing Since Sliced Bread? (2024), Elizabeth Christensen walked through the 8 kB page, the ~2 kB toast_tuple_target, storage strategies, and the practical guidance: updating a toasted row rewrites toast chunks, frequent access of compressed values has a cost, and large JSON or text that you query often belongs in a better data model, not in a giant toasted blob.

That mental model is still the right one. TOAST did not get replaced; it got a better default compressor.

What changed:

Postgres 19 advice: Keep Elizabeth’s modeling advice. Prefer structured columns for hot lookup data when you can. Prefer EXTERNAL when you have already compressed the payload yourself. On a fresh Postgres 19 cluster, LZ4 is what new toasted values get unless you override it.

When you do need a rewrite to reclaim bloat, Postgres 19’s native REPACK is the clean path; CONCURRENTLY keeps the table readable and writable while the new heap is built:

REPACK (CONCURRENTLY, ANALYZE) events;

CONCURRENTLY needs a primary key or index-based replica identity. It is not MVCC-safe in the same narrow sense as TRUNCATE: a concurrent transaction that took a snapshot before the final swap and had not yet touched the table can briefly see it as empty.

BRIN: still tiny, richer opclasses, faster to build

What we wrote: In PostgreSQL BRIN Indexes: Big Data Performance With Minimal Storage (2019, Postgres 11), Jonathan showed BRIN crushing a B-tree on size for append-mostly sensor data. Later posts sharpened when BRIN shines: high correlation with physical order. Random or shuffled layouts need a different shape of index.

What changed:

Postgres 19 advice: Start with Paul’s question: is this column correlated with physical order? If yes, use BRIN. On 14+, prefer multi-minmax when the table is large and not perfectly ordered. Tune pages_per_range to your typical filter width.

On Postgres 17+, BRIN builds on large tables can use parallel workers automatically. Re-run the bake-off on your storage, especially cloud disks, and pick the winner with fresh numbers.

Covering indexes: INCLUDE still helps, and skip scan covers more queries

What we wrote: In Why Covering Indexes in Postgres Are Incredibly Helpful (2018, Postgres 11), Jonathan introduced B-tree INCLUDE columns so queries could satisfy SELECT lists from the index alone. That tradeoff is unchanged. INCLUDE is still how you build a covering index without polluting the sort key.

What changed:

Postgres 19 advice: Still use INCLUDE when you know a hot query’s filter columns and its select list. For your other indexes, check whether skip scan already covers a query before you add another index, especially when the leading column has low cardinality.

Partitioning: still for lifecycle, now smoother to operate

What we wrote: In Partitioning with Native Postgres and pg_partman (2022), Elizabeth Christensen framed partitioning as a scale and lifecycle tool: archive old ranges by detaching, create tomorrow’s child, let pg_partman handle the calendar. That still works.

What changed:

Postgres 19 advice: Keep partitioning for retention and operational boundaries first. For day-to-day rotation, prefer concurrent detach. When you need to coarsen history or split a hot range, Postgres 19 can do it in SQL:

ALTER TABLE events
  MERGE PARTITIONS (events_2024_01, events_2024_02, events_2024_03)
  INTO events_2024_q1;

Plan merge/split for a maintenance window: they take ACCESS EXCLUSIVE on the parent and do not yet offer CONCURRENTLY. Exporting a partitioned table is simpler too: COPY TO runs directly on the root relation and pulls rows from all descendants.

What hasn’t changed

1. Prefer COPY over row-by-row INSERT for bulk loads.
2. Store JSON as jsonb, not text json.
3. Keep hot data out of TOAST when you can.
4. Indexes are a tradeoff, not free.
5. The planner may skip your index. Verify with EXPLAIN (ANALYZE, BUFFERS).
6. B-tree is the default workhorse.
7. BRIN wins on correlated, append-mostly columns.
8. Covering indexes (INCLUDE) enable index-only scans.
9. Partition for lifecycle and cheap drops first.
10. Unique indexes on partitioned tables need the partition key.
11. Use a default partition as a safety net, and drain it.
12. pg_partman still handles the schedule.

The advice did not flip. Postgres got better at delivering it: more resilient loads, faster compression by default, more BRIN shapes, wider B-tree usefulness, quicker disk reads, and smoother partition operations. If you still follow those older posts on Postgres 19, keep the mental model, update the edges of the checklist, and re-test after upgrade with EXPLAIN (ANALYZE, BUFFERS, IO).