It a gradual process. For example, we have 3 reference tables that get
updated very day. they have between 3,5 and 7M records. All we do is
simple inserts, deletes. The number of records is different each day so
its a bit difficult to say.
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
>
