10 ms·

Database constraints: The last line of defense

87 points by anarazel 9y ago

esaym 9y ago
These are good, I've forgotten about some of them as well. As to: EXCLUDE USING gist ( id WITH =, period WITH && );" What the heck?
- waffle_ss 9y ago
  It's so you can't define two billings for the same period (say, the same month). To prevent you from double-billing someone. && is the range operator that checks for overlap.[1] You have to use gist indexes to do this. And in fact this page doesn't mention it but you have to enable the btree_gist extension with: CREATE EXTENSION btree_gist; before the snippet above will work. [1]: https://www.postgresql.org/docs/current/static/functions-range.html#RANGE-OPERATORS-TABLE https://www.postgresql.org/docs/current/static/functions-ran...
  - tokenizerrr 9y ago
    They're very nifty, as you can use this to easily prevent all kinds of date range overlaps.
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 ago
  Exactly. 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 ago
  It seems the constraints are mainly useful while developing. You probably never should hit constraints in a production system.
  - justherefortart 9y ago
    Nah, 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 ago
      What's laughable here?
    - jimktrains2 9y ago
      Why laughable? Different levels can support different amounts of context in logging and error handing and ultimately the message the end user sees.
    - SigmundA 9y ago
      Why is defense in depth a laughable concept?
  - GiorgioG 9y ago
    Constraints are useful when people are trying to manipulate the data outside the bounds of your application. Think ETL, “fixing” data, etc.
    - acdha 9y ago
      Also 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 ago
    With 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.
- jack9 9y ago
  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.
  - ams6110 9y ago
    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.
    - falcolas 9y ago
      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).
  - deleted 9y ago
    [deleted]
- stouset 9y ago
  A 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 ago
  You 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 ago
    Did 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 ago
      No. At least not yet. Rolling aggregations are broken in InfluxDB aswell.
  - nopzor 9y ago
    Thanks 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 ago
  What, 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 ago
    Bad 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 ago
      How 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 ago
        The data is not dropped silently but very loudly, immediately. Unless of course you are using Mysql, well then...
        xapata 9y ago
        So you stop the program immediately and allow no more transactions while you debug? Unlikely. Keep the data. Log that it's weird.
        stouset 9y ago
        What 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 ago
  Can all the constrains of your data really be expressed via postrgres constraints? Should they be? What is the balance?
Slansitartop 9y ago
Does MySQL support CHECK constraints yet? Last time I used it it silently ignored them if they were in the DDL.
- moolcool 9y ago
  Good heavens, then why even have them?
  - Slansitartop 9y ago
    I figured it was so MySQL wouldn't barf so badly on DDL written for other databases. That's the only justification I can think of, and it's a pretty terrible one. I have a pretty low opinion of it because of this.
    - Something1234 9y ago
      It already barfs on a fair amount of valid SQL. Autoincrement vs auto_increment anybody?
      - jimktrains2 9y ago
        Actually there isn't* a standard for auto increment columns. * There is one for an identity column, which often ends up being similar `GENERATED BY DEFAULT AS IDENTITY` https://blog.2ndquadrant.com/postgresql-10-identity-columns/ https://blog.2ndquadrant.com/postgresql-10-identity-columns/ https://dba.stackexchange.com/a/161356 https://dba.stackexchange.com/a/161356 and I don't believe mysql supports that syntax https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html https://dev.mysql.com/doc/refman/5.7/en/create-table-generat...
  - jimktrains2 9y ago
    Everytime I deal with mysql I ask why there is mysql. It's terrible when compared to postgres at consistency, correctness, functionality (e.g. check constraints, cte, and window functions, pg_tgrm, full text search, index flexibility and that's without thinking about PostGIS, pg-routing and many of the other excellent 3rd party extensions) and error handling and nearly equivalent when it comes for basic queries that mysql even supports. Edit: forgot transactional ddls, real ones, not the ones mysql has in the pipeline.
rbranson 9y ago
Errors in memory are temporary, errors on disk are permanent. Don't approach them both the same way.
ams6110 9y ago
Check constraints and foreign key constraints will save your bacon. I don't like triggers though, as a rule. Not that they are always the wrong thing, but its easy to kill performance with them, and just generally having "behind the scenes" side-effects triggered by other operations can be surprising, to say the least.
- mdpopescu 9y ago
  You might want to look at [1]; you can send a message to a queue and process that independently. It breaks transactional boundaries though (if you abort the transaction the message is still in the queue). Using transactional queues will probably not help because then the transaction has to wait for the message to be processed so you're back to square one. [1] http://www.madeiradata.com/service-broker-asynchronous-triggers/ http://www.madeiradata.com/service-broker-asynchronous-trigg...
