A deep retrospective revisits years of Crunchy Data's Postgres advice on COPY, TOAST, BRIN indexes, covering indexes, and partitioning, mapping which Postgres versions (14 through the upcoming 19) changed the underlying mechanics while the core recommendations largely held. Highlights for Postgres 19 (currently in beta) include SIMD-accelerated COPY parsing, ON_ERROR SET_NULL, LZ4 becoming the default TOAST compression algorithm, native REPACK CONCURRENTLY, native MERGE/SPLIT PARTITION syntax, COPY TO working directly on partitioned parents, autoscaling async I/O workers, parallel autovacuum workers, and JIT being turned off by default after being on since version 12. The piece closes with a checklist of advice that remains unchanged across all these releases.
Table of contents
Async I/O: faster scans and vacuum on modern storageLoading data: COPY is still king, and now more resilientTOAST: same mechanism, faster default compressionBRIN: still tiny, richer opclasses, faster to buildCovering indexes: INCLUDE still helps, and skip scan covers more queriesPartitioning: still for lifecycle, now smoother to operateWhat hasn’t changedQuestions this post answers
What is the default TOAST compression algorithm in Postgres 19?
LZ4 becomes the default TOAST compression method in Postgres 19, replacing pglz, via the default_toast_compression setting. LZ4 compresses and decompresses much faster than pglz with a similar compression ratio and still fails fast on incompressible data. After upgrading from an older version, existing toast values keep their original pglz compression until rewritten; only new writes use LZ4. Track how default_toast_compression and other Postgres 19 defaults affect your upgrade plan on daily.dev.
Is JIT compilation on or off by default in Postgres 19?
JIT is off by default in Postgres 19, reversing the setting that had been on since Postgres 12. The change was made because the old cost model for deciding when to JIT-compile was unreliable. Large analytical or parallel scans that previously compiled at runtime will no longer do so unless jit is explicitly re-enabled, so plans should be rechecked after upgrading. Developers upgrading Postgres majors can watch for defaults flips like this one on daily.dev.
How do you merge or split table partitions natively in Postgres 19?
Postgres 19 adds native ALTER TABLE ... MERGE PARTITIONS and SPLIT PARTITION syntax to combine or divide partitions directly in SQL, without manually creating tables and moving data. Both operations take an ACCESS EXCLUSIVE lock on the parent for the full duration of the physical tuple copy and do not yet support CONCURRENTLY, so they should be scheduled during quieter maintenance windows rather than run on high-throughput tables. Engineers planning partition maintenance can follow new Postgres partitioning features on daily.dev.
133.2K Impressions3 Comments