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.apa...@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?