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?