I am trying to figure out the best way to schedule a data deletion on our production database.
First option I can see is by a trigger. I am not a PostgreSQL developer, but since I need to maintain our growing database, how can I create trigger to delete say data older than X days or retain only X number of transactions/events? I'm looking to see a parameterize trigger for us to define X number of days or X number of transactions.
The draw back I see using the trigger is that it is event driven on a CRUD statements. Meaning during insert, delete, or update, trigger will be fired off (that is how as far as I do understand a general trigger is in any of database engine, unless otherwise PostgreSQL has a different way of implementing this). Why do I say this is a draw back? --> Coz I want to trigger the deletion on during off peak hours.
Or is there any other brilliant idea/implementation aside from trigger? I did try the scheduling a job thru pgAdmin but the draw back is I can't script the job (like how we usually do it on MS SQL) to create and deploy it. We have almost a thousand machines to deploy this (using our deployment script/technology), so creating a pgAdmin job will be a pain in the butt for us if scripting the job is not possible.
Our environment is Windows OS with pgAdmin 9.1. The implementation I am thinking of should consider when Windows OS is swapped with Linux/Unix environment in furure, so I am not leaning towards Windows scheduler job to execute batch script, etc.