Thread: auto vacuum, not working?
Hi,
yesterday I delete about 200 million rows of a table (about 150GB of data), after delete completes the autovacuum process start.
The autovacuum is running for about 11 hours but no space is released
Autovacuum parameters are with default values in postgresql.conf
The postgres version is 9.0.3
The pg activity reports:
select (now()-query_start) as duration, waiting, current_query from pg_stat_activity where current_query ilike '%auto%'
10:42:19.829 f "autovacuum: VACUUM ANALYZE public.myTable"
How can I release the space used by deleted rows? Without block the table.
Thanks!
Hi,
yesterday I delete about 200 million rows of a table (about 150GB of data), after delete completes the autovacuum process start.
The autovacuum is running for about 11 hours but no space is released
Autovacuum parameters are with default values in postgresql.conf
The postgres version is 9.0.3
The pg activity reports:
select (now()-query_start) as duration, waiting, current_query from pg_stat_activity where current_query ilike '%auto%'
10:42:19.829 f "autovacuum: VACUUM ANALYZE public.myTable"
How can I release the space used by deleted rows? Without block the table.
Thanks!
vacuum does not reclaim space, just marks tuples dead. You need vacuum full.
Mario Weilguni <roadrunner6@gmx.at> wrote: >> yesterday I delete about 200 million rows of a table >> How can I release the space used by deleted rows? >> Without block the table. > vacuum does not reclaim space, just marks tuples dead. You need > vacuum full. VACUUM FULL will lock the table, blocking all other access, and it can run for quite a while. If you expect to be adding 200 million new rows to the table in the foreseeable future, a regular VACUUM (or autovacuum) will make that space available for reuse by that table. The space won't show in the file system; it will still be allocated to the database but available for new rows. -Kevin
How can I release the space used by deleted rows? Without block the table.
The database can only reduce the size tables by returning space to the operating system in one situation: there is free space at the very end of the table. In that case, if it's possible to get a brief exclusive lock on the table, it can shrink in size.
There are some programs available that reorganize table for goals like this, without having any long-lasting locks on the tables. pg_reorg is the most popular example: http://pgfoundry.org/projects/reorg/
-- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com