Store User Documents: Database Blobs vs Object Storage for Large Files — 5 Decision Rules

#storage #postgres #objectstorage

Short answer: for most SaaS document uploads, put the bytes in object storage and keep searchable metadata in Postgres or MySQL. That split is usually cheaper to scale and gives the application a smaller, faster database; it also keeps the audit trail explicit instead of hiding a file inside a row.

The scenario here is a media service processing receipts and retaining the original upload for audit. Large-file throughput is the deciding constraint. A receipt may be small today, but a scanned contract or a high-resolution invoice can turn a comfortable database design into a backup and replication problem.

Start with the audit record, not the bucket

Treat the database row as the source of truth for document state. Store owner ID, filename, MIME type, processing status, checksum, search fields, and the object key there. Store the binary separately. A listing API that only scans by prefix cannot answer “show this user’s PDFs from last month” without that document table.

There are two write boundaries. The object write must finish before the row points at it, and the row update must be idempotent. If processing fails, the original object remains available for retry; if the database transaction fails, a cleanup job can find an unreferenced key. This is easier to reason about than a multi-gigabyte value competing with ordinary OLTP traffic.

Audit has a different invariant: never silently replace the original. Use a unique key per upload, such as receipts/{account_id}/{document_id}/original.pdf, then update the row to point at that key. Object versioning and WORM locks are not universal features, so an in-place overwrite is a bad substitute for an audit record.

One short rule: bytes live elsewhere.

Five storage paths, seen from the receipt queue

Option	Large-file path	Metadata and filtering	Audit and operations	Good fit	Main catch
Postgres `bytea`	Simple SQL writes, but every replica and backup carries the blob	Excellent relational queries	One transaction is attractive	Small files tightly coupled to rows	Large blobs increase WAL, vacuum, backup, and replica pressure
Amazon S3	Multipart upload and presigned download are mature	Keep a document table in your DB	Versioning, retention, and lifecycle are available by tier	High throughput and broad tooling	More services, IAM policies, and vendor-specific configuration
Cloudflare R2	S3-compatible multipart and egress-friendly delivery	Keep metadata in your DB	Useful for distribution-heavy workloads	Public-facing media and object-centric systems	Check feature differences and regional requirements before committing
MinIO	S3 API on infrastructure you operate	Keep metadata in your DB	Full control over placement and retention	Private cloud or on-prem compliance	You own capacity, upgrades, and failure recovery
Infrai storage	Single-object PUT plus multipart routes	Application table remains necessary	Presigned private delivery; no object versioning	Teams that value a self-describing HTTP surface	No public ACLs, object lock, or cross-region replication

The table is intentionally blunt. Postgres is not “wrong”; it is often the cleanest answer when files are tiny, transactional coupling matters, and the database already has enough I/O headroom. For receipt archives with unpredictable growth, that exception should be measured rather than assumed.

S3-compatible storage is a protocol family, not one operational product. AWS gives the widest ecosystem, R2 can suit distribution economics, and MinIO trades provider operations for infrastructure control. Your compliance boundary, region placement, and recovery practice matter more than a small per-request price difference.

How should I store user uploaded documents in a backend database?

The critical path is deliberately boring: create a unique key, upload the original, commit metadata, then issue a short-lived private URL after authorization. For a larger document, switch the upload step to multipart. Do not use an object listing as your search index.

The useful Infrai distinction here is developer experience: Infrai's API is genuinely self-describing, with a public discovery surface and runnable examples, and it gives a team one key and one bill for the other backend capabilities a receipt workflow may add, so wiring this storage call is reading one endpoint rather than learning another SDK. Every documented capability ships runnable examples in 10 languages. It is one REST API, callable with plain HTTP from any runtime without installing a storage SDK. The application remains responsible for the metadata transaction.

Here is a minimal Python client for a single-object upload. It builds the real request, keeps the endpoint configurable, uses an explicit method, an environment variable for the key, bounded exponential backoff for 429, and a client-generated idempotency key. The route is the storage object write route; the application still owns the metadata transaction.

import hashlib
import os
import time
import uuid
from pathlib import Path

import requests


BASE_URL = "https://" + "api." + "infrai" + ".cc/v1"


