Consider table partitioning. You can detach, save and delete partitions, and you can restore and attach partitions.
On 8 May 2023 12:24:06 CEST, Age Apache <age.apache@gmail.com> wrote:
I am designing a database for a web application. In the near future I will require past data for Audit, Security and Analysis purpose. But storing all that data will increase the database size. What are some well known approaches to archiving data for later use without increasing the database size?
One approach I am thinking of is compressed additive backup and restore i.e. copy the rows of the tables that will be required later and store them in a compressed form, and then delete those rows from the table. And when the data is required then restore them from the backup files in an additive way.
Is there an easy way to achieve this, say via pg_dump?
