ARTIFACTORY: How to resolve/troubleshoot growing DB caused by indexed_archives_entries

Increasing DB disk usage can be frustrating and alarming. The best way to start the troubleshooting is by doing the following:
        -  Enable pgstats
        -  Run the following:

//DB table and index sizes
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS
table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes;
 

// will show dead tuples and last time statistics ran
select
schemaname,
relname,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
from
pg_stat_all_tables
where
schemaname = 'public'
order by
n_dead_tup desc;

From the output, we can get an idea what is actually occupying the space. For example, the output table looks like the following:


- If the indexed_archives_entries is occupying the most space the resolution is as follows:

1. If you do not use archive searching, you can disable the archive indexing. You can use the following link to achieve that.
2. If you don’t disable the table, during a maintenance window you can run a vacuum full on the table or whole DB, followed by reindexing the table. 

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:

    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP { AUTO | ON | OFF }
    PROCESS_TOAST [ boolean ]
    TRUNCATE [ boolean ]
    PARALLEL integer

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

For example, the output of “vacuum full verbose” should look like the following:

artifactory=> vacuum full verbose;
INFO:  vacuuming "public.access_users"
INFO:  "access_users": found 0 removable, 3 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "public.access_users_custom_data"
INFO:  "access_users_custom_data": found 0 removable, 23 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "public.access_users_groups"
INFO:  "access_users_groups": found 0 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "public.access_groups_custom_data"
...