Postgresql – When the primary key is also the foreign key, in Postgres

foreign keypostgresqlprimary-key

In Postgres 12, I am tracking photographs. Each is large, about 6 to 8 megs each. For quick browsing I will store a thumbnail image along with the full image. Both full and thumbnail are BYTEA type columns.

For performance, I want to avoid loading the full image when only the thumbnail is needed. As I understand it, when accessing a row, all the BYTEA field values will be loaded into memory. So displaying a list of thumbnails to the user will necessarily be loading the full photos on the server even when not explicitly requested in the query.

So I will break that photo table into two, storing the thumbnail on the main table, and storing the thumbnail in a separate child table, in a One-To-One.

In such a scenario, the child full-photo table carries the ID of its parent thumbnail row as a foreign key. That foreign key column could logically serve as the primary key as well.

Can I designate that one column of a child to be both a foreign key and also a primary key? If so, are there any caveats to be aware of?

Best Answer

Primary key & Foreign key

Can I designate that one column of a child to be both a foreign key and also a primary key?

Yes absolutely: 

create table photo 
(
  id integer primary key, 
  ... other columns ...
);

create table thumbnail 
(
  id integer primary key references photo, 
  ... other columns ...
);

TOAST

bytea columns are stored outside of the normal column data (in so called "toast tables") and are not retrieved unless you include them in the SELECT list.

Quote from the manual

The big values of TOASTed attributes will only be pulled out (if selected at all) at the time the result set is sent to the client.

This only means that even if a query is forced to do a Seq Scan, the bytea columns are not retrieved until the rows are identified that need to be sent back. So if your query does a seq scan on 1 million rows but only 1 row is returned, only one bytea value is read and sent to the client.

Note that the thumbnails might actually be stored inline (not in the toast table) if they are small enough (TOASTing is only triggered for sizes below approx. 2k)

Related Question