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

Much of the foundational guidance on PostgreSQL architecture remains relevant, even as the database enters the Postgres 19 era. Historical advice, often benchmarked against Postgres 10 or 11, continues to hold true for core operations because the fundamental design principles—how data enters the system, resides on disk, and is retrieved—remain consistent. While the functionality described herein is based on current betas and subject to change before General Availability, this section frames the evolution of your operational playbook across four primary buckets: load, storage, indexes, and partitioning.

The transition to modern PostgreSQL releases brings significant shifts in performance and resiliency. Key advancements include:

• Async I/O: Expanded infrastructure that allows backends to queue multiple disk reads, significantly improving throughput on latency-bound storage.
• COPY Resiliency: Enhanced ingestion pipelines featuring improved error handling, SIMD-accelerated parsing, and direct support for partitioned targets.
• Compression Defaults: A transition to LZ4 as the default TOAST compression, offering superior speed and efficiency over legacy pglz.
• Index and Partitioning Efficiency: The introduction of richer BRIN index opclasses and "skip scan" capabilities, paired with more fluid operations for partition management.

Engineers should view these updates as increased headroom for established architectural patterns rather than a shift in core logic. For instance, while COPY remains the standard for bulk ingestion, the addition of ON_ERROR configurations and REJECT_LIMIT parameters allows for more robust ETL pipelines without discarding entire datasets due to minor formatting irregularities. Similarly, while TOAST remains the mechanism for handling oversized attributes, the shift to LZ4 necessitates a review of storage strategies for hot, large-object workloads.

This section explores how to integrate these specific Postgres 19 features into your existing workflows, ensuring that your implementation benefits from the latest executor and storage optimizations while maintaining the structural integrity of your database design.

The 2019 benchmark of Postgres 11 established a critical performance principle: parallel sequential scans can outperform BRIN indexes. Because BRIN relies on heap-level range summaries, a parallel executor utilizing four workers to scan the heap can often retrieve data faster than an index lookup, which may suffer from high bitmap heap scan overhead. Engineers should treat indexes as a tradeoff against the raw throughput of the parallel executor.

Postgres 18 significantly shifted this balance by introducing asynchronous I/O. This subsystem allows backends to queue multiple disk reads simultaneously rather than waiting for individual blocks to return. This reduces latency bottlenecks for sequential scans, bitmap heap scans, and vacuum operations. Benchmarks indicate performance gains of up to ~3x on cold, latency-bound cloud storage. The default configuration, io_method = worker, provides this capability out-of-the-box, while Linux 5.1+ systems can opt for io_method = io_uring for lower overhead.

Postgres 19 expands this functionality with several operational improvements:

• Autoscaling I/O: New parameters io_min_workers and io_max_workers allow the I/O subsystem to dynamically adjust to workload demand.
• Maintenance Concurrency: Parallel autovacuum workers are now available via autovacuum_max_parallel_workers, allowing maintenance to fan out across large tables.
• Observability: EXPLAIN (ANALYZE, IO) provides visibility into how the async subsystem manages read-ahead and queueing.

With JIT compilation now disabled by default, the executor's cost model is more predictable, though analytical queries previously relying on JIT may require manual re-enablement. Our updated guidance is to prioritize indexes for high-selectivity lookups, but to use EXPLAIN (ANALYZE, BUFFERS, IO) to re-test BRIN-vs-parallel plans after upgrading. As a baseline, tune effective_io_concurrency and maintenance_io_concurrency, keeping in mind that these defaults have been increased to 16 to accommodate the higher throughput afforded by the async I/O subsystem.

For bulk data loading, the advice from the Postgres 10 era remains valid: prefer COPY over row-by-row INSERT, generate CSV or newline-delimited JSON, pipe it into psql, store JSON as jsonb, and add a GIN index when containment queries are needed. That load path did not need reinventing; it became more resilient.

• Postgres 16: COPY FROM maps a sentinel string to a column DEFAULT.
• Postgres 17: ON_ERROR ignore skips bad type conversions and continues; LOG_VERBOSITY reports skipped rows.
• Postgres 18: REJECT_LIMIT caps tolerated bad rows; LOG_VERBOSITY silent quiets noise; CSV \. handling is clearer.
• Postgres 19: SIMD-accelerated text/CSV parsing, ON_ERROR SET_NULL, multiple header-line skipping, COPY TO emitting JSON or a single JSON array with FORCE_ARRAY, and direct COPY to partitioned tables.

A practical 19-era load for imperfect feeds looks like:

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. Prefer ON_ERROR ignore when a bad cell should drop the whole row, pairing it with REJECT_LIMIT for a hard cap:

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

