Hello
I am reaching out to get your expert guidance on optimizing our production database environment. We are currently running an aggregate-only DHIS2 instance.
While analyzing our disk space footprint via pg_catalog.pg_statio_user_tables, we identified two specific characteristics of our deployment that we would like to optimize:
Systemic Analytics Index Bloat: Across all historical partition years (2011–2026), our index sizes are disproportionately larger than the actual table sizes (consistently maintaining an approximate 1.7:1 to 2:1 index-to-data ratio). For example, analytics_2025 occupies 14 GB of data but carries 25 GB of indexes.
Co-located Databases: Our Postgres instance currently manages both hmis23 (282 GB) and an older hmis database (217 GB).
Below is the exact output of our top table sizes for your review:
Plaintext
table_name total_size table_size index_size
datavalue 41 GB 24 GB 17 GB
analytics_2025 39 GB 14 GB 25 GB
analytics_2024 35 GB 13 GB 22 GB
analytics_2023 29 GB 11 GB 19 GB
analytics_2021 28 GB 10 GB 18 GB
analytics_2020 27 GB 10034 MB 17 GB
analytics_2022 26 GB 9681 MB 17 GB
analytics_2026 15 GB 5498 MB 9585 MB
Based on your experience handling large-scale deployments, we would highly appreciate your support on the following points:
Analytics Execution Strategy: Given that our analytics_* tables carry significant index overhead, what are the current best practices for configuring the DHIS2 Analytics Export job frequency to minimize Postgres page bloat?
Safe Reindexing Workflows: Do you recommend automating a standard REINDEX TABLE CONCURRENTLY routine for older, static historical partitions (e.g., 2011–2024), or should we rely strictly on the regular DHIS2 analytics regeneration process to drop and rebuild these?
Instance Separation: To optimize resource allocation (RAM/IOPS), do you advise completely decoupling the legacy 217 GB hmis database into a standalone Postgres cluster rather than keeping it co-located with our active instance?
Thank you for your time and continuous support.
Best Regards,
Gerald Thomas