PostgreSQL database management: regular vacuum

Vacuum is an important task in PostgreSQL database management.

The reason is as follows:

1. Release the disk space occupied by update/delete rows.

2. Updates the statistics used in the PostgreSQL query plan.

3. This prevents old data from being lost due to the reset of the transaction ID.

The first reason is that the PostgreSQL data insertion, update, and deletion operations are not actually put into the database space. If the space is released from time to time, the query speed will be greatly reduced due to too much data.

The second reason is that when PostgreSQL performs query processing, the execution plan will be determined based on the statistical data in order to improve the query speed. If the query is not updated in time, the query effect may be less than expected.

The third reason is that every transaction in PostgreSQL generates a transaction ID, but this number is limited. when the transaction ID reaches the maximum value, the cycle starts from the minimum value again. in this way, if the previous data is not released in time, the old data will be lost due to the loss of the transaction ID.

In other words, vacuum operations can be performed manually and automatically. If there are dedicated database maintenance personnel, they can be performed in a timely manner. However, many systems need to rely on automatic vacuum to save maintenance costs.

Although regular vacuum is a weakness of postgresql, after version 8.3, you can assign this task to automatic vacuum.

To make the automatic vacuum valid, you must set the track_counts parameter to true. For specific settings, refer to the official documentation.

It is better to write a shell to automatically execute vacuum on a regular basis.

 

Statements executed by Huang Hai in Windows:

vacuumdb -U postgres -d lxyy_db --analyze

In crontab, the user who runs the shell is the database superuser. However, if you create a. pgpass authentication file under the Super User's home, you can regularly execute batch.

 

1. Summary of vacuumdb is a tool for clearing PostgreSQL databases. In fact, vaccumdb is an external packaging of the SQL command vacuum.

2. vacuumdb useful parameters

-A/-- all vacuum all databases

-D dbname: only vacuum dbname indicates the database.

-F/-- full execute full vacuum

-T table: only vacuum table data table

-Z/-- analyze calculate statistics for use by the optimizer

3. Actual Maintenance

Vacuumdb-D yourdbname-F-z-V is still very effective. One of the tables suddenly changed from 3G to 600 m. We haven't cleared the garbage for a long time, so we need to clear it every day: Add the following in crontab:

02 2 *** Postgres vacuumdb-D digibot-F-z-V>/tmp/vacuumdb. Log

Clean up at 02:02 every day. Haha