On Thu, Dec 9, 2010 at 1:37 AM, Maxim Boguk <maxim.boguk@xxxxxxxxx> wrote: > Hi there, > > First: I must say thanks to authors of this two posts: > http://blog.endpoint.com/2010/09/reducing-bloat-without-locking.html > and > http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/ > These two posts was provided me exellent ideas and starting point to > create somewhat fast and reliable tool. > > Second: sorry for long post. I don't have my own blog to post such things. > > Unfortunatelly, exessive table bloat still can happen in real projects > and task of compacting PostgreSQL table without long downtime is very > common. > So I wrote the tool which can perform incremental vacuum and compact > table without completely locking target table. > > This message has 2 files attached: finished storable procedure and > compact table wizard. They must be put in the same directory. Then run > vacuum_table.pl --help to see possible options. > Usage sample: > ./vacuum_table.pl --dbname=billing --table=changes > > Storable procedure itself can be used stand-alone, but vacuum_table.pl > is an easy to work with wizard to perform table compation. > Before you choose to try it in production databases, PLEASE read > source code and make sure you UNDERSTAND what is my code doing. > > Good features: > 1) plays nice with triggers and rules on table (prevents on update > trigger firing with set local session_replication_role to replica), > therefore it can be used with active slony/londiste replication (on > both master and slave servers). > 2) has good performance (on my tests only 3-5 times slower than common > VACUUM FULL) > 3) can be restarted anytime > 4) doesn't produce exessive index bloat (not like as VACUUM FULL) > 5) is easy to use can you take some time to explain the mechanism of vacuum? looking at your code, the workhorse portion is the sql loop 'FOR _new_tid in EXECUTE...'. how does this compact the table/indexes? merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
