Search Postgresql Archives

reclaiming space from heavily used tables?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

I've got a large table from which I'd like to completely reclaim space. I read the docs and it sounds like I can’t run `vacuum full`, because this table is accessed constantly and can’t have downtime. Assuming that’s true, what do you think of the following idea? Is there a better alternative?

1. Replace the table T with two tables T1 and T2 and a view T that is `select * from T1 union T2`.
2. Write only to T1, and slowly move records from T2 to T1. 
3. When T2 is empty, redefine the view T to be simply `select * from T1`.
4. Recreate or vacuum full T2, so space is fully reclaimed.
5. Redefine view as the union select and repeat process going other direction from T1 to T2, as needed.

I guess one question is whether I can do 1 and 3 (renaming tables, redefining views) atomically without disturbing concurrent select queries.

Rob






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux