Why Quartz 2.* does not cleanup indexes in Postgres database?

Question

I use Quartz 2.* in cluster mode. Quartz uses Postgres database for clustering feature. Database size grows as the Java application works. The only reason for this growing is that Quartz does not cleanup it's indexes on tables:

quartz.qrtz_triggers
quartz.qrtz_cron_triggers
quartz.qrtz_fired_triggers
quartz.qrtz_scheduler_state

One week of application working increases database by ~500 MiB. I have 5 microservices which use Quartz, so the whole database grows by 5*500 MiB each week.

Then I need to execute manually:

REINDEX TABLE quartz.qrtz_cron_triggers;
REINDEX TABLE quartz.qrtz_fired_triggers;
REINDEX TABLE quartz.qrtz_triggers;
REINDEX TABLE quartz.qrtz_scheduler_state;

And after it the database's size becomes adequate.

Is it normal?

How to configure Quartz/Postgres to cleanup it's stuff atomatically?

Answer 1

While not being that experienced with the quartz / postgres combination, this seems to be an index fragmentation problem.

I.e. index size grows and performance suffers while table rows are being updated or deleted repeatedly. This could be the case if your quartz jobs run in short intervals while creating a corresponding number of entries in your cluster database.

Reindexing the database tables (a common maintenance procedure) refreshes the index and potentially reduces the index size also. I would propose to create a database job running the reindexing commands on a regular basis, from what i found this can be done using either the pg_cron or pgAdmin tool.

You will also find some useful queries regarding index size/usage and index bloat in the PostgreSQL Wiki:

https://wiki.postgresql.org/wiki/Index_Maintenance