Issue

For a specific PostgreSQL table, the total size of its indexes is significantly larger than the table size itself. For example, a table of approximately 11 GB may have indexes totaling 23 GB, which can give the impression of inefficient or excessive storage usage. This article explains why this might be expected.

Environment

Applications hosted in Mendix Cloud

Cause

A PostgreSQL table can have multiple indexes, and each index is a separate data structure that stores its own copy of the indexed column values along with pointers to the table rows. The presence of multiple indexes is determined by the domain model and how it was designed

When multiple indexes exist on a table, their combined size can exceed the table size. This situation commonly occurs when indexes are created on several columns to support different query patterns. The apparent discrepancy is more noticeable when viewing the database table vs. index size metric, where table sizes often dominate. 

Large index sizes do not automatically indicate bloat or a configuration issue. They often reflect the schema design from the domain model and the read performance requirements of the application.

Solution / Workaround

Confirm whether multiple indexes exist and determine if each one is required or if the domain model can be optimized or adjusted.

Restoring a backup does not reduce index size when the indexes accurately reflect the schema’s requirements defined in the domain model. However, it can reduce index size only when index bloat is present.

Additional information

Mendix documentation:

• Configuring a Domain Model
• Operation Metrics | Database Table vs. Index Size