2

I am designing an application where there will be one column that stores a large amount of text. I'm debating whether the text should be stored in the database itself, or whether the database should store a reference to an object store such as Google Cloud Storage or Amazon S3, and have the application use that reference to pull from those services. My thinking is that the object store offers a significantly cheaper storage cost per GB than the database; but I am uncertain what I am trading off in doing this.

So what tradeoffs are there to storing large binary data in my database as opposed to an object store?

  • 1
    These days.... Why? You get so much more reliability with S3, it's easy to set up cross-region replication, and it's just faster to use. – Berin Loritsch Jan 7 at 4:41
  • Agree with @BerinLoritsch S3 also has a number of tiers which you can choose from based on your access pattern, choose the one that fits your need. – skott Jan 7 at 9:45
  • Do you have a db in the cloud? are there security requirements? are there transactional constraints? is the blob text or binary? could the blob become relevant for querying ? di you have other objects in a store? I think we go to fast into a technical solution, without looking at the context. Can you provide us some more elements about the tradeoff ? – Christophe Jan 7 at 10:55
  • 1
    Do you need to full text search it? Not sure if you can do that with an object store. – GrandmasterB Jan 7 at 15:46
4

The law of the instrument tells us that:

If you have a hammer in your hand, every problem starts to look like a nail.

So before jumping too quickly to a solution look objectively at the needs and requirements. In particular security needs, access control, and transactional consistency requirements.

Blob in DB:

  • Pros:

    • benefits from the access security of your database, as well as its backup strategy
    • confidentiality against unauthorized third party access (e.g. if you’re in governement or military affairs)
    • simplification of architecture due to homogeneous access
    • reduction in the external bandwidth if db is on premises and if you have lots of internal users.
    • blob is managed with transactional integrity, as the remaining data
  • smaller text blobs could fit into text fields (eg up to 1GB in Postgresql), allowing to use content in queries.

  • Cons:

    • technical limits of the db (eg 1GB on Postgresql, a little less than 2GB on SqlServer, 4GB on Oracle, support only via GridFS in MongoDb)
    • significantly increases size of the db
    • increase operational cost of the db, especially if storage goes significantly beyond initial assumptions.
    • waste of expensive infrastructure (tier 1 disk space meant for heavily used db data but misused to store objects that are not frequently accessed and could go to tier 2 or 3).

Blob in an object store

  • Pros:

    • very effective and specialised solutions on the market
    • cost effective
    • easy to set up
    • resilient (depending on the offer), with backup included
  • Cons:

    • accessibility risks if mission critical application (e.g. in case of denial of service attack on the provider or on your internet access point)
    • confidentiality risk (but IMHO this only applies to highly sensitive and classified information in governmental and military affairs)
    • additional access security may be needed ( eg AWS identity management and acl management)
    • you need to take care of transactional consistency aspects (e.g. what to do if a blob update fails, regarding the related db changes)

Your current context may influence this evaluation. For example, if you already have some cloud services and identity management active, this will no longer be a cons. Or if your current db has a very small limit for blobs (eg Mongodb) many of the object store cons will not be relevant for you since you’ll have to care for this anyhow.

  • For proper cloud provided blob storage, you might be able to take down one region with a very powerful DDOS attack but you can't take them all down. Combine that with the equivalent of Route 53, to route traffic to the best endpoint, and your accessibility risk goes away. – Berin Loritsch Jan 7 at 15:01
  • Confidentiality can be addressed by making proper use of all the security controls that your provider gives you. There are certain regions of AWS for example that are vetted for Government. That also can address your additional access concerns. – Berin Loritsch Jan 7 at 15:02
  • If your object store is not from a cloud provider, your cons do ring true. Otherwise I think they are overstated. – Berin Loritsch Jan 7 at 15:03
  • @BerinLoritsch You are right: the risk is low. But what if the impact is high ? “Mission critical” means that lives depend on it. It must work at full speed all the time. But if you are in a deeply disturbed area, your traffic will compete with a lot of trafic to cross the Atlantic and you might time out before reaching the second region. My message here, is only that a careful threat assessment must be done: what is acceptable for a normal business could not be acceptable for a critical system (eg. government, hospital, nuclear powerplant, power grid...) and I don’t know the context of OP. – Christophe Jan 7 at 15:11
  • @cristoffe, every con you have in the blob storage side is doubly a con on the DB side. It's pretty easy to overwhelm a database. – Berin Loritsch Jan 7 at 15:12
3

I have the same argument, with different conclusions. The problem statement is that you have large text objects that need to be stored, but did not mention what actions need to be taken outside of storage.

Bottom line:

  • Blob storage is cheap, fast, and just about seven nines of uptime
  • Generally well secured, and you have good control over traffic can be shaped
  • Great for general storage needs

What you give up is:

  • The ability to index the text for fast searching--you might use ElasticSearch as a service to meet that need

Databases are generally efficient with blob storage up to about 1MB or so, and beyond that most databases recommend storing those blobs outside of the database (Microsoft SQL Server, notably makes this recommendation). You can easily bloat the database making the logistics of replication, backup, and restore more difficult.

1

I think both Cristophe and Berin make good points about the considerations. I will not repeat these but offer some basic advice based on some personal experience with this.

If your database can support the LOBs you wish to store (they are within the limits for your platform) and your total space allocated to these LOBs is not a significant percentage of your total DB storage, there's not a strong driver for avoiding using the DB for this. The added complexity is probably not worth it without some other additional reason for wanting to eliminate the LOBs from the DB.

However if you are going to need to use a large portion of the DB storage for LOBs e.g. more than 50%, then it's worth considering. While you can add LOBs to a DB, it's not really what relational databases are meant for. It's like buying a Swiss army knife because you need a toothpick.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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