Why We Should Not Use Database Foreign Keys: Insights from a GitHub Engineer
A GitHub engineer argues that database foreign keys should be avoided because they shift integrity responsibilities to the DB, incur performance overhead, and cause complications during online schema migrations, illustrating the points with parent‑child tables, gh‑ost, and pt‑online‑schema‑change tools.
A recent Hacker News discussion revived an older blog post that sparked debate about whether database foreign keys (FK) should be used. The author, a GitHub engineer, firmly argues against using FK in production systems.
First, relying on FK makes applications depend on the database to enforce data integrity and cascading deletes, which hampers the ability to split or extract data independently and forces code changes whenever the schema evolves.
Second, FK introduces performance penalties: each insert or delete must perform additional index lookups, adding overhead even though indexes are often required anyway.
Third, FK complicates online schema migrations. In a typical parent‑child (P & C) scenario, renaming tables with tools like gh-ost moves the FK together with the table, preventing the new child table from referencing the renamed parent without breaking consistency, especially when asynchronous migration methods are used.
The author links to his earlier article on Facebook OSC ideas and to Percona’s pt-online-schema-change documentation for further reading.
Original source: https://github.com/github/gh-ost/issues/331#issuecomment-266027731 (author Shlomi Noach, GitHub employee, translated by 高可用架构).
High Availability Architecture
Official account for High Availability Architecture.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.