Here is a normal vacuum (without FULL) of pg_statistic. It does report a large number of dead-but-not-removable rows:
# vacuum analyze verbose pg_statistic;
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
INFO: vacuuming "pg_catalog.pg_statistic"
INFO: index "pg_statistic_relid_att_inh_index" now contains 482186 row versions in 1325 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_statistic": found 0 removable, 567740 nonremovable row versions in 34667 out of 34667 pages
DETAIL: 558093 dead row versions cannot be removed yet.
There were 21663 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.12u sec elapsed 0.13 sec.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
INFO: vacuuming "pg_toast.pg_toast_2619"
INFO: index "pg_toast_2619_index" now contains 3319 row versions in 12 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_2619": found 0 removable, 3319 nonremovable row versions in 667 out of 667 pages
DETAIL: 3097 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
# vacuum analyze verbose pg_statistic;
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
INFO: vacuuming "pg_catalog.pg_statistic"
INFO: index "pg_statistic_relid_att_inh_index" now contains 482186 row versions in 1325 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_statistic": found 0 removable, 567740 nonremovable row versions in 34667 out of 34667 pages
DETAIL: 558093 dead row versions cannot be removed yet.
There were 21663 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.12u sec elapsed 0.13 sec.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
INFO: vacuuming "pg_toast.pg_toast_2619"
INFO: index "pg_toast_2619_index" now contains 3319 row versions in 12 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_2619": found 0 removable, 3319 nonremovable row versions in 667 out of 667 pages
DETAIL: 3097 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
On Tue, Oct 31, 2017 at 6:12 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Arjun Ranade <ranade@xxxxxxxxxxxxxxxxx> writes:
> There is nothing in pg_prepared_xacts, however, in pg_stat_activity there
> are two pglogical processes that have a "backend_start" of "2017-06-17"
> when the last time we restarted this server. Both are not waiting and have
> null for "state." This might be expected behavior for pglogical.
I don't know much about pglogical, but if it's holding back the xmin
horizon (as it appears to be doing) that is a really bad pglogical bug.
If you try vacuum verbose (doesn't have to be FULL) on some table that
gets lots of update traffic, does it report a large number of dead-but-
not-removable rows?
> I did try TRUNCATE before but even as the postgres user, but for some
> reason it didn't allow me to truncate because it was a system table.
Mmm. You could get around that, but possibly not without restarting
in single-user mode, which is probably more interruption in service
than you want. It might be easier to force-quit the pglogical sessions.
regards, tom lane
