When you have a TEXT field in MySQL or PostgreSQL, should you put it in a separate table?

Question

I heard that if you have a table with a TEXT column that will contain a large fragment of text data, it is better for performance to move this column to a separate table and get it through JOINs in the base record.

Is that true, and if so, why?

Answer 1

Not with PostgreSQL manual :

Very long values ​​are also stored in background tables, so they do not interfere with quick access to shorter column values.

Thus, a column of large characters (for example, TEXT or VARCHAR without size limit) is stored away from the data of the main table. Thus, PostgreSQL has a built-in optimization "placing it in a separate table." If you are using PostgreSQL, arrange your table and leave the PostgreSQL data layout.

I do not know how MySQL or other RDBMs organize their data.

The reason for this optimization is that the database usually saves data for each row in adjacent blocks on disk to shorten the search when a row needs to be read or updated. If you have a TEXT column (or another variable length) in the row, then the row size is variable, so more work is required to move from row to row. An analogue would be the difference between accessing something in a linked list and accessing an array; with a linked list, you have to read three elements one at a time to go to the fourth, with an array that you just shift 3 * element_size bytes from the beginning, and you are at one step.

Answer 2

From MySQL Guide :

For a table with multiple columns, to reduce memory requirements for queries that do not use the BLOB column, consider splitting the BLOB column into a separate table and link, if necessary, with a connection request.

Answer 3

In some scenarios this may be true. The reason is that let's say your table:

 create table foo ( id serial primary key, title varchar(200) not null, pub_date datetime not null, text_content text ); 

Then you execute the following query:

 select id, title, pub_date from foo; 

You will have to load many more pages from disk that you would have if you did not have the text_content field in this table. Query optimization basically reduces disk I / O to a minimum.