Postgres Core Space Reclamation

6 posts · Apr 2026 – Jul 2026

What this trail covers

Postgres core space reclamation now includes an online REPACK command, similar to pg_squeeze, allowing tables to be rewritten to reclaim space from dead tuples without locking the table for reads and writes. This feature requires logical decoding and has specific compatibility requirements. Additionally, JIT compilation is disabled by default in Postgres 19, and the query planner has improvements like eager aggregation and faster NOT IN clauses. Other changes include LZ4 as the TOAST default, logging of lock waits, faster foreign-key inserts, wider MultiXact offsets, parallel autovacuum, new SQL features, and property graphs.

All PlanetScale trails

Jul 2026What's new in Postgres 19This post details the new `REPACK (CONCURRENTLY)` command in Postgres 19, which allows for online table rewrites to reclaim space from dead tuples. It explains the mechanism, requirements (e.g., `wal_level`, primary key), and caveats (disk space, replication slots, MVCC safety, deadlocks). It also covers the default disabling of JIT compilation and the introduction of eager aggregation and anti-join optimization for the query planner. Minor changes like LZ4 TOAST default, logging of lock waits, and new SQL features are also mentioned.
Jul 2026Every UPDATE Leaves a Ghost: MVCC, Bloat, and VACUUM in PostgreSQLThis post provides a deep dive into PostgreSQL's MVCC implementation, explaining how tuples, transaction IDs (XIDs), and snapshots work together to provide consistent and isolated data views. It details the `xmin` and `xmax` system columns for tracking tuple versions, the mechanics of snapshots using `pg_current_snapshot()`, and how visibility rules are applied. The post also introduces subtransactions and their impact on XIDs, highlighting the underlying mechanisms that lead to bloat and the eventual need for VACUUM.
Jun 2026The only scalable delete in Postgres is DROP TABLEThis post details the performance implications of large `DELETE` operations in PostgreSQL, explaining how MVCC, dead tuples, and replication overhead contribute to performance degradation. It contrasts this with the efficiency of `DROP TABLE` and `TRUNCATE` for bulk data removal, highlighting their immediate space reclamation and lack of dead tuple generation. The post introduces a transactional strategy for one-off large deletes using temporary tables and `TRUNCATE`, and advocates for date-based partitioning to transform ongoing delete workloads into periodic `DROP TABLE` operations on partitions. It also mentions `pg_squeeze` as a tool for tables with existing bloat.
Apr 2026Keeping a Postgres queue healthyThis post details how job queue workloads, characterized by high insert/delete churn, can lead to performance degradation and table bloat in Postgres due to the accumulation of dead tuples. It explains the mechanics of MVCC, dead tuples, and how autovacuum struggles to keep up when write churn outpaces cleanup or when transactions hold locks. The post uses a concrete example of a job queue table and worker process to illustrate how `DELETE` operations mark rows as dead, which are then scanned by both heap and index traversals, incurring overhead. It emphasizes that the problem is not just throughput but also the visibility of dead tuples to active transactions and the impact of competing workloads (like OLAP) on autovacuum's effectiveness.
Advertisement