Old, Good Database Design

Re:Old, Good Database Design by pizza234 on Monday September 14, 2020 @03:40PM (#24474237)

I suppose that in every RDBMs that makes the distinction, it's an optimization matter - VARCHAR being stored on-page, while TEXT off-page (although there can be optimizations for short TEXT values); the latter will cause an extra page seek on access.

Some database [versions] may also be unable to apply certain optimizations in certain cases, when TEXT is used (eg. temp tables on MySQL <= 5.7).

That doesn't prevent one from always using TEXT, and possibly, for most of the use cases (surely, if one uses SQLite, that's the case), the performance impact is not meaningful.

[ Parent | on HN ]

Re:Old, Good Database Design by nemothekid on Monday September 14, 2020 @04:40PM (#24474876)

For what I understand in the docs, in postgres at least, VARCHAR and TEXT are the same thing, and CHAR actually has the performance hit.

[ Parent | on HN ]

Re:Old, Good Database Design by colanderman on Monday September 14, 2020 @04:53PM (#24475022)

They are implemented the same, but they aren't the same type. Notably, each parameterization of varchar (or char) is its own type. This can cause issues when trying to change the length parameter. For this reason I prefer to use TEXT with a CHECK constraint.

[ Parent | on HN ]