Logical Replication Sync - Issue with Table Bloat

From: Tim <timfosho(at)gmail(dot)com>
To: posgres support <pgsql-admin(at)postgresql(dot)org>
Subject: Logical Replication Sync - Issue with Table Bloat
Date: 2021-10-08 17:22:07
Message-ID: CAKhLO5iVHr4BQzxOr-j4soEeWbwXsKEm_RduTCLy9m6YQ2ECrQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-admin by date

  From Date Subject
Next Message Wells Oliver 2021-10-08 21:27:55 13.4 on RDS, SSL SYSCALL EOF on restore
Previous Message Tom Lane 2021-10-08 15:03:16 Re: Commit and Exception Block