1

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?

Hyungsik Jo
  • 146
  • 1
  • 14
  • " other processes could only be used by a single user" It is always the case that any given process can only be servicing a single user. What are you seeing that makes you think this is the case only during vacuuming? – jjanes Dec 01 '21 at 16:54
  • The error phrase is: sqlalchemy.exc.OperationalError: (psycopg2.errors.ProgramLimitExceeded) multixact "members" limit exceeded DETAIL: This command would create a multixact with 2 members, but the remaining space is only enough for 0 members. Is the error caused by modifying the xact id while vacuum is running? – Hyungsik Jo Dec 03 '21 at 05:41

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you for answer ! Among the above questions, when vacuum freeze is executed, is it correct that the related tables are switched to single-user mode? – Hyungsik Jo Dec 01 '21 at 06:51
  • No, everything stays normal. Anti-wraparound vacuum is a normal maintenance procedure. Note that it does not do the equivalent of a `VACUUM (FREEZE)` by default, only if you change `vacuum_freeze_min_age` to 0. – Laurenz Albe Dec 01 '21 at 06:53