DEFAULT '__DEFAULT__' means a CSV cell containing that literal triggers the column’s default expression; choose a sentinel that never appears as real data and avoid \N in CSV because readers confuse it with the NULL marker. For one-shot loads into a freshly created or truncated staging table, FREEZE remains the right performance trick to skip a later freeze vacuum. For export, COPY TO STDOUT WITH (FORMAT json) streams NDJSON, while FORMAT json, FORCE_ARRAY returns a single JSON array. Confirm the final GA documentation for edge cases around DEFAULT, ON_ERROR, and REJECT_LIMIT once Postgres 19 ships.

The core TOAST mental model remains unchanged: Postgres manages data exceeding the 8 kB page size by splitting it into smaller, separately stored chunks, typically targeting ~2 kB per toast_tuple_target. Developers should continue to categorize storage strategies as PLAIN, EXTENDED, EXTERNAL, or MAIN. Crucially, updating toasted rows still triggers a rewrite of those specific toast chunks. High-frequency access to large JSON or text blobs remains an anti-pattern; such data is best moved to structured columns or externalized to preserve performance.

The primary evolution is the transition to LZ4 as the default compression algorithm. While pglz served as the historical standard, LZ4 offers significantly higher throughput for compression and decompression with comparable compression ratios, while maintaining the ability to fail fast on incompressible data.

Operational Guidance for Postgres 19:

• Defaults: default_toast_compression now defaults to lz4. New writes automatically adopt this algorithm. Note that existing toast values retain their original compression algorithm (e.g., pglz) after a major version upgrade until the rows are explicitly updated.
• Measurement: Verify compression gains and current state using:

  SELECT pg_column_size(payload) AS stored_bytes, 
         octet_length(payload) AS raw_bytes, 
         pg_column_compression(payload) AS algorithm 
  FROM table_name WHERE length(payload) > 100;

• Optimization: Continue to use EXTERNAL storage for payloads that are already compressed by the application. Only initiate rewrites for storage or CPU gains if the performance impact on hot toasted columns justifies the operational overhead.
• Native REPACK: Use the native REPACK (CONCURRENTLY, ANALYZE) command to manage bloat. This operation requires a primary key or unique index to serve as a replica identity for tracking concurrent WAL updates.

REPACK Considerations:

• Footprint: The process requires approximately 2× the disk space of the target relation during the rewrite.
• Constraints: REPACK CONCURRENTLY does not apply to partitioned parent tables; iterate over individual partitions instead.
• Consistency: The operation is not strictly MVCC-safe in the same manner as TRUNCATE; concurrent transactions may briefly perceive the relation as empty if they hold snapshots taken prior to the final heap swap.
• Limitations: Neither standard nor concurrent REPACK re-compresses existing toast values.

BRIN indexes remain the smallest practical index structure in PostgreSQL for append-mostly workloads. A Postgres 11-era comparison on time-series sensor data measured a 32 kB BRIN against a 214 MB B-tree on the same timestamp column. BRIN stores per-page-range summaries (min/max values) rather than one entry per row, so range queries perform best when physical order matches logical order: the planner skips page ranges whose summaries prove no matching rows exist, then completes the query with a bitmap heap scan.

The heap side of that path changed meaningfully in Postgres 18. Async I/O lets backends queue multiple disk reads instead of waiting on each one, accelerating sequential scans, bitmap heap scans, and vacuum. io_method = worker is on by default; Linux 5.1+ systems can use io_method = io_uring. Postgres 19 adds I/O worker autoscaling (io_min_workers/io_max_workers), improved read-ahead scheduling, and EXPLAIN (ANALYZE, IO) output that exposes the async subsystem's behavior.

For BRIN specifically, Postgres 19 contributes richer opclasses and faster index build times. Parallel query is still present, and the original benchmark lesson still applies: a four-worker parallel sequential scan can beat an index when selectivity is poor. Selectivity, not index size, drives the index choice.

• Re-test BRIN-vs-parallel plans after upgrade with EXPLAIN (ANALYZE, BUFFERS, IO); async I/O changes the heap-path cost balance.
• Tune effective_io_concurrency and maintenance_io_concurrency; defaults rose to 16 in Postgres 18.
• Use BRIN for wide timestamp range filters and B-tree for point lookups.
• Pair BRIN with proper COPY ingest, TOAST modeling, and covering indexes; treat async I/O as headroom, not a substitute for index design.

The Postgres 11 guidance was not wrong, just incomplete. Async I/O narrows the cost gap between BRIN-driven bitmap scans and parallel sequential scans, but physical-order dependence remains. Re-measure with the new defaults; the executor's estimates will tell you which path wins on your storage.