3 ms·

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: `ALT

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 5y ago
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 5y ago
  > 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.
  - masklinn 5y ago
    Can't edit anymore but after checking up on https://www.postgresql.org/docs/current/sql-altertable.html 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.

brandur 5y ago

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 5y ago
> 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.
- masklinn 5y ago
  Can't edit anymore but after checking up on https://www.postgresql.org/docs/current/sql-altertable.html 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.