If you need to speed up your deletes, might I suggest not storing data that you don’t need. It is much faster, cheaper and better protects user privacy.
Modern SQL engines can parallelize the loop and the code is about enabling humans to be able to reason about what exactly is being done and to know that it is being done correctly.
At least in PG, that is explicitly not the case, unless I’m misunderstanding:
Similarly, a PL/pgSQL loop of the form FOR x IN query LOOP … END LOOP will never use a parallel plan, because the parallel query system is unable to verify that the code in the loop is safe to execute while parallel query is active.
Also you are not doing hard deletes on the main table but only on the _A table. As you can always retrieve the main table values from the _A table (which only deletes records based on audit_date when they have aged out) and that is not something that the user or even any of the service accounts will have access to do. (Only a specialized clean up job on a restricted account would have delete permissions on the _A tables and access to nothing else)
If you need to speed up your deletes, might I suggest not storing data that you don’t need. It is much faster, cheaper and better protects user privacy.
Modern SQL engines can parallelize the loop and the code is about enabling humans to be able to reason about what exactly is being done and to know that it is being done correctly.
At least in PG, that is explicitly not the case, unless I’m misunderstanding:
https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html
At any rate, I feel like it’s questionable design to have a user making row-by-row decisions on hard deletes.
The key part is while the query is active.
Also you are not doing hard deletes on the main table but only on the _A table. As you can always retrieve the main table values from the _A table (which only deletes records based on audit_date when they have aged out) and that is not something that the user or even any of the service accounts will have access to do. (Only a specialized clean up job on a restricted account would have delete permissions on the _A tables and access to nothing else)