Re: Quite a fast lockless vacuum full implemenation

On Thu, Dec 9, 2010 at 1:37 AM, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> 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