5

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.

7
  • can you post your version? Sep 26, 2012 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, 2012 at 19:55
  • 1
    Care to explain why? Or do you mean you do not know your version for some reason? Sep 27, 2012 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. Sep 27, 2012 at 7:04
  • What pattern your UPDATEs and DELETEs follow?
    – dezso
    Sep 27, 2012 at 7:05

1 Answer 1

2

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.

1
  • 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, 2012 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.