8 ms·
Every single time I've seen someone say something like, "We don't need constraints; we have validations", they've had to clean up their data — when it turned ou
by rosser 9y ago
Every single time I've seen someone say something like, "We don't need constraints; we have validations", they've had to clean up their data — when it turned out validations aren't enough.
Please do future-you a favor: use constraints.
- SuperGoodJared 9y agoExactly. Validations are a really good tool for validating input and displaying validation issues to users. They are not a great tool for ensuring that relational data stays valid forever.
- deleted 9y ago[deleted]
- maxxxxx 9y agoIt seems the constraints are mainly useful while developing. You probably never should hit constraints in a production system.
- justherefortart 9y agoNah, have them at every level. Javascript, server side code, then business layer code, then data layer (why not, we're doing in the DB as well), then in the db itself. Laughable.
- maxxxxx 9y agoWhat's laughable here?
- jimktrains2 9y agoWhy laughable? Different levels can support different amounts of context in logging and error handing and ultimately the message the end user sees.
- SigmundA 9y agoWhy is defense in depth a laughable concept?
- GiorgioG 9y agoConstraints are useful when people are trying to manipulate the data outside the bounds of your application. Think ETL, “fixing” data, etc.
- acdha 9y agoAlso plain bugs. If your database changes less rapidly than the client code it’s a great way to avoid simple mistakes taking a ton of work to repair. This is especially important if you have a large team and your best developers don’t work on the “boring” parts.
- anarazel 9y agoWith concurrency it's certainly reasonable to hit constraints in production. Another session deletes something, another inserts a row that attempt to reference the deleted row. Or you have multiple users registering with the same account name at the same time. Etc.
- maxxxxx 9y agoThat makes sense. I guess most of the stuff I do doesn't do much concurrent database access.
- semiquaver 9y agoYou might think that, but the most common cause of constraint violation in the web-programming world is unintentional user double clicks setting up a race between simultaneous requests.
- Something1234 9y agoCause they get frustrated when your app isn't fast or responsive enough.
- vbsteven 9y agoYou would be surprised to see how many (often older) people double click everything by default. Explorer folders, desktop shortcuts, web forms, it is all the same to them. I’ve run a number of event sites for luxury car brands and it was stunning how many double-click form submissions we received on those. Often just milliseconds apart.
- maxxxxx 9y agoIt's not only old.people who do the double click thing. I see the same with a lot of our sales reps when I watch them use our stuff.
- daveFNbuck 9y ago
- DrScump 9y agoEven if there is perfect enforcement of constraint criteria at deployment, code has been known to be changed after deployment. Sometimes.
- jack9 9y agoI 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.
- ams6110 9y agoA 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.
- falcolas 9y agoWith 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).
- deleted 9y ago[deleted]
- stouset 9y agoA million times this. Cleaning up bad data after the fact can actually be impossible. When it is possible, it can be annoying, tedious, and easy to fuck up. All of this can be avoided by an astonishingly small amount of forward thinking. What columns should not be nullable? What are the maximum sizes needed for strings, numbers, and binary data? Which columns or sets of columns should only ever be inserted once? What columns point to other rows, should they be required, and what should happen if one side or the other has deletion attempted? Constraints should cover basic data consistency guarantees. Validations are fine for business logic and business requirements, since those might change more frequently.
- gsich 9y agoYou start to miss constraints when you use a database system which has none. I used Postgres to store various sensor data (some Raspberry, ESP and other "mini" stuff). Then tried InfluxDB, mainly because Grafana supported it, while Postgres did not (it does now!). Now there are sensor malfunctions, due to old age, no proper connection, bugs or whatever. A Temperature sensor which shows 85 °C while only being capable of 50 °C? You could capture that in the application, but this is cumbersome. Just do it in the database, constraint "temperature < 50" and you are done. To top it off, deleting wrong data in InfluxDB is a real pain.
- jadbox 9y agoDid you move back to Postgres? What are some things you did to bring some of the things you needed from Influx into PG (like rolling aggregations)?
- gsich 9y agoNo. At least not yet. Rolling aggregations are broken in InfluxDB aswell.
- nopzor 9y agoThanks for the grafana shoutout. Glad you find the Postgres support in it useful. Out of curiosity, re: constraints in your example couldn't you easily do something like removeAboveValue(50) when retrieving your data from these faulty sensors? It seems like basic constraints, windowing, etc should be part and parcel of most time series databases?
- xapata 9y agoWhat, you'd rather drop data on the floor? Your scientists would prefer you keep all measurements, even the bad ones. Sometimes those "bad" data actually have information. Why not have two databases, one with all data without constraints and another, or even many which impose various constraints?
- stouset 9y agoBad data of this for is almost always the result of bugs, not of real data failing to conform to your modeling. In this case, yes, you should absolutely be dropping it on the floor (and alerting the devs with an exception) rather than creating a mess that needs cleaning up later.
- xapata 9y agoHow would you know if you'd made a false negative if you drop all "bad" data? It's more of a mess when you realize you've accidentally been losing records for the last few years.
- mixmastamyk 9y agoThe data is not dropped silently but very loudly, immediately. Unless of course you are using Mysql, well then...
- xapata 9y agoSo you stop the program immediately and allow no more transactions while you debug? Unlikely. Keep the data. Log that it's weird.
- stouset 9y agoWhat is your software development background? I ask because it would not have once occurred to me in the past fifteen years of software development that “enforcing database constraints” in any way implies shutting everything down and stopping transactions until the cause of a violation is rooted out. If you’re approaching it from the position of collecting raw data from sensors or something, that might make sense. But from the perspective of a webapp, allowing duplicate (or empty) usernames or failing to keep foreign key relations valid often violate basic assumptions of soundness that the application is based upon. In these cases, the right answer is to drop the data on the floor (loudly) so a developer can look at it. Inconveniencing a customer once here is infinitely better than inconveniencing them constantly due to the site being buggy or broken as a result of the invalid data, and prevents needing to migrate the data in the future which can actually be impossible to do correctly in some cases.
- blain_the_train 9y agoCan all the constrains of your data really be expressed via postrgres constraints? Should they be? What is the balance?