Skip to content

Trade-offs · Foundations

Object storage vs storing files in the database

When to keep files in object storage and when to store them inside the database, the numbers that decide, and what breaks with each.

Asked at: SDE-1SDE-2

The decision

A blob (binary large object) is a file stored as raw bytes, such as a photo or a PDF. You can put the bytes in a database column. Or you can use object storage, a service built for files. Then the database keeps only a small record: the owner, the object key, the size and the status. Most apps choose the second way.

Choose object storage when

  • Files are larger than a few hundred KB, such as photos and videos.
  • The total grows without a clear limit. Object storage grows by the byte, and a database disk does not.
  • Many users download the same files. You can put a CDN, a network of cache servers near users, in front.
  • Phones upload files on slow networks. A pre-signed link lets the phone send the file straight to the store. A pre-signed link is a web address that gives permission for one action on one object for a short time.

Choose the database when

  • Files are tiny, under about 100 KB (assumption), such as an avatar thumbnail.
  • The count and total size are small and stay small. For example, 10,000 files of 5 KB make 50 MB.
  • The file and its row must change together in one transaction, so there is never a row without a file.
  • You want one thing to back up and restore, and the system is small.

The numbers that change the answer

Worked example at Indian scale. A photo app. Assumptions: 1,000,000 uploads a day of 3 MB each. A metadata row is 1 KB. There are 500 photo views a second. A phone downloads at 0.5 MB per second. The database disk is 4 TB and allows 200 connections.

QuestionCalculationResult
New bytes per day1,000,000 x 3 MB3,000,000 MB = 3 TB
Days to fill a 4 TB disk4 / 31.3 days
Bytes per year3 TB x 3651,095 TB
Metadata per day1,000,000 x 1 KB1 GB
Time to send one photo3 MB / 0.5 MB per second6 seconds

The last row matters most. Requests in progress = requests per second x seconds each takes.

  • Photos read through the database, which holds a connection while it streams: 500 x 6 = 3,000 connections. The limit is 200, so the database fails at 200 / 6 = 33 views per second.
  • Photos from object storage and a CDN: the database only reads the 1 KB row, say in 5 ms. That is 500 x 0.005 = 2.5 connections.

Two limits to know. A Postgres value can be at most 1 GB, and a row wider than about 2 kB gets big values compressed or moved aside (TOAST). One S3 object can be 48.8 TiB, and S3 advises multipart upload from 100 MB.

What breaks with each choice

  • Database blobs: the database grows fast (3 TB a day here). Backups, restores and every new copy of the database move every byte. Big values push useful rows out of the memory cache. Connections stay busy during slow downloads.
  • Object storage: the file and the row are in two systems, so one can exist without the other. A user may start an upload and never finish. Fix: save the row as "uploading" and set "ready" after you check the object. Run a clean-up job for old "uploading" rows and objects with no row.
  • Object storage: you cannot change a few bytes in place, and you cannot search by content. You replace the whole object, and you search the metadata in the database.
  • Object storage: a pre-signed link works for anyone who holds it. Keep it short-lived and out of logs.

What it costs

  • Database blobs are cheap to start and costly to leave. Moving later means copying everything. Calculation: if one year of photos were in the database, that is 1,095 TB = 1,095,000,000 MB. A 1 Gbps link carries 125 MB per second. The copy takes 1,095,000,000 / 125 = 8,760,000 seconds, which is 101 days.
  • Object storage charges per GB stored, per request and for data sent out. Check the provider price page. Many tiny files can cost more in request fees than in space.
  • Object storage needs extra code: the upload steps and the clean-up job.

How each level answers

SDE-1: Files are large, so I keep the bytes in object storage and only the metadata in the database.

SDE-2: I let the phone upload straight to the store with a pre-signed link, so my servers do not carry the bytes. I save the row first as "uploading" and mark it "ready" after the check.

Senior: One day of photos is 3 TB, so a database disk of 4 TB is full in 1.3 days, and one year is 1,095 TB. Reading through the database would need 3,000 connections against a limit of 200. The risk is orphans, so I run a clean-up job. For 5 KB thumbnails, I would still use the database.

Staff: I ask what the files are worth and how long we keep them. I set lifetime rules to delete old files or move them to cheaper storage. One team owns the clean-up job and the bill. If a team starts with database blobs, I set a size threshold at which we move, because the copy takes 101 days on one link. I check the choice by cost per GB, orphan count, and database size.

Say it in the interview

I would keep photos in object storage and only a 1 KB record in the database, because one day of uploads is 3 TB. The phone uploads straight to the store with a pre-signed link. The risk is a row without a file, so I use an "uploading" status and a clean-up job.

Common mistakes

  • Saving big files in the database for simplicity. Backups, replicas and connections pay later.
  • Sending uploads through the app server. Every byte then passes your server, and a slow phone holds a connection for a long time.
  • Trusting the size and type the phone claims. Check the object after it arrives.
  • Forgetting the clean-up job. Unfinished uploads stay, and you keep paying.

Sources

Understand the trade-off. Practise explaining your choice.

In an AI mock interview, the interviewer can push on a choice like this one: it changes a number and asks if your answer still holds.

See the mock interviewsAll trade-offs