I have a small table with 2 GIN indexes ( gin_idx1 and gin_idx2). During our job runs, the table size grows from 50MB to about 1 GB - high DML activity. Once the job finishes, auto vacuum clears the table size and gin_idx1 size. But gin_idx2 just keeps on growing. Using the sql to find index usage -Unused Indexes I see gin_idx2 is not used. We are on PG 12.4 and as of now the table size is 50MB, gin_idx1 is 20MB but gin_idx2 is 20GB. I know REINDEX will fix it but why is auto vacuum able to take care of 1 gin index but not the other. Is this some kind of bug or an expected behavior? Thoughts? Explanation?
2 Answers
The expected behavior is that autovacuum does not shrink tables and indexes, so your gin_idx2 is behaving as expected.
If the other objects shrink, that must be because the DELETEs that you are running happen to empty the blocks at the end of the relation. In that case autovacuum will truncate those terminal empty pages.
Autovac never shrinks an index, not even if all tuples are deleted from its table. So it must not be the case that the small index is growing and then not getting shrunk; but rather is prevented from getting large in the first place by space reuse.
With GIN indexes (and unlike Btree indexes) once a value is inserted into the index, it never gets removed, even if no living tuples possess that value anymore.
So if one GIN index is over something with a smallish set of possible values (say, words in the English language derived from a tsvector structure) and the other is over a very large set of possible values (say, a bigint[] which contains primary key values for some other table which never get reused once deleted) then the first one will enjoy efficient space reuse while the 2nd one will have a "permanent" (to the extent you never run out of 64-bit integers) leak.
Related