Strategy for Managing PostgreSQL Table Bloat in DHIS2

Support - Assistance technique
1

Hi DHIS2 community,

I’m observing significant table bloat in a DHIS2 PostgreSQL database, especially in audit and event-related tables. This is impacting storage usage and other things.

1. Dead tuple / bloat analysis

Frompg_stat_user_tables, I observe high dead tuple ratios and low autovacuum activity:

SELECT
relname,
n_live_tup,
n_dead_tup,
round(
100.0 * n_dead_tup /
NULLIF(n_live_tup + n_dead_tup, 0),
2
) AS dead_pct,
last_autovacuum,
last_autoanalyze,
autovacuum_count
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY n_dead_tup DESC;

Results (sample):

Table Live tuples Dead tuples Dead % Last autovacuum
trackedentitydatavalueaudit 2,585,517 2,709,389 51.17% ( missing)
event 83,916 23,724 22.04% (missing)
trackedentityattributevalue 170,549 17,557 9.33% (missing)

2. Table size overview

SELECT
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_only_size,
pg_size_pretty(pg_indexes_size(relid)) AS indexes_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Results (top tables):

  • trackedentitydatavalueaudit → 44 GB (31 GB table + 13 GB indexes)
  • trackedentityaudit → 33 GB
  • fileresource → 23 GB
  • event → 21 GB

3. Questions

Given the observations above, I would appreciate guidance from DHIS2/PostgreSQL experts on best practices for managing table bloat in production environments, especially in DHIS2 systems.

In particular, I’m looking for advice on:

  • Safe and recommended approaches to VACUUM (FULL) in DHIS2 environments (downtime requirements, risks, scheduling, and alternatives)
  • Whether VACUUM (FULL) is generally discouraged in DHIS2, and what is typically used instead
  • Best practices for controlling and preventing bloat in high-write tables (especially audit and event-related tables)

I’m also open to any additional recommendations or operational experience from DHIS2 experts, even if it goes beyond VACUUM strategies.

2

Could you share more about how you’ve configured the auditing in your system? Specifically, the dhis.conf settings (audit.tracker) and (changelog.aggregate).

Do you have the ‘Enable tracked entity instance audit log’ set for all the TEIs in your tracker programs?

Also, are you doing bulk imports into your system? If yes, do you have the setting skipAudit set to true.

Would a manual clearing of the audit table directly help, see: Can I clear out data in audit table?

You might want to configure the file limit in your system: File size limit on uploading files through the fileResources API

You might want to check the “File resource clean up” job in the Scheduler app and see if the size decreases. Are all the files uploaded assigned, please see, How can I get a list of fileResource IDs? - #2 by Stian

I don’t have specific answers to your questions about VACUUM and autovacuum so I will leave this for the experts on this. Thanks!

One more thing please, I think it will help greatly if you’d include the DHIS2 version of your instance since many of these features have been improved over the years. Thanks!