Efficiently prune postgresql tables
I have about 10 tables with over 2 million records and one with 30 million. I would like to efficiently delete old data from each of these tables.
My general algorithm:
- create a temporary table for each large table and populate it with new data.
- truncate source tables
- copy tmp data back to original tables using: "insert into originaltable (select * from tmp_table)"
However, the last step of copying data back takes longer than desired. I was thinking about dropping the original tables and making the temporary tables "permanent", but I'm losing my constraint / foreign key information.
If I delete from tables directly it takes much longer. Given that I need to preserve all foreign keys and constraints, are there faster ways to delete old data?
Thanks.
a source to share
The fastest process is likely to be as you drew:
- Copying new data to a temporary table
- Fall indices and foreign keys
- Delete old table
- Copy temporary table back to old table name
- Rebuild indexes and foreign keys.
There are execution suggestions in the Postgres manual that may or may not apply. In all fairness, it is significantly faster to drop a table than to dump millions of rows (since every delete is done by tuple by tuple), and significantly faster to insert millions of rows into a table without constraints or indexes (since every constraint must be checked and every index must be updated to each record, by removing all constraints, you limit this to one index row and one check for the constraint).
a source to share
The “standard” solution to these problems usually involves splitting your tables into the appropriate key, so that when you need to delete old data, you can simply delete the entire partition - by far the fastest delete you'll ever get.
However, partitioning in PostgreSQL is not as easy as some other databases - you have to manually move the data using triggers and there are caveats (e.g. global primary keys)
a source to share