Hacker News Reader

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

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?

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.

The check constraint should only be applied when the column is updated so depends on your usage.

Thanks for the suggestion, I edited my post to provide an example of using domains with TEXT fields.

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

This detailed analysis?

    As you can see times are very similar – there are 
    differences between data types, but there is no clear 
    “winner", and I'm much more inclined to assume that 
    these differences come from random fluctuations of load 
    on my machine, than from anything else.

    ....
    This (I believe) proves that performance of all 4 
    datatypes is the same.

In other words, there is no difference between them in terms of performance. There is a slight increase in disk usage possible for CHAR, but really, that's used for fixed sized fields anyway (like product codes), so that's not an issue.

The idea that you should just blindly use TEXT fields is horrible advice. You should model your data accurately to make sure you can use your database to do its job - protect and store your data. Otherwise, why not just skip the pretenses and use a NoSQL storage engine.

If you need a TEXT field to store data that could be large, then do it. But don't make your "username" field a TEXT when VARCHAR(300) would do.

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

> using the standardized standards at the standard level

Because standards?

the standard is people are trained on standards

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

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.

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

Across languages? Across speed requirements? Across locations?

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

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

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

Isn't that what varchar basically is?

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.

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.

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!

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.

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

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.

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