Performance Issues with parallel FOREIGN KEYS ... ON DELETE CASCADE

[jw1u1]

Summary

DELETE FROM pipelines WHERE id = 1; runs for hours due to too many FOREIGN KEYS ... ON DELETE CASCADE in the Database, some of them are references between the same Tables.

Steps to reproduce

create 1500 builds
explain analyze DELETE FROM pipelines WHERE id = 1;

Expected results

Much faster Execution

Actual results

even after the pipeline_id was deleted from builds, the FK Trigger takes 3.3 seconds of 3.4 total Execution Time:
QUERY PLAN | Trigger for constraint successful_build_outputs_rerun_of_fkey on builds: time=3299.810 calls=1

Additional context

work around:
psql -c " select 'delete from builds where id='||id||';' from builds where pipeline_id in (select id from pipelines where name like 'old%');" concourse -At | psql concourse

PS
trigger based dynamic DDL as in on_pipeline_insert and on_pipeline_delete is a source of performance issues, too.

Triaging info

• Concourse version: v6.3.0
• Browser (if applicable):
• Did this used to work?

[taylorsilva]

This is on an old version of Concourse (6.3.0).

Should compare results on v7.0+

Which version of Postgres are you on? That may also be helpful, though I'm not sure how much performance would change between versions of postgres.

[jw1u1]

concourse is version: v7.8.2 now.
Postgres is stillversion 11.
Still tons of foreign keys with ON DELETE CASCADE.
This isn't how a database schema should look alike, regardless of the DB Version:
Builds for example is referenced by:
TABLE "build_comments" CONSTRAINT "build_comments_build_id_fkey" FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "build_image_resource_caches" CONSTRAINT "build_image_resource_caches_build_id_fkey" FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "build_pipes" CONSTRAINT "build_pipes_from_build_id_fkey" FOREIGN KEY (from_build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "build_pipes" CONSTRAINT "build_pipes_to_build_id_fkey" FOREIGN KEY (to_build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "build_resource_config_version_inputs" CONSTRAINT "build_resource_config_version_inputs_build_id_fkey" FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "build_resource_config_version_outputs" CONSTRAINT "build_resource_config_version_outputs_build_id_fkey" FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "builds" CONSTRAINT "builds_rerun_of_fkey" FOREIGN KEY (rerun_of) REFERENCES builds(id) ON DELETE CASCADE
TABLE "containers" CONSTRAINT "containers_build_id_fkey" FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE SET NULL
TABLE "jobs" CONSTRAINT "jobs_latest_completed_build_id_fkey" FOREIGN KEY (latest_completed_build_id) REFERENCES builds(id) ON DELETE SET NULL
TABLE "jobs" CONSTRAINT "jobs_next_build_id_fkey" FOREIGN KEY (next_build_id) REFERENCES builds(id) ON DELETE SET NULL
TABLE "jobs" CONSTRAINT "jobs_transition_build_id_fkey" FOREIGN KEY (transition_build_id) REFERENCES builds(id) ON DELETE SET NULL
TABLE "next_build_pipes" CONSTRAINT "next_build_pipes_from_build_id_fkey" FOREIGN KEY (from_build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "resource_cache_uses" CONSTRAINT "resource_cache_uses_build_id_fkey" FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "successful_build_outputs" CONSTRAINT "successful_build_outputs_build_id_fkey" FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE CASCADE
TABLE "successful_build_outputs" CONSTRAINT "successful_build_outputs_rerun_of_fkey1" FOREIGN KEY (rerun_of) REFERENCES builds(id)
TABLE "worker_artifacts" CONSTRAINT "worker_artifacts_build_id_fkey" FOREIGN KEY (build_id) REFERENCES builds(id) ON DELETE SET NULL