pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum full is not reclaiming the space and showing non-removable rows
4+ messages / 2 participants
[nested] [flat]
* vacuum full is not reclaiming the space and showing non-removable rows
@ 2016-11-18 12:00 rajan <vgmonnet@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: rajan @ 2016-11-18 12:00 UTC (permalink / raw)
To: pgsql-admin
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-...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum full is not reclaiming the space and showing non-removable rows
@ 2016-11-18 16:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: rajan <vgmonnet@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2016-11-18 16:01 UTC (permalink / raw)
To: rajan <vgmonnet@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum full is not reclaiming the space and showing non-removable rows
@ 2016-11-20 06:44 rajan <vgmonnet@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: rajan @ 2016-11-20 06:44 UTC (permalink / raw)
To: pgsql-admin
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-...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Re: vacuum full is not reclaiming the space and showing non-removable rows
@ 2016-11-20 16:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: rajan <vgmonnet@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2016-11-20 16:32 UTC (permalink / raw)
To: rajan <vgmonnet@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-11-20 16:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-18 12:00 vacuum full is not reclaiming the space and showing non-removable rows rajan <vgmonnet@gmail.com>
2016-11-18 16:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-20 06:44 ` rajan <vgmonnet@gmail.com>
2016-11-20 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox