Postgres Pro
Downloads
Home   >   mailing lists

Thread: rotating table question

rotating table question

From
"Roman Havrylyak"
Date:
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?

Re: rotating table question

From
Martijn van Oosterhout
Date:
On Fri, Aug 17, 2001 at 02:29:47PM +0200, 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?

I'm not sure whether it works, it may only work with a recent release, but
wouldn't the solution be:

begin;
alter table data rename to data_archive;
create table data (...);
commit;

I think with the recent decoupling of filenames and table names that should
be possible.

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.

Re: rotating table question

From
Mike Mascari
Date:
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@mascari.com