8 ms·

I think for smaller projects just storing images as BLOBs in e.g. PostgreSQL works quite well. I know this is controversial. People will claim it's bad for per

I think for smaller projects just storing images as BLOBs in e.g. PostgreSQL works quite well. I know this is controversial. People will claim it's bad for performance. But that's only bad if performance is something you're having problems with, or going to have problems with. If the system you're working on is small, and going to stay small, then that doesn't matter. Not all systems are like this, but some are. Storing the images directly in the database has a lot of advantages. They share a transactional context with the other updates you're doing. They get backed up at the same time, and if you do a "point in time" restore they're consistent with the rest of your data. No additional infrastructure to manage to store the files.

andyp-kw 3y ago
I've seen companies store pdf invoices in the database too, for the same reasons you spoke of.
mohamedattahri 3y ago
BLOB on Postgres are awesome, but there's also a full-featured file API called Large Objects for when the use-case requires seeking/streaming. Wrote a small Go library to interface with it: https://github.com/mohamedattahri/pgfs https://github.com/mohamedattahri/pgfs Large Objects: https://www.postgresql.org/docs/current/largeobjects.html https://www.postgresql.org/docs/current/largeobjects.html
- dventimi 3y ago
  Large Objects works great for images and stylesheets and other file assets for my small PostgREST projects.
- ibgeek 3y ago
  TIL thanks!
doubled112 3y ago
I've seen BLOBs in an Oracle DB used to store Oracle install ISOs, which I think is ironic on some level. Let's attach what we're using to the ticket. All of it. Why is the ticketing DB huge? Well, you attached all of it.
- richbell 3y ago
  I once helped migrate data from a 3+ TB Oracle database for a ticketing system. It was supposed to be shut down more than a year prior but the task kept getting passed around like a hot potato. I can't imagine how much money we were paying in licensing fees and storage costs.
