On Thu, 2007-03-01 at 14:32 +0200, Hannu Krosing wrote: > Ühel kenal päeval, N, 2007-03-01 kell 12:02, kirjutas Simon Riggs: > > Use case for VACUUM FULL is very low these days. VACUUM does the most > > important part of what VACUUM FULL offers, yet does it concurrently > > rather than with a full table lock. VACUUM FULL also > > - has very long execution time > > - generates lots of WAL traffic > > - uses lots of memory while it runs > > - isn't as good at compacting a relation as CLUSTER > > - sometimes requires multiple runs to properly compact data > > Yet another way to achieve VACUUM FULL-like results would be a COMPACT > TABLE command, which would do the following: > > 1 - start a forward sequential scan to find free space > > 2 - start a backwards seqscan to find live tuples > > move live tuples to free space by doing a null update (UPDATE without > changing any field values) with new version being placed to lowest > possible age until the two scans intersect.
Thats almost exactly what VACUUM FULL does now, just using two passes of the table. 1st scan builds the free block list and tuples to move list, then it starts from back, moving rows until it gets to the front. This does sound like it would save some I/O by avoiding the second half of the first scan. In most other respects its the same thing, AFAICS. CLUSTER would still achieve better compaction and you'll still need to write lots of WAL doing this. ISTM a radical approach is needed, so I'm very open to discussion about this and how we cope. If we break down the main thoughts into a few parts: 1. would like a way to CLUSTER/VACUUM FULL where we don't have to move all of the tuple versions, just the current ones. 2. would like a way to compact a table more efficiently Your idea does (2) in a concurrent manner, which is very good. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
