MIME-Version: 1.0
From: Jacque Edmund <jacqueedmund@gmail.com>
Date: Fri, 26 Mar 2021 14:00:08 -0400
Message-ID: 
 <CANOmo02GKKLsGbszLtAzLLX--Ab6d-9Bp51bYDC3dfRhe8dgvA@mail.gmail.com>
Subject: Native Foreign Keys housekeeping time intensive for Relational Model
To: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000001db8d905be744ebd"
Archived-At: 
 <https://www.postgresql.org/message-id/CANOmo02GKKLsGbszLtAzLLX--Ab6d-9Bp51bYDC3dfRhe8dgvA%40mail.gmail.com>
Precedence: bulk

--0000000000001db8d905be744ebd
Content-Type: text/plain; charset="UTF-8"

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?

--0000000000001db8d905be744ebd
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I&#39;m looking at a herculean project before me.=C2=A0 =
=C2=A0<div><br></div><div><div>I&#39;ve already seen that my current set of=
 FK relationships between &lt; 100 tables and data footprint has yielded ve=
ry large lookup times for the underlying referential integrity of the FKs t=
o vet whether it can DELETE a row or not based on its FK relationships.=C2=
=A0 That same underlying vetting is performing a lot of &quot;SELECT 1&quot=
; statements across the related tables and its squeezing timelines.=C2=A0=
=C2=A0</div></div><div><br></div><div>I have indexes on all the requisite c=
olumns participating in FKs.=C2=A0 I don&#39;t think theres=C2=A0another wa=
y of speeding up lots of row DELETEs from the=C2=A0 particular way I have t=
he relationship implemented and I&#39;m denying myself the &quot;set sessio=
n_replication_role =3D &#39;replica&#39;&quot; method=C2=A0so that I can be=
 fully operational without the postgres userid=C2=A0(aka portable to cloud =
environments).</div><div><br></div><div>Correction.=C2=A0 =C2=A0Actually, I=
&#39;m thinking I can take out the system implementation of FK&#39;s and us=
e constraint=C2=A0triggers instead.=C2=A0 That way I&#39;d get to relax the=
 triggers and gut the vetting that, with my wield relationships and data, i=
s time intensive.</div><div><br></div><div>Maybe even keep postgres as the =
owner of the constraint=C2=A0triggers so they can&#39;t be hacked by app us=
ers.</div><div><br></div><div>I&#39;m guessing that, while not often,=C2=A0=
 this approach happens to folks every now and then?</div><div><br></div><di=
v><br></div></div>

--0000000000001db8d905be744ebd--