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

Question

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?

• jmelesky: I have two reactions: 1- why store the photos in the DB at all? You could store file paths for the photos (and thumbnails) instead, and leverage your filesystem (or k/v store, or whatnot) for the file storage. 2- If you do store the photos in the DB, why not just SELECT id, thumbnail FROM .... to avoid fetching the full image? That gets you out of needing a secondary table.
• Basil Bourque: @jmelesky (A) Regarding your first point, storing photos inside the database versus in the file system as image files, that debate is outside the scope of this Question. (B) Regarding your second point, about the images loading, my understanding is that any and all BYTEA field values on a row will be read into memory whenever the row is accessed. I added this issue to my Question. If my understanding is wrong please correct me as that is the premise underlying my Question.

Answer 1

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 above approx. 2k)

Answer 2

Columns are toasted individually, so there is no problem with keeping both images in a single table. Only the image that appears in the SELECT list will be detoasted.

Moreover, if an UPDATE does not affect the toasted columns, they won't have to be rewritten.

• user1822: So my recommendation to split up the table into two in case of many updates to the other columns doesn't really make sense?
• Laurenz Albe: Yes, I think so. Mostly, though, the fear of OP that all byteas are detoasted if only one of them is in the SELECT list is unfounded.