From: Jacque Edmund <jacqueedmund@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Native Foreign Keys housekeeping time intensive for Relational Model
Date: Fri, 26 Mar 2021 14:00:08 -0400
Message-ID: <CANOmo02GKKLsGbszLtAzLLX--Ab6d-9Bp51bYDC3dfRhe8dgvA@mail.gmail.com> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jacqueedmund@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Native Foreign Keys housekeeping time intensive for Relational Model
In-Reply-To: <CANOmo02GKKLsGbszLtAzLLX--Ab6d-9Bp51bYDC3dfRhe8dgvA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox