Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a pgsql table which grows quite large. I would like to rotate it using a cron job - the data is only used to generate reports and after a week or two, it is not required.

share|improve this question

2 Answers 2

The proper way to do this is to set up table partitioning.

http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html

You establish a check constraint on a column, then use a rule driven by that check to direct rows inserted into the parent table to one of several child tables. In your case, I'd suggest weekly partitioning. If you want to let users query a single partition, you just name the partitions logically - say 2009_week_32, or 2009_august_week_1 and have them issues queries against the proper table.

If you want them to query multiple tables together, you set up a view that does a union select across multiple tables, and users query the view.

When you want to remove data that is no longer required, you just alter the table to change the rule and drop the table containing the old data.

This is a bit more work than disabledleopard's answer, but the technique is fairly common across other database engines. The downside to just using a "delete where" on a timestamp column is that it can take a very long time on large tables and that the data is written to the journal. Truncate is designed to get around the journal writing issue, but you can't specify a where clause. Dropping a table is very quick, as you're not dealing with individual rows.

You will need to have some kind of maintenance to set up rules to cover future dates. Some people script this monthly (just check 5 days before the end of the month if the next month's partition exists, and if not create it and perform the alter table magic to change the rules) while others just set up several months or years of rules in advance and perform the maintenance work manually every quarter / year as appropriate.

share|improve this answer

my thought would be to alter the schema of this table to log the timestamp the data was inserted.

ALTER TABLE blah ADD COLUMN entry_time timestamp DEFAULT current_timestamp;

this way what is currently creating the table need not change.

Then you're cron could then just be this command (should work with postgres 8.x but I don't have a server running to confirm sorry)

psql -c "DELETE FROM blah WHERE entry_time < (current_timestamp - interval '14 days') RETURNING *;" > deletelog.$( date "+%Y%m%d").log

Edit: I forgot to mention the "RETURNING *" bit makes psql return full details of all deleted rows as well as the usual "N rows removed" so this log could be used with a bit of awk magic to re-insert data if removed prematurely. Also, audit logs for automatic deletes are always a good idea.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.