Table B have several thousand records that references table A with a foreign key.
The application sends a request to delete the row from table A. It's important that the synchronous operation would be immediate and won't be at risk for timeouts.
If a cascade delete is used, and thus several thousands of records will be deleted as well, could it cause the deletion to take a long time? (not immediate).
Assuming that it could lead to a long deletion operation, what could be done as an alternative?
I've though perhaps to delete the record as deleted
and to perform the actual deletion in some background process (where the referencing rows would be deleted before the parent), but this approach feels rather error prone (since all of the existing queries will need to reference this new markedAsDeleted
column.
Any ideas?
markedAsDeleted
column would be as expensive direct delete. Ensure you have an index on the reference column, then it should work.