Deleting records from two-child tables in parallel (concurrently)

Question

I have a postgres database where I am using the pg_cron extension to automatically perform some database "maintenance" at night time - Specifically, my database has some "offers" in it with timestamps, so I move those records to archive tables if they are "expired", and then delete the original records from my primary tables.

Anyway, I have two-child tables that both have Foreign Keys that are referencing the Primary Key of the parent-table. Maybe this diagram will help:

pg_cron lets you use these "background_workers", which from my understanding of being a Java Developer, are like Threads and basically let you run SQL scripts (i.e. functions/procedures) in parallel/concurrently. Thus, I was trying to leverage this and perform as many of my INSERTS/DELETES scripts concurrently (if possible).

The problem I'm having now is that when my Cron job triggers some of my DELETE scripts ("script" == Postgres plpgsql procecure) to run for these three tables, I have it coded such that:

1. the child tables delete BEFORE parent (but they can run in-any order AND concurrently)
2. the parent tables deletes last (ALWAYS)

As far as I know, this avoids any weird FK-exception errors (like trying to delete from the Parent table first).

Anyway, because I am using the background workers, both the child-table delete scripts CAN AND WILL RUN CONCURRENTLY (as I stated above). I thought this was OK, but I see now, one of the scripts will run a delete on one of the child tables just fine - but whichever child table script runs slightly after, ends up never completing (the Job just shows "running" in pg_cron.job_run_details table).

Am I not able to delete concurrently from the child-tables that contain a FK reference to the PK in the same parent-table ?

As I depicted on the diagram, the FK properties for both tables are: MATCH FULL ON DELETE NO ACTION ON UPDATE NO ACTION

Also FYI, I did test altering the FK constraints and set the ON DELETE to "CASCADE" so I can only need 1 plpgsql Procedure but the damn Delete was too damn slow (if anyone has any tips on this maybe I can go back...)

Any help greatly appreciated!

Answer 1

I have no explanation for that pg_cron behavior — those two DELETE statements should be able to run in parallel just fine.

Try to run the two deletes by hand in two database sessions and see if you can reproduce the behavior.

Make sure that the foreign key column is indexed on both child tables, otherwise deleting from the parent will be very slow (which might explain your problems with cascading delete).

Deleting many rows takes time and resources, and it bloats the table. Consider using partitioning: then deleting all the data in a partition is as fast and easy as running DROP TABLE.