Hacker News Reader

Comment by jack9 | Hacker News Reader

I would never use constraints ever again. Period. Billions of rows of data to POCs. It's interesting to note that AMZ data largely runs without constraints (the largest systems). Working in Southern California, where tenures run a few years before you end up somewhere else, everyone comes to a consensus in a decade. It's obvious to me that embedding business logic in SQL along with the server app and whatever presents data to third parties, is just making everything harder. Then you run into the inevitable of data changes and fighting the existing constraints. It's a waste of effort, but an obvious path-to-job-security for DBs.

> And of course you can get quite clever and fun here....create a function that checks if a function is a fibonacci number

The fact this is presented as a reasonable (trivial!) thing to embed in the DB screams, this is a terrible place to work.

View on HN

Sorry but to put it bluntly, this is a very ridiculous thing for you to stay. I tell everyone, it's easy to remove constraints, but it's very difficult to add it once your data has been polluted. Think of constraints like a canary in the mine for your data. If things are wrong or perhaps have changed, it alerts you. If you can expand it, you do so, if you have no choice, you can drop it. But always begin with constraints!

A lot of things that are really, really good ideas for most applications might not apply at Amazon scale. Most of us don't work at Amazon scale.

I agree on the fib constraint, though. Fun, but nothing you'd ever really do.

> embedding business logic in SQL along with the server app and whatever presents data to third parties, is just making everything harder

Not in many cases. In the past 15-20 years, think about how many application stacks have come and gone. We've had Postgres that whole time, and plsql. Core business logic in the DB never needs to be rewritten, no matter how many different front-end rewrites you go through.

With your last point, you're conflating language longevity with code longevity. Code in the DB changes as often as any other code: as often as the business requires it. The last company I worked for who used stored procedures had a change for those stored procedures with every frontend release.

And logic in the DB is inherently capable of only scaling as large a your DB cluster (i.e. not very), and can cause your DB cluster to scale prematurely (an event which incurs a lot of potential technical debt and additional monitoring).

I don't understand why. I see constraints doing the same job as objects to in programming: they keep the data and the rules for manipulating that data together, ensuring that the invariants are always respected.

If "you can't have a price <= 0" is a business rule then it should be enforced in all layers: in the UI because you want to warn the user they're trying to do something incorrect without sending obviously invalid data to the server, in the back-end because you don't want to write incorrect data to the database, and in the database because you don't know that someone else will not write "just a quick app" that will bypass your back-end and mess up the data.

Problem with constraints is that they reflect current business logic, but database stores past data. This is inherent mismatch and it'll cause conflicts. You can't add constraint which conflict with old data, yet you need to check that constraint for current data, therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones. If you have duplicate constraints, you must not forget to keep them in sync. I'm not saying that constraints are bad, database without constraints would be a very strange beast, even UTF-8 text is a constraint upon arbitrary byte strings, but it's a dangerous construct and must not be misused, otherwise it'll cause more harm than good.

> You can't add constraint which conflict with old data

Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward.

Also, the reason why you bother with "additional" constraints by implementing them on the database is because the database is typically the thing that can enforce them. Unless you go out of your way to roll your own single-write via queue or something.

I'm not sure I agree with your take-away since you can just drop constraints. Easy come, easy go. Meanwhile bad data is easy come and potentially a nightmare to fix.

>Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward.

Or you can just expand the constraint to superset of two, or figure out how the old data needs to be treated by new system and migrate it. There are many right options if you actually care about data integrity that are not as easy as not caring. Doesn't make it good general advice not to care.

Saying a feature of database that validates the data inside a database is dangerous is very much like saying seatbelts are dangerous because you can drawn. Anything can be misused, but there is a clear use case, because an ACID database provides a whole transactional, reliable and coherent application that can enforce rules no other layer in the system can, unless it reimplements the entire DB with sql validation and type checking.

> You can't add constraint which conflict with old data, yet you need to check that constraint for current data,

If there was a way to distinguish "old" and "new" data, you could write your constraint to honor the difference.

> therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones.

IMHO, if you must only have one set of constraints, put it in the data store.

The thing about constraints is that they provide pretty solid guarantees that let you better reason about the data at its base level.

You can't reason well about data that was "checked" by dozens of different versions of validations over time. What you have in that case is garbage in need of cleanup.

> I would never use constraints ever again

So you'll never again use a uniqueness or foreign key constraint?

Also, I don't know what pocmeans, bit it provides no insight into the problems you had and why it was ok to accept otherwise bad data.

> The fact this is presented as a reasonable (trivial!) thing to embed in the DB screams, this is a terrible place to work.

Yes, a cute example showing you that you can constrain by anything is indicative of how terrible a place this is! /s

Do you rely on file system permissions as a sanity check to your application code being correct? (If you don’t you probably are in the wrong job)

A filesystem is just a hierarchical key-value database.

Assuming you do believe in file permissions, why wouldn’t you use the additional sanity of validation available in a SQL database?

Reply on news.ycombinator.com