Handling systemic index bloat in aggregate-only DHIS2 instance

Support - Assistance technique
,
1

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

2

Hi @Gerald_Thomas
Thank you for posting this to the community which means the discussion here will be shared for the benefit of everyone. :folded_hands:

I see @jason created a bug issue that seems to revolve around the index bloat problem, Allow skipping the index on the analytics id column for instances not using continuous analytics table updates.

I have a strong feeling that you’d benefit greatly from this DAC2026 session: “How to Set Up a Dedicated Analytics Database for DHIS2 using Apache Doris and ClickHouse”:

DHIS2 version 42 features the ability to use a dedicated analytics database, optimized for high query speed and performance, and reduced analytics table update runtime. It allows for interactive visualizations and dashboards for large DHIS2 databases.
In this session, we will learn about the new analytics architecture, how to configure DHIS2 to set it up, and what to expect from the new analytics database solution.

Please watch the video… looking forward to your follow-up comments/questions!