Should you store images in the database or in object storage?

Almost every app stores images: avatars, product photos, receipts. Putting them all in the database looks like the simplest path, since it already exists, already has backups and already has transactions. It works, but it usually gets expensive.

Below are each database's limits, the effect on performance and backups, and the pattern most projects end up with. There is no universal winner, but there is a point where the math changes.

How do you store an image in a database?#

In relational databases the image becomes a binary column, and the file content travels inside the INSERT. The types differ by database:

• MySQL: BLOB (up to 64 KB), MEDIUMBLOB (up to 16 MB) and LONGBLOB (up to 4 GB). What can actually be transferred also depends on max_allowed_packet, which must be raised on both the server and the client.
• PostgreSQL: bytea, limited to 1 GB per value. Large values go to a TOAST table, outside the main row. Beyond that limit there are large objects, with their own API.
• MongoDB: a BSON document holds at most 16 MiB. For larger files the documentation points to GridFS, which splits a file into 255 KiB chunks by default and writes them to fs.chunks, with metadata in fs.files. If every file is under 16 MiB, the documentation itself suggests storing each one in a document using the BinData type.

In every case the file is just one more piece of database data. That is where both the advantages and the problems come from.

What are the advantages of storing images in the database?#

• Transactional consistency. Deleting the row deletes the image. There are no orphaned images and no rows pointing at a missing file.
• One system. No second service, no second set of credentials, no CORS or bucket permissions to configure.
• A single backup. One dump holds data and images together, in the same version.
• Reused access control. Whoever can read the row can read the image, under the rules you already have.

For a prototype, an internal tool or a handful of small icons, this favors the database.

What are the problems with storing images in the database?#

• The database bloats. A database with 2 GB of data grows to tens of GB once the photos move in. Restoring, migrating and replicating take hours, and every dump carries all the files again.
• Every image goes through the database connection. To show a photo, your API reads the bytes from the database and resends them to the browser. Database connections and memory, usually the scarcest resource, get tied up serving static files.
• No direct CDN. An image served by your API reaches an edge cache only with extra work. With object storage, the URL can already be cached by a CDN.
• Contested memory. Large binaries compete with the hot data the database should keep cached.
• Hard limits. bytea stops at 1 GB and BSON at 16 MiB, and PDFs and videos hit that quickly. Unless you select fields, an ORM that loads the whole row pulls the binary column along.
• Scaling cost. Growing a database just to fit files means paying for query capacity the files never use.

What is object storage and what does it solve?#

Object storage keeps files as objects in buckets, each with a key. You upload a file over HTTP and it gets a URL. Object storage was designed for this: many files, varied sizes, reads from anywhere on the internet.

It covers what a database does not: CDN-cacheable public URLs, presigned URLs for private files, lifecycle rules such as automatic expiry, direct browser uploads that skip your API, and multipart uploads for large files. In exchange, no transaction spans storage and database, so keeping the two consistent becomes your job.

Database or object storage: which one should you pick?#

Pick the database when files are few, small (a few KB), rarely read and must change together with their row. Pick object storage when images come from user uploads, exceed a few hundred KB, appear on public pages or the volume is likely to grow. When in doubt, start with storage: pulling GBs of images out of a database later is far more work than starting outside it.

How do you implement "file in storage, key in the database"?#

Upload the file to storage, write only the key and metadata to the database, and build the URL at read time. The example uses Python, boto3 and psycopg 3 (pip install boto3 "psycopg[binary]"), credentials in environment variables:

import os
import uuid

import boto3
import psycopg

s3 = boto3.client(
    "s3",
    endpoint_url=os.environ.get("S3_ENDPOINT"),  # omit on AWS S3
    aws_access_key_id=os.environ["S3_ACCESS_KEY"],
    aws_secret_access_key=os.environ["S3_SECRET_KEY"],
)
BUCKET = os.environ["S3_BUCKET"]


def save_avatar(conn: psycopg.Connection, user_id: int, data: bytes) -> str:
    key = f"avatars/{user_id}/{uuid.uuid4()}.webp"
    s3.put_object(Bucket=BUCKET, Key=key, Body=data, ContentType="image/webp")
    try:
        conn.execute(
            "UPDATE users SET avatar_key = %s, avatar_size = %s WHERE id = %s",
            (key, len(data), user_id),
        )
        conn.commit()
    except Exception:
        s3.delete_object(Bucket=BUCKET, Key=key)  # leave no orphaned object
        raise
    return key


def avatar_url(key: str) -> str:
    # private file: presigned URL valid for 10 minutes
    return s3.generate_presigned_url(
        "get_object",
        Params={"Bucket": BUCKET, "Key": key},
        ExpiresIn=600,
    )

Three details matter. The first is order: write the file before the row, and delete the object if the database fails. The worst case is then a leftover object, never a row pointing at nothing. The second is a new key on every upload (here, a UUID), because replacing the content under the same key leaves old caches serving the stale image. The third is to store the key, not the URL, and decide at read time whether the image is public or private (see public vs private files).

To clean up orphans that slipped through, run a job now and then that compares the bucket's keys with the database's, or send temporary uploads to a folder with an expiry rule.

How to do this on Square Cloud#

Square Cloud's Blob Storage is object storage with an S3-compatible API, so the code above works with boto3, aws-cli, rclone or the AWS SDKs, pointing S3_ENDPOINT at https://s3-blob.squarecloud.app (region auto) with the keys from the "S3 access" tab of the dashboard. You can also use the REST API or the dashboard. Each file can be up to 10 GB, and multipart is only required above 100 MB.

Public files, such as avatars and product photos, are served by a global CDN at the edge, which caches them. For private ones, the SigV4 presigned URL your code generates can last up to 7 days, and the dashboard offers a temporary link (up to 24 hours) and a share link (up to 30 days, revocable). There are no egress or ingress fees, and a quota is included in every plan.

For the data itself, MongoDB, PostgreSQL, MySQL and Redis hosting starts on the Standard plan, in the same datacenter as the applications. With images in Blob, the database holds only the key and metadata.

Explore Blob Storage