4 ms·

I have to disagree. You should always put limits on everything, as said nicely here [1]: "Put a limit on everything. Everything that can happen repeatedly put

by susi22 13y ago

I have to disagree. You should always put limits on everything, as said nicely here [1]: "Put a limit on everything. Everything that can happen repeatedly put a high limit on it and raise or lower the limit as needed. Block users if the limit is passed. This protects the service. Example is uploading files of logos for subreddits. Users figured out they could upload really big files and harm the system. Don’t accept huge text blobs either. Someone will figure out how to send you 5GB of text." Sure, you should ideally do this in your application code. But if there is multiple interfaces (such as a REST api etc) to your database then you have to remember to put them in place everywhere. I don't see a good reason to make a username field to be TEXT instead of a generous VARCHAR(300). If somebody wants to choose a longer username than that, he's probably malicious. It protects you with zero cost and allows you to make some user input sanitation mistakes (we're all humans) in your application code. [1] http://highscalability.com/blog/2013/8/26/reddit-lessons-learned-from-mistakes-made-scaling-to-1-billi.html http://highscalability.com/blog/2013/8/26/reddit-lessons-lea...

joevandyk 13y ago
You can put check constraints on a TEXT field to prevent bad data.
- deleted 13y ago
  [deleted]
  - deleted 13y ago
    [deleted]
- deleted 13y ago
  [deleted]
- Demiurge 13y ago
  Isn't that what varchar basically is?
  - jeltz 13y ago
    Yes, but with some minor caveats[1]. I prefer always using check constraints since then you get all length constraints in the same place in the table definition. 1. The SQL standard requires truncation of trailing excess spaces on inserts which overflow the limit. Additionally the limit must be less or equal to 10485760 which is less than the maximum length of a string which is 1GB.
  - radiowave 13y ago
    One difference is that changing the length of a varchar column in an existing table can be a major pain if you have other database objects that must be the same type. For example, any views which also carry this column will need to be dropped and recreated (or otherwise modified), or else Postgres won't allow you to make the change. Working with the text datatype and using check constraints on length makes this much easier.
dragonwriter 13y ago
> Sure, you should ideally do this in your application code. But if there is multiple interfaces (such as a REST api etc) to your database then you have to remember to put them in place everywhere. If its not a logical constraint on the data but a limit on input to prevent malicious actions, the right tradeoff to prevent malicious action may not be the same across all interfaces, and, in any case, even if it is its not clear that -- not being a logical constraint on the data -- it belongs in the schema in any case. > It protects you with zero cost No, as TFA, the detailed analysis linked in TFA, and the documentation point out, it is not "zero cost". CHAR(x) and VARCHAR(x) have a non-zero performance cost compared to VARCHAR and TEXT. It may be a justified cost, but its absolutely not zero cost.
- AsymetricCom 13y ago
  >the right tradeoff to prevent malicious action may not be the same across all interfaces right, but it is the same at the same place, the database, so that's where you should put that constraint. There's not a lot of conceptual load here. Put limits on the database so your database doesn't get knocked over. No, don't put limits in your client so your database doesn't get knocked over, put the limits for the database in the database. I don't see where the gap is here. Sounds like premature optimization to me. Yes it does matter that Postgre abstracts the standard SQL datatypes away in the backend, no it doesn't matter what the performance impact of that is. Why? because unless you're committed to this database backend, trying to make it run faster is a waste of effort. Lets Postgre do that for you. Do your job as a programmer and setup your database/schema right using the standardized standards at the standard level, then let the database do its job and setup the actual bits how it thinks is best.
  - dragonwriter 13y ago
    > using the standardized standards at the standard level Because standards?
    - AsymetricCom 13y ago
      the standard is people are trained on standards
Jonanin 13y ago
The article certainly doesn't advocate for removing any constraints; there are just much, much more flexible ways to accomplish them in postgres, some of which offer similar performance. A domain using a TEXT field and constraints is probably the most performant (and flexible).
AaronFriel 13y ago
Another thing is to plan for a solution that can expand. Don't use a data type that requires massive table rebuild times if you ever increase its size. If adding a column or expanding a field takes O(n) time, don't expect n to be small forever. If an operation takes greater than O(n) time, realize that you'll pay that price eventually. Basically, establish soft upper bounds. And as a P.S.: don't expect anything sanitized by your application layer to be an invariant. Your database and the rules enforced by it are the only real invariants. If an unexpected character in a name field will blow up your application, you should fix it in the database (varyingly difficul with many RDBMS solutions) or treat it as user input and sanitize/scrub it at the application layer (more common with NoSQL solutions). Ideally, do both!
VladRussian2 13y ago
>I have to disagree. You should always put limits on everything like any orthodoxy it should have a limit put on it. >But if there is multiple interfaces (such as a REST api etc) to your database then you have to remember to put them in place everywhere. multiple interfaces going directly to database - that's a much bigger problem that the rest pales before it. In particular, multiple updates of limits would constitute only minor share of headache causing updates and the multiple updates itself would be just a minor share of the whole headache.
- Pxtl 13y ago
  Good God, yes. I've worked on too many older projects where all the old business logic lived in the database, and all the new business logic lived in the application, and keeping them sorted out was a nightmarish pain. The defense for using horrible database-specific procedural languages for all that business code was that it meant we could have one back-end for all the front-ends. I've seen this pattern so many times, and it's pure agony.
deleted 13y ago
[deleted]
mbell 13y ago
> Sure, you should ideally do this in your application code. But if there is multiple interfaces (such as a REST api etc) to your database then you have to remember to put them in place everywhere. Whenever I've done this I've just separated out the model/db access layer into a module that is shared between projects that need it and built the validation into that layer. DRY.
- deleted 13y ago
  [deleted]
- kasey_junk 13y ago
  Across languages? Across speed requirements? Across locations?
rpedela 13y ago
Just define a column constraint (syntax might be wrong): CREATE TABLE t (col TEXT CHECK length(col) < 50); What is nice is that you just need to add/drop a constraint if you want to change the max value check rather than changing the data type which can result in a table rewrite depending on the scenario. You can also add a min length check as well using this method or regex check, etc. I think the author should have made this point rather than just glossing over it with "constraints, triggers are more flexible".
- Jonanin 13y ago
  Thanks for the suggestion, I edited my post to provide an example of using domains with TEXT fields.
- srisa 13y ago
  Doesn't the use of check constraints add an overhead and possibly negate the slight performance advantage that was gained by using text instead of varchar/char?
  - ddebernardy 13y ago
    Yeah. Technically, `foo varchar(n)` amounts to syntactic sugar for `foo varchar check(length(foo) <= n)`. What the benchmarks show, if anything, is that while the `check` involves negligible overhead per row, the overhead can eventually be enough to make a measurable difference. Not to mention, the Postgres actually manual says as much as the above, in its description of text types.
  - epo 13y ago
    The check constraint should only be applied when the column is updated so depends on your usage.
markrages 13y ago
I like to think I taught this lesson: http://www.reddit.com/r/reddit.com/128pc http://www.reddit.com/r/reddit.com/128pc
voltagex_ 13y ago
Wouldn't that kind of code live in a data layer that sits between the outside world and your database? So you may manipulate the database in multiple ways but they all go through the same/similar codepaths.
Sharlin 13y ago
As long as the limits are actually generous and based on actual user studies. So many horror stories about varchar(1000)s in places such as medical reports where it can be a matter of live and death that the users can include everything they think is relevant, without awkward abbreviated language that's easily misunderstood...
ds9 13y ago
As others have pointed out, you don't have to put length constraints in multiple places if you have DB access go thru a suitable layer or module, and this is generally (AFAIK) good architecture. Surprised someone hasn't pointed out, too, that catching this in code is faster/cheaper than a database hit and erroring back to the front end. (It's less of a difference with AJAX but significant in traditional server-side applications, of which there are more than you might think.)
- asdasf 13y ago
  I do have all my different applications go through a suitable layer to access the data. That layer is called postgresql. It stores all the data, and all the rules about what constitutes valid data. This way, there is never any chance of invalid data getting in from one of the half dozen applications written in various languages forgetting one of the hundreds of rules.

by susi22 13y ago

joevandyk 13y ago

You can put check constraints on a TEXT field to prevent bad data.

deleted 13y ago
[deleted]
- deleted 13y ago
  [deleted]
deleted 13y ago
[deleted]
Demiurge 13y ago
Isn't that what varchar basically is?
- jeltz 13y ago
  Yes, but with some minor caveats[1]. I prefer always using check constraints since then you get all length constraints in the same place in the table definition. 1. The SQL standard requires truncation of trailing excess spaces on inserts which overflow the limit. Additionally the limit must be less or equal to 10485760 which is less than the maximum length of a string which is 1GB.
- radiowave 13y ago
  One difference is that changing the length of a varchar column in an existing table can be a major pain if you have other database objects that must be the same type. For example, any views which also carry this column will need to be dropped and recreated (or otherwise modified), or else Postgres won't allow you to make the change. Working with the text datatype and using check constraints on length makes this much easier.

dragonwriter 13y ago

> Sure, you should ideally do this in your application code. But if there is multiple interfaces (such as a REST api etc) to your database then you have to remember to put them in place everywhere. If its not a logical constraint on the data but a limit on input to prevent malicious actions, the right tradeoff to prevent malicious action may not be the same across all interfaces, and, in any case, even if it is its not clear that -- not being a logical constraint on the data -- it belongs in the schema in any case. > It protects you with zero cost No, as TFA, the detailed analysis linked in TFA, and the documentation point out, it is not "zero cost". CHAR(x) and VARCHAR(x) have a non-zero performance cost compared to VARCHAR and TEXT. It may be a justified cost, but its absolutely not zero cost.

AsymetricCom 13y ago
>the right tradeoff to prevent malicious action may not be the same across all interfaces right, but it is the same at the same place, the database, so that's where you should put that constraint. There's not a lot of conceptual load here. Put limits on the database so your database doesn't get knocked over. No, don't put limits in your client so your database doesn't get knocked over, put the limits for the database in the database. I don't see where the gap is here. Sounds like premature optimization to me. Yes it does matter that Postgre abstracts the standard SQL datatypes away in the backend, no it doesn't matter what the performance impact of that is. Why? because unless you're committed to this database backend, trying to make it run faster is a waste of effort. Lets Postgre do that for you. Do your job as a programmer and setup your database/schema right using the standardized standards at the standard level, then let the database do its job and setup the actual bits how it thinks is best.
- dragonwriter 13y ago
  > using the standardized standards at the standard level Because standards?
  - AsymetricCom 13y ago
    the standard is people are trained on standards

Jonanin 13y ago

The article certainly doesn't advocate for removing any constraints; there are just much, much more flexible ways to accomplish them in postgres, some of which offer similar performance. A domain using a TEXT field and constraints is probably the most performant (and flexible).

AaronFriel 13y ago

Another thing is to plan for a solution that can expand. Don't use a data type that requires massive table rebuild times if you ever increase its size. If adding a column or expanding a field takes O(n) time, don't expect n to be small forever. If an operation takes greater than O(n) time, realize that you'll pay that price eventually. Basically, establish soft upper bounds. And as a P.S.: don't expect anything sanitized by your application layer to be an invariant. Your database and the rules enforced by it are the only real invariants. If an unexpected character in a name field will blow up your application, you should fix it in the database (varyingly difficul with many RDBMS solutions) or treat it as user input and sanitize/scrub it at the application layer (more common with NoSQL solutions). Ideally, do both!

VladRussian2 13y ago

>I have to disagree. You should always put limits on everything like any orthodoxy it should have a limit put on it. >But if there is multiple interfaces (such as a REST api etc) to your database then you have to remember to put them in place everywhere. multiple interfaces going directly to database - that's a much bigger problem that the rest pales before it. In particular, multiple updates of limits would constitute only minor share of headache causing updates and the multiple updates itself would be just a minor share of the whole headache.

Pxtl 13y ago
Good God, yes. I've worked on too many older projects where all the old business logic lived in the database, and all the new business logic lived in the application, and keeping them sorted out was a nightmarish pain. The defense for using horrible database-specific procedural languages for all that business code was that it meant we could have one back-end for all the front-ends. I've seen this pattern so many times, and it's pure agony.

deleted 13y ago

[deleted]

mbell 13y ago

> Sure, you should ideally do this in your application code. But if there is multiple interfaces (such as a REST api etc) to your database then you have to remember to put them in place everywhere. Whenever I've done this I've just separated out the model/db access layer into a module that is shared between projects that need it and built the validation into that layer. DRY.

deleted 13y ago
[deleted]
kasey_junk 13y ago
Across languages? Across speed requirements? Across locations?

rpedela 13y ago

Just define a column constraint (syntax might be wrong): CREATE TABLE t (col TEXT CHECK length(col) < 50); What is nice is that you just need to add/drop a constraint if you want to change the max value check rather than changing the data type which can result in a table rewrite depending on the scenario. You can also add a min length check as well using this method or regex check, etc. I think the author should have made this point rather than just glossing over it with "constraints, triggers are more flexible".

Jonanin 13y ago
Thanks for the suggestion, I edited my post to provide an example of using domains with TEXT fields.
srisa 13y ago
Doesn't the use of check constraints add an overhead and possibly negate the slight performance advantage that was gained by using text instead of varchar/char?
- ddebernardy 13y ago
  Yeah. Technically, `foo varchar(n)` amounts to syntactic sugar for `foo varchar check(length(foo) <= n)`. What the benchmarks show, if anything, is that while the `check` involves negligible overhead per row, the overhead can eventually be enough to make a measurable difference. Not to mention, the Postgres actually manual says as much as the above, in its description of text types.
- epo 13y ago
  The check constraint should only be applied when the column is updated so depends on your usage.

markrages 13y ago

I like to think I taught this lesson: http://www.reddit.com/r/reddit.com/128pc http://www.reddit.com/r/reddit.com/128pc

voltagex_ 13y ago

Wouldn't that kind of code live in a data layer that sits between the outside world and your database? So you may manipulate the database in multiple ways but they all go through the same/similar codepaths.

Sharlin 13y ago

As long as the limits are actually generous and based on actual user studies. So many horror stories about varchar(1000)s in places such as medical reports where it can be a matter of live and death that the users can include everything they think is relevant, without awkward abbreviated language that's easily misunderstood...

ds9 13y ago

As others have pointed out, you don't have to put length constraints in multiple places if you have DB access go thru a suitable layer or module, and this is generally (AFAIK) good architecture. Surprised someone hasn't pointed out, too, that catching this in code is faster/cheaper than a database hit and erroring back to the front end. (It's less of a difference with AJAX but significant in traditional server-side applications, of which there are more than you might think.)

asdasf 13y ago
I do have all my different applications go through a suitable layer to access the data. That layer is called postgresql. It stores all the data, and all the rules about what constitutes valid data. This way, there is never any chance of invalid data getting in from one of the half dozen applications written in various languages forgetting one of the hundreds of rules.