After switching to Postgresql 13 (13.6) from Postgresql 12 (12.10), we noticed that the manual VACUUM operation takes significantly more time, often more than double compared to Postgresql 12. Which is really strange given that Postgresql 13 supports parallel vacuum.
We run vacuum like this in version 13:
VACUUM (VERBOSE, ANALYZE, PARALLEL 2) my_table
and in version 12:
VACUUM VERBOSE ANALYZE my_table
This is run after massive data insertion (billions of rows), on four tables. We did not perform version upgrade, instead we created a new RDS instance for Postgresql 13. The tables are used only for reading after inserting the complete dataset.
The times needed for the VACUUM statement have gone (34m -> 81m) for the smallest table and (370m -> 815m) for the largest one.
Unfortunately we don't have the output of the VACUUM command available. Is there anything that could lead to such a huge time increase between these two versions?
EDIT 2022-12-14: Here's the output of VACUUM command from version 12:
INFO:__main__:INFO: vacuuming "public.table_1"
INFO:__main__:INFO: index "table_1_pkey" now contains 1545723861 row versions in 4191167 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 7.43 s, system: 14.21 s, elapsed: 150.62 s.
INFO:__main__:INFO: index "table_1_col_b_idx" now contains 1545723861 row versions in 8128097 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 12.50 s, system: 28.37 s, elapsed: 154.44 s.
INFO:__main__:INFO: index "table_1_col_a_idx" now contains 1545723861 row versions in 6904914 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 11.60 s, system: 31.21 s, elapsed: 141.95 s.
INFO:__main__:INFO: "table_1": found 0 removable, 1545723861 nonremovable row versions in 14866347 out of 14866347 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 830
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 195.56 s, system: 402.60 s, elapsed: 2043.27 s.
INFO:__main__:INFO: vacuuming "pg_toast.pg_toast_16415"
INFO:__main__:INFO: index "pg_toast_16415_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:__main__:INFO: "pg_toast_16415": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 830
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:__main__:INFO: analyzing "public.table_1"
INFO:__main__:INFO: "table_1": scanned 300000 of 14866347 pages, containing 31191198 live rows and 0 dead rows; 300000 rows in sample, 1545663909 estimated total rows
and from version 13:
INFO:__main__:INFO: vacuuming "public.table_1"
INFO:__main__:INFO: launched 2 parallel vacuum workers for index cleanup (planned: 2)
INFO:__main__:INFO: index "table_1_col_b_idx" now contains 1557689525 row versions in 8216885 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 12.89 s, system: 26.59 s, elapsed: 118.25 s.
INFO:__main__:INFO: "table_1": found 0 removable, 1557689525 nonremovable row versions in 14980412 out of 14980412 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 864
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 3375.73 s, system: 258.42 s, elapsed: 4788.42 s.
INFO:__main__:INFO: vacuuming "pg_toast.pg_toast_16416"
INFO:__main__:INFO: "pg_toast_16416": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 864
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:__main__:INFO: analyzing "public.table_1"
INFO:__main__:INFO: "table_1": scanned 300000 of 14980412 pages, containing 31194184 live rows and 0 dead rows; 300000 rows in sample, 1557672428 estimated total rows
Unfortunately, there doesn't seem to be anything in there to explain the difference in duration. Only a confirmation of the longer time required in PostgreSQL 13. Some notes:
- Output for some index vacuuming is missing in version 13. I'm not sure why that is; could be because indexes are being handled by parallel workers? Also could be that some output may be missing since psycopg2 keeps only the last 50 messages in
conn.noticesattribute. - The number of rows is slightly larger in v13 run; this is due to our normal weekly dataset growth and it should not be enough to explain the difference in time. In fact, prior to the v12 run there had been another v13 run with less data that still took more time.
I've been skimming though the different / newly introduced configuraton options (actually RDS parameter groups) between PostgreSQL 12 and 13... could maintenance_io_concurrency setting be a factor? It was introduced in version 13 and has a default value of 10. I am wondering that because we are performing multiple maintenance operations concurrently, via different client sessions (one session per table; 4 tables in total). What would be the equivalent maintenance_io_concurrency value in PostgreSQL 12?
