pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNative Foreign Keys housekeeping time intensive for Relational Model
4+ messages / 3 participants
[nested] [flat]
* Native Foreign Keys housekeeping time intensive for Relational Model
@ 2021-03-26 18:00 Jacque Edmund <jacqueedmund@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jacque Edmund @ 2021-03-26 18:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Native Foreign Keys housekeeping time intensive for Relational Model
@ 2021-03-27 10:24 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Jacque Edmund <jacqueedmund@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2021-03-27 10:24 UTC (permalink / raw)
To: Jacque Edmund <jacqueedmund@gmail.com>; pgsql-admin@lists.postgresql.org
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Native Foreign Keys housekeeping time intensive for Relational Model
@ 2021-03-27 14:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-03-27 14:43 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Jacque Edmund <jacqueedmund@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Native Foreign Keys housekeeping time intensive for Relational Model
@ 2021-03-28 04:49 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2021-03-28 04:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jacque Edmund <jacqueedmund@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-03-28 04:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-26 18:00 Native Foreign Keys housekeeping time intensive for Relational Model Jacque Edmund <jacqueedmund@gmail.com>
2021-03-27 10:24 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-27 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-28 04:49 ` Laurenz Albe <laurenz.albe@cybertec.at>
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