Re: VACUUM FULL doesn't reduce table size

pinker wrote:
> I have deleted a large number of records from my_table, which originally had
> 288 MB. Then I ran vacuum full to make the table size smaller. After this
> operation size of the table remains the same, despite of the fact that table
> contains now only 241 rows and after rewriting it in classic way: CREATE
> TABLE new_table AS SELECT * FROM old_table - new_table size is 24kB.
> What went wrong? And how can I remove those blocks?

Normally this happens when you have transactions that are older than the
delete, so the deleted rows cannot be removed by vacuum because that
transaction might still see them. If you close all old transactions,
vacuum full would be able to reclaim all the space. You might have
prepared transactions also, see select * from pg_prepared_xacts;

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services