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
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
A few other considerations related to the benefits of using a DB are worth considering:
- 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.
- Data and binary are backed up together. It is due to point 1.
- You benefit from ACID properties due to using DB transactions.
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.
Popular in Questions
Other popular topics
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance









