Database size reduction: Index vs Table size - Restore and rebuild

3
Size reduction Our app has a steady growing database and  we frequently have to decrease the disk size by a restore and rebuild, which only temporarily helps. If we don't restore and backup our app performance serious suffers. On the mendix support page about database size reduction the following is written: The only reason to use either the VACUUM FULL or restore and backup option is to physically reclaim lost space of a table (records of the same entity) that was once very large but has drastically been reduced in size (deleted records) and which you do no longer expect to grow back to (nearly) its previous size in the future. As in that case, and in that case only, leaving the deleted records in place to be reused makes no sense anymore. source: https://docs.mendix.com/howtogeneral/support/database-maintenance-size-reduction As i started, our database grows steady in size and we frequently do a restore and backup to reclaim database size. However, on the mendix support page is explained only to restore and backup if the database is not expected to grow to it's previous size.  It's seems that our actions contradicts with the support page. Can this have any consequences? Table/index size ration Furthermore, we can clearly notice that after a restore and backup our database size steady grows again. Just after restore and back up the index/table ratio is close to 1, but as the size increases again the increase in index size is four times larger than the increase in table size (see figure). Can this strong increase in index size have any effects? What could be the cause of this relative big increase in index size?
asked
2 answers
3

Jelle,

I would argue that the statement on not using vacuum or restore when the expectation is that the table will grow to the same size in the future is incorrect. From the behaviour of your database including the size reduction after the restore, you are experiencing bloat. This is common in a Multi-Version Concurrency Control (MVCC) database like postgres. The "dead space" that is created will not be reclaimed by the database without tools like auto vacuum or restore database actions. See for example https://www.citusdata.com/blog/2016/11/04/autovacuum-not-the-enemy/. 

Your app is probably creating and deleting a lot of data, causing bloat in the tables and accompanying indexes. This will, over time, impact performance of queries.

Why the index size is increasing more rapidly than the table size could be caused by the use of vacuum full, this could increase bloating on the indexes to get worse over time, see https://wiki.postgresql.org/wiki/VACUUM_FULL. Don't know if Mendix is using this option, but it might be a cause.

Currently the actions that you are performing are the only ones that you have available as you can't perform maintenance on the db in another way in the Mendix cloud.

Hope this is answering the questions you have.

answered
1

In your case,  the graph shows that space used by indexes is growing out of proportion to the space used by data itself. So the linked documentation does not really fit the problem. Well, not the data part. Doing a backup and restore has the effect of creating indexes again, so that will still help of course.

Can you give a high level description of the behavior of the application? Does it just add data, or does it add and remove (cycle) data. What kind of data is it? Is there an index on a time/date field?

answered