2

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.

1
  • You might want to swap < for > in your SQL command? :-)
    – Vérace
    Commented Oct 13, 2021 at 16:34

1 Answer 1

5

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...

4
  • Thanks @laurenz-albe something I have inherited since joining. The deleting would be something that would be done yearly will run a few tests to see how long it would take etc. My thought around this would be that if we had a smaller data subset of information we actually need to keep queries, vacuuming etc would see a significant performance improvement along with queries that hit this table. If we was to go down the partitioning route queries that currently run against this table via the application, would these need to be altered? (I will take a look at partitioning also).
    – rdbmsNoob
    Commented Oct 14, 2021 at 7:41
  • If you don't have very many partitions, the impact should be hardly noticeable. Commented Oct 14, 2021 at 8:24
  • thanks again for the suggestions, explored the CTE route just concerned as its quite a large table, would a Temp table be a suitable alternative?
    – rdbmsNoob
    Commented Oct 14, 2021 at 15:38
  • Perhaps, depends on how you want to use it. Commented Oct 15, 2021 at 2:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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