One of the primary responsibilities of the DBA is to create regular backups. However, maintenance of the database is not limited to this.
The mechanisms that enable a relational database to run fast are based on a complex storage system that degrades over time. It leads to a general database slowdown.
What does PostgreSQL Database Maintenance Consist of?
The following commands will help keep the PostgreSQL database in good shape:
These commands will be discussed in more detail below.
When building a query, the query optimizer relies on data distribution statistics. As data changes, these statistics become outdated.
The ANALYZE command updates the data statistics. This command does not scan all the data but makes a random statistically significant sample, so this command is not particularly time consuming.
It makes sense to run ANALYZE manually either after bulk-adding/updating/deleting, for example, after import or synchronization. Or after running a VACUUM command. It is considered good practice to combine VACUUM with ANALYZE.
Running ANALYZE on a single column
For a separate table, with the VERBOSE option to track the progress of the command
ANALYZE VERBOSE [table-name]
For the entire database, type
When executing DELETE and UPDATE commands, PostgreSQL does not physically delete the data but only marks it as deleted. It leads to a disproportionate increase in the database size because the deleted data is still on disk. The VACUUM command solves this problem.
There are two types of this command:
- VACUUM – obsolete data in the database is marked as writable. This command will not reduce the size of the database file but allows you to reuse the space.
- VACUUM FULL – the database file is completely overwritten. It results in reducing the size of the database as small as possible. However, this command takes a lot of time and completely blocks the table.
Running VACUUM on a specific table
Running VACUUM FULL with the VERBOSE option to track the progress of the cleanup. Please note that this command blocks the database
Running for the entire database
When running VACUUM, keep in mind that this command places a significant load on the database.
Over time, blank or nearly blank pages form in the indexes. To optimize the index and reduce the space it takes up, you can run a REINDEX command from time to time. Also, REINDEX can fix a broken index.
The REINDEX command completely blocks read access to the table. As a rule, it is executed manually if there is suspicion of index corruption or as one of the measures to increase the speed of queries.
REINDEX can be applied both to the entire database and to a table or a specific index. To rebuild the index named my_index, use the following command
REINDEX INDEX my_index;
To rebuild all indexes in a table
REINDEX TABLE my_table;
And to rebuild all indexes in the database
REINDEX DATABASE broken_db;
About the AUTOVACUUM Daemon
PostgreSQL has an AUTOVACUUM Daemon that automatically executes a VACUUM(ANALYZE) command when the number of dead records exceeds a threshold. More details about the execution principles are available here.
However, relying on AUTOVACUUM alone may not be the best solution. The command places a significant load on the hard drive. If VACUUM starts at the time of peak load on the database, it will lead to significant delays.
What and When to Run?
You can run VACUUM and ANALYZE commands once a day at the time of minimum load on the server, for example, at night. It will prevent them from being automatically executed by the AUTOVACUUM Daemon during peak loads.
It makes sense to run REINDEX command manually when there are index issues.
How to Automate VACUUM ANALYZE
All the commands described above are SQL queries that can be run using the psql utility from the console. In its simplest form, this command looks like this
psql -u postgres -p[password] -d [my-database] -q ‘VACUUM(ANALYZE)’
In Linux, you can schedule a regular run of the console command via cron
00 00 * * * psql -u postgres -p[password] -d [my-database] -q ‘VACUUM(ANALYZE)’
In Windows, this can be done using the Windows Schedule, as follows:
Press win+r, type taskschd.msc
Select Create Task
From the Triggers tab, add execution schedule
From the Action tab, add the following data into the program field, specify psql in add argument field:
-u postgres -p[password] -d [my-database] -q ‘VACUUM(ANALYZE)’
How to Automate Maintenance Jobs with SqlBak
The SqlBak service is focused primarily on the regular creation of backups. However, it also allows you to automate database maintenance.
Compared to automation through standard operating system utilities, SqlBak has several advantages:
- Email notifications
- Job history
- Running .sh or .bat scripts
To automate PostgreSQL maintenance using SqlBak follow these steps:
Install a thin client on the server and connect the database. Connection details are available here.
Create a maintenance job.
Add SQL script.
Specify your email (optional) to receive job execution updates.
Start the job.