Обсуждение: Native Foreign Keys housekeeping time intensive for Relational Model

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/

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2021-03-26 at 14:00 -0400, Jacque Edmund wrote:
>> 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.

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

Deletes being slow often means that you don't have an index on the
referencing column(s).  PG requires you to have an index on the referenced
columns, but not on the other side of the FK relationship.

            regards, tom lane

On Sat, 2021-03-27 at 10:43 -0400, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> 
> > On Fri, 2021-03-26 at 14:00 -0400, Jacque Edmund wrote:
> > > 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.
> 
> > I would be surprised if correctly indexed foreign keys would slow deletes down so that
> > the performance becomes intolerable.
> 
> Deletes being slow often means that you don't have an index on the
> referencing column(s).  PG requires you to have an index on the referenced
> columns, but not on the other side of the FK relationship.

Exactly, that's what I wanted to say: I suspect that such indexes are missing
or not created correctly.  Otherwise, the performance should be good.

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