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