In PostgreSQL, Is It Faster To Include TEXT Columns In The Same Table, Rather Than A Separate Table?

Question

Which design do you think runs faster on PostgreSQL?

A. Making a 15 column table of varchars and the like, but putting all TEXT columns in a separate table with an fkey link back to this table. And let's imagine you want to search for the record with ID of "4" but then pull all the rows back, including the stuff from the TEXT columns in the joined table. And let's imagine the tables have 500,000 rows.

B. Making a 15 column table of varchars and the like, and include your TEXT columns in the same table. Again, imagine the same as above -- grab record ID 4 and pull the full record, and there are 500,000 rows in the table.

I mean, in most databases, the way I understand it, when you go down to the physical layer of how those TEXT columns work, they keep a small ID actually in the table column on each row, and that ID goes to a separate, exclusive page block (or other nomenclature) in the database. So, to me, it would seem that option B would run faster because there's no need for the overhead of the fkey join, and because the TEXT columns are not actually occupying any more than an integer space in that column in that given table -- and that integer is a key in the database to a page block somewhere else.

Answer 1

(B) is correct, for the reason given in the question itself.

Answer 2

PostgreSQL doesn't handle TEXT columns in the same way as other DBMS's.

From their docs:

Tip: There are no performance differences between these three types, apart from increased storage size when using the blank-padded type, and a few extra cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, it has no such advantages in PostgreSQL. In most situations text or character varying should be used instead.

Check out the manual