From: | Jeff Hoffmann <jeff(at)propertykey(dot)com> |
---|---|
To: | Steve Wampler <swampler(at)noao(dot)edu> |
Cc: | postgres-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Rollover of tables? |
Date: | 2000-05-26 17:52:33 |
Message-ID: | 392EB9E1.295095E5@propertykey.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Steve Wampler wrote:
> 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
>
> I like (b), personally, but would like to know if anyone
> sees any "gotcha's", especially w.r.t. postgresql as the
> database system.
>
if you vacuum on a regular basis, (b) shouldn't be a problem. right
now, postgres won't reuse the deleted pages unless you vacuum it. your
table will balloon until you do a vacuum. ideally you should be able to
do (a) in a transaction, but i'd have some concern if table
renaming/creation is in fact isolated in the transaction.
if you're worried about having the table unavailable, though (even for a
split second), i don't know if either one would work well for you if
you're needing to update the table during a vacuum. you may want to
approach it in the application where you have the application log to
tables named for the date or week # (e.g., log_20000526). you can then
play with the log files at your leisure.
jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Barry Lind | 2000-05-26 17:59:36 | Re: Speed of locating tables? |
Previous Message | Tom Lane | 2000-05-26 17:36:30 | Re: Speed of locating tables? |