peterburkimsher 9y ago
"Want to ensure tweets aren’t longer than 140 characters, make sure you set your tweet column to varchar(140) and not to text." This is fine if all your users are content with ASCII. As soon as double-width characters from Chinese or emoji start interfering, the database will limit some users to 70 characters. It's ok to make restrictions, but be sure to fully understand how they'll affect your users.
- chronid 9y ago
  In PostgreSQL varchar uses actual characters (as specified by the database character encoding), not bytes, IIRC. The same thing should be true for (at least recent versions of) MySQL.
combatentropy 9y ago
One thing I like about SQL constraints is that they're so much shorter than in a procedural language. Has anyone developed an elegant system for bubbling them up to the user interface? It seems you either have to write the same constraints again in JavaScript or build some massive list that maps ugly database errors to friendly user errors.
- mdpopescu 9y ago
  C# MVC has an attempt at creating some validation and automagically promoting that to the UI but it's (of course) limited to stuff like "this field is numeric" or "this field is required". Anything more complex needs duplication. I would assume that something like node.js or meteor have it easier, since they're using the same language in both layers; I believe there are also attempts at tools that automatically translate other languages to JS but I haven't used any of them.
siscia 9y ago
Once I was building a small application, I didn't want to use a whole database such as postgres that is amazing but does require some maintenance, so I settled for using just Redis. It was fine but the lack of constrains and structure was killing me making everything harder, but I liked the zero maintenance things... So I build RediSQL[1] a module to provide SQL capabilities to Redis to get the best of both worlds: stability, easy maintenance, velocity, constrains and structure in data. [1]: https://github.com/RedBeardLab/rediSQL https://github.com/RedBeardLab/rediSQL
krylon 9y ago
Yup. I still remember the day I found out about constraints, and I became kind of angry nobody had told me about this before. I can imagine one can get carried away and shoot themselves in the foot when being careless, but all in all, I think constraints are a valuable tool to protect the integrity of my data.

87 points by anarazel 9y ago

These are good, I've forgotten about some of them as well. As to: EXCLUDE USING gist ( id WITH =, period WITH && );" What the heck?

waffle_ss 9y ago
It's so you can't define two billings for the same period (say, the same month). To prevent you from double-billing someone. && is the range operator that checks for overlap.[1] You have to use gist indexes to do this. And in fact this page doesn't mention it but you have to enable the btree_gist extension with: CREATE EXTENSION btree_gist; before the snippet above will work. [1]: https://www.postgresql.org/docs/current/static/functions-range.html#RANGE-OPERATORS-TABLE https://www.postgresql.org/docs/current/static/functions-ran...
- tokenizerrr 9y ago
  They're very nifty, as you can use this to easily prevent all kinds of date range overlaps.

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 ago
Exactly. 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 ago
It seems the constraints are mainly useful while developing. You probably never should hit constraints in a production system.
- justherefortart 9y ago
  Nah, 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 ago
    What's laughable here?
  - jimktrains2 9y ago
    Why laughable? Different levels can support different amounts of context in logging and error handing and ultimately the message the end user sees.
  - SigmundA 9y ago
    Why is defense in depth a laughable concept?
- GiorgioG 9y ago
  Constraints are useful when people are trying to manipulate the data outside the bounds of your application. Think ETL, “fixing” data, etc.
  - acdha 9y ago
    Also 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 ago
  With 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.
jack9 9y ago
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.
- ams6110 9y ago
  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.
  - falcolas 9y ago
    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).
- deleted 9y ago
  [deleted]
stouset 9y ago
A 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 ago
You 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 ago
  Did 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 ago
    No. At least not yet. Rolling aggregations are broken in InfluxDB aswell.
- nopzor 9y ago
  Thanks 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 ago
What, 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 ago
  Bad 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 ago
    How 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 ago
      The data is not dropped silently but very loudly, immediately. Unless of course you are using Mysql, well then...
      - xapata 9y ago
        So you stop the program immediately and allow no more transactions while you debug? Unlikely. Keep the data. Log that it's weird.
        stouset 9y ago
        What 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 ago
