Message-ID: <fb45b9f1134ad17b944eb073ac8523f37f4199a9.camel@cybertec.at>
Subject: Re: Native Foreign Keys housekeeping time intensive for Relational
 Model
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jacque Edmund <jacqueedmund@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Sat, 27 Mar 2021 11:24:30 +0100
In-Reply-To: 
 <CANOmo02GKKLsGbszLtAzLLX--Ab6d-9Bp51bYDC3dfRhe8dgvA@mail.gmail.com>
References: 
	<CANOmo02GKKLsGbszLtAzLLX--Ab6d-9Bp51bYDC3dfRhe8dgvA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/fb45b9f1134ad17b944eb073ac8523f37f4199a9.camel%40cybertec.at>
Precedence: bulk

On Fri, 2021-03-26 at 14:00 -0400, Jacque Edmund wrote:
> 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?

I think that is all wrong.  Foreign keys are a good thing, and the idea to get rid of them out
of performance considerations is usually premature "optimizazion" that does more ill than good.

It is not necessarily the right thing to index all columns that are pert of a foreign key,
for keys with several columns, you need a multi-column index.
You can use the query in this blog post [1] to look for missing indexes.

I would be surprised if correctly indexed foreign keys would slow deletes down so that
the performance becomes intolerable.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

  [1]: https://www.cybertec-postgresql.com/en/index-your-foreign-key/