[HN Gopher] Postgres: Boundless `Text` and Back Again
___________________________________________________________________
Postgres: Boundless `Text` and Back Again
Author : timf
Score : 79 points
Date : 2021-09-10 16:59 UTC (6 hours ago)
(HTM) web link (brandur.org)
(TXT) w3m dump (brandur.org)
| hermanradtke wrote:
| 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.
| brandur wrote:
| 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.
| mjw1007 wrote:
| 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.)
| the_duke wrote:
| 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.
| brandur wrote:
| 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.
| masklinn wrote:
| > 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.
| espadrine wrote:
| 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.
| radiowave wrote:
| > 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 ...
| perlgeek wrote:
| 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.
| tyingq wrote:
| >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.
| marsdepinski wrote:
| Max length of email address is 254 per the email address RFC.
| https://www.rfc-editor.org/errata_search.php?rfc=3696
| brandur wrote:
| 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.
| tyingq wrote:
| Sure. People just tend to grab stuff from articles written by
| people with your depth of expertise, so I thought it was
| worth noting.
| brandur wrote:
| 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
| tragomaskhalos wrote:
| 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.
| iammisc wrote:
| My goodness... This has nothing to do with postures and just bad
| security models when programming.
| brandur wrote:
| > _My goodness... This has nothing to do with postures and just
| bad security models when programming._
|
| Author here. Could you elaborate with some specifics?
| wswope wrote:
| 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?
| iammisc wrote:
| 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.
| kam wrote:
| What does string width have to do with SQL injection? You
| use parameterized queries, not just rely on your validation
| to reject quotes, right?
| radiowave wrote:
| 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.
| pphysch wrote:
| 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.
| jonplackett wrote:
| 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.
| tuatoru wrote:
| Chesterton's Fence strikes again.
|
| It's almost like those people back in the 60s and 70s had thought
| about this.
| actuallyalys wrote:
| 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.
| jph wrote:
| 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.
| brandur wrote:
| 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.
___________________________________________________________________
(page generated 2021-09-10 23:01 UTC)