We started using PGroonga to index objects and documents.

Before enabling PGroonga, the PostgreSQL database size was approximately 200 GB.

After enabling PGroonga, the database size rapidly increased to about 1 TB before the database eventually crashed. We restored the database from backup.

After restoration, the database size is now around 500 GB and continues to increase every day.

PGroonga is installed in the schema indexation.

We currently use two PGroonga indexes on the following tables:

• FTS_SEARCH_CHUNK
• FTS_INDEX

Database size:

SELECT pg_size_pretty(pg_database_size(current_database()));

Current result:

541 GB

Filesystem usage inside PGDATA:

523G base
577M pg_wal

We inspected PGroonga internal files directly and found many very large pgrn.* files, including files larger than 50 GB.

Example:

53G  pgrn.xxxxx
51G  pgrn.xxxxx
55G  pgrn.xxxxx
28G  pgrn.xxxxx

There are also many additional files between 600 MB and 1.1 GB.

However, PostgreSQL reports much smaller index sizes.

For example:

SELECT pg_size_pretty(SUM(pg_indexes_size(oid)))
FROM pg_class
WHERE relkind = 'r';

Result:

27 GB

For PGroonga indexes specifically, the reported logical size is also relatively small.

Example:

SELECT pg_size_pretty(
         SUM((elem->>'disk_usage')::bigint)
       ) AS total_bytes
FROM (
    SELECT jsonb_array_elements(
             indexation.pgroonga_command(
               'object_inspect',
               ARRAY[
                 '--table',
                 indexation.pgroonga_table_name(
                   'indexation.idx_pgroonga_fts_index_fts_attributes'
                 )
               ]
             )::jsonb
           ) AS elem
) sub
WHERE elem ? 'disk_usage';

Result:

45 MB

We also tried rebuilding the PGroonga indexes using REINDEX.

Could you help us understand:

1. Why PGroonga creates and retains such large internal files
2. Why the filesystem usage is vastly larger than PostgreSQL reported index sizes
3. Whether these files are expected or indicate corruption/leakage
4. Whether old PGroonga segment files can safely be removed
5. Whether additional maintenance steps are required after REINDEX
6. Whether this behavior is a known issue with PGroonga on PostgreSQL 14