sodapopcan

sodapopcan

Postgres and bytea - do you think it's ok to store small images in the DB as byte arrays?

I apologize for the not-really-Elixir-specific post, but I’m currently working solo and y’all are the only programming community I engage with, so I’m hoping this is ok and promise I won’t make it a habit.

For the first time in my life I’m considering storing some images in Postgres as byte arrays (bytea type). I’ve done my reading and it seems ok, but having dealt with images a lot in my career and having it pretty hammered into my head to store file paths in the db (or bucket paths), I just wanted to say this out loud to other devs:

The images are quite small, generally under 300k and even as small as < 1k! They are assets used to generate more complex images as well as print files (I will not be storing these generated images in Postgres). There are not a lot of them—if business continues to go well then in the next 5 years we will have maybe accumulated around 3_000 of them. The only time they are ever shown to a user is in backoffice pages that, other than when the records are initially being created, have a low chance of ever being looked at ever again. They are otherwise sent, along with other data from the same query, to the image processing service which will happen with some regularity (batches of them will accessed daily).

So this is all feeling pretty good to me as it greatly simplifies the implementation. Is there anything I’m missing? Is this still somehow a bad idea anyway?

Thanks for reading!

Most Liked

benwilson512

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

From what you have said: Nah, seems fine. The closest thing from my experience is PDFs. For certain small services I’ve run I’ve stored generated PDFs as binary and it’s worked fine.

My only advice is to probably segregate it to a dedicated table where your general model holds a foreign key to it. While Ecto makes it pretty easy to query it even if you put it as a regular column on your table it is often annoying to integrate with other postgres clients, and if your overall row count is as low as you say the cost of having it in another table shouldn’t really matter.

lkuty

lkuty

A few other considerations related to the benefits of using a DB are worth considering:

  1. Do your binaries have to reside the closest possible to your data ? If the binaries (image, PDF, …) are meaningless without the accompanying data or the other way around, the data is meaningless without the binary, then it might be necessary to store the binary in the DB, close to the data. For example, operators might write reports on the field and take pictures to augment it, which means the report may be unusable if the pictures are not present. The data and the binaries cannot be unlinked contrary to what might happen with a file outside the DB.
  2. Data and binary are backed up together. It is due to point 1.
  3. You benefit from ACID properties due to using DB transactions.
AstonJ

AstonJ

My only experience of storing images in the DB goes back about 20 years - when we stored (tiny) avatars in the DB. However on further reading I learned that this was only really an option intended for people who could not set the appropriate permissions on the file system (usually because of the type of (more restrictive) hosting they might have had) and that the file system was preferred. Sure enough switching to the file system made everything feel much snappier, even on forums with just a couple thousand members.

Not sure how far DBs have come, or whether the possible bottleneck might effect you in your situation but I always remember what @joeerl said whenever I hear someone talk about storing images in a DB:

On top of that you have to consider things like back-ups, DB size, what happens if the DB becomes corrupted, etc, so personally I would avoid storing images in the DB if at all possible.

But you may be ok in your use-case if you take into account what @benwilson512 and @mindok have said above.

derek-zhou

derek-zhou

for un-compressable image data, you may want to disable compression:

mindok

mindok

I’m all for it for small images (e.g. thumbnails). I’ve done quite a lot of extracting and databasing 128x128 images into SQLite, SQL Server & Postgres recently - admittedly the images are relatively small - 5-15k. Everything is self-contained and performance is great for LiveView front ends - you can push the images to LiveViews over web sockets (Base64 encoded first) which reduces chatter over the network connection (at the expense of slightly larger payloads).

As usual, though, there are tradeoffs to consider. Traditional web-serving of the images will be a little trickier from db rather than disk (not much harder, but you would need to write a controller rather than just use Plug.Static), and you won’t be able to edit the images in a normal editor without a bit of fiddling around.

Where Next?

Popular in Questions Top

srinivasu
How to handle excepions in elixir? Suppose i have A, B, C ,D, E modules. and each module has get() function. A.get() method will call th...
New
bsollish-terakeet
Credo is smart enough to check for (something like) this: assert length(the_list) == 0 with this response: Checking if an enum is empt...
New
pgiesin
This should be a simple problem but I just can’t seem to figure it out. I have a standalone Elixir app that won’t find the database. Dep...
New
fireproofsocks
I’m working on defining a simple Ecto schema for a table (in PostGres), but I don’t see where I can define a column as NOT NULL. Conside...
New
sacepums
Hey guys. I'm new to elixir and im really stocked about it. But I ran into a bit of problem - I need to convert a date sting, for examp...
New
script
If I have a string “1000 cfu/ml” . I want to remove the characters and / and space . So the string is like this "1000" What is the ...
New
chewm
Hi guys, nice to meet you to the whole forum, I’m new here, I’m trying to configure visual studio code for elixir, right now the intellis...
New
WestKeys
Currently suffering from paralysis by [HTTP client] analysis. This is rather unusual in Elixirland as there tends to be consensus on the ...
New
Exadra37
Sometimes I want to check if the input into a function is not a blank string. My first approach: defmodule Example do def do_stuff(s...
New
lanycrost
Hi everyone! I need implement if…else if…else condition from my elixir code, and anymore of this control flow structures not work proper...
New

Other popular topics Top

sorentwo
Hello! tl;dr Announcing Oban, an Ecto based job processing library with a focus on reliability and historical observability. After spen...
977 41022 311
New
yawaramin
In the Dialyzer docs ( http://erlang.org/doc/man/dialyzer.html#requesting-or-suppressing-warnings-in-source-files ), there is a way to tu...
New
albydarned
Hello all! I am typing this post from my new MacBook Pro with the M1 chip. I’m loving it so far, and will probably use it as my daily dr...
New
New
vac
Hi, I'm quite new in Elixir and I'm trying to format a string to a PEM format. I have the certificate value like MIIDBTCCAe2...... and ...
New
chrismccord
As promised, the first release candidate of Phoenix 1.3.0 is out! This release focuses on code generators with improved project structure...
New
aadeshere1
I have a another noob question about loop. Since elixir is immutable, while loop is not directly possible. total = 10 while total != 0 ...
New
shahryarjb
Hello, I have map which I want to convert it to string like this: the map: %{last_name: "tavakkoli", name: "shahryar"} the string I ne...
New
aesmail
Hello guys, I have finally made it. I created an admin interface for a framework. It’s been on my todo list for years and with the curre...
New
Nvim
Elixir appears to be a superior language to Python. I don’t see any advantage of Python over Elixir. Are there any?
New

We're in Beta

About us Mission Statement