Postgres: Boundless `Text` and Back Again | Hacker News Reader

Postgres: Boundless `Text` and Back Again

brandur.org

99 pointstimf35 comments

If you're at all likely to be defining views that contain any of these columns, I would strongly advocate using text with the check constraint, rather than varchar. The reason being: if you ever need to increase the column's maximum length, in the case of varchar you will be prohibited from making this change because it violates the data type dependency between the table and the view.

To cope with this, in the same transaction that you're increasing the length of the varchar, you will also need to drop and recreate all such dependent views (and any other views that depend upon those views, etc).

This is not fun.

In comparison: increase the maximum length of the check constraint, job done.

This approach makes a lot more sense. The real problem here is input validation, not type mismatch.

Arguably, this could extend to real numerical data (use NUMERIC everywhere as a default), but the performance impacts of that are far more severe.

The way I like to solve this is to still define the columns as `TEXT`, but just add a custom named CHECK constraint that validates the maximum length.

Eg: `ALTER TABLE users ADD CONSTRAINT username_max_len CHECK LENGTH(username) <= 100 NOT VALID;`

The NOT_VALID option skips checking the existing data, so the migration is quasi-instant.

That way the constraint can easily be swapped out by removing the old one and adding a new one, and you have maximum flexibility going forward.

This also doesn't require an index rebuild, and better models what's actually happening internally.

I used to recommend `CHECK` constraints as well, but was directed towards Postgres' very good intelligence around being able to relax a `VARCHAR(n)` -> `VARCHAR(>n)` as basically a no-op, so I simplified here somewhat just because VARCHARs are easier to use, and make much more succinct examples in write ups like this one.

The index rebuild is still a bit of an open question — I wouldn't be surprised if Postgres had some optimizations there as well to not need it in instances where it's not necessary, but I might be wrong on that one.

> I used to recommend `CHECK` constraints as well, but was directed towards Postgres' very good intelligence around being able to relax a `VARCHAR(n)` -> `VARCHAR(>n)` as basically a no-op

I don’t think that’s the case when you have views on the table though.

The locking could also be a concern, altering the table will almost certainly need an exclusive write lock, altering a contraint may not (to check). Even if the operation is very short, the need to block every read coming after the DDL can be quite impactful to service.

Can't edit anymore but after checking up on https://www.postgresql.org/docs/current/sql-altertable.html only `FOREIGN KEY` constraints can get by with a lock below ACCESS EXCLUSIVE. So constraint or column makes no difference to the locking, sadly.

We had similar experiences, and now use varchar 99, varchar 999, varchar 9999. The purpose of the "9" digits is to highlight that it's our plausible guesstimate for "good enough" and can be increased later, rather than any definite unchangeable limit.

Nice — I like the idea of the "9" convention. It serves to catch the eye (9s are positively _harsh_ compared to other numbers) and show that it's meant more as a ballpark rather than a precisely chosen number for this specific field.

When thinking about length limits, always try to come up with use cases for the data that limit the length.

If you have a legal name, you might want to write that person a letter (an invoice, injunction, notification of breach, whatever), so it must fit into the address field. If you allow a 2000 character name, it likely won't fit. Same for street names and the likes.

Not fitting a full name into a field sucks, but at least it gives the one filling it the choice how to shorten it.

If the data goes into some kind of webpage, does it even render properly if it's very large? If it goes into a hover text, or in a box that's too small for a proper scroll bar, it becomes unreadable it too big.

If text becomes really big (and it might be gigabytes if there's no limit), storage cost becomes an issue.

From this perspective, you basically never want unlimited TEXT fields anywhere, at least not exposed to customers. Maybe it's OK for internal some internal, at least if you can yell at your users if they abuse it too much.

At a minimum, you can think about whether the text should fit on a single line (edited with a text box) or can it be multiple lines (a text area).

That doesn’t help you decide how long a single line can be, though. Also, does the single-line text field prohibit newlines and how do you do that properly?

I recently discovered the joy of PostgreSQL via Supabase.io (which is very awesome too, anyone else using it?)

The text thing is awesome but I never thought I could be so excited about Policies. It makes everything feel so much more relaxing knowing you can’t accidentally give someone access to something they shouldn’t be able to edit.

I am on this journey as well. We are using constraints that check the length of a text field instead of using varchar. I like the idea of setting a few standard lengths though.

A word of caution when using domains: many SaaS offerings that replicate your Postgres database to a data warehouse do not support domains.

With regards to the CHECK constraints, I actually used to recommend those because relaxing them seemed to be operationally safer, but re-reading the Postgres docs, it didn't seem like there was a particularly big advantage to them because Postgres is pretty smart changing types efficiently where possible. Is there another benefit I missed?

Interesting on the data warehouse note — we haven't actually implemented the domain idea yet (what's in the article is more musing about it), but it's good to know about this possible downside.

Another advantage of using `varchar(n)` rather than a constraint is that the limit is visible in `information_schema.columns` and `pg_attribute`, so tools using this sort of introspection have a better chance of doing something useful.

(Incidentally, distinguishing between "text" and "varchar" the way you do in the article can be confusing, because in PostgreSQL plain `varchar` instead of `varchar(N)` is another way of getting an unconstrained character type.)

>varchar(200) for shorter-length strings like names, addresses, email addresses, etc...The idea is to pick liberal numbers that are easily long enough

