Archive old data in Postgresql

Question

I'm currently expecting for somebody to advice me on the process which I'm gonna take forward for DB archiving.

I've database (DB-1) which has 2 very large tables, one table having 25 GB of data and another is 20 GB of data. Which cause major performance issues even I have indexes.

So, we considered to archive the old data with the below process,

1. Clone a new database (DB-2) from existing database (DB-1).
2. Delete the old data from DB-1, so it will have only the last 2 years records. In case If I need old data can connect DB-2.
3. Every month should move an old data from DB-1 to DB-2, and delete the moved rows from DB-1.

Answer 1

That is the wrong approach.

What you are looking for is partitioning.

You can create range partitions covering one year each. To remove old data all you need to do is to drop the partition for the year(s) no longer needed.

If you need to keep the data for some reasons, you can also just detach the partition from the table. Then the data is still "lying around", but would not show up in the (partitioned) table. You could query the (detached) partition directly to access that data. You could even move that (detached) partition to a slower harddisk to free up space on your fast disks if you have more than one.

But you might even see that partitioning alone might already improves performance, but that depends a lot on your queries.

Note that you should use Postgres 11 for that, as partitioning wasn't that sophisticated in older versions.

Answer 2

While you should no doubt upgrade your current version (I'd suggest moving away from the EDB system you are working on now, and going to community based Postgres 11) even if you can't upgrade, partitioning is still a much better answer than creating a second database.

By recreating your table as a set of partitions within the same database, you will be able to add/remove data in a much cleaner fashion, and it will make dealing with Vacuums much easier. Even in 9.5, you can take advantage of table inheritance to build out partitions by first adding partitions for incoming data, and then creating partitions at various intervals (probably monthly, since you want to run monthly cleanup) and moving the data into those partitions. This can be accomplished atomically with a series of INSERT INTO partition SELECT * FROM table WHERE <timestamp> style statements.

I suspect you can probably manage this yourself (you need basic sql and the ability to write simple triggers/functions... here is a link to the 9.5 docs), but if you need help, you can engage with one of the Postgres chat communities, or contact a support company if you want a deeper dive.