Large PostgreSQL table: better to add a column or create a new table to store metadata?

Question

I have a large table (~2 million rows), each row of which represents one image. I want to store the EXIF metadata in JSON format for each image. This JSON blob is about 6KB per image.

This EXIF metadata won't be queried/used very often, and I'm wondering whether it'd be significantly more efficient to store it in a separate table with two columns (imageid, exifjson), or whether PostgreSQL will deal with this just fine as a text column on existing table. I wouldn't want adding the column to significantly slow down ordinary queries on the table, or the millions of 6KB text values to bog PostgreSQL down.

Answer 1

I would make that column TOAST-ed.

 ALTER TABLE ... ALTER <column> SET STORAGE <EXTERNAL|EXTENDED>;
 -- EXTERNAL - out-of-line storage, not compression[1]
 -- EXTENDED - both compression and out-of-line storage

PostgreSQL already try to use it for data larger than ~2kB.

[1] "The compression technique used is a fairly simple and very fast member of the LZ family of compression techniques."

Answer 2

Better to use separate table, but you will be ok with existing table. You'll scarcely get a impact unless you retrieve this field using existing "select * from" queries. And you'll never fill postgres up with such data since it has almost infinite thresholds:

• Maximum Database Size Unlimited
• Maximum Table Size 32 TB
• Maximum Row Size 1.6 TB
• Maximum Field Size 1 GB
• Maximum Rows per Table Unlimited
• Maximum Columns per Table 250 - 1600 depending on column types
• Maximum Indexes per Table Unlimited

http://www.postgresql.org/about/

About interfering during selection of other column data:

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

http://www.postgresql.org/docs/current/static/datatype-character.html