Users upload profile photos, invoices and attachments. Where do the bytes go: into a BLOB/bytea/varbinary(max) column next to the rest of the record, or into a file store with just a path in the database?
It's one of the oldest database design questions. The answer for most web applications is object storage plus metadata in the database, but storing files in the database is sometimes the right call.
The case against large files in the database
- Backups and restores grow with every upload. A 50 GB database that is 45 GB of PDFs takes much longer to back up, restore, replicate and copy to staging, and you pay for that on every replica and every backup you keep.
- Memory and cache pressure. Reading a file through the database pulls it through the buffer pool or cache, evicting the hot rows your queries need.
- Serving files is not what databases are good at. Every download holds a database connection and streams bytes through your application server. Object storage plus a CDN serves files from the edge, with range requests and caching, without touching your database.
- Scaling costs. Database storage (fast SSD, replicated, provisioned IOPS) usually costs several times more per GB than object storage.
- Tooling: image processing, virus scanning and CDN integrations all expect files in a file or object store.
The standard pattern: object storage + metadata row
CREATE TABLE attachments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
owner_id bigint NOT NULL REFERENCES users (id),
storage_key text NOT NULL UNIQUE, -- e.g. "attachments/2026/09/7f3c…/invoice.pdf"
file_name text NOT NULL,
content_type text NOT NULL,
size_bytes bigint NOT NULL,
sha256 bytea NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
- Store the bytes in S3, GCS, Azure Blob or Cloudflare R2 (or a filesystem, if you're a single server), under a generated key. Never use the user-supplied file name as the key.
- Let clients upload and download directly with pre-signed URLs, so large transfers skip your app servers and database.
- Keep private files private: short-lived signed URLs, not public buckets.
Handling consistency
The trade-off: a database transaction can't include the file write. Plan for the gaps:
- Upload first, then insert the row. If the insert fails, you have an orphaned object, which is harmless and can be cleaned up by a periodic job or a bucket lifecycle rule on an "uploads/pending/" prefix.
- Delete the row first, then the object (or mark it deleted and purge asynchronously). Never leave a row pointing to a missing file.
- Store a checksum and size, so you can verify integrity and deduplicate.
- Turn on versioning or retention on the bucket if accidental deletes would hurt. Your database backups don't cover the files any more, so the bucket needs its own backup and retention policy.
When storing in the database is reasonable
- Small files, and not many of them: avatars of a few KB, small signatures, short documents in an internal tool. The simplicity of one system and one backup can outweigh the costs.
- You need transactional consistency between the file and the row, for example in regulated workflows where "the record exists" must imply "the document exists", with no orphans ever.
- Embedded or offline apps: SQLite on a device, where a single file is the whole point. SQLite is often faster than the filesystem for small blobs.
- There's no object storage available in the environment.
Engine features that help if you do:
- PostgreSQL
byteavalues are compressed and stored out of line automatically (TOAST), up to 1 GB per value. AvoidSELECT *on those tables so queries don't drag the blobs along. - SQL Server FILESTREAM / FileTable keeps blobs on the NTFS filesystem while staying transactionally consistent with the row.
- Put blobs in a separate table (
attachment_content (attachment_id, data)) so scans and ORMs that load the main row don't read the bytes.
Checklist
- Default: bytes in object storage, metadata (key, type, size, checksum, owner) in the database.
- Use pre-signed URLs for uploads and downloads, and generated keys, never user file names.
- Plan for orphans and deletions, and back up the bucket separately.
- Store in the database only for small, few, or strictly transactional files, and keep them in a separate table.
Get the weekly commit
New database deep dives every week.
