Did I store pictures in the correct format in my Postgres database?

Question

I have a Postgres database with a separate table for pictures. I've stored pictures in the database and when I list them like so;

SELECT id,
   pg_typeof(image) || ': ' || encode(image::bytea, 'escape') AS image_representation
FROM picture_table;

I see this list;

The question is, did I store the picture the correct way?, I'm suspect that I store the base64 data as binary, and thus takes in more space that required.

Is this the most efficient way?, it's a 'bytea' type in Postgres.

Example: If I have a picture of 900 bytes as binary, if it's stored as base64, that would take up around 2400 bytes when stored as base64. And I see it stored as base64. Or is it just how it's displayed after the query.

Answer 1

You wrote text into a bytea column in the database. Text saved into type text take the same amount of disk space as text saved as type bytea. You can do this if you want, but since working with bytea is awkward, you should probably avoid doing it needlessly.

Since it is already encoded as text, you could just save it as text. The "data:image/png;base64" are literal characters you (or your client) have saved into the database (along with the rest of the characters), it is not something being supplied to you by PostgreSQL.

Example: If I have a picture of 900 bytes as binary, if it's stored as base64, that would take up around 2400 bytes when stored as base64

No, 900 bytes encoded as base64 only takes a little over 1200 bytes, not 2400. Even encoding it as hex doesn't take 2400 bytes. This is just looking at the raw sizes, not taking into account any compression that might be going on. If you want to take compression into effect, you should look at the actual size your data takes up, rather than trying to address it theoretically.

And I see it stored as base64. Or is it just how it's displayed after the query.

It is stored as base64. But that is not PostgreSQL's doing, it is just the way your client sent the data to be saved. You told it to use the escape format, but just looking at the image you showed, no escaping is actually being done as there is nothing which needs to be escaped.