I have 5 GIN indexes on a PG 9.3 table containing about 50 million records. Each index covers a "group" of common records like billing address, shipping address, contact names, etc.
When first created, the indexes works miracles in speeding up the full text search of these fields. However, I'm running into some serious maintenance headaches.
After I insert a few thousand new records, the indexes seem to have no effect. A query that might normally take 1 second now takes 5 minutes.
If I drop and recreate the indexes, then performance returns to normal, but this causes a couple hours of downtime, even if I recreate indexes concurrently.
Why are these GIN indexes becoming unusable after a large number of inserts? Is this a known limitation or is there anything I can do to fix this?
Chris