Re: How to detect if a postgresql gin index is bloated

Hi

Let me clarify this for you. Although pgstattuple is a widely used
extension included in PostgreSQL’s source code,

It is not precompiled with the database installation. To use it, you first
need to build it from the source code in the 'contrib' directory.

After building the extension, start your PostgreSQL server and connect to
the database using psql.

For your case:
postgres=# select * from pgstattuple('employees_systems_access');
ERROR: "employees_systems_access" (gin index) is not supported; I
misprinted table_name with gindex_name.

this is true pgstattuple support table and standard indexes

I consider analyzing the table and index sizes directly,
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.

And used pgstattuple for tables and standard indexes.
pgstatginindex specifically for GIN indexes to get relevant statistics and
detect bloat.

Then, if needed, use re-index or vaccum as per requirement.

Make sure the pgstattuple extension is installed and properly configured
in your PostgreSQL database before using these functions.

I recently build the source code from 17 beta. And attaching the extension
version 1.5 file as well.

[image: image.png]

On Fri, Jul 26, 2024 at 6:11 PM Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
wrote:

>
>
> On Fri, Jul 26, 2024 at 6:49 AM khan Affan <bawag773(at)gmail(dot)com> wrote:
>
>> 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
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
>