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.

Where Next?

Popular in Questions Top

vegabook
I’m brand new to Phoenix and I have stripped one of the demo applications to the bone. I just want to get an svg up on the screen. Here i...
New
hariharasudhan94
Lets say I have map like this fetching from my database %{"_id" =&gt; #BSON.ObjectId&lt;58eb1a7a9ad169198c3dXXXX&gt;, "email" =&gt; ...
New
_russellb
I want to try my hand at web scraping. What tools/libraries do I need to use. I’m hoping to turn this into something professional so don’...
New
skosch
To my knowledge, put_in, Map.update etc. all have the one limitation of not automatically creating intermediate keys when needed (for exa...
New
beno
I will often find my self writing things similar to: case some_value do nil -&gt; something() "" -&gt; something() _ -&gt; somethi...
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
chokchit
** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2733ms. You can configure how long re...
New
jerry
Good day to you all. I have been struggling to get a query involving like and ilike to work. Can anyone assist me on this, please? pro...
New
pmjoe
I have a relationship of love and hate with Elixir. Lots of things are just absolutely right, but there are some things that are kind of ...
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

Other popular topics Top

JeremM34
Hello, how can I check the Phoenix version ? Thanks !
New
vertexbuffer
Hello, can anybody help here..? I have a list of players and I what to delete an element, but every for loop the list is reverting to ori...
New
johnnyicon
Hi all, I’ve just started learning Elixir and Phoenix Framework, so please pardon my n00bness at this stage. I’m trying to use Postgres...
New
jaysoifer
Is there a way to rollback a specific migration and only that one (“skipping” all the other ones)? Would mix ecto.rollback -v 200809061...
New
vonH
In asking this question I am more interested about the expressiveness of the language itself and less concerned about the availability of...
New
jay1
Why is it that the mnesia database isn’t the most preferred database for use in Elixir/Phoenix?
New
Nvim
Anybody knows a comprehensive comparison of Django and Phoenix, thanks for the help. Where are they similar? Where do they differ the m...
New
AngeloChecked
What learn first? Rust or Elixir Hi Elixir community! I’m here because i want learn a new language. I’m a junior developer and mainly i ...
New
marius95
Hello everyone, I try to use an Javascript Event Handler in my root.html.leex file. Therefore I created a function in the app.js file: ...
New
svb
Hi! Currently I want to submit a form by pressing the Enter key. However, since my input field is of type “textarea” this is just adds a...
New

Latest on Elixir Forum

We're in Beta

About us Mission Statement