ltbarcly3 3y ago
I think there is too much emphasis on 'the right way' sometimes, which leads people to skip over an analysis for the problem they are solving right now. For example, if you are building something that will have thousands or millions of records, and storing small binaries in postgresql lets you avoid integrating with S3 at all, then you should seriously consider doing it. The simplification gain almost certainly pays for any 'badness' and then some. If you are working on something that you hope to scale to millions of users then you should just bite the bullet and integrate with S3 or something, because you will use too much db capacity to store binaries (assuming they aren't like 50 bytes each or something remarkably small) and that will force you to scale-out the database far before you would otherwise be forced to do so.
- crooked-v 3y ago
  > and that will force you to scale-out the database far before you would otherwise be forced to do so Disk space is cheap, even when it's on a DB server and not S3. Why worry that much about it?
  - tomnipotent 3y ago
    > Disk space is cheap Disk I/O less so. An average RDBMS writing a 10MB BLOB is actually writing at minimum 20-30MB to disk - once to journal/WAL, once to table storage, and for updates/deletes a third copy in the redo/undo log. You also get a less efficient buffer manager with a higher eviction rate, which can further exasperate disk I/O.
    - crooked-v 3y ago
      But if the files are important enough that you need transactional updates or point-in-time recovery for them, you're stuck with the equivalents of those things anyway, wherever those files end up, plus the extra overhead of coordinating two systems.
      - ltbarcly3 3y ago
fiedzia 3y ago
> If the system you're working on is small, and going to stay small, then that doesn't matter. Having good default solution saves a lot of problems with migration. Storing files outside of database today really isn't that more complicated, while benefits even for small files are significant: you can use CDN, you don't want traffic spikes to affect database performance, you will want different availability guarantees for your media than for database and so on.
EvanAnderson 3y ago
The issue I've run into re: storing files as BLOBs in a database has been the "impedance mismatch" coming from others wanting to use tools that act on filesystem objects against the files stored in the database. That aside I've had good experiences for some applications. It's certainly a lot easier than keeping a filesystem hierarchy in sync w/ the database, particularly if you're trying to replicate the database's native access control semantics to the filesystem. (So many applications get this wrong and leave their entire BLOB store, sitting out on a filesystem, completely exposed to filesystem-level actors with excessive permission.) Microsoft has an interesting feature in SQL Server to expose BLOBs in a FUSE-like manner: https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/b... I see postgresqlfs[0] on Github, but it's unclear exactly what it does and it looks like it has been idle for at least 6 years. [0] https://github.com/petere/postgresqlfs https://github.com/petere/postgresqlfs
- harlanji 3y ago
  Minio is easy enough to spin up, S3-compatible. That seems like my default path to persistence going forward. More and more deployments options seem like they'll benefit from not using the disk directly but instead using the dedicated storage service path, so might as well use a tool designed for that. S3 can be a bit of a mismatch for people who want to work with FS objects as well, but there are a couple options that are a lot easier than dealing with blob files in PGsql. S3cmd, S3fs; perhaps SSHfs to the backing directory of Minio or direct access on the host (direct routes untested, unsure if it maps 1:1).
SigmundA 3y ago
Main issues with large blobs in DB is moving and or deleting them. Not sure if PG has this issue I would like to confirm, but in say SQL server if you delete a blob or try and move it to another file group you get transaction logging equal to the size of the blobs. You would think at least with a delete especially in PG with the way it handles MVCC the blob would just have an entry saying which blob was deleted or something then if you need rollback you just undelete the blob. So just being able to move and delete the data after it is in there becomes a real problem with a lot of it.
vbezhenar 3y ago
It makes backups PITA. I migrated blobs to S3 and managed to improve backups from once a month to once a day. Database is now very slim. HDD space is no longer an issue. Can delete code which serves files. Lots of improvements with no downsides so far.
- crooked-v 3y ago
  Of course, that also means that now you don't have backups of your files.
  - 0x457 3y ago
    They do have backups if versioning is enabled on S3.
    - ericbarrett 3y ago
      Yes, S3 is extremely reliable and versioning protects against “oopsies.” I do always recommend disallowing s3:DeleteObjectVersion for all IAM roles, and/or as a bucket policy; manage old versions twith a lifecycle policy instead. This will protect against programmatic deletion by anybody except the root account.
      - brazzledazzle 3y ago
        I second these sensible protections. Also would recommend replicating your bucket as a DR measure. Ideally to another (infrequently used and separately secured) account. Even better if you do it to another region but I think another account is more important since the chances of your account getting owned is higher than amazon suffering a total region failure.
lovasoa 3y ago
How big does the database need to be for large BLOBs to become a problem ? "big" and "small" are quite subjective terms. How many BLOBs does one need to have, and how often to we need to touch them for this solution to become untenable ?
- crabbone 3y ago
  In storage you measure things in blocks. Historically, blocks were meant to be 512 bytes big, but today the tendency is to make them bigger, 4K would be the typical size in server setting. So, the idea here is this: databases that store structured information, i.e. such that needs to store integers, booleans, short strings are typically something like relational databases, eg. PostgreSQL. Filesystems (eg. Ext4) usually think about whole blocks, but are designed with the eye for smaller files, i.e. files aren't expected to be more than some ten or hundred blocks in size for optimal performance. Object stores (eg. S3) are the kinds of storage systems that are supposed to work well for anything larger than typical files. This gives the answer to your question: blobs in a relational database are probably OK if they are under one block big. Databases will be probably able to handle bigger ones too, but you will start seeing serious drops in performance when it comes to indexing, filtering, searching etc. because such systems optimize internal memory buffers in such a way that they can fit a "perfect" number of elements of the "perfect" size. Another concern here is that with stored elements larger than single block you need a different approach to parallelism. Ultimately, the number of blocks used by an I/O operation determines its performance. If you are reading/writing sub-block sized elements, you try to make it so that they come from the same block to minimize the number of requests made to the physical storage. If you work with multi-block elements, your approach to performance optimization is different -- you try to pre-fetch the "neighbor" blocks because you expect you might need them soon. Modern storage hardware has a decent degree of parallelism that allows you to queue multiple I/O requests w/o awaiting completion. This later mechanism is a lot less relevant to something like RDBMS, but is at the heart of an object store. In other words: the problem is not the function of the size of the database. In principle, nothing stops eg. PostgreSQL from special-casing blobs and dealing with them differently than it would normally do with "small" objects... but they aren't probably interested in doing so because you already have appropriate storage for that kind of stuff, and PostgreSQL, like most other RDBMS sits on top of the storage for larger objects (filesystem), so they have no hopes of doing it better than the layer below them.
deleted 3y ago
[deleted]
rajman187 3y ago
Several years ago Walmart dramatically sped up their online store's performance by storing images as blobs in their distributed Cassandra cluster. https://medium.com/walmartglobaltech/building-object-store-storing-images-in-cassandra-walmart-scale-a6b9c02af593 https://medium.com/walmartglobaltech/building-object-store-s...
bastawhiz 3y ago
I think the one big problem with BLOBs, especially if you have a heavily read-biased DB, is you're going to run up against bandwidth/throughput as a bottleneck. One of the DBs I help maintain has some very large JSON columns and we frequently see this problem when traffic is at its peak: simply pulling the data down from Postgres is the problem. If the data is frequently accessed, it also means there are extra hops the data has to take before getting to the user. It's a lot faster to pull static files from S3 or a CDN (or even just a dumb static file server) than it is to round trip through your application to the DB and back. For one, it's almost impossible to stream the response, so the whole BLOB needs to be copied in memory in each system it passes through. It's rare that any request for, say, user data would also return the user avatar, and so you ultimately just end up with one endpoint for structured data and one to serve binary BLOB data which have very little overlap except for ACL stuff, but signed S3 URLs will get you the same security properties with much better performance overall.
- refulgentis 3y ago
  Do you have any thoughts on when a JSON column is too large? I've been wondering about the tradeoffs between a jsonb column in postgres that may have values, at the extreme, as large as 10 MB, usually just 100 KB, versus using S3.
  - bastawhiz 3y ago
    It depends on how much you're getting back total. 100 rows returning a kb of data is the same as one row with 100kb. I get worried when the total expected data returned by a query is more than 200kb or so.
  - xmprt 3y ago
    Wouldn't the same reasoning for BLOBs apply to JSON columns? Unless you're frequently querying for data within those columns (eg, filtering by one of the JSON fields), then you probably don't need to store all the JSON data in the DB. And even if that is the case, you could probably work out a schema where the JSON data is stored elsewhere and only the relevant fields are stored in the DB. At the same time, I'm working with systems where we often store MBs of data in JSON columns and it's working fine so it's really up to you to make the tradeoff.
deleted 3y ago
[deleted]
cryptonector 3y ago
> But that's only bad if performance is something you're having problems with, or going to have problems with. The first part is easy enough, but how do you predict when you're going to hit a knee in the system's performance?
davedx 3y ago
We ran into issues doing this straightaway, because you also need to fetch the file out of the database in your application server, then send it to clients through the load balancer. That introduces bottlenecks. Generally I agree don't prematurely optimize. But for us, not even having huge files (5-25 MB on average), it slowed our application down unacceptably storing file data in the database, and we ended up moving file data to S3 with a streaming API in front of it.
hot_gril 3y ago
I'm on board with using temporary solutions for small projects, but I feel like BLOB isn't all that convenient either unless you have some particular reason you only want Postgres as a dependency. I can either set up something like S3 where I upload via some simple API and get URLs to send back to clients that will never break, or I can build my own mini version of that with BLOBs. The BLOB way is a little more manual if anything. Also sometimes gets annoying with certain DB drivers. Similar story with caches. Sometimes I've used Postgres as a cache, but writing the little logic for that is already more hassle than just plopping in memcached or Redis and using a basic client.
plasticeagle 3y ago
I would absolutely agree with this, were it not for the fact that your table appears to grow without bound - and is very very hard to clean up. I know this, because this is exactly what I did. Put the images and the logfiles from an automated test suite directly into the DB. Brilliant, I thought! No more tracking separate files, performance was great, everything was rosy. And then I tried to prune some old data.

andyp-kw 3y ago

I've seen companies store pdf invoices in the database too, for the same reasons you spoke of.

mohamedattahri 3y ago

BLOB on Postgres are awesome, but there's also a full-featured file API called Large Objects for when the use-case requires seeking/streaming. Wrote a small Go library to interface with it: https://github.com/mohamedattahri/pgfs https://github.com/mohamedattahri/pgfs Large Objects: https://www.postgresql.org/docs/current/largeobjects.html https://www.postgresql.org/docs/current/largeobjects.html

dventimi 3y ago
Large Objects works great for images and stylesheets and other file assets for my small PostgREST projects.
ibgeek 3y ago
TIL thanks!

doubled112 3y ago

I've seen BLOBs in an Oracle DB used to store Oracle install ISOs, which I think is ironic on some level. Let's attach what we're using to the ticket. All of it. Why is the ticketing DB huge? Well, you attached all of it.

richbell 3y ago
I once helped migrate data from a 3+ TB Oracle database for a ticketing system. It was supposed to be shut down more than a year prior but the task kept getting passed around like a hot potato. I can't imagine how much money we were paying in licensing fees and storage costs.

ltbarcly3 3y ago

I think there is too much emphasis on 'the right way' sometimes, which leads people to skip over an analysis for the problem they are solving right now. For example, if you are building something that will have thousands or millions of records, and storing small binaries in postgresql lets you avoid integrating with S3 at all, then you should seriously consider doing it. The simplification gain almost certainly pays for any 'badness' and then some. If you are working on something that you hope to scale to millions of users then you should just bite the bullet and integrate with S3 or something, because you will use too much db capacity to store binaries (assuming they aren't like 50 bytes each or something remarkably small) and that will force you to scale-out the database far before you would otherwise be forced to do so.

crooked-v 3y ago
> and that will force you to scale-out the database far before you would otherwise be forced to do so Disk space is cheap, even when it's on a DB server and not S3. Why worry that much about it?
- tomnipotent 3y ago
  > Disk space is cheap Disk I/O less so. An average RDBMS writing a 10MB BLOB is actually writing at minimum 20-30MB to disk - once to journal/WAL, once to table storage, and for updates/deletes a third copy in the redo/undo log. You also get a less efficient buffer manager with a higher eviction rate, which can further exasperate disk I/O.
  - crooked-v 3y ago
    But if the files are important enough that you need transactional updates or point-in-time recovery for them, you're stuck with the equivalents of those things anyway, wherever those files end up, plus the extra overhead of coordinating two systems.
    - ltbarcly3 3y ago

fiedzia 3y ago

> If the system you're working on is small, and going to stay small, then that doesn't matter. Having good default solution saves a lot of problems with migration. Storing files outside of database today really isn't that more complicated, while benefits even for small files are significant: you can use CDN, you don't want traffic spikes to affect database performance, you will want different availability guarantees for your media than for database and so on.

EvanAnderson 3y ago

The issue I've run into re: storing files as BLOBs in a database has been the "impedance mismatch" coming from others wanting to use tools that act on filesystem objects against the files stored in the database. That aside I've had good experiences for some applications. It's certainly a lot easier than keeping a filesystem hierarchy in sync w/ the database, particularly if you're trying to replicate the database's native access control semantics to the filesystem. (So many applications get this wrong and leave their entire BLOB store, sitting out on a filesystem, completely exposed to filesystem-level actors with excessive permission.) Microsoft has an interesting feature in SQL Server to expose BLOBs in a FUSE-like manner: https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/b... I see postgresqlfs[0] on Github, but it's unclear exactly what it does and it looks like it has been idle for at least 6 years. [0] https://github.com/petere/postgresqlfs https://github.com/petere/postgresqlfs

harlanji 3y ago
Minio is easy enough to spin up, S3-compatible. That seems like my default path to persistence going forward. More and more deployments options seem like they'll benefit from not using the disk directly but instead using the dedicated storage service path, so might as well use a tool designed for that. S3 can be a bit of a mismatch for people who want to work with FS objects as well, but there are a couple options that are a lot easier than dealing with blob files in PGsql. S3cmd, S3fs; perhaps SSHfs to the backing directory of Minio or direct access on the host (direct routes untested, unsure if it maps 1:1).

SigmundA 3y ago

Main issues with large blobs in DB is moving and or deleting them. Not sure if PG has this issue I would like to confirm, but in say SQL server if you delete a blob or try and move it to another file group you get transaction logging equal to the size of the blobs. You would think at least with a delete especially in PG with the way it handles MVCC the blob would just have an entry saying which blob was deleted or something then if you need rollback you just undelete the blob. So just being able to move and delete the data after it is in there becomes a real problem with a lot of it.

vbezhenar 3y ago

It makes backups PITA. I migrated blobs to S3 and managed to improve backups from once a month to once a day. Database is now very slim. HDD space is no longer an issue. Can delete code which serves files. Lots of improvements with no downsides so far.

crooked-v 3y ago
Of course, that also means that now you don't have backups of your files.
- 0x457 3y ago
  They do have backups if versioning is enabled on S3.
  - ericbarrett 3y ago
    Yes, S3 is extremely reliable and versioning protects against “oopsies.” I do always recommend disallowing s3:DeleteObjectVersion for all IAM roles, and/or as a bucket policy; manage old versions twith a lifecycle policy instead. This will protect against programmatic deletion by anybody except the root account.
    - brazzledazzle 3y ago
      I second these sensible protections. Also would recommend replicating your bucket as a DR measure. Ideally to another (infrequently used and separately secured) account. Even better if you do it to another region but I think another account is more important since the chances of your account getting owned is higher than amazon suffering a total region failure.

lovasoa 3y ago

How big does the database need to be for large BLOBs to become a problem ? "big" and "small" are quite subjective terms. How many BLOBs does one need to have, and how often to we need to touch them for this solution to become untenable ?

crabbone 3y ago
In storage you measure things in blocks. Historically, blocks were meant to be 512 bytes big, but today the tendency is to make them bigger, 4K would be the typical size in server setting. So, the idea here is this: databases that store structured information, i.e. such that needs to store integers, booleans, short strings are typically something like relational databases, eg. PostgreSQL. Filesystems (eg. Ext4) usually think about whole blocks, but are designed with the eye for smaller files, i.e. files aren't expected to be more than some ten or hundred blocks in size for optimal performance. Object stores (eg. S3) are the kinds of storage systems that are supposed to work well for anything larger than typical files. This gives the answer to your question: blobs in a relational database are probably OK if they are under one block big. Databases will be probably able to handle bigger ones too, but you will start seeing serious drops in performance when it comes to indexing, filtering, searching etc. because such systems optimize internal memory buffers in such a way that they can fit a "perfect" number of elements of the "perfect" size. Another concern here is that with stored elements larger than single block you need a different approach to parallelism. Ultimately, the number of blocks used by an I/O operation determines its performance. If you are reading/writing sub-block sized elements, you try to make it so that they come from the same block to minimize the number of requests made to the physical storage. If you work with multi-block elements, your approach to performance optimization is different -- you try to pre-fetch the "neighbor" blocks because you expect you might need them soon. Modern storage hardware has a decent degree of parallelism that allows you to queue multiple I/O requests w/o awaiting completion. This later mechanism is a lot less relevant to something like RDBMS, but is at the heart of an object store. In other words: the problem is not the function of the size of the database. In principle, nothing stops eg. PostgreSQL from special-casing blobs and dealing with them differently than it would normally do with "small" objects... but they aren't probably interested in doing so because you already have appropriate storage for that kind of stuff, and PostgreSQL, like most other RDBMS sits on top of the storage for larger objects (filesystem), so they have no hopes of doing it better than the layer below them.

deleted 3y ago

[deleted]

rajman187 3y ago

Several years ago Walmart dramatically sped up their online store's performance by storing images as blobs in their distributed Cassandra cluster. https://medium.com/walmartglobaltech/building-object-store-storing-images-in-cassandra-walmart-scale-a6b9c02af593 https://medium.com/walmartglobaltech/building-object-store-s...

bastawhiz 3y ago

I think the one big problem with BLOBs, especially if you have a heavily read-biased DB, is you're going to run up against bandwidth/throughput as a bottleneck. One of the DBs I help maintain has some very large JSON columns and we frequently see this problem when traffic is at its peak: simply pulling the data down from Postgres is the problem. If the data is frequently accessed, it also means there are extra hops the data has to take before getting to the user. It's a lot faster to pull static files from S3 or a CDN (or even just a dumb static file server) than it is to round trip through your application to the DB and back. For one, it's almost impossible to stream the response, so the whole BLOB needs to be copied in memory in each system it passes through. It's rare that any request for, say, user data would also return the user avatar, and so you ultimately just end up with one endpoint for structured data and one to serve binary BLOB data which have very little overlap except for ACL stuff, but signed S3 URLs will get you the same security properties with much better performance overall.

refulgentis 3y ago
Do you have any thoughts on when a JSON column is too large? I've been wondering about the tradeoffs between a jsonb column in postgres that may have values, at the extreme, as large as 10 MB, usually just 100 KB, versus using S3.
- bastawhiz 3y ago
  It depends on how much you're getting back total. 100 rows returning a kb of data is the same as one row with 100kb. I get worried when the total expected data returned by a query is more than 200kb or so.
- xmprt 3y ago
  Wouldn't the same reasoning for BLOBs apply to JSON columns? Unless you're frequently querying for data within those columns (eg, filtering by one of the JSON fields), then you probably don't need to store all the JSON data in the DB. And even if that is the case, you could probably work out a schema where the JSON data is stored elsewhere and only the relevant fields are stored in the DB. At the same time, I'm working with systems where we often store MBs of data in JSON columns and it's working fine so it's really up to you to make the tradeoff.

deleted 3y ago

[deleted]

cryptonector 3y ago

> But that's only bad if performance is something you're having problems with, or going to have problems with. The first part is easy enough, but how do you predict when you're going to hit a knee in the system's performance?

davedx 3y ago

We ran into issues doing this straightaway, because you also need to fetch the file out of the database in your application server, then send it to clients through the load balancer. That introduces bottlenecks. Generally I agree don't prematurely optimize. But for us, not even having huge files (5-25 MB on average), it slowed our application down unacceptably storing file data in the database, and we ended up moving file data to S3 with a streaming API in front of it.

hot_gril 3y ago

I'm on board with using temporary solutions for small projects, but I feel like BLOB isn't all that convenient either unless you have some particular reason you only want Postgres as a dependency. I can either set up something like S3 where I upload via some simple API and get URLs to send back to clients that will never break, or I can build my own mini version of that with BLOBs. The BLOB way is a little more manual if anything. Also sometimes gets annoying with certain DB drivers. Similar story with caches. Sometimes I've used Postgres as a cache, but writing the little logic for that is already more hassle than just plopping in memcached or Redis and using a basic client.

plasticeagle 3y ago

I would absolutely agree with this, were it not for the fact that your table appears to grow without bound - and is very very hard to clean up. I know this, because this is exactly what I did. Put the images and the logfiles from an automated test suite directly into the DB. Brilliant, I thought! No more tracking separate files, performance was great, everything was rosy. And then I tried to prune some old data.