I'm looking at a herculean project before me.
I've already seen that my current set of FK relationships between < 100 tables and data footprint has yielded very large lookup times for the underlying referential integrity of the FKs to vet whether it can DELETE a row or not based on its FK relationships. That same underlying vetting is performing a lot of "SELECT 1" statements across the related tables and its squeezing timelines.
I have indexes on all the requisite columns participating in FKs. I don't think theres another way of speeding up lots of row DELETEs from the particular way I have the relationship implemented and I'm denying myself the "set session_replication_role = 'replica'" method so that I can be fully operational without the postgres userid (aka portable to cloud environments).
Correction. Actually, I'm thinking I can take out the system implementation of FK's and use constraint triggers instead. That way I'd get to relax the triggers and gut the vetting that, with my wield relationships and data, is time intensive.
Maybe even keep postgres as the owner of the constraint triggers so they can't be hacked by app users.
I'm guessing that, while not often, this approach happens to folks every now and then?
