On Thu, Jun 4, 2009 at 7:31 AM, Erik Aronesty <erik@xxxxxxx> wrote: > Seems like "VACUUM FULL" could figure out to do that too depending on > the bloat-to-table-size ratio ... > > - copy all rows to new table > - lock for a millisecond while renaming tables > - drop old table. You'd have to lock the table at least against write operations during the copy; otherwise concurrent changes might be lost. AIUI, this is pretty much what CLUSTER does, and I've heard that it works as well or better as VACUUM FULL for bloat reclamation. However, it's apparently still pessimal: http://archives.postgresql.org/pgsql-hackers/2008-08/msg01371.php (I had never heard this word before Greg Stark used it in this email, but it's a great turn of phrase, so I'm reusing it.) > Locking a whole table for a very long time is scary for admins. Agreed. It would be nice if we had some kind of "incremental full" vacuum that would run for long enough to reclaim a certain number of pages and then exit. Then you could clean up this kind of problem incrementally instead of in one shot. It would be even nicer if the lock strength could be reduced, but I'm guessing that's not easy to do or someone would have already done it by now. I haven't read the code myself. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
