Thread: vacuum full is not reclaiming the space and showing non-removable rows

Hi,

vacuum full verbose chnges_23_21;
INFO:  vacuuming "public.chnges_23_21"
INFO:  "chnges_23_21": found 0 removable, 8602500 nonremovable row versions
in 88699 pages
DETAIL:  8436850 dead row versions cannot be removed yet.
CPU 0.61s/3.38u sec elapsed 5.45 sec.
VACUUM

We deleted(using a delete query) a lot of rows from the above table and did
a vacuum full. But we are not able to reclaim the space(occupied by this
table) and the result is showing that there are nonremovable rows.

Can anyone help on how it can be fixed?



-----
--
Thanks,
Rajan.
--
View this message in context:
http://postgresql.nabble.com/vacuum-full-is-not-reclaiming-the-space-and-showing-non-removable-rows-tp5930937.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

rajan <vgmonnet@gmail.com> writes:
> We deleted(using a delete query) a lot of rows from the above table and did
> a vacuum full. But we are not able to reclaim the space(occupied by this
> table) and the result is showing that there are nonremovable rows.

There's an old open transaction somewhere that's possibly still able to
see those rows.  Look for idle sessions in pg_stat_activity.

I don't remember at the moment whether prepared-but-uncommitted
transactions can hold back reclaiming dead data, but look into
pg_prepared_xacts as well.

            regards, tom lane

thanks for the reply.

Restarting the DB and running vacuum again did recover some space. But still
there are dead rows.

vacuum full verbose chnges_23_21;
INFO:  vacuuming "public.chnges_23_21"
INFO:  "chnges_23_21": found 8436850 removable, 239750 nonremovable row
versions in 89460 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 1.38s/0.36u sec elapsed 4.82 sec.
VACUUM

And my pg_prepared_xacts is empty.



-----
--
Thanks,
Rajan.
--
View this message in context:
http://postgresql.nabble.com/vacuum-full-is-not-reclaiming-the-space-and-showing-non-removable-rows-tp5930937p5931145.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

rajan <vgmonnet@gmail.com> writes:
> Restarting the DB and running vacuum again did recover some space. But still
> there are dead rows.

> vacuum full verbose chnges_23_21;
> INFO:  vacuuming "public.chnges_23_21"
> INFO:  "chnges_23_21": found 8436850 removable, 239750 nonremovable row
> versions in 89460 pages
> DETAIL:  0 dead row versions cannot be removed yet.

No, you're misreading it.  It deleted 8436850 rows, and 239750 live rows
remain.  We can tell they're all live because there are 0 dead-but-not-
removable rows.  So you're good now.

            regards, tom lane