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?
SELECT id, thumbnail FROM ....
to avoid fetching the full image? That gets you out of needing a secondary table.