PostgreSQL Table Maintenance

Periodic maintenance is required for optimal database performance. When you update a record in PostgreSQL, internally, the original record remains there deactivated, until you run a VACUUM. The same thing occurs, when deleting a record. The unused, unnecessary data are called bloat. If bloat exceeds live data 5-7 times, the database performance drops dramatically.

PostgreSQL includes autovacuum - it runs VACUUM on bloated tables as necessary. In its default configuration, it usually just delays the problem and you need to run VACUUM FULL after some time. On the other hand, it can be tuned to be more aggressive, but that comes with a performance hit. You can customize autovacuum settings for each table.

For smaller databases (with table sizes up to hundreds of megabytes), we can set up a cron job, which will run full vacuum. That makes the database maintenance almost effortless. For larger projects, we would recommend running VACUUM during a maintenance window, since it locks tables and therefore your application will not run correctly.

Table maintenance is done using VACUUM FULL and REINDEX commands. You can run them yourself as needed. Naturally, you can also ask our technical support to run them for you.

We periodically monitor the database state and contact the customer, if maintenance is required.