Best practice for removing old data in postgres table but retaining a copy of removed data

Question

We currently have a table that has approx 20 columns (one of the columns being a timestamptz datatype) in our database server which has 834 million rows. This in terms of size is a big table (approx 250GB (inc indexes etc).

I want to find the most efficient and best way of remove the old data that is older than 2 years but also retain this data on a periodic basis if we need it for reporting purposes, this table also has FK constraints.

What would be the best way of handling this?. We want to be able to look at this data should we need to. May or may not be on the same server.

Would it be best to firstly run a COPY which selects the data

COPY (SELECT * FROM TABLENAME WHERE CAST((timecreated_on AT TIME ZONE 'GMT') AS date) > DATE '2020-01-01 00:00:01') TO '/path/to/a/dump/file';

Then removal of the data in the table?

DELETE FROM TABLENAME where CAST((timecreated_on AT TIME ZONE 'GMT') AS date) > DATE '2020-01-01 00:00:01'

Am just looking at a way this can be an automated process that I can schedule via a cronjob on the linux server after every year to remove the data older than 1 year.

Know this may not be the best way but need to look at how I manage the FK key constraints, would I drop them and re-apply which could then cause issues with data integrity?

Any help is much appreciated.

Answer 1

Any solution that DELETEs a lot of rows from a database table is painful. You will always end up doing something along the lines of WITH d AS (DELETE ... RETURNING ...) INSERT INTO ... SELECT * FROM d, which takes a long time, entails a lengthy autovacuum run and leaves the table bloated.

The king's way to solve this problem is to use partitioning. Then you run a split-second ALTER TABLE ... DETACH PARTITION ... to remove the data, but retain the partition with all its data, which is useful for archiving or whatever you want to do with the removed data.

Of course, partitioning requires that you plan ahead and start thinking about the problem before you have a painfully large table on your hands...