3 ms·

I've worked on exactly one project where performance concerns lead to removing Fkeys. The compromise we came to was to enforce them in dev and qa in order to c

by __jal 7y ago

I've worked on exactly one project where performance concerns lead to removing Fkeys. The compromise we came to was to enforce them in dev and qa in order to catch bugs, and relax them in prod. I still strongly believe that database constraints are a developer's best friend, in that you can trivially make your data structures fight back against misuse. This makes several classes of bugs obvious. But like anything, there are times they are not optimal. I'll also say I think there are very few cases where a small performance advantage outweighs the costs, and would be hesitant to head down this path with a team less competent than the folks at Github.

WorldMaker 7y ago
I've worked on projects where adding foreign keys increased performance tremendously. A good relational database can take advantage of them in all sorts of ways in execution planning. Even scenarios such as some types of sharding, a relational database sometimes can use foreign keys for useful data proximity information, to better co-locate "families" of records together. You need to know your database engine, how normalized you are or are not, how appropriate your data is for its relational model. It's not always your FK constraints that are the bottleneck you might think they are. Sometimes their "slowness" is hiding a problem elsewhere in your schema, a bad shard or an index that could be better or a key-value pool with soft version controlled enums pretending to relational data. Sometimes "optimal" isn't a simple spectrum but a checklist of trade-offs and no "right" answer.