S513: As a developer, I want to use reference tables with Foreign Keys to store data like zip codes or coupons without impacting performance by more than X% of my workload.

Rachel Casali commented: Implementation Work -Enable FK constraints without an index in the referencing table

Mandating a secondary index on both sides incurs a penalty on write latencies on the referencing table. Users who know that the referenced table is infrequently modified would prefer to trade off a higher latency on updating the referenced table (with full scans on the referencing table) instead of a higher latency on updating the referencing table. To achieve this we would remove the requirement of an index on the referencing table.

This also impacts PG compatibility (e.g., Hibernate test suite) as customers can receive error messages when attempting to tune performance using ORMs.

Example of workaround needed https://github.com/cockroachdb/movr/pull/83#issuecomment-485531698

Today a constraint "a REFERENCES b" requires an index on both the a and b sides. In postgres only an index on the b side is required. We should change CockroachDB to enable constraints without an index on the a side (referencing) like pg.

cockroach-staging / hello-world

S513: As a developer, I want to use reference tables with Foreign Keys to store data like zip codes or coupons without impacting performance by more than X% of my workload. #501