Effect of vacuum freeze in Postgresql-CodePudding

I am using postgresql for cloudSQL on GCP.

One table is almost in the process of inserting. (Theoretically more than 10 million per day)

Auto vacuum was performed when the data was about 10 billion.

Also, while auto vacuum was running, other processes could only be used by a single user.

Perhaps it is the effect of vacuum freeze.

What is the exact cause?

And I decided that the execution period would be shorter if auto vacuum was run in a smaller amount and frequently, so I modified the parameter as follows.

autovacuum_freeze_max_age : 2 billion -> 100 million
autovacuum_multixact_freeze_max_age : 2 billion -> 100 million

Are there any parameters that need to be modified to further increase performance?

CodePudding user response：

Yes, those are the correct settings to make anti-wraparound autovacuum run more often, so that individual runs are smaller.

You can further improve matters for this table if you set vacuum_freeze_min_age to 0, so that all rows are frozen when autovacuum runs.

Note that you can set these parameters on a single table like this:

ALTER TABLE tab SET (
   autovacuum_freeze_max_age = 100000000,
   autovacuum_multixact_freeze_max_age = 100000000,
   vacuum_freeze_min_age = 0
);

That is better, because other tables in your database may be served better with the default settings for these parameters.

Note that an easy alternative to all this is to upgrade to PostgreSQL v13 or better, where autovacuum will run more often on insert-only tables for exactly this reason.

As always with VACUUM, setting maintenance_work_mem high will improve performance.