4 ms·

> The solution was to grow the slug column to 512 characters and retry. No, the solution is to always use `text` as your column type, and use a check constrain

by jeffdn 4y ago

> The solution was to grow the slug column to 512 characters and retry. No, the solution is to always use `text` as your column type, and use a check constraint if you need to enforce a limit. It's much easier and safer to alter a check constraint than it is to change a column type live in production. The `text` type and `varchar(x)` are identical under the hood, `text` takes up no more space on disk.

masklinn 4y ago
Note: that's true for postgres and sqlite (which ignores the length limit entirely as they discovered anyway), not necessarily for other database systems.
- coolsunglasses 4y ago
  How convenient then that he's comparing PostgreSQL and SQLite then!
SigmundA 4y ago
I thought as of PG 9.2 expanding a varchar column was metadata only operation and therefore low overhead. I know in SQL Server there are two issues with doing varchar(max) for everything and increasing a columns size is metadata only. First indexes have a limit of 900 byte values and will fail at runtime if you index a column with no max length and insert a value larger than 900 bytes. PG seems to have this issue as well but the limit is 2712 bytes. Second the query planner makes use of the size to determine how much memory to pre-allocate for the query, with unlimited length field it assume something like 4096 bytes and wastes working memory if your values are not actually that size. Not sure if PG has the second issue, having a max value defined is valuable information to a database engine along with other type information such as UUID's only taking 16 bytes instead of 36 bytes.
- masklinn 4y ago
  > PG seems to have this issue as well but the limit is 2712 bytes. Note that this is only for bytes indexes. Hash is not so limited. Tho at this sort of sizes it seems unlikely such simple indexes are of much use. I guess the range matching abilities of btrees could be but that seems unlikely, a 2700 bytes path-like datum is a lot.
luhn 4y ago
For Postgres, there's a strong case to be made VARCHAR(n) is preferable to TEXT+check constraint. As you note, they're stored the same on disk. But for a varchar, you can increase the length with just this: ALTER TABLE a ALTER COLUMN b TYPE VARCHAR(n) This only requires ACCESS EXCLUSIVE for a fraction of a second while the metadata is updated. Whereas updating a CHECK constraint will require a full scan of the table. If you do it naively, this locks the table for the entire scan. If you want to avoid that, it's three steps: ALTER TABLE a DROP CONSTRAINT b_c, ADD CONSTRAINT b_c CHECK (length(b) < n) NOT VALID; COMMIT; ALTER TABLE a VALIDATE CONSTRAINT b_c; So as long as you're only increasing the length (which in my own experience is a safe assumption), VARCHAR is much easier to work with.
- jeffdn 4y ago
  Yep, that's fair enough. In my experience, however, in the vast, vast majority of cases, folks either don't actually care about the length, or they should be using business logic in code to do the validation, rather than waiting for the database to yell at them, invalidate their transaction, etc.
  - HideousKojima 4y ago
    >they should be using business logic in code to do the validation I trust myself to do this, I don't trust my coworkers and future devs writing applications for the same database to follow the same constraints in their code though. Constraints at the DB level keep your data clean(er) even if some other devs tries to shit it up.
  - layer8 4y ago
    The benefit is that you can treat the database schema as the single source of truth as to what data is valid. Otherwise what tends to happen is that different code components will have different ideas of what exactly is valid. This is also important because databases tend to be longer-lived than the code that accesses them.
  - msbarnett 4y ago
    If I had a dollar for every time I saw someone doing application-layer-only validation of data and end up being surprised that some data that wasn't supposed to be valid ended up in the database only to later blow up in their face, I'd be a fairly well-off man. In practice, declaratively creating data constraints at schema-creation time has a much much higher success rate than trying to imperatively enforce data constraints at write time, in my experience. The missed cases and bugs always come back to haunt you.

by jeffdn 4y ago

masklinn 4y ago

Note: that's true for postgres and sqlite (which ignores the length limit entirely as they discovered anyway), not necessarily for other database systems.

coolsunglasses 4y ago
How convenient then that he's comparing PostgreSQL and SQLite then!

SigmundA 4y ago

I thought as of PG 9.2 expanding a varchar column was metadata only operation and therefore low overhead. I know in SQL Server there are two issues with doing varchar(max) for everything and increasing a columns size is metadata only. First indexes have a limit of 900 byte values and will fail at runtime if you index a column with no max length and insert a value larger than 900 bytes. PG seems to have this issue as well but the limit is 2712 bytes. Second the query planner makes use of the size to determine how much memory to pre-allocate for the query, with unlimited length field it assume something like 4096 bytes and wastes working memory if your values are not actually that size. Not sure if PG has the second issue, having a max value defined is valuable information to a database engine along with other type information such as UUID's only taking 16 bytes instead of 36 bytes.

masklinn 4y ago
> PG seems to have this issue as well but the limit is 2712 bytes. Note that this is only for bytes indexes. Hash is not so limited. Tho at this sort of sizes it seems unlikely such simple indexes are of much use. I guess the range matching abilities of btrees could be but that seems unlikely, a 2700 bytes path-like datum is a lot.

luhn 4y ago

For Postgres, there's a strong case to be made VARCHAR(n) is preferable to TEXT+check constraint. As you note, they're stored the same on disk. But for a varchar, you can increase the length with just this: ALTER TABLE a ALTER COLUMN b TYPE VARCHAR(n) This only requires ACCESS EXCLUSIVE for a fraction of a second while the metadata is updated. Whereas updating a CHECK constraint will require a full scan of the table. If you do it naively, this locks the table for the entire scan. If you want to avoid that, it's three steps: ALTER TABLE a DROP CONSTRAINT b_c, ADD CONSTRAINT b_c CHECK (length(b) < n) NOT VALID; COMMIT; ALTER TABLE a VALIDATE CONSTRAINT b_c; So as long as you're only increasing the length (which in my own experience is a safe assumption), VARCHAR is much easier to work with.

jeffdn 4y ago
Yep, that's fair enough. In my experience, however, in the vast, vast majority of cases, folks either don't actually care about the length, or they should be using business logic in code to do the validation, rather than waiting for the database to yell at them, invalidate their transaction, etc.
- HideousKojima 4y ago
  >they should be using business logic in code to do the validation I trust myself to do this, I don't trust my coworkers and future devs writing applications for the same database to follow the same constraints in their code though. Constraints at the DB level keep your data clean(er) even if some other devs tries to shit it up.
- layer8 4y ago
  The benefit is that you can treat the database schema as the single source of truth as to what data is valid. Otherwise what tends to happen is that different code components will have different ideas of what exactly is valid. This is also important because databases tend to be longer-lived than the code that accesses them.
- msbarnett 4y ago
  If I had a dollar for every time I saw someone doing application-layer-only validation of data and end up being surprised that some data that wasn't supposed to be valid ended up in the database only to later blow up in their face, I'd be a fairly well-off man. In practice, declaratively creating data constraints at schema-creation time has a much much higher success rate than trying to imperatively enforce data constraints at write time, in my experience. The missed cases and bugs always come back to haunt you.