14 ms·

I understand https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage in a way that Postgres enco

by ktpsns 7y ago

I understand https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage in a way that Postgres encourages users always to stick to the text type. Which is, from a non-DB-centric view, certainly the most convenient thing to do: Tell the database just to store a string, don't worry about length or so. Having worked with MySQL a lot in the past, I wonder if this is a kind of paradigm shift, whether PostgreSQL tries to be programmer-friendly or whether this is just the way how to do SQL nowadays...?

pizza234 7y ago
There have always been low-level differences in the way MySQL treats the two (VARCHAR <> TEXT/BLOB) data types, although some have been removed over the time. A notable example is that before version 8, internal temporary tables didn't support BLOBs when in-memory¹, so that they would swap to disk, causing a performance penalty. Regarding the length, an important concept is that it can be used for optimizations. InnoDB adds a certain number of bytes for each value stored, depending on the size. For example, a TINYTEXT consumes 3 bytes less than LONGTEXT for the same value. Another difference is the location of the data. TEXT/BLOBs up to 40 bytes long will be stored in line, while the limit is 768 bytes for VARCHARs. A 100 bytes long TEXT will require one page access more than the corresponding VARCHAR. Of course, it's up to the engineer to choose whether to consider optimizations like this or not, but as a matter of fact, those differences exist. ¹=note that this concept is different from tables backed by the `MEMORY` engine, and different from user temporary tables.
derefr 7y ago
Postgres built up a whole separate infrastructure for BLOB storage, with a streaming API in/out of BLOBs et al; but after landing the TOAST-table logic, it became basically† obsolete—you can just use a regular TEXT or BYTEA column to achieve the same things now. † BLOBs are still relevant in the cases where you have such huge data in each BLOB that you need to work with it in a streaming manner. But people don’t tend to build systems with this concern (i.e. object stores) on top of RDBMSes, so it’s pretty darn rare that anyone actually turns to Postgres’s BLOBs to solve a problem in practice.
- merb 7y ago
  actually bytea and blobs are useful for storing pdf's in an acid way. something like invoices that need to be generated 100% acid fashion.

by ktpsns 7y ago

pizza234 7y ago

There have always been low-level differences in the way MySQL treats the two (VARCHAR <> TEXT/BLOB) data types, although some have been removed over the time. A notable example is that before version 8, internal temporary tables didn't support BLOBs when in-memory¹, so that they would swap to disk, causing a performance penalty. Regarding the length, an important concept is that it can be used for optimizations. InnoDB adds a certain number of bytes for each value stored, depending on the size. For example, a TINYTEXT consumes 3 bytes less than LONGTEXT for the same value. Another difference is the location of the data. TEXT/BLOBs up to 40 bytes long will be stored in line, while the limit is 768 bytes for VARCHARs. A 100 bytes long TEXT will require one page access more than the corresponding VARCHAR. Of course, it's up to the engineer to choose whether to consider optimizations like this or not, but as a matter of fact, those differences exist. ¹=note that this concept is different from tables backed by the `MEMORY` engine, and different from user temporary tables.

derefr 7y ago

Postgres built up a whole separate infrastructure for BLOB storage, with a streaming API in/out of BLOBs et al; but after landing the TOAST-table logic, it became basically† obsolete—you can just use a regular TEXT or BYTEA column to achieve the same things now. † BLOBs are still relevant in the cases where you have such huge data in each BLOB that you need to work with it in a streaming manner. But people don’t tend to build systems with this concern (i.e. object stores) on top of RDBMSes, so it’s pretty darn rare that anyone actually turns to Postgres’s BLOBs to solve a problem in practice.

merb 7y ago
actually bytea and blobs are useful for storing pdf's in an acid way. something like invoices that need to be generated 100% acid fashion.