PostgreSQL 13 cut DHIS2 database size in half

I have the last 2 days tested various DHIS2 databases running under the new PostgreSQL 13 (Win10-64bits), and on a new/fast workstation. These are the key findings:
1.
The new, more efficient btree-indexing in pg13 yields an approximately 50% reduction in the size of the DHIS2 db WITH analytics. That in itself will probably also increase output performance a bit (less disk i/o)
2.
I saw an up to 5x performance boost in running analytics when installing pg13 and tomcat 8.5 on my empty D drive instead of as usual on the C drive together with the Operative System. Both drives are SSD and the D drive is around twice as fast as the C drive, but it seems having the OS on one drive and the DBMS and data on another has major additional benefits.
3.
Despite the major performance boost, it became clear that DHIS2 running analytics were still only utilising 5-10% of the CPU (32 cores) for populating analytics tables and 30-50% doing indexing. Documentation indicates that DHIS2 will scale with the number of cores, but I’ve confirmed with Luciano (core dev) that multi-threading is limited to 7-8 processes (if any of those are large, they could be split - but that will require code changes).

Whether that split will work the same on Linux remains to be seen (please test if you can).

CONCLUSION: I WOULD RECOMMEND MOVING TO PG13 AS SOON AS POSSIBLE

The detailed test results are below.

Regards
Calle, HISP-SA

4 Likes

This is an amazing analysis. :clap:

Thanks!!!

Hi, (this was actually posted last week, but I inadvertently posted it to a derived private thread),

Bob Joliffe just informed me that these results running on Win10 OS are replicated on Linux. Using the Rwanda HMIS, he found that

  • Disk usages is 181GB (pg12) versus 89GB (pg13);
  • Running time for full analytics is 94 minutes (pg12) versus 69 minutes (pg13)

Note also that the flyway issue with pg13 support has been resolved, see JIRA 9797. Flyway 7.04 (pg13 compatible) has been included with 2.33.7 (not yet released), 2.34.2 (released), 2.35.1 (not yet released), and 2.36 (trunk). That is just a formality, though - I have not seen a single problem with flyway upgrades from e.g. 2.31.9 to 2.33.6, or from 2.33.6 to 2.34.1 to 2.35.0.

Best regards
Calle