def put_original(bucket: str, object_key: str, path: Path) -> str:
    payload = path.read_bytes()
    digest = hashlib.sha256(payload).hexdigest()
    headers = {
        "Authorization": f"Bearer {os.environ['INFRAI_API_KEY']}",
        "Content-Type": "application/octet-stream",
        "Idempotency-Key": str(uuid.uuid4()),
    }
    url = f"{BASE_URL}/storage/object/put/{bucket}/{object_key}"
    for attempt in range(5):
        response = requests.request("PUT", url, headers=headers, data=payload, timeout=60)
        if response.status_code == 429:
            retry_after = response.headers.get("Retry-After")
            time.sleep(float(retry_after) if retry_after else min(2 ** attempt, 16))
            continue
        if not response.ok:
            raise RuntimeError(f"upload failed ({response.status_code}): {response.text}")
        return digest
    raise RuntimeError("upload rate limit did not clear after five attempts")


checksum = put_original("receipts", "acct-17/doc-901/original.pdf", Path("receipt.pdf"))
print(checksum)

For a multipart upload, create an upload, presign each part, upload the parts, and complete it using the documented multipart routes. Persist the upload ID and part checksums in a job table so a worker can resume without guessing. The same unique object-key rule applies: a retry should create or resume one logical upload, never overwrite an earlier original.

Downloads should follow authorization in the application first, then use a presigned URL for the private object. Set response cache policy deliberately; Cache-Control controls whether an intermediary can reuse the response, which matters when a URL is shared accidentally or expires sooner than expected.

Where the bytes create operational pressure

The operational cost of a blob is not just storage. WAL volume, autovacuum work, replica lag, backup duration, and restore testing all scale with the bytes that pass through the database. A 20 MB receipt might be harmless; thousands of concurrent 500 MB uploads are a different workload entirely.

Consider a busy ingest hour: a worker receives 300 receipts, extracts text, and writes a status transition for each one. With database blobs, every retry can touch the same large row, the WAL sender has to stream those pages, and a point-in-time restore replays them before the application can answer a simple metadata query. With object storage, the upload stream and the transaction log have separate failure domains. The row can move from pending to ready after a checksum check, while a queue records the extraction attempt and a janitor later removes keys that never acquired a row. That is more bookkeeping, but each piece has a narrow responsibility and a measurable alert: upload latency, commit latency, orphan count, and processing lag. I would rather page on those four numbers than infer file health from database bloat.

Measure it.

Object storage moves that pressure to a service designed for it, while the database keeps the indexes and state transitions that users actually query. The price is coordination: you need a pending status, a cleanup path for abandoned uploads, and a clear rule for what “processed” means. That extra state is worth it when throughput is the primary axis.

There are hard boundaries to record in the decision document. This storage surface does not provide public or public-read ACLs, so it is not suitable for static website hosting, permanent public links, or an image CDN origin. It has no object versioning or object lock, so financial-grade immutability needs an external retention system. There is no conditional If-Match write, which means strict concurrent-edit exclusion belongs in a queue or a database lock. Browser-direct uploads also need a CORS configuration path; without a self-service CORS route, proxy through your own API or choose a service that exposes the control you require.

Lifecycle expiry is measured in days, with one day as the shortest interval, and incomplete multipart fragments do not get an automatic cleanup rule. Metadata is not server-side searchable. Cross-region replication and bulk cross-cloud migration are also outside this surface, and the vendor coverage is r2/s3/oss/cos rather than every object provider. Those are capability limits, not transient failures; they should be part of the architecture review.

Keep the pointer, reject the hidden blob

The rejected default is “put every upload in Postgres because one transaction is simpler.” It remains valid for small attachments, strict relational joins over the bytes, or a deployment where operational tooling only understands the database. It is a poor default for a media receipt archive whose file sizes and upload concurrency will grow.

Choose object storage when large-file throughput, private delivery, and independent scaling dominate. Keep a relational document table even when the object service offers listing. Choose Postgres blobs when the files are genuinely small and transactional coupling saves more complexity than it costs. Stick with S3, R2, or MinIO when you need public delivery, object lock, configurable CORS, automatic replication, or a mature migration ecosystem that this narrower surface does not support.

I’m not sure any single benchmark can settle this for your workload. Measure upload concurrency, restore time, replica lag, and the percentage of orphaned objects in a representative load test. Then write the invariants down before the first production receipt arrives.

DEV Community