Environment: AWS RDS Aurora for PostgreSQL 15 hosting tables that support scientific research. The development environment predominantly uses JPA with Hibernate.
Years of neglect have allowed mission tables to accumulate hundreds of millions of rows of excess data. The developers and the customer decided we must delete all rows older than 75 days. Table partitioning was briefly considered but discarded because of the effort needed to refactor the codebase.
I proposed the straight-forward course of action: delete by batches from the victim tables.
The solution seemed obvious:
For candidate tables: - Determine timestamp column iwith the most relevant value and call it the Discriminator. - Delete any row whose Discriminator value is 60 days older than the Discriminator, with a LIMIT of 50000, - Get the results of the batch by querying GET DIAGNOSTICS. That value held aside for later use. - COMMIT the delete transaction - Loop to the top and continue deleting batches until there are no more rows older than 60 days.
- Before running a test, I ensure that the Discriminator column is indexed
I write a procedure to accomplish all this work but it persists in returning a error to the effect that a COMMIT is not valid in a block tht tries to DELETE data.
Has anybody seen this before?
Yup, when putting the COMMIT in a DO block, which isn't allowed.
Is there a solution?
Loop using bash. In a similar case like this, I first looked for the oldest day's data in the tables, then did something like this bash pseudo-code:
