3 ms·

There are databases with arbitrary limits on strings? That strikes me as really poor design.

by continuational 9y ago

jlgaddis 9y ago
You must be really new to databases.
bobwaycott 9y ago
They aren’t arbitrary.
Pxtl 9y ago
Almost every SQL database has arbitrary limits on text. Personally I find it ludicrously anachronistic.
- snuxoll 9y ago
  I mean, there’s good reasons to limit the size of fields in database tuples - relational databases are made for storing small to medium amounts of normalized data, you can abuse them (and I have, storing a couple million 60-200K PDF’s in a Postgres database at work) but you can shoot yourself in the foot by creating tons of 60% full pages and the stupid amount of extra IOPS those wide columns create. Still, not allowing unrestricted text and binary columns is still silly, so I agree for the most part - but you still need to know what you’re getting yourself into when you start making wide tables.
KekDemaga 9y ago
Why you are being down voted: As far as I know in most databases TEXT and BLOB types have no "arbitrary" length parameters but are stored at the end of the database file making access slower especially on spinning rust, VARCHAR(x) can only be x characters long but is stored inline with the table so it's much faster. If you know the length of a string won't exceed a relatively small number of characters VARCHAR is the right tool for the job.
- lobster_johnson 9y ago
  Fun fact: Postgres is one of the few databases to do this right. "varchar" is just an alias for the "text" data type, which has a limit of 2GB (last I checked). A small amount of text is stored inside the row, and the overflow is compressed and stored in a series of linked child rows. The system is called TOAST, and quite fast. So the underlying storage is completely abstracted, and setting a limit doesn't impact storage; it only enforces a limit on what input is allowed. MySQL's text type, on the other hand, is like a binary blob and has (last I checked) different semantics than the varchar type.
assface 9y ago
Not string lengths, but definitely other things. For example, Oracle is notorious for only allowing 1000 columns per table: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53174778859588 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUE... They bumped it up to 1000 from 255 a few years ago. It was a major engineering effort to support that. It's unlikely that they will be willing to do it again.

There are databases with arbitrary limits on strings? That strikes me as really poor design.

jlgaddis 9y ago

You must be really new to databases.

bobwaycott 9y ago

They aren’t arbitrary.

Pxtl 9y ago

Almost every SQL database has arbitrary limits on text. Personally I find it ludicrously anachronistic.

snuxoll 9y ago
I mean, there’s good reasons to limit the size of fields in database tuples - relational databases are made for storing small to medium amounts of normalized data, you can abuse them (and I have, storing a couple million 60-200K PDF’s in a Postgres database at work) but you can shoot yourself in the foot by creating tons of 60% full pages and the stupid amount of extra IOPS those wide columns create. Still, not allowing unrestricted text and binary columns is still silly, so I agree for the most part - but you still need to know what you’re getting yourself into when you start making wide tables.

KekDemaga 9y ago

Why you are being down voted: As far as I know in most databases TEXT and BLOB types have no "arbitrary" length parameters but are stored at the end of the database file making access slower especially on spinning rust, VARCHAR(x) can only be x characters long but is stored inline with the table so it's much faster. If you know the length of a string won't exceed a relatively small number of characters VARCHAR is the right tool for the job.

lobster_johnson 9y ago
Fun fact: Postgres is one of the few databases to do this right. "varchar" is just an alias for the "text" data type, which has a limit of 2GB (last I checked). A small amount of text is stored inside the row, and the overflow is compressed and stored in a series of linked child rows. The system is called TOAST, and quite fast. So the underlying storage is completely abstracted, and setting a limit doesn't impact storage; it only enforces a limit on what input is allowed. MySQL's text type, on the other hand, is like a binary blob and has (last I checked) different semantics than the varchar type.

assface 9y ago

Not string lengths, but definitely other things. For example, Oracle is notorious for only allowing 1000 columns per table: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53174778859588 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUE... They bumped it up to 1000 from 255 a few years ago. It was a major engineering effort to support that. It's unlikely that they will be willing to do it again.