Skip site navigation (1) Skip section navigation (2)

Rollover of tables?

From: Steve Wampler <swampler(at)noao(dot)edu>
To: postgres-general <pgsql-general(at)postgresql(dot)org>
Subject: Rollover of tables?
Date: 2000-05-26 17:28:53
Message-ID: 392EB455.5FF21C4C@noao.edu (view raw or flat)
Thread:
Lists: pgsql-general
I've got a (very simple) postgresql database that records logging
information from a group of distributed applications.
It's already beginning to get somewhat large (and the projected
lifetime of the application set is 25 years...).  Some
sort of rollover is going to be needed archive old log information.

I can see two easy approaches (feel free to suggest better ones!):

(a) rename the table as an 'archive' log table and then recreate the
    'active' log table.

(b) extract the old log information into an archive table,
    removing it from the original

In both cases, the archive table will later be unloaded (first
compressed and then to tape) to conserve disk space.  This will be
done infrequently.

Any feelings on which is a better way to go?  (a) should be
nice and fast (right?), but (b) has the advantage of allowing partial
extractions - so only log information over a week or month old
would be archived each time, plus there should be no problem with
insertions happening during the rollover process (right?).

I like (b), personally, but would like to know if anyone
sees any "gotcha's", especially w.r.t. postgresql as the
database system.

Thanks!

--
Steve Wampler-  SOLIS Project, National Solar Observatory
swampler(at)noao(dot)edu

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2000-05-26 17:36:30
Subject: Re: Speed of locating tables?
Previous:From: Barry LindDate: 2000-05-26 16:47:17
Subject: Re: Speed of locating tables?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group