Can all the constrains of your data really be expressed via postrgres constraints? Should they be? What is the balance?

Slansitartop 9y ago

Does MySQL support CHECK constraints yet? Last time I used it it silently ignored them if they were in the DDL.

moolcool 9y ago
Good heavens, then why even have them?
- Slansitartop 9y ago
  I figured it was so MySQL wouldn't barf so badly on DDL written for other databases. That's the only justification I can think of, and it's a pretty terrible one. I have a pretty low opinion of it because of this.
  - Something1234 9y ago
    It already barfs on a fair amount of valid SQL. Autoincrement vs auto_increment anybody?
    - jimktrains2 9y ago
      Actually there isn't* a standard for auto increment columns. * There is one for an identity column, which often ends up being similar `GENERATED BY DEFAULT AS IDENTITY` https://blog.2ndquadrant.com/postgresql-10-identity-columns/ https://blog.2ndquadrant.com/postgresql-10-identity-columns/ https://dba.stackexchange.com/a/161356 https://dba.stackexchange.com/a/161356 and I don't believe mysql supports that syntax https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html https://dev.mysql.com/doc/refman/5.7/en/create-table-generat...
- jimktrains2 9y ago
  Everytime I deal with mysql I ask why there is mysql. It's terrible when compared to postgres at consistency, correctness, functionality (e.g. check constraints, cte, and window functions, pg_tgrm, full text search, index flexibility and that's without thinking about PostGIS, pg-routing and many of the other excellent 3rd party extensions) and error handling and nearly equivalent when it comes for basic queries that mysql even supports. Edit: forgot transactional ddls, real ones, not the ones mysql has in the pipeline.

rbranson 9y ago

Errors in memory are temporary, errors on disk are permanent. Don't approach them both the same way.

ams6110 9y ago

Check constraints and foreign key constraints will save your bacon. I don't like triggers though, as a rule. Not that they are always the wrong thing, but its easy to kill performance with them, and just generally having "behind the scenes" side-effects triggered by other operations can be surprising, to say the least.

mdpopescu 9y ago
You might want to look at [1]; you can send a message to a queue and process that independently. It breaks transactional boundaries though (if you abort the transaction the message is still in the queue). Using transactional queues will probably not help because then the transaction has to wait for the message to be processed so you're back to square one. [1] http://www.madeiradata.com/service-broker-asynchronous-triggers/ http://www.madeiradata.com/service-broker-asynchronous-trigg...

peterburkimsher 9y ago

"Want to ensure tweets aren’t longer than 140 characters, make sure you set your tweet column to varchar(140) and not to text." This is fine if all your users are content with ASCII. As soon as double-width characters from Chinese or emoji start interfering, the database will limit some users to 70 characters. It's ok to make restrictions, but be sure to fully understand how they'll affect your users.

chronid 9y ago
In PostgreSQL varchar uses actual characters (as specified by the database character encoding), not bytes, IIRC. The same thing should be true for (at least recent versions of) MySQL.

combatentropy 9y ago

One thing I like about SQL constraints is that they're so much shorter than in a procedural language. Has anyone developed an elegant system for bubbling them up to the user interface? It seems you either have to write the same constraints again in JavaScript or build some massive list that maps ugly database errors to friendly user errors.

mdpopescu 9y ago
C# MVC has an attempt at creating some validation and automagically promoting that to the UI but it's (of course) limited to stuff like "this field is numeric" or "this field is required". Anything more complex needs duplication. I would assume that something like node.js or meteor have it easier, since they're using the same language in both layers; I believe there are also attempts at tools that automatically translate other languages to JS but I haven't used any of them.

siscia 9y ago

Once I was building a small application, I didn't want to use a whole database such as postgres that is amazing but does require some maintenance, so I settled for using just Redis. It was fine but the lack of constrains and structure was killing me making everything harder, but I liked the zero maintenance things... So I build RediSQL[1] a module to provide SQL capabilities to Redis to get the best of both worlds: stability, easy maintenance, velocity, constrains and structure in data. [1]: https://github.com/RedBeardLab/rediSQL https://github.com/RedBeardLab/rediSQL

krylon 9y ago

Yup. I still remember the day I found out about constraints, and I became kind of angry nobody had told me about this before. I can imagine one can get carried away and shoot themselves in the foot when being careless, but all in all, I think constraints are a valuable tool to protect the integrity of my data.