2

I have read several discussions about storing BLOBs in the database vs in an object storage. What I need in addition though is a functionality for querying these BLOBs. The BLOBs will be immutable compressed multi-dimensional arrays that will be 10s of MBs. I mention that, since they are not files that will be served, but rather objects that will be used for some heavy computation outside of the database.

Let me illustrate that by presenting the intended DB schema with a small example, assuming we will be storing them in a relational database.

items

| id       | price          | company_id    |
| -------- | -------------- |-------------- |
| 1        | 5              |1              |
| 2        | 25             |1              |

compute_info

| id       | item_id        | type     | info         |
| -------- | -------------- | ---------|--------------|
| 99       | 1              | simple   | [large-blob] |
| 231      | 1              | complex  | [large-blob] |
| 77       | 2              | simple   | [large-blob] |
| 449      | 1              | complex  | [large-blob] |

I want to be able to construct complex query that will return only the info for items that match certain criteria, e.g.

SELECT items.id, compute_info.info
FROM items
JOIN compute_info
ON items.id = compute_info.item_id
WHERE items.company_id = 1
AND items.price > 20
AND compute_info.type = simple

Some of the assumptions and other requirements are:

  • The query SELECT COUNT(*) FROM items WHERE company_id = ? will usually return <1 million.
  • The BLOB content itself will not be queried.
  • There might be multiple concurrent users. There might be 1000s of them in total, but 100s who will actually be reading the BLOBs.
  • There are no fixed requirements in terms of database choice or object storage. The goal is to pick the best one that solves this particular problem.
  • We can assume a distributed architecture.

The question is what is the proper way to store these BLOBs if performance is critical without introducing too much complexity? The options that I have considered are:

  • Store them as BLOBs in the database, just as illustrated in the example. This has the advantage that I could use a JOIN directly.
  • The info column stores a path to an object storage. This has the problem that we'd need to access a couple of thousand separate files.
  • Use something like TensorStore given that the application is ML. I don't have any experience with this, but I'd still need efficient random access.
  • Some other approach.
14
  • 4
    From what I can tell you will not query any data inside the blobs, only metadata stored in the database table. In that case, I see no difference between your blobs and images, and you should probably use the file system, see 1 2
    – JonasH
    Jan 25, 2023 at 15:52
  • "I have read several discussions about storing BLOBs in the database vs in an object storage." If you were reading good discussions/answers, I would expect that they informed you that there's no real benefit to putting BLOBs in the DB. Can you explain what you think you gathered from these?
    – JimmyJames
    Jan 25, 2023 at 16:18
  • Yes, that assumption is correct. The data inside the BLOBs won't be queried. Jan 25, 2023 at 16:18
  • "I mention that, since they are not files that will be served, but rather objects that will be used for some heavy computation." Outside of the DB, correct?
    – JimmyJames
    Jan 25, 2023 at 16:21
  • 4
    A typical concern with outsourcing blobs to file systems or object storage is the difficulty of keeping them consistent with the database. This isn't a concern here since the blobs are immutable. Storing the blobs externally might lead to extra latencies (more network roundtrips until client receives blob contents), but typical object storage has much better facilities for streaming and caching data than database drivers so it might be faster in practice.
    – amon
    Jan 25, 2023 at 23:52

2 Answers 2

4

You wrote

What I need in addition though is a functionality for querying these BLOBs.

but then in a comment something which should have been part of your question, because it makes an important difference:

The data inside the BLOBs won't be queried.

So as long as you write a query which just returns non-BLOB data, with any sane relational DB system it should not make a huge difference whether the BLOBs are stored directly inside the DB or outside. The definition of indexes on the columns which might be used in the WHERE condition of your queries is not affected by the storage of the BLOBs.

Now, when it comes to getting the BLOBs themselves out of the database again, query time will be mostly a result of how quick the non-BLOB part of the query is executed plus the time to transfer the BLOB data over the network, which is mostly dependend on the amount of data to be transfered. In theory, this is still not dependend from where the BLOB data is stored, in the database or on some other server or storage media, as long as the same network between server and client is used in the middle. At least, the "Big-O" running time order of your queries will be the same.

In reality, however, you will notice performance differences. But these are heavily dependend on a lot of low-level details: which DBMS you are using (and how well it is optimized for BLOB storage, which may differ from version to version, or dependent on specific table storage configuration), what kind of external storage system you have available, and where it is located in the network between the DB and the client. It may also depend on implementation details of your client software, and how much optimization effort you invest.

So, my best recommendation here is to RTFM of your DBMS and see what it tells you about BLOB storage and how to optimize it. Make some tests, and when it turns out you don't reach the performance you like to get, then try out something more sophisticated.

Addendum: somewhere in a comment you mentioned PostgreSQL. Here is a full Wiki page about the pros and cons of storing binary files inside or outside a PostgreSQL database. Hope this helps.

