Ü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. This, together with ordinary VACUUM would achieve almost the same results as VACUUM FULL with much lower overhead. And it can also be done in lots of smaller transactions instead of one big one if required. If we can trust FSM, the whole process just becomes the backward scan and null updates until the null update does not move tuple to a lower page. Also, for the duration of COMPACT TABLE the updated tuple should always be placed in lowes available slot, that is no same-page updates should be tied before going to FSM. This has some downsides : 1 - the original xmin will be lost 2 - as with any updates, it may block/abort other concurrent updates, so it could be a good thing to teach the update mechanism about null updates. Still I think that this would be the chepest way to get VACUUM FULL behaviour without locking the whole table for long time -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
