I'm trying to find-out a way how to prevent performance degradation in my PostgreSQL database.

The table is updated every minute with 100,000 - 200,000 INSERTs. Every 5 minutes the table is updated with the same scale of aggregated UPDATE and a number of DELETEs. Aggregations and DELETEs are done in a way to keep the size of the table around 10 million records.

Apparently updates are too frequent so AUTO VACUUM cannot be completed. I started to run VACUUM after each DELETE series. That improved behavior a lot.

I've a suspicion that periodic reindexing would help but haven't verified it yet. Actually it's very suspicious that PostgreSQL has a dedicated command for reindexing.

I have played with different fillfactor for the indexes, with no visible difference.

What else should I consider to make keep performance stable? Let's say that slow but stable over time is better than sometimes fast and sometimes very slow.

UPD: vacuum analyzed after each delete + reindex every 6 hours solved the issue.

  • can you post your version? – Jack Douglas Sep 26 '12 at 13:38
  • Jack, no I cannot post it. anyway it's fairly table simple index and fairly simple insert/update/delete/vacuum/reindex commands. – m5ba Sep 26 '12 at 19:55
  • 1
    Care to explain why? Or do you mean you do not know your version for some reason? – Jack Douglas Sep 27 '12 at 6:30
  • 1
    @BLNK: Jack asked about the specific version of Postgres server. Is it 9.1, 8.3, some other? I suppose it does matter for the AUTO VACUUM behaviour, so if you want better answers for your scenario, it's good to answer first the comment/questions. – ypercubeᵀᴹ Sep 27 '12 at 7:04
  • What pattern your UPDATEs and DELETEs follow? – dezso Sep 27 '12 at 7:05

Some things depend on your PostgreSQL version. On some older versions pay attention to fsm settings to make sure it can track free space in your tables for re-use. You might also look at scheduling a periodic cluster if you can afford the downtime. If not, pay attention to the indexes.

| improve this answer | |
  • it's newer than fsm versions. Clustering - will check how long it takes. Acceptable periodic downtime is 1-2 min only. 1 Aggregation, vacuum and reindexing all together take ~1 min – m5ba Sep 26 '12 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.