Database Maintenance For PostgreSQL & VACUUM Command

Hello everyone,
Today, I would like to talk about database maintenance for PostgreSQL. In my previous blog, I explained that how we can install PostgreSQL. The best tool to use for physical maintenance which is related to disk usage, and analytical maintenance which is related to increasing performance, is VACUUM SQL command or its equivalent command-line vacuumdb. This command removes all temporary data that other processes may leave and any leftover data from rollbacks. That's why it's a good tool for disk usage. Also, it analyses data and activities in database to improve performance, especially when you want to update/add/delete a large number of data in database.That's why it's a good tool for increasing performance too.

NOTE:
Be aware that vacuum command will pause any query on the table that vacuum command is running until it completes its job on that table. Therefore, it's a good idea to run this tool at midnight when there is no or less database activities or during maintenance window when there is no any connection to database.

VACUUM SQL Syntax:

vacuum [Full] [verbose] [analyze] [table [(column,...)]];

vacuum command without any keywords will be run on all tables in connected database. If you want to run vacuum on a specific table or even on a specific column, you can use table/column keywords.

"analyze" keyword is used for query optimizer and it examines the allocation of data on each table/s and column/s.
Let's see an example. Figure 1 and 2 show the usage of vacuum sql statement with different keywords. I connect to "library" database and going to work with either all tables or only "books" table:

                                                                           Figure 1

                                                                             Figure 2

 

VACUUMDB Command Line Syntax:

We can do the same thing in command line with more options and flexibilities. Figure 3 shows all options and syntax with this command:

                                                                              Figure 3

It's pretty much self-explanatory. So, let's try some examples: (Figure 4)

                                                                              Figure 4

With PostgreSQL command-line, we can write a script and schedule it to run at midnight with cron command. It runs at 3 AM every night. Figure 5 and 6:

                                                                               Figure 5

                                                                              Figure 6

and here is the output (Figure 7):

                                                                            Figure 7

Hope you enjoyed.
Khosro Taraghi