The pgstattuple function provides the following useful columns:
table_len: The length of the table.
tuple_count: The number of tuples.
tuple_len: The length of the tuples.
dead_tuple_count: The number of dead tuples.
dead_tuple_len: The length of dead tuples.
free_space: The amount of free space.
Example
How you might use this function:
SELECT*FROM pgstattuple('gin_index_name');
Interpreting Results
If the dead_tuple_count is high relative to the tuple_count, this indicates bloat.
If free_space is a significant portion of table_len, the index may be bloated.
Using pg_trgm for Trigram Indexes
If you're using a GIN index with the pg_trgm extension (for trigram indexes), you might also consider analyzing the table and index sizes directly:
SELECT pg_size_pretty(pg_relation_size('table_name')) AS table_size, pg_size_pretty(pg_total_relation_size('table_name')) AS total_size, pg_size_pretty(pg_relation_size('gin_index_name')) AS index_size;
Vacuum and Reindex
If you determine that your GIN index is bloated, you can use VACUUM and REINDEX to reclaim space:
VACUUM ANALYZE your_table_name;
REINDEX INDEX your_gin_index_name;
By following these steps, you should be able to detect and address bloat in your PostgreSQL GIN indexes effectively.
Have you tried using that pgstattuple function with a GIN index?
postgres=# select * from pgstattuple('employees_systems_access'); ERROR: "employees_systems_access" (gin index) is not supported
There is a function specifically for them and it does not return the same info
postgres=# select * from pgstatginindex('employees_systems_access'); -[ RECORD 1 ]--+-- version | 2 pending_pages | 0 pending_tuples | 0
