6

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.

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

1 Answer 1

Reset to default
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.

Improve this answer
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 Commented Sep 26, 2012 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.