HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Do Postgres GIN indexes need to be rebuilt periodically?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresneedginindexesperiodicallyrebuilt

Problem

Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.

Solution

Empirically, I've found GIN indexes need to be re-indexed after I've inserted a large number of rows into the table.

I had a table with 15 million rows that had a GIN index that let me do a keyword search in under 3 seconds. After I inserted another million rows, that same search took 30 seconds. I waited for a day after my inserts, but the search was still slow, so I manually ran REINDEX on my GIN index, which took a little over an hour, but afterwards my search was back to 3 seconds.

Context

StackExchange Database Administrators Q#83258, answer score: 3

Revisions (0)

No revisions yet.