Обсуждение: 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