We are having a Postgres table which is filling very drastically (almost 5GB/day). We wanted to purge the Table for every 2 months old record.
We want to implement a Datastage job for auto purging the table. Kindly suggest the possible ways to achieve this task.
I suggest to use a partitioned table.
Partition the table by Month (like 2023-01, 2023-02,...) using PARTITION BY RANGE (month-column) at the create table statement. Periodically remove older partitions usig the ALTER TABLE DETACH PARTITION command. You could also partition by week or so.
Before using partitioned tables, make sure to read the docs and understand how it works and where the pros and cons are. Partitioned tables are fast for reading data, if the select statement includes the partitioning column (like the month), but you generally pay some more overhead during query planning and execution. In a data warehouse environment, this doesn't really matter. If you're in a real-time transactional database, this should be considered.
As far as I know, Postgress does not dynamically create new partitions, so this must be managed, e.g. by the software that writes the data. If it's DataStage, build a job that fetches a distinct list of months from the source and create new partitions if needed. Run this job exclusively before writing new data into the table. (Oracle for examle can add new partitions dynaically.)
Not sure about compression methods in PostgreSQL, but in general, I also suggest to enable compression if you have many text columns. Compression will spare a lot i/o traffic and table space. In most cases the CPU time for compression can be neglected.
