With partitioning in PG11, we can't have foreign keys referencing a partitioned table (PG12 supports this). Since we'll minimally support PG11 for a while, we'll have to deal with this and implement other strategies for foreign keys.
In addition to implementing cascading deletes in #201872 (closed), we'll have to find ways to
check/enforce referential integrity and
deal with referential inconsistencies.
In this issue, we discuss ways to maintain referential integrity in the absence of foreign keys referencing partitioned tables.
I think that we can also extend that approach and alter the reverse triggers to also support cascading deletes when necessary (normal triggers that delete rows instead of constraint ones).
As expected, this solution adds some overhead, and, if we like it, we should test it also with our data set.
Open question, looking forward to sharding (@pbair): would such a solution work with citus? Are triggers properly propagated to shards?
Good question, but looks like the answer is no though. Seems citus doesn't support distributed triggers, although that can be worked around by creating the trigger manually on worker nodes individually. It does have a concept of distributed functions, which are pushed down to the nodes were they execute on local data, but no easy way to trigger them automatically.
But, citus does support normal FK between tables, provided the two records live on the same physical server, either by FK from reference table -> reference table, distributed table -> reference table, or distributed table -> distributed table where the distribution column is part of the columns in the constraint, and they have the same colocation parameter.
I think for most relationships we have in the app currently, at least following the current namespace tenancy model, db foreign keys would work as distributed data would live on the same node, or point at a reference table. Good articles though, something to test for sure.
What I find interesting is that triggers operating on the non-partitioned tables, i.e. the "check if referenced record exists", run primary key lookups on the partitioned table, but without the partitioning key:
This would mean scanning all partitions (index lookups), unless we also have the partitioning key available in the referencing table (which in our case is unlikely, at least at the beginning). I wonder how much of an impact this has...
With the approach outlined in #201873 (comment 314138572), we would have triggers to check any modifications - i.e. for new data and updates coming into a table, we would be sure about referential integrity.
Should we also think about existing data? Adding a normal FK today would check any existing data, too and only allow you to add the FK, if existing data is consistent, too.