Comment by panzi
2 days ago
So what are your thoughts on constraints then? Foreign keys? Should that only be handled by the application, like Rails does (or did, haven't used in a long time).
2 days ago
So what are your thoughts on constraints then? Foreign keys? Should that only be handled by the application, like Rails does (or did, haven't used in a long time).
I don't think of those as business logic, per se. They're just validity checks on what the data should look like before it's written to disk - they're not actionable in the way L/N is. That being said, constraints usually end up being duplicated outside the db anyway, but having them where the data rests (so you don't have to assume every client is using the correct constraint code) makes sense.
I see. Further I have used triggers to automatically populate log tables or aggregate statistics on write. Why do I need fast statistics? For API limits. Customers have N free operations per months and such, so I have to query that on every operation. Do you consider these things as business logic that don't belong in the database?
Rails fully supports constraints and encourages you to use them.
You can either execute SQL in your migration or use add_check_constraint.
Back when I used Rails the sentiment was: You don't need foreign keys, this is all handled by ActiveRecord.
You still use constraints even if you put all your business logic in stored procedures.
What happens to FKs when you've to partition/shard the db? At a certain scale, they actually hinder the inserts.
FK Constraints on partitioned tables has been a solved problem for years for Postgres. MySQL still doesn’t support them, unfortunately.
For sharding, Vitess kind of supports them; Citus fully supports them.
You’re correct that they do impact performance to an extent, but as a counter argument, if your data is incorrect, it doesn’t matter how quickly you wrote it.
FKs are nothing special. It's just more INSERTs/UPDATEs/DELETEs. If you can't have a few more DMLs in your transactions in your sharded DB then you've already got scaling problems.
Really, FKs are typically implemented internally by RDBMSes as TRIGGERs that do what you expect FKs to do, which means they really are nothing more than syntactic sugar.