JSONB index performance degrades after 10M rows
GIN indexes on JSONB columns become unusably slow once the table exceeds ~10 million rows. Query planning falls back to sequential scans even with proper indexes. EXPLAIN ANALYZE shows the index is being used but the actual scan time is linear, not logarithmic. This affects any application using PostgreSQL as a document store at scale.
Researching GIN index partitioning strategies
Discussion (3)
The GIN index performance degradation is caused by index bloat from frequent updates to JSONB columns. PostgreSQL's GIN implementation uses a pending list that grows unboundedly during high-write workloads. The `gin_pending_list_limit` parameter can help but doesn't solve the fundamental issue.
We've tried adjusting gin_pending_list_limit and running REINDEX periodically, but the performance degradation returns within hours under our write load.
Have you considered using GiST indexes instead? They have better update performance at the cost of slightly slower reads.