Big PostgreSQL table: is it 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 EXIF ​​metadata in JSON format for each image. This JSON frame is about 6 KB per image.

This EXIF ​​metadata will not be requested / used very often, and I wonder if it will be much more convenient to store it in a separate table with two columns (imageid, exifjson) or whether PostgreSQL will be just fine with this, like the text column in an existing table . I would not want to add a column to significantly slow down regular queries in a table or millions of 6K text values ​​to disable PostgreSQL.

Answer 1

I would make this TOAST -ed column.

  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 is already trying to use it for data larger than ~ 2KB.

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

Answer 2

It is better to use a separate table, but you will be fine with the existing table. You are unlikely to get an impact unless you extract this field using existing "select * from" queries. And you will never populate postgres with such data, as it has almost infinite thresholds:

• Maximum database size without limits
• Maximum table size 32 TB
• Maximum line size 1.6 TB
• Maximum field size 1 GB
• Maximum number of rows in a table without limits
• The maximum columns for a table are 250 to 1600, depending on the types of columns.
• Maximum indices for a table without restrictions

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

About interference while selecting other column data:

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

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