From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Roman Havrylyak <roma(at)ukr(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: rotating table question |
Date: | 2001-08-17 12:16:32 |
Message-ID: | 3B7D0B20.E80F1932@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Roman Havrylyak wrote:
>
> Suppose we have table with indexes, attached triggers, and finnaly alot of
> records (more then 100,000). Records are adding continuously.
>
> Once a month you have to do archiving of records. Simple INSERT INTO ARCHIV
> followed by DELETE and then VACUUM take alot of time, offen even hang up
> sessions. Dropping triggers and indexes is bad due to the continuously
> adding records.
>
> Is there any ways to solve problems?
Try TRUNCATE TABLE instead of DELETE. It won't fire your triggers,
will instantaneously remove all of your records, and if you choose
to perform a 'VACUUM ANALYZE foo;' afterwards to update the
statistics, they will be updated instantly. I'm guessing 95% of the
time used in your situation is VACUUM reclaiming the space used by
the DELETE.
Hope that helps,
Mike Mascari
mascarm(at)mascari(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Feite Brekeveld | 2001-08-17 12:18:25 | Re: slow update but have an index |
Previous Message | Michael Widenius | 2001-08-17 12:15:36 | Re :MySQL Benchmark page - Problem with vacuum() in PostgreSQL |