4 ms·
Storing images / blobs in a database is fine! https://sqlite.org/intern-v-extern-blob.html https://sqlite.org/intern-v-extern-blob.html I just recently built a
by olavgg 10y ago
Storing images / blobs in a database is fine! https://sqlite.org/intern-v-extern-blob.html https://sqlite.org/intern-v-extern-blob.html
I just recently built a e-commerce site that stores the images in the database. But be careful, some ORM's will automatically fetch that blob together with the rest of your object.
- ryanbrunner 10y agoThe thing is, for most cases relevant to web apps, you don't even actually want binary data. Your application isn't going to touch the file itself anyway - a client somewhere is going to be the one processing and downloading the file. There's never a circumstance where dealing with hundreds of kilobytes or more of data is going to be faster than a 50-character file name, regardless of where it's stored.
- naasking 10y agoBut if you replicate your DB, now you also have to replicate your filesystem. Easier to just segregate BLOBs in their own tables and let DB replication do all the work.
- ryanbrunner 10y agoIt depends on why you're replicating but I would say in most cases you don't need to. If you're forking your DB (i.e. there will now be two copies that will diverge from each other) than sure, you would need to replicate your filesystem. But that's not complicated (copying directories is a solved problem). If you're talking about replication for performance reasons, then DB replication and file replication aren't necessarily going to go hand-in-hand, and the solutions are going to look fairly different (for web apps, "file replication" probably looks like a CDN).
- olavgg 10y agoActually in some cases you want your application to touch that blob, especially if want to automatically generate thumbnails and have aggressive/best effort compression on that image. It is the best way to do something about users that uploads a 10MB image that is uncompressed :-)
- ryanbrunner 10y agoFor sure - but you should ask yourself if you need to routinely deal with that blob, or just process it on the way in? If your upload functionality generates thumbnails and resizes images properly, you can isolate file handling to that section of the code and the rest of your app can just deal with nice, simple URLs.
- brianwawok 10y agoSo if you do this on a large site, what % of your DB load is going to be serving images? If you put your images behind a CDN, you solve 98% of that.. but you still have a thundering herd problem if your cache gets cold and a bunch of people want to see a non-CDNed iamge. IMHO it is way way way easier to just throw the image up on google cloud storage (or s3), and store the URL in your database. Or better yet, derive the path from the ID so nothing to store. You can handle millions of hits per second with no DB load.
- olavgg 10y agoI use Varnish, so it is never a performance issue.
- brianwawok 10y agoDoes Varnish preload your images from cache? What if 100 people hit the page at the same time for an uncached image? I am not saying it can't be done, just having trouble figuring out the benefits.. its easier to store an image in cloud storage over in a database... and cheaper.. and less bug prone...
- dalyons 10y agoVarnish will actually hold those 100 connections open while it dispatches 1 single request to the backend. When that request returns (assuming the cache control headers allow it), it will serve that one response to all 100 requestors. It's a very powerfull tool for anything that's even slightly cachable.
- brianwawok 10y agoIf your site has 1 varnish, if your site has 20 varnishes it would do 20 connections.