Repack Concurrently | Scaling Postgres 414
Get the full intelligence
Search transcripts, export clips, track mentions, and explore all topics from “Repack Concurrently | Scaling Postgres 414” inside PodZeus.
PostgreSQL 19 is poised to bring transformative changes to table maintenance and performance tuning, with the long-anticipated concurrent repack feature set to eliminate disruptive access exclusive locks during table compaction. The new approach uses a shared update exclusive lock, replication slots, and background logical decoding to safely copy tables while allowing continuous application access—only requiring a brief exclusive lock at the final swap. This could make bloat reduction accessible to far more teams. Beyond repack, the episode explores several advanced topics: using CTIDs for 30x faster batch processing on static data, workarounds for global uniqueness across partitioned tables via deduplication triggers, and performance pitfalls in upserts that silently lock rows and consume transaction IDs. A deep dive into PostgreSQL's resistance to query hints reveals a strategic shift toward out-of-band plan stabilization via pg_plan_advice, aiming to preserve planner integrity while ensuring plan stability across upgrades. Meanwhile, async IO improvements in 19 promise dynamic worker scaling and better I/O tracking, signaling a major leap in PostgreSQL’s ability to handle high-throughput workloads.
Use CTIDs for 30x faster batch processing on static data by leveraging physical row order instead of UUIDs
Concurrent repack in Postgres 19 will allow table compaction without access exclusive locks, using replication slots and background logical decoding
Avoid upserts with ON CONFLICT UPDATE if you don’t need the update—use CTEs with INSERT DO NOTHING to prevent unnecessary row locks and transaction ID assignment
Global uniqueness across partitioned tables requires triggers or dedicated deduplication tables, as Postgres doesn’t support global unique indexes
Postgres 19’s pg_plan_advice enables out-of-band plan stabilization without inline hints, preserving planner integrity while ensuring upgrade stability
…and 3 more takeaways available in PodZeus
Postgres 19's Concurrent Repack: A Game-Changer for Table Compaction
“When you repack concurrently, it no longer acquires an access exclusive lock while the new copy of the table is being created. It creates this copy under a shared update exclusive lock which is basically the same as vacuum...”
CTID-Based Processing for 30x Speedups on Static Data
“Using this solution was essentially 30 times faster than his initial implementation. And he says also because he's using a CTID-based processing approach, it is possible to paralyze it as well.”
Avoiding Hidden Locks in Upserts: The Datadog Case Study
“The problem with the onConflict update is it actually locks the row, even if this update doesn't happen. And they discovered this by looking at the metrics with increased writes happening predominantly due to the wall.”
Enforcing Global Uniqueness Across Partitioned Tables
Since Postgres doesn’t support global unique indexes on partitioned tables, the solution involves triggers or dedicated deduplication tables to prevent duplicates across partitions.
The Rise of pg_plan_advice: Plan Stability Without Hints
Postgres has resisted inline query hints due to maintainability and scalability issues. The new pg_plan_advice extension offers out-of-band plan guidance, allowing users to stabilize plans across upgrades without embedding hints in SQL.
“The problem with the onConflict update is it actually locks the row, even if this update doesn't happen. And they discovered this by looking at the metrics with increased writes happening predominantly due to the wall.”
“Postgres has never had those explicit inline query hints. But he says the answer to that has been the extension pghint plan. So this basically gives you Oracle style hints.”
“You do need accurate statistics and good configuration to make sure the planner is making the correct decision based upon the reality.”
Host
Postgres 19
other
PG Repack
other
TheBuild.com
product
Christoph Pettis
person
pg_plan_advice
other
pg_hint_plan
other
Depeche.com
product
Conor Hallman
person
Datadog
organization
hygo.ca
product
Get the full intelligence
Search transcripts, export clips, track mentions, and explore all topics from “Repack Concurrently | Scaling Postgres 414” inside PodZeus.
Start discovering podcast insights today
Start with a 7-day trial and explore a growing catalog of popular podcasts. No credit card required.
No credit card required • 7-day trial • Cancel anytime