search

citusdata / citus

Distributed PostgreSQL as an extension
https://www.citusdata.com
GNU Affero General Public License v3.0
10.12k stars 651 forks source link

Reduce overhead of building foreign constraint graph #7581

Open JelteF opened 2 months ago

JelteF commented 2 months ago

Currently on every foreign key change, each backend has to rebuild their foreign key graph. This can be quite some overhead when using schema based sharding with many tables and many constraints, because we do a sequence scan over pg_constraint.

Two ideas on how to improve this:

  1. Lazily build the parts of the graph that are needed. For schema based sharding it's expected that there are many small and completely disjoint graphs (one for each schema/colocation group). It does not make much sense to build all these little graphs, if you're only interested in the graph for a single schema.
  2. Don't invalidate the full graph on a foreign key change. I'm not sure if this is even really possible.
JelteF commented 2 months ago

Another option could be to stop caching this graph and only get the information we need on the fly. But that would need some investigation into when we actually use this information and how expensive it is to get it.