I'm not convinced 200 is liberal enough for some of that.

For email, a 64 character local part is RFC compliant, then 1 for the '@', and the domain can be 253 characters.

Probably similar edge cases for addresses.

I note pretty explicitly in there that you should pick your own numbers.

The idea I was trying to get across is that of order of magnitude tiers so that you don't end up with VARCHARs of a hundred random lengths.

Sure. People just tend to grab stuff from articles written by people with your depth of expertise, so I thought it was worth noting.

Yeah, you'd need a longer field for maximum correctness for sure. Reminds me of the RFC 822 regex for validating an email address [1] (clocking in at a full page, although now there are simpler ones you can use).

I tend to think that there is a point where you trade off some correctness for pragmatism. It is true that you could have a user with a 250 character email address, but in a lot of years I've never really seen a legitimate one, and the likelihood is that if you do see one then it's someone doing something weird as opposed to a legitimate customer.

Email is actually one that you might want to just leave as `TEXT` and make sure you're validating with a package, which is something you might want to do anyway. However, I still think it serves as a useful example because most people have a good feel for about how long an email address is.

---

[1] http://www.ex-parrot.com/~pdw/Mail-RFC822-Address.html

Max length of email address is 254 per the email address RFC. https://www.rfc-editor.org/errata_search.php?rfc=3696

Yes in fact in many domains there are standards that can be ransacked for appropriate domain sizes. The problem is that it's a chore to dig these out, data architects would sooner just guess some numbers.

On the other hand, 200 characters is already so long that it might not render correctly, particularly on mobile.

I agree with the “order of magnitude” text size limit. I tend to put 10x of what I would consider the expected average. A bit like cryptographers put 2x the number of rounds they think may be eventually broken.

Why should the database be the input sanitizer, though? Intermediary systems can break or degrade before it reaches PostgreSQL.

> Why should the database be the input sanitizer, though?

More in the realm of traditional business IT than what HN is focused on, the database is often the point of integration between several different systems, with views and triggers providing the means of abstraction at the interface boundry.

Of course ideally all such systems would only ever profer perfectly valid data, but in practice ...

Chesterton's Fence strikes again.

It's almost like those people back in the 60s and 70s had thought about this.

I thought of the DOMAIN solution as I was reading the article (although I didn't know DOMAIN was the right mechanism), but the naming convention I had in mind mimicked Rust/stdint and put the character limit in the name—something like varchar200 or char200.

I believe that Postgres, following the SQL standard, silently truncates longer strings when using varchar(n), which is probably not a desirable result if you're using it for validation.

You might be thinking of older versions on MySQL. PostgreSQL never had this behavior of silently altering on write. It goes in the database as provided, or it raises an error. Period.

> PostgreSQL never had this behavior of silently altering on write. It goes in the database as provided, or it raises an error. Period.

That is not actually true but the GP might have gotten their wires crossed a bit between MySQL's behaviour of strait truncating and the SQL Standard's behaviour (which postgres implements) of truncating trailing spaces if they go beyond the specified length limit.

So per-spec "xxx" will fail to insert in a varchar(2), but "xx " will succeed returning just "xx" when fetching.

> I believe that Postgres, following the SQL standard, silently truncates longer strings when using varchar(n)

AFAIK Postgres has always triggered an error in that case, though it was apparently not documented in 7.1 here is the documentation from 7.2:

> Both of these types can store strings up to n characters in length. An attempt to store a longer string into a column of these types will result in an error

What you may be referring to is the following note:

> unless the excess characters are all spaces, in which case the string will be truncated to the maximum length. (This somewhat bizarre exception is required by the SQL standard.)

So the SQL standard requires silently stripping (truncating spaces) from the end until the value fits or no more spaces can be truncated.

My goodness... This has nothing to do with postures and just bad security models when programming.

> My goodness... This has nothing to do with postures and just bad security models when programming.

Author here. Could you elaborate with some specifics?

The person you’re replying to is being needlessly condescending, but I suspect what they’re getting at is that having a mindset of “what possible undesirable/malicious inputs are possible from this web form?” really precludes this scenario. Using unbounded text fields for a web form is a serious rookie move, as it’s a very obvious form of undesirable/malicious input. Point being, if you mess that up, what more subtle and dangerous inputs might you also be leaving yourself open to?

You should always validate input to a database. Data ought to be validated at the edge, which in this case is the http endpoints. You should not rely on internal services (a database) to do validation

One of the dangers in switching to high level languages is that people have lost any sense of data size. In C you almost always specify a max string width because that is the easiest easy to deal with strings. While C has many shortcomings, forcing the responsible programmer to deal with string width is a good thing.

Honestly as a stripe user, this sort of unvalidated input terrifies me. Stripe deals with money. All inputs ought to be validated. I figured the company would spend a lot of engineering time making sure data was cleaned and couldn't escape like this.

Today it's a string width issue. Tomorrow it's a sql injection. I'm surprised stripe let you publish this.

What does string width have to do with SQL injection? You use parameterized queries, not just rely on your validation to reject quotes, right?

I do... Yes. I follow best practices like parameterized queries, string width validation, password hashing via memory intensive hash functions etc. These are all basic beat practices. When a company admits to not doing one, it makes me worry about the others.

This is the result of build fast and break things mentality which is toxic

Reply on news.ycombinator.com