Logical Replication Sync - Issue with Table Bloat
| От | Tim |
|---|---|
| Тема | Logical Replication Sync - Issue with Table Bloat |
| Дата | |
| Msg-id | CAKhLO5iVHr4BQzxOr-j4soEeWbwXsKEm_RduTCLy9m6YQ2ECrQ@mail.gmail.com обсуждение исходный текст |
| Список | pgsql-admin |
Hello All,
I'm currently in the process of creating a logical replica of our production database. Due to an oversight on my part in leaving archive_mode = on, I had an issue in the middle of the process with disk space and had to stop the database, clean up some stuff, and restart it. All the slots reconnected fine with no issues.
But when I did this there was a slot doing the initial sync COPY command on 2 large tables which are together about 150GB in size. It was about 95% done when I had to restart and by what I can tell, the COPY command had to restart from scratch.
The problem is now that it's finished, when I look at the tables with pg_relation_size(), the tables take up almost twice as much space on disk (130 GB more than the publisher DB). Vacuum doesn't seem to have any effect on reducing the size.
So my question is, should I be concerned about this? Will vacuum eventually take care of this wasted space or is a VACUUM FULL my only option? (Running VACUUM FULL also locks out the logical replication worker, which I'm worried will run so long due to the table size that it will disconnect from the replication slot on the publisher)
The output from VACUUM VERBOSE is attached if that helps:
vacuum verbose xxxxx;
INFO: vacuuming "xxxxx"
INFO: scanned index "xxxxx_pk" to remove 77776 row versions
DETAIL: CPU: user: 5.52 s, system: 0.06 s, elapsed: 9.00 s
INFO: "xxxxx": removed 77776 row versions in 49782 pages
DETAIL: CPU: user: 2.90 s, system: 0.28 s, elapsed: 4.17 s
INFO: index "xxxxx_pk" now contains 50342450 row versions in 197929 pages
DETAIL: 75933 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: "xxxxx": found 90656 removable, 50337375 nonremovable row versions in 12064534 out of 24323128 pages
DETAIL: 151584 dead row versions cannot be removed yet, oldest xmin: 35093074
There were 22277 unused item identifiers.
Skipped 0 pages due to buffer pins, 12258594 frozen pages.
81 pages are entirely empty.
CPU: user: 1316.26 s, system: 118.43 s, elapsed: 1844.49 s.
INFO: vacuuming "pg_toast.pg_toast_74120881"
INFO: scanned index "pg_toast_74120881_index" to remove 114 row versions
DETAIL: CPU: user: 0.02 s, system: 0.01 s, elapsed: 0.07 s
INFO: "pg_toast_74120881": removed 114 row versions in 33 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index "pg_toast_74120881_index" now contains 670843 row versions in 3704 pages
DETAIL: 114 index row versions were removed.
1861 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pg_toast_74120881": found 114 removable, 670840 nonremovable row versions in 114080 out of 228343 pages
DETAIL: 99 dead row versions cannot be removed yet, oldest xmin: 35093074
There were 3468 unused item identifiers.
Skipped 0 pages due to buffer pins, 114263 frozen pages.
0 pages are entirely empty.
CPU: user: 14.34 s, system: 7.36 s, elapsed: 30.94 s.
VACUUM
В списке pgsql-admin по дате отправления: