Hacker News Reader

Comment by panzi | Hacker News Reader

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).

View on HN

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.

I’m guessing that must have been the 00s then. I haven’t seen anyone downplay database constraints for a very long time.

Sometime between 2005 and 2012 I think. It definitely was for a stretch of time where Rails people said that.

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.

Reply on news.ycombinator.com