3 ms·
> All modern popular relational database support (almost) unlimited sized strings with a text or blob-type column, so why not use that? The reason is indexes.
by remus 5y ago
> All modern popular relational database support (almost) unlimited sized strings with a text or blob-type column, so why not use that? The reason is indexes.
Indexes are not the only reason. Typical databases (mysql, mssql etc.) are row oriented so they store rows of data in blocks on disk. If you have a text or blob field it is potentially unbounded in size and if the field does get too big it will typically be moved 'off the row' and replaced with a pointer to the full object. If instead you have a varchar field then it's size is bounded and the db can keep all the data in contiguous blocks on the disk. Having all your data in contiguous blocks can improve read performance (though it's less of an issue with SSDs).
- jeltz 5y agoPostgreSQL is also row oriented but can store small text and blob (bytes) fields in the main table. PostgreSQL determines per value if it fits in the main table storage or not. This means that varchar and text are virtually identical. This is done for all variably sized data types, including third party GIS polygons.
- spookthesunset 5y agoPostgres always amazes me how well designed it is. It’s too bad MySQL has the brand recognition it does because Postgres is just far and away the better choice for almost everything.