Global unique index a different approach
summary
Patch implements a global unique index by keeping one index per partition but building a single long-lived spool and doing a global tape/merge sort to verify uniqueness; index uses btree with new relkind 'g'. It uses existing external sorting when maintenance_work_mem 64MB fills. Benchmarks show 6x slower on inserts for six partitions (26k → 156k ms) and ~35% slower index create for 6M rows, while selects on an 800M rows 1200 partitions test are orders of magnitude faster. Attach and concurrent inserts suffer locking and deadlock risks; workarounds proposed (per-partition min/max bounds, attach-concurrently style flow) but feature remains experimental.
AI-written summary · about
chapters (12)
- 0:00Intro
- 1:35Few Words About Me: Cary
- 4:13Global Index - Benefit
- 8:43Global Unique Index - Syntax
- 10:43Global Unique Index - New Relkind
- 13:22How Global Unique Index
- 21:47Benchmark Number - SELECT Only Using Unique Column as Lookup Key
- 23:15Benchmark Number - SELECT + UPDATE
- 24:30Performance Impact Summary
- 31:32Global Unique Index - Possible Solution to ATTACH Lock Problems
- 33:41Global Unique Index - Summary
- 35:15Related Links
related talks
51mA Quantitive Look at Partitions
27mLeveraging table partitioning for query performance and data archiving
50mPerformance Improvements of Partitioning: Past and Future
24mFun With UUIDs
49mFlexible Indexing with Postgres
26mMy Postgres partitioning cookbook
38mFun with UUIDs
45mA Deep Dive into PostgreSQL Indexing