1

For example lets say we have table A:

create table if not exists T(
column1 int,
column2 varchar,
column3 date
);

and archiving table TArchive:

create table if not exists TArchive(
column1 int,
column2 varchar,
column3 date
);

What would be the best approach to inserting data older than x date into TArchive without locking down table T in production? Under the assumption that table T has a big amount of rows.

I've been researching this for hours. In SQL Server you have different approaches like: https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/ But in PostgreSQL I can barely find anything.

Should you just directly take the data from the T table and import it into TArchive?

Should you import your data into a temp table first, then import that into the archive table? And if so, why would this approach be better when you're doing 2x the inserts for the same data.

How many functions should you make? One function to rule them all? Or a function for archiving and another for deleting the old data?

Are there any other approaches?

  • The most efficient approach would be to partition the table by e.g. month and then simply detach the partitions for those months that are no longer needed – a_horse_with_no_name Sep 11 '19 at 7:04
2

You probably cannot find any articles on the topic because it is so simple:

WITH old_rows AS (
   DELETE FROM t
   WHERE ...
   RETURNING *
)
INSERT INTO tarchive
SELECT * FROM old_rows;

This won't lock table t very much — concurrent SELECTs and data modifications won't be affected (unless they are trying to modify one of the deleted rows).

Your main problems are:

  • This statement may take a long time, holding locks and blocking autovacuum as it runs.

  • Afterwards, the table will not be smaller, but emptier. This hurts sequential scans and wastes cache space.

Particularly the second problem hurts, and the remedies all require making the table unavailable for a time:

  • VACUUM (FULL) will reorganize the table, but blocks any access to the table while it runs.

  • Replacing the table with a different one will block data modifications for a while:

    BEGIN;
    LOCK TABLE bloated IN SHARE ROW EXCLUSIVE MODE;
    CREATE TABLE bloated_copy (LIKE bloated INCLUDING ALL);
    INSERT INTO bloated_copy SELECT * FROM bloated;
    DROP TABLE bloated;
    ALTER TABLE bloated_copy RENAME TO bloated;
    COMMIT;
    

Because of all that, it is a good idea to plan how you want to get rid of old data right when you design your system, but in my experience this is almost always forgotten. The least painful way to get rid of old data is to partition the tables by time.

  • Out of curiosity, how would you design a system that avoid such issues? – Chessbrain Sep 11 '19 at 9:02
  • 1
    I would partition the table by whatever criterion is used to delete the data, by time or by user or whatever. Then when I want to delete old data, I simply detach and archive a partition. – Laurenz Albe Sep 11 '19 at 9:05
  • Oh, when you said partitioning in your answer, I thought you meant window functions in a query which confused me. I never knew of this concept before. Thanks! – Chessbrain Sep 11 '19 at 9:10
2

The approach that worked for me was to have the archival script work in batches with breaks in between. So it would look like this:

  • find the first 5000 rows that need archiving
  • copy them to the archive
  • delete them from the source table
  • sleep (30s)
  • keep repeating until no more rows need archiving

This way your server won't be under a heavy load while this runs and the database will never be locked for long. The downside is that this will take longer than some of the other approaches.

  • When you say "take a break", I assume you mean pg_sleep, right? – Chessbrain Sep 11 '19 at 9:03
  • Yes, sleep. Just to let the CPU catch up. You can tweak the row count and sleep length to make the most out of your CPU and memory. – Nick Zinger Sep 11 '19 at 13:09
0

Before digging further, have you considered logical replication? It's pretty sweet in Postgres. It may or may not be the right match for your requirements, but it's worth having in the mix as you consider options. As an example, you could have the archive table in a second Postgres instance and use logical replication to replicate all INSERT and UPDATE operations, but no deletes. No triggers required. (If you want a trigger-based solution, there are comprehensive solutions out there.) If you need to search the archive, it's in another table on another server, but you can use a Foreign Data Wrapper to make it visible from your primary server.

Some questions:

  • What kind of query requirements do you have on your archived data?

  • How will you delete the rows in the master table? It might be better to delete in batches in a loop?

  • What kind of numbers are you talking about deleting at one time? AUTOVACUUM isn't something you'll necessarily anticipate coming from SQL Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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