We are moving our application from one-instance to multiple instances (all connected to the same Postgres database) in order to increase resiliency of one of our services, and I am now facing a synch issue between multiple instances of the app all trying to delete "old" data once a day (basically, "delete all rows since 30 days ago or more").
I am considering using an advisory lock in Postgres as a synchronisation token so that only one instance actually performs the deletion.
So this is how I envision it (assuming all instances run the exact same code): At, say, 4 AM each morning all 3 instances will try to acquire the advisory lock; only one will succeed initially, and it will perform the deletion of old data, while the other app instances will wait on the lock.
Once the first instance is done, the other instances will, in turn, acquire the lock but will find that there a no more eligible rows and return.
Please let me know if you see any issue with the above approach.