Hello, Thanks to everyone who gave me hints and feedbacks. I managed to solve the problem. My understanding of what was happening is the following : - The gin index (as explained on [1]), stores temporary list, and when they grow big enough, those are dispatched into the real index. Vacuum also does this index flush, in background. - This index flush, on a table with 500k rows, means making changes to a lot of disk pages, filling the WAL in one big burst, forcing an immediate checkpoint, and blocking the INSERT that triggered it. I managed to solve the problem by adjusting two set of parameters : - The work_mem variable, which sepcify the maximal size of the temporary list before the gin index is "flushed". - The autovacuum parameters. The main idea was to increase the size of temporary lists (through work_mem) and increase the frequency of autovacuums, to ensure that under real life load (even heavy real life load), the "index flush" is always done by the autovacuum, and never by the "list is full" trigger. With this setup, I managed to handle indexing 10 000 objects in 2 hours without any stall, which is much more than we'll have to handle under real life load. Regards, [1] http://www.postgresql.org/docs/8.4/static/gin-implementation.html -- Gaël Le Mignot - gael@xxxxxxxxxxxxxxxx Pilot Systems - 9, rue Desargues - 75011 Paris Tel : +33 1 44 53 05 55 - www.pilotsystems.net Gérez vos contacts et vos newsletters : www.cockpit-mailing.com -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
