Quite a fast lockless vacuum full implemenation

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

Known limitations or problems:
1) Requires PostgreSQL 8.4+
2) Requires pl/pgsql installed
3) Requires superuser access to DB (such tasks must be done only by DBA anyway)
4) Will not work in presence of 'always' or 'replica' ON UPDATE
triggers on target table (I have never seen one used before)
5) Can't reduce bloat of TOAST table (no way to access toast table data outside)
6) Is still producing some index bloat but not much
7) Theoretically can produce deadlocks on heavly updated tables (but I
never seen this problem during two week testing in production)
8) Can produce big index bloat in presence of very long running
transactions (that is bad situation anyway)
9) Ignores table fillfactor (will try compact table to 100%)
10) Truncating empty pages from end of the table still requires short
exclusive lock (which can be hard to acquire on loaded systems)
11) Might have some hidden or unknown for me bug (sorry if you get hit
by this one... I did my best during testing).

The tool has been tested on some medium and large projects during last
two weeks. No serious problems has been found (but see "Known
limitations and problems").

Now about performance test:

Here is SQL commands to create bloated test table used in my perfromance test:
========================================
DROP TABLE IF EXISTS __test;
CREATE TABLE __test as select id,random() as f1,random() as
f2,random()::text as f3,now() as mtime,(random()>1)::boolean as flag
FROM generate_series(1,10000000) as t(id);
DELETE FROM __test where id%5<>0;
ALTER TABLE __test add primary key (id);
CREATE INDEX __test_f1_key ON __test(f1);
CREATE INDEX __test_f2_key ON __test(f2);
CREATE INDEX __test_f3_key ON __test(f3);
VACUUM __test;
CREATE OR REPLACE FUNCTION __set_mtime() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.mtime = clock_timestamp();
return NEW;
END;
$$;
CREATE TRIGGER set_mtime
BEFORE UPDATE ON __test
FOR EACH ROW
EXECUTE PROCEDURE __set_mtime();

SELECT sum(pg_relation_size('public.'||indexname))::bigint/current_setting('block_size')::bigint
FROM pg_indexes WHERE schemaname='public' AND tablename='__test';
SELECT pg_relation_size('__test')/current_setting('block_size')::bigint;
===================================================

Test results:
1)VACUUM FULL __test;
Table size (pages) 113574 -> 22714
Index size (pages) 26364 -> 51616 (index bloat 95%)
Time: 211873,227 ms (3.5 minutes)

vs (all tests performed through pgbouncer in session pooling mode to
save some connect time)

2)time ./vacuum_table.pl --table=__test --pages-per-round=10
--pages-per-vacuum=1000
(some system load observed because of lot of shell+psql calls...
probably good idea move to DBD::Pg persistant connect)
Table size (pages) 113574 -> 23576
Index size (pages) 26364 -> 30750 (index bloat: 16%)
real 21m0.221s
That is somewhat lighter version which don't put too much stress on IO
system (6 times slower then VF but no long locks)

3)time ./vacuum_table.pl --table=__test --pages-per-round=100
--pages-per-vacuum=10000
Table size (pages) 113574 -> 23594
Index size (pages) 26364 -> 32242 (index bloat: 22%)
real 12m10.300s
This run was performed with default options. Only 3.5 time slower then VF.

Thank you for your time. I hope my tool can help someone.
And of course I would be happy to get some feedback and especially bug reports.

--
Maxim Boguk
Senior Postgresql DBA.

Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
МойКруг: http://mboguk.moikrug.ru/

Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не все.