3
  • 3
    Great answer, I'd like to stress the network part of the answer. If you're system has a lot of DB-server communication you really don't want to block/throttle other parts of your app just because a BLOB-fetching query's output hit the bandwidth. Query execution can be optimised to give exponentially better results, query I/O can only provide linear improvements without major architectural changes.
    – 5ar
    Jan 26, 2023 at 10:47
  • Thanks for the answer. The issue is that I haven't chosen a DBMS. It's essentially part of the question to chose the most optimal solution. Jan 26, 2023 at 12:03
  • 1
    @IvayloToskov: we do not recommend specific DBMS, that's off-topic for this site. And what "most optimal" for you is depends on many factors beyond the scope of what we can evaluate here for you. This includes budget, prior knowledge in your team, intended life time of the system, performance requirements, existing ecosystem at your workplace, licensing, intended operating environment, and so on.
    – Doc Brown
    Jan 26, 2023 at 12:46
2

I want to be clear that this is not a black-and-white situation. There are a lot of factors here and some of them are not strictly technical.

Let's start with the pros of keeping the BLOBs in DBMS:

  • Simplicity - both the relation data and non-relational data is in the same place with the same access controls

Now we can talk about the potential cons:

  • Constraints on storage options
  • Increased load on the DB
  • DB backups slower, more space required
  • Horizontal scaling challenges

There really isn't much advantage to storing binary data in a relational database. Sometimes this is done to allow the data in the LOBs to be queried from within the database (not something I recommend either) but that's not relevant here. Essentially, BLOB storage is a way to stuff some bytes in a place where it doesn't really belong and use the DB as a glorified file system. It's not always a bad idea, if you have a few LOBs that you need to store, it might not be worth the trouble of finding another solution.

When you lock yourself into that as a solution, however, it will limit your options. For example, if you put the blobs in some sort of distributed storage such as S3, you have a lot more options. For example, if your users are located around the world, you can replicate these BLOBs regionally with very little effort. Your storage costs are also likely to be a lot cheaper than putting them on the disk that supports the database.

If you keep the LOB data in the DB, that DB now can potentially become a bottleneck of the solution. If a number of BLOB references are returned by one query, can you easily pull them down in parallel? If you have 100 clients pulling 100 BLOBs from the same DB at the same time, what does that mean for the requirements of the DB server? Much of that depends on the DB you choose. This is the kind of situation that undermines the simplicity of keeping everything on the DB.

It's also often the case that the RDBMS has the heaviest load of any component of a system. I therefore prefer to avoid putting things on the DB when there's no specific advantage to doing so. On the other hand, there are costs involved in adding complexity to a system.

One option here is to keep URIs in the table instead of BLOB pointers. Where that URI actually resolves to is abstract. You could still keep the BLOBs in by setting up a simple HTTPS server in front of them or you could put them in any other kind of storage you like, even in memory. But let's assume you kept them in the DB and you start to have issues because of that. You could create a separate DB instance for the BLOBs as a quick fix and/or move to some other solution without disrupting the clients.

Another thing to consider is that you might even be able to store some, if not all of the LOB data local to the clients. This might just be a local cache which could work with any option here, including BLOBs in the DB. As long as the data really doesn't change, there's no need to repeatedly transfer them across the network.

6
  • Thank you for your elaborate answer! This gives me a good direction for my choice. Do you think there are any performance benefits for storing the LOBs in DBMS? What I considered as a potential problem is that you will need to open multiple files in an object storage to read all the data, but maybe that's not too much of an issue. Jan 30, 2023 at 21:47
  • 1
    @IvayloToskov I don't see any perofrmance advantages, only problems. This is something that I've (unfortunately) had a lot of experience with and it's generally a problem. Essentially you are making the DB responsible for managing more IO throughput. By storing the binary data in something designed for that purpose, you let the DB (and its tuning) be focused on what relational DBs are designed for and spread that IO load to other resources. I'd be really interested if someone can point to a performance advantage.
    – JimmyJames
    Jan 30, 2023 at 21:59
  • @IvayloToskov "By storing the binary data in something designed for that purpose" on re-reading this, I may not have been totally clear: relational DBs are not (in general) designed to be binary storage systems. This kind of thing doesn't at all fit into the relational model. It's mainly a convenience.
    – JimmyJames
    Jan 30, 2023 at 22:22
  • @IvayloToskov there is a potential "performance benefit" in that you'll get all the data in a single network request (the one to the DB) and don't need to do any extra checks to preserve transactional properties (e.g. referential integrity between the DB and BLOB storage). However, this is only a benefit if your system is processing one request at a time, which is rarely the case (unless the system is very small, basically in prototype phase).
    – 5ar
    Jan 31, 2023 at 13:02
  • in addition to the mentioned potential "performance benefit", storing it somewhere else than the DB still might be a better option even for a single user session in case of larger BLOBs, as solutions specialised for BLOBs will probably provide performance improving features such as localised caching and chunking/streaming larger files
    – 5ar
    Jan 31, 2023 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.