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.