Files in database anti-pattern

TL;DR

• Storing binary files (images, PDFs, videos, audio) as BLOBs in a relational database uses a storage engine designed for structured, indexed, queryable data to store bytes it cannot query, index, or compress.
• The costs: row bloat slows all queries (not just file queries), backups take forever, replication lag grows, sequential table scans read giant BLOBs, VACUUM is slow, and you cannot CDN-cache database content.
• In most systems, store files in an object store (S3, GCS, Azure Blob Storage). Store the file path or key in the database. Serve files via CDN when the access pattern benefits from it.
• Common exceptions include very small, frequently updated binary data (thumbnails under 100KB in a system that never serves them to end users) where the overhead of S3 round-trips dominates.

Introduction

A file has a different storage lifecycle from the metadata that describes it. Relational databases are excellent at transactions, constraints, and queries over narrow records; object stores are designed for large opaque payloads. The anti-pattern appears when a database is made responsible for both without checking download volume, backup impact, or retention needs.

Mental Model

Keep the file and its record separate but linked:

• Database: ownership, filename, content type, size, checksum, retention, and the object key.
• Object store: the immutable or versioned bytes.
• CDN or direct object access: the delivery path for end users, so application and database connections are not held open for the transfer.

The transaction usually protects metadata and the reference. A cleanup process, checksum, versioning, or an outbox can handle the fact that the object store and database are separate systems.

The Problem

A developer building a document management system stores uploaded PDFs directly in the database. The schema looks reasonable:

CREATE TABLE documents (
    id         UUID PRIMARY KEY,
    filename   VARCHAR(255),
    content    BYTEA,           -- stores the PDF binary
    uploaded_at TIMESTAMP,
    user_id    UUID
);

Upload 50,000 documents averaging 2MB each. Your database is now 100GB larger just from content columns. Your nightly backup takes 6 hours instead of 20 minutes. A sequential scan of the documents table for any reason reads 100GB of binary data. VACUUM must process every BYTEA column on every dead tuple. Replication log includes all 100GB, so your replica has 30 minutes of lag.

Your CDN cannot cache the PDFs because the browser downloads them through an API endpoint that hits your database. Every document download consumes a database connection for the duration of the transfer. At 2MB per file and 100ms network time, that is one connection tied up per download.

For example, a PostgreSQL deployment with 50,000 user-uploaded documents can show this pattern: a simple SELECT filename FROM documents WHERE user_id = ? took 800ms because the planner had to skip over BYTEA toast pages. Moving files to S3 dropped that query to 3ms.

Before and After

Before, the database row owns both metadata and the payload:

CREATE TABLE documents (
    id UUID PRIMARY KEY,
    filename VARCHAR(255),
    content BYTEA
);

After, the database owns the reference and the object store owns the bytes:

CREATE TABLE documents (
    id UUID PRIMARY KEY,
    filename VARCHAR(255),
    s3_key VARCHAR(512) NOT NULL,
    size_bytes BIGINT NOT NULL,
    sha256 CHAR(64) NOT NULL
);

Uploads can write the object and metadata through an idempotent workflow; downloads can return a pre-signed URL or a CDN URL instead of streaming the payload through a database connection.

Why It Happens

Teams store files in the database because it feels simpler. One system, one backup, one transaction. The individually-reasonable logic:

1. "One system is easier to operate." True for development. Catastrophic at scale when that one system does two fundamentally different jobs.
2. "We need transactions around the file." You need the metadata to be transactional. The file itself is immutable after upload.
3. "S3 is another dependency." It is, but managed object storage is designed for opaque objects and high-volume access. S3's standard storage documentation cites 99.999999999% durability; the architectural point is to choose the system whose access and recovery characteristics match the payload.
4. "We only have a few files." Every system that stores files in the database started with "just a few files."

The mismatch is fundamental. Databases are optimized for narrow, indexed, queryable rows. Files are the opposite: