Another table is a price database. This is where we actually see the biggest difference. The 2 tables have about 2M records each, each day we add about 60-80k records and update about the same number. At the end of the day about 80% of these will be removed. So the tables grow by 500k records a month, but at the end of the month again we remove about 300-400k of these records. What we see is that this load of 60-80 records at the beginning of a month and after re creating the db takes in the are of 8min or so. after 2-3 month the whole process will take up to 20-25 minutes. even though the two tables have only grown max. 500k. Even the cleanup at the end of the month does not help a lot. it speeds things up but nowhere close to what it would be after recreating the db.
Alex
Richard Huxton wrote:
Alex wrote:
Hi,
we have a database. not to big about 50 tables, 20m records. On a daily basis we update/insert/delete between 500k to 1M records in total. We run a full vacuum every night. The db grows probably by 200k records each day and at the end of the month it gets cleaned up.
We run 7.4.1 and 7.4.6
Now, after a few weeks the db starts to slow down after about 2 months same operations take 2-3 times as long as originally. The only way to speed it up again is to drop the entire database; run an initdb, create and restore the database.
Information, Alex, information.
Can you give a specific example of where a query gets slower?
Is this a gradual effect or sudden?
Do you know if your indexes are growing unexpectedly? (shouldn't be, but worth keeping an eye on)
Are you happy PG is tuned
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
