I had a database with no RI and it was a nightmare, filled with bugs.
So we added RI and it was awesome, and the bugs did go away with time.
But then we became dogmatic and had to have RI for everything, including things like maintaining local RI lookup tables for non local data. This just bogged us down so much that we couldn’t move.
Today, we use RI strictly within a single database. For foreign keys to remote databases, we assume the remote database knows what it’s talking about and that the incoming data has already been checked.
This works really well for us and in particular sharding is not a problem (sharding being a design decision not an implementation decision IMO)
Someone said elsewhere that Rails does the RI so the DB doesn’t need to. I don’t use Rails, and I don’t bet, but I’d wager that it’s faster to do RI inside Postgres than inside Rails.
The idea that we should throw out ALL RI is just dogmatic nonsense. I should know :)
Rails in 2010 was just coming around to adopting foreign keys, and the rubygem Foreigner made it happen before it was a proper rails feature.
The idea of rails enforcing unique constraints and FKs back then was... Optimistic. The design constraints assumed exactly one rails instance would access the database at any given time to ensure consistency.
So we added RI and it was awesome, and the bugs did go away with time.
But then we became dogmatic and had to have RI for everything, including things like maintaining local RI lookup tables for non local data. This just bogged us down so much that we couldn’t move.
Today, we use RI strictly within a single database. For foreign keys to remote databases, we assume the remote database knows what it’s talking about and that the incoming data has already been checked.
This works really well for us and in particular sharding is not a problem (sharding being a design decision not an implementation decision IMO)
Someone said elsewhere that Rails does the RI so the DB doesn’t need to. I don’t use Rails, and I don’t bet, but I’d wager that it’s faster to do RI inside Postgres than inside Rails.
The idea that we should throw out ALL RI is just dogmatic nonsense. I should know :)