slowness what only full vacuum can solve - Mailing list pgsql-general

We are running Postgres 8.3 on our production servers on Amazon EC2.

We have a reoccurring problem of slowness initially in every 2 months, after enabling autovacuum every 6 months what only full vacuum can solve. It's kind of a problem as it requires 2hrs downtime and we want to avoid that. What we are doing now is moving to Postgres 9.1 as a desperate measure, but we would like to understand better the root cause of the problem.

If you've seen similar before or have an idea about what's going on here we would really appreciate that insight.

Please see bellow the details.

Thanks guys.

Laszlo

We have a table with 2-3 million rows, with 30 fields with type varchar, text and number. The table is heavily updated and read too. A super simple query like the following takes unacceptable long time to run. 4-15 sec compared to the under a second run when Postgres works well. We tried index rebuild, more disks, more memory, but nothing helped, only full vacuum. It seems to me that Postgres can't keep internal things in order to operate nicely. Also I see extreme high IO load on the server when this problem occurs. And again, after a full vacuum, things are perfect.

You can see the explain output here:

explain analyze select count(comment0_.id) as col_0_0_ from Comment comment0_ where comment0_.sourceId='xxx' limit 2;