I have found the problem, using this query (found here http://stackoverflow.com/questions/3312929/postgresql-idle-in-transaction-diagnosis-and-reading-pg-locks)
select pg_class.relname, pg_locks.transactionid, pg_locks.mode, pg_locks.granted as "g", pg_stat_activity.current_query, pg_stat_activity.query_start, age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid
and pg_stat_activity.procpid = <AN IDLE TRANSACTION PROCESS>
order by query_start;
And indeed, we constantly have idle transcations. They all use the same dummy table, a dual table substitute containing only one column, and one row.
We use this table with tomcat-jdbc-pool to check connections health with 'select 1 from dual' (we don't use 'select 1' for portability reasons, to work with oracle also).
And these transactions are never commited. So we have a bunch of running transactions, constantly running and recreated by tomcat-jdbc-pool. Some of them run for hours.
This seems to impact significally the ability of postgresql to vacuum... and thus to keep efficient indexes!
Changing the configration of tomcat-jdbc-pool to 'select 1 from dual; commit;' seems to resolve the problem.
I'm going to ask on tomcat-jdbc-pool mailing-list if this is ok.
Thanks a lot for your help.
Franck
