4 ms·

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 varc

by 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.
  - jeffdn 4y ago
    I'm not talking about all validation, I'm talking about a length constraint on a string (that isn't something like a US state, which should be a two-character field) -- already a pretty uncommon thing. At work, our codebase has 975 instances of a column storing a text value, and the length is material only in a very small handful of those.
    - JamesSwift 4y ago
      How do you handle user input in your case? Frontend-only length limits? Or just allow them to take up more storage if thats what they submit?
- luhn 4y ago
  It's good practice to have a length constraint on all text fields, just to put some cap on field sizes. You don't want somebody uploading 100MB of garbage into the `first_name` field—A 1000-char max is more than enough. Constraints generally should be enforced both application- and database-side wherever possible. Application constraints can be more robust and have better UX, but only database constraints can offer you guarantees about what is actually in your database.
singron 4y ago
Btw this procedure doesn't work for partitioned tables. For some reason, it locks the table and does a table scan anyway. The workaround we came up with was to add check constraints for instant partition attachment, lock the table, detach all the partions, update the type on each partition, update the parent table type, then reattach all the partitions. In our case, we didn't already have those instant-attach check conditions, so we incurred table scans anyway to validate those, but if you were to make multiple updates, you only have to do that once.
radiowave 4y ago
On the other hand, changing the length of a varchar column in Postgres is actually changing the type of the table, and if you have any other objects in the database that are dependent upon that type (e.g. views), you'll have to drop and recreate them (and transitively, any views that depend upon those views) in the same transaction where you alter the column. This can easily spiral into a lot of work.

by luhn 4y ago

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.
- jeffdn 4y ago
  I'm not talking about all validation, I'm talking about a length constraint on a string (that isn't something like a US state, which should be a two-character field) -- already a pretty uncommon thing. At work, our codebase has 975 instances of a column storing a text value, and the length is material only in a very small handful of those.
  - JamesSwift 4y ago
    How do you handle user input in your case? Frontend-only length limits? Or just allow them to take up more storage if thats what they submit?
luhn 4y ago
It's good practice to have a length constraint on all text fields, just to put some cap on field sizes. You don't want somebody uploading 100MB of garbage into the `first_name` field—A 1000-char max is more than enough. Constraints generally should be enforced both application- and database-side wherever possible. Application constraints can be more robust and have better UX, but only database constraints can offer you guarantees about what is actually in your database.

singron 4y ago

Btw this procedure doesn't work for partitioned tables. For some reason, it locks the table and does a table scan anyway. The workaround we came up with was to add check constraints for instant partition attachment, lock the table, detach all the partions, update the type on each partition, update the parent table type, then reattach all the partitions. In our case, we didn't already have those instant-attach check conditions, so we incurred table scans anyway to validate those, but if you were to make multiple updates, you only have to do that once.

radiowave 4y ago

On the other hand, changing the length of a varchar column in Postgres is actually changing the type of the table, and if you have any other objects in the database that are dependent upon that type (e.g. views), you'll have to drop and recreate them (and transitively, any views that depend upon those views) in the same transaction where you alter the column. This can easily spiral into a lot of work.