Re: Native Foreign Keys housekeeping time intensive for Relational Model

On Sat, 2021-03-27 at 10:43 -0400, Tom Lane wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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