Image on Table #

Storing images directly in the database is one of those architecture decisions most often made for convenience — and most often regretted once the system is large. At small scale, storing images as BLOBs or Base64 in a database column feels practical: no external storage configuration, no syncing to think about, all data in one place. But databases aren’t designed to handle large binary data. Every query touching a table containing BLOBs reads the entire binary payload, backups balloon many times over, replication slows down, and CDNs can’t be used at all. This article covers why this approach is fundamentally problematic, the different impacts of BLOB vs Base64, and the correct architecture for handling files in production.

Two Most Common Wrong Ways #

Before discussing solutions, it’s important to understand the two storage variants most often found — along with their different performance impacts.

BLOB: Raw Binary Data #

BLOB (Binary Large Object) is a column type that stores binary data directly. MySQL offers four variants based on maximum capacity:

-- The four BLOB variants in MySQL:
TINYBLOB   -- maximum 255 bytes
BLOB       -- maximum 65 KB
MEDIUMBLOB -- maximum 16 MB  ← most often used for images
LONGBLOB   -- maximum 4 GB

-- Example table storing images as BLOBs
CREATE TABLE product_images (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id BIGINT UNSIGNED NOT NULL,
    data       MEDIUMBLOB NOT NULL,   -- ✗ the image is stored here
    mime_type  VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Query to fetch the image
SELECT data, mime_type FROM product_images WHERE product_id = 42;
-- → Every query reads the full binary payload, possibly 2–5 MB per row

Base64: Layered Waste #

Base64 is an encoding that turns binary data into ASCII text — often chosen because it’s “easier to store in a TEXT or JSON column”. The problem: Base64 adds about 33% size overhead over the original data, and the fundamental problem of storing images in a database remains, even getting worse.

-- Example table storing images as Base64
CREATE TABLE user_avatars (
    user_id    BIGINT UNSIGNED NOT NULL,
    image_b64  LONGTEXT NOT NULL,  -- ✗ Base64 strings can be very long
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
);

-- A 1 MB image on disk = ~1.33 MB as Base64 in the database
-- A 3 MB image on disk = ~4 MB as Base64 in the database
-- Plus encode/decode overhead on every read and write

BLOB vs Base64 comparison as image storage methods in a DB:
──────────────────────────────────────────────────────────────────────
  Aspect                │ BLOB           │ Base64
──────────────────────────────────────────────────────────────────────
  Size in storage       │ Same as        │ ~33% larger than the original
                        │ the original   │
  Encoding overhead     │ None           │ Yes — encode on write,
                        │                │ decode on read
  JSON compatibility    │ Not direct     │ Can be stored in a JSON field
  Indexing              │ Not possible   │ Not possible (too large)
  Database compression  │ Can be enabled │ Less effective (Base64 already
                        │                │ expands the data)
  Query impact          │ Bad            │ Worse
──────────────────────────────────────────────────────────────────────
  Conclusion: both are wrong. Base64 only adds problems on top
  of the problems BLOB already has.

Why This Breaks Database Performance #

The problem of storing images in a database isn’t just about “memory full” or “disk full”. The impact goes much deeper and touches how the database engine fundamentally works.

Innocent Queries Get Hit Too #

This is the most unexpected impact. Imagine you only want to fetch product names and prices — but the table stores images in the same table:

-- A products table with a BLOB column for images
CREATE TABLE products (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(255) NOT NULL,
    price       DECIMAL(15, 2) NOT NULL,
    stock       INT UNSIGNED NOT NULL,
    image_data  MEDIUMBLOB,  -- the image is stored here, ~2 MB on average
    PRIMARY KEY (id),
    INDEX idx_products_name (name)
);

-- A "simple" query that's actually very expensive
SELECT id, name, price FROM products WHERE name LIKE 'Laptop%';

Even though the query only asks for id, name, and price, MySQL’s engine (InnoDB) uses a clustered index — meaning all row data is stored together in the B-tree index. When reading a row to get name and price, the engine must navigate to the data page that also contains image_data. For large tables, this means:

BLOB impact on clustered indexes (InnoDB):
──────────────────────────────────────────────────────────────
  Without BLOBs:
    Average row size: ~200 bytes
    One InnoDB page (16 KB) fits: ~80 rows
    Scanning 10,000 rows: ~125 pages read

  With 2 MB BLOBs per row:
    Average row size: ~2 MB (large data stored off-page)
    Off-page pointer in every row: extra overhead
    Scanning 10,000 rows: the database must follow off-page
    pointers for every row → multiplied I/O

  Net effect: the same query runs 5–20× slower
  depending on how often the engine accesses the BLOBs
──────────────────────────────────────────────────────────────

Buffer Pool Pollution #

The database uses a buffer pool (RAM memory area) to store frequently accessed data pages — a kind of internal cache. When images are stored in the database, BLOB pages enter the buffer pool and evict more useful data (indexes, small data rows) out of the cache:

BLOB impact on the buffer pool:
──────────────────────────────────────────────────────────────
  Buffer pool size: 4 GB (a common configuration)

  Without BLOBs:
    4 GB can hold around 250 thousand pages (16 KB/page)
    → Frequently accessed indexes and data stay in RAM
    → High cache hit rate → fast queries

  With BLOBs (average 2 MB per row, 10 thousand products):
    10,000 products × 2 MB = ~20 GB of BLOB data
    Far exceeding the 4 GB buffer pool
    BLOBs constantly enter and leave the buffer pool
    → Evicting more useful indexes and small data
    → Cache hit rate plummets → the database reads more from disk
    → Disk I/O spikes → the whole system slows down
──────────────────────────────────────────────────────────────

Replication and Backups Balloon #

In architectures with read replicas, every data change is replicated from the primary to replicas via the binlog. When images are stored in the database, every INSERT or UPDATE on a row with a BLOB is replicated with its full payload:

BLOB impact on replication:
──────────────────────────────────────────────────────────────
  Uploading 100 images per day, ~2 MB per image:
    → 200 MB/day of replication traffic just from images
    → 6 GB/month just from BLOBs
    → Replica lag increases → reads from replicas can be stale
    → With 3 replicas: 18 GB/month of replication traffic

  Daily database backups:
    Without BLOBs:  50 MB (structured data only)
    With BLOBs: 50 MB + all accumulated images
    After 1 year: backups can reach tens of GB
    → Disaster recovery restores: hours longer
    → Incident downtime windows become far longer
──────────────────────────────────────────────────────────────

A database backup containing BLOBs isn’t just slow — it creates a real operational risk. During production incidents, every minute of downtime has a cost. Restoring a database holding tens of GB of images can turn a 30-minute incident into a 4-hour one.

CDNs Can’t Be Used #

This is an impact often overlooked but very significant for user experience. CDNs work by storing files on edge servers close to users — but a CDN can only cache files with static URLs that can be hit directly. If images are stored in the database and returned through an API endpoint, the CDN can’t effectively cache the results:

The image delivery flow from a database (not optimal):

flowchart LR
    User["User"] --> API["API Server"]
    API --> DB["Database query"]
    DB --> Decode["Decode BLOB/Base64"]
    Decode --> Return["Return binary response"] --> User

Problems: ✗ Every new request presses the database ✗ CDN can’t be used because the endpoint response is dynamic ✗ Browser caching with ETag/Last-Modified can’t be leveraged ✗ No range requests (partial downloads for large videos/PDFs) ✗ No on-the-fly resizing (WebP, thumbnails, etc.) ✗ Server bandwidth drained serving binary files

The image delivery flow from object storage + CDN (optimal):

flowchart LR
    User["User"] --> CDN["CDN Edge"]
    CDN -->|"cache hit"| Direct["File directly from edge"] --> User
    CDN -->|"cache miss"| OS["Object Storage"]
    OS -->|"CDN cache"| User

Benefits: ✓ The database is never involved in image delivery ✓ CDN cache on edge servers near users → very low latency ✓ ETag and Cache-Control configurable ✓ Range requests supported for large media ✓ Image transformation (resize, format) can happen at the CDN layer

The Correct Architecture: Object Storage + CDN #

The fundamental solution is separating responsibilities: the database stores image metadata (URL, size, type), object storage stores the file, and the CDN handles the delivery to users.

The Correct Database Schema #

-- ANTI-PATTERN: images stored in the database
CREATE TABLE products (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name       VARCHAR(255) NOT NULL,
    image_data MEDIUMBLOB,      -- ✗ don't store files here
    PRIMARY KEY (id)
);

-- CORRECT: the database only stores metadata and URLs
CREATE TABLE products (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name       VARCHAR(255) NOT NULL,
    price      DECIMAL(15, 2) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE product_images (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id  BIGINT UNSIGNED NOT NULL,
    storage_key VARCHAR(500) NOT NULL,    -- path in object storage
    cdn_url     VARCHAR(500) NOT NULL,    -- public URL via CDN
    mime_type   VARCHAR(100) NOT NULL,
    size_bytes  BIGINT UNSIGNED NOT NULL,
    width_px    INT UNSIGNED,
    height_px   INT UNSIGNED,
    is_primary  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_product_images_product_id (product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

-- Example stored data
-- storage_key: "products/42/images/hero-20260418-abc123.webp"
-- cdn_url:     "https://cdn.example.com/products/42/images/hero-20260418-abc123.webp"
-- size_bytes:  245760  (240 KB after compression)
-- mime_type:   "image/webp"

The Upload Flow with Pre-Signed URLs #

The best flow ensures large files never pass through the application server — the client uploads directly to object storage using a temporary URL generated by the backend.

The upload flow with pre-signed URLs:

sequenceDiagram
    participant Client
    participant Backend
    participant S3 as "Object Storage (S3/GCS)"
    participant DB as "Database"

    Client->>Backend: "1. POST /api/products/42/images/upload-url (request upload permission)"
    Backend->>S3: "2. Generate pre-signed URL"
    Backend-->>Client: "3. Response (upload_url, storage_key)"
    Client->>S3: "4. PUT directly to upload_url (upload the file)"
    Client->>Backend: "5. POST /api/products/42/images/confirm"
    Backend->>S3: "6. Verify the file (check existence + size)"
    Backend->>DB: "Store metadata"
    Backend-->>Client: "Response with complete image data"

Benefit: large files never pass through the application server → The backend stays light even when users upload large files

Implementation in Go (Backend) #

// Handler: generate a pre-signed URL for direct upload to S3
func (h *ImageHandler) GenerateUploadURL(c *fiber.Ctx) error {
    productID := c.Params("product_id")
    
    var req struct {
        Filename string `json:"filename"`
        MimeType string `json:"mime_type"`
        SizeBytes int64 `json:"size_bytes"`
    }
    if err := c.BodyParser(&req); err != nil {
        return c.Status(400).JSON(fiber.Map{"error": "invalid request"})
    }

    // Validate the allowed file types
    allowedMimes := map[string]bool{
        "image/jpeg": true,
        "image/png":  true,
        "image/webp": true,
    }
    if !allowedMimes[req.MimeType] {
        return c.Status(400).JSON(fiber.Map{"error": "unsupported file type"})
    }

    // Generate a unique storage key
    ext := filepath.Ext(req.Filename)
    storageKey := fmt.Sprintf(
        "products/%s/images/%s%s",
        productID,
        uuid.New().String(),
        ext,
    )

    // Generate the pre-signed URL (valid for 15 minutes)
    presigner := s3.NewPresignClient(h.s3Client)
    presignResult, err := presigner.PresignPutObject(c.Context(),
        &s3.PutObjectInput{
            Bucket:      aws.String(h.bucket),
            Key:         aws.String(storageKey),
            ContentType: aws.String(req.MimeType),
        },
        s3.WithPresignExpires(15*time.Minute),
    )
    if err != nil {
        return c.Status(500).JSON(fiber.Map{"error": "failed to generate upload url"})
    }

    return c.JSON(fiber.Map{
        "upload_url":  presignResult.URL,
        "storage_key": storageKey,
        "expires_in":  900,
    })
}

// Handler: confirm after the upload finishes, store metadata in the database
func (h *ImageHandler) ConfirmUpload(c *fiber.Ctx) error {
    productID, _ := strconv.ParseInt(c.Params("product_id"), 10, 64)
    
    var req struct {
        StorageKey string `json:"storage_key"`
    }
    c.BodyParser(&req)

    // Verify the file really exists in S3
    headResult, err := h.s3Client.HeadObject(c.Context(), &s3.HeadObjectInput{
        Bucket: aws.String(h.bucket),
        Key:    aws.String(req.StorageKey),
    })
    if err != nil {
        return c.Status(400).JSON(fiber.Map{"error": "file not found in storage"})
    }

    // Store metadata in the database (not the file)
    cdnURL := fmt.Sprintf("https://cdn.example.com/%s", req.StorageKey)
    _, err = h.db.ExecContext(c.Context(), `
        INSERT INTO product_images
            (product_id, storage_key, cdn_url, mime_type, size_bytes)
        VALUES (?, ?, ?, ?, ?)
    `, productID, req.StorageKey, cdnURL,
       aws.ToString(headResult.ContentType),
       aws.ToInt64(headResult.ContentLength))
    
    if err != nil {
        return c.Status(500).JSON(fiber.Map{"error": "failed to save metadata"})
    }

    return c.JSON(fiber.Map{
        "cdn_url":     cdnURL,
        "storage_key": req.StorageKey,
        "size_bytes":  aws.ToInt64(headResult.ContentLength),
    })
}

CDN and Cache Header Configuration #

Once files are in object storage, configure the CDN for optimal delivery:

# Example Nginx configuration as a CDN proxy to object storage
location /media/ {
    proxy_pass https://your-bucket.s3.amazonaws.com/;
    
    # Cache at the CDN edge for 30 days
    proxy_cache_valid 200 30d;
    
    # Cache instructions to the browser
    add_header Cache-Control "public, max-age=2592000, immutable";
    
    # ETag for conditional requests
    add_header ETag $upstream_http_etag;
    
    # Allow range requests for large files
    proxy_set_header Range $http_range;
}

Handling Deletion and Data Consistency #

One aspect often forgotten: when a row in the database is deleted, the file in object storage isn’t automatically deleted too. A mechanism must ensure both stay consistent.

-- ANTI-PATTERN: deleting metadata without deleting the storage file
DELETE FROM product_images WHERE id = 123;
-- → The file still exists in S3, storage costs keep running
-- → Old links might still be accessible

-- CORRECT: soft delete + async cleanup worker
ALTER TABLE product_images
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL,
ADD INDEX idx_product_images_deleted_at (deleted_at);

-- Soft delete: mark as deleted
UPDATE product_images
SET deleted_at = NOW()
WHERE id = 123;

-- A periodically running worker: delete the file from S3
-- then hard-delete from the database
SELECT id, storage_key
FROM product_images
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL 1 HOUR;
-- → For each row: delete from S3, then DELETE from the DB

// Worker to clean up soft-deleted files
func (w *CleanupWorker) Run(ctx context.Context) {
    ticker := time.NewTicker(1 * time.Hour)
    for {
        select {
        case <-ticker.C:
            w.cleanupDeletedFiles(ctx)
        case <-ctx.Done():
            return
        }
    }
}

func (w *CleanupWorker) cleanupDeletedFiles(ctx context.Context) {
    rows, err := w.db.QueryContext(ctx, `
        SELECT id, storage_key
        FROM product_images
        WHERE deleted_at IS NOT NULL
          AND deleted_at < NOW() - INTERVAL 1 HOUR
        LIMIT 100
    `)
    if err != nil {
        return
    }
    defer rows.Close()

    for rows.Next() {
        var id int64
        var storageKey string
        rows.Scan(&id, &storageKey)

        // Delete from S3 first
        _, err := w.s3Client.DeleteObject(ctx, &s3.DeleteObjectInput{
            Bucket: aws.String(w.bucket),
            Key:    aws.String(storageKey),
        })
        if err != nil {
            continue  // skip, retry later
        }

        // Only after successfully deleting from S3, hard-delete from the DB
        w.db.ExecContext(ctx,
            "DELETE FROM product_images WHERE id = ?", id)
    }
}

When BLOBs Are Still Acceptable #

There are limited conditions where storing binary data in a database can still be considered. It’s important to be honest about the limitations:

Conditions where BLOBs are still acceptable:
──────────────────────────────────────────────────────────────
  ✓ Very small files (icons, thumbnails < 50 KB)
    → Row size doesn't significantly burden the buffer pool
    → Replication traffic stays small

  ✓ Internal tools with very low traffic
    → < 100 requests per day, no scaling needed
    → Operational convenience matters more than performance

  ✓ Data that must be atomic with database transactions
    → Encrypted documents that must be consistent with other data
    → No condition allowed where the file exists but the metadata doesn't

  ✓ Prototyping / MVPs not yet touched by real users
    → With a refactor-to-object-storage plan before launch

Conditions where BLOBs can't be justified:
──────────────────────────────────────────────────────────────
  ✗ Product images, user avatars, profile photos
  ✗ PDF documents, spreadsheet files
  ✗ Audio or video in any form
  ✗ Systems that will have more than 10 thousand files
  ✗ Systems needing CDNs or fast delivery
  ✗ Systems with performance or uptime SLAs

Don’t use Base64 as an alternative to BLOBs even for small files. Base64 only adds ~33% size and encoding/decoding overhead with no benefit over BLOB. If you really must store in the database, use BLOB. But reconsider whether you really need to.

Anti-Patterns to Avoid #

-- ✗ Anti-pattern 1: storing images as BLOBs in transactional tables
CREATE TABLE orders (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id      BIGINT UNSIGNED NOT NULL,
    total        DECIMAL(15, 2) NOT NULL,
    receipt_scan MEDIUMBLOB,  -- receipt scans stored in the orders table
    PRIMARY KEY (id)
);
-- ✓ Solution: upload to object storage, store only the URL
-- receipt_scan_url VARCHAR(500) -- URL to the S3 file

-- ✗ Anti-pattern 2: storing Base64 in JSON columns
INSERT INTO users (id, profile)
VALUES (1, '{"name":"Budi","avatar":"data:image/jpeg;base64,/9j/4AAQ..."}');
-- ✓ Solution: store the avatar URL, not the image content
-- VALUES (1, '{"name":"Budi","avatar_url":"https://cdn.example.com/avatars/1.webp"}')

-- ✗ Anti-pattern 3: serving images from the database via an API endpoint
-- GET /api/products/42/image → SELECT image_data FROM products WHERE id = 42
-- ✓ Solution: return the CDN URL from the database, redirect or use the URL directly
-- GET /api/products/42 → { ..., "image_url": "https://cdn.example.com/..." }

-- ✗ Anti-pattern 4: no soft delete, deleting DB rows without S3 cleanup
DELETE FROM product_images WHERE product_id = 42;
-- → S3 files aren't deleted, storage costs keep running, files accessible via old URLs
-- ✓ Solution: soft delete + cleanup worker as described above

-- ✗ Anti-pattern 5: storing multiple image resolutions as separate BLOBs
INSERT INTO product_images (product_id, size, data) VALUES
    (42, 'thumbnail', [BLOB 50KB]),
    (42, 'medium', [BLOB 300KB]),
    (42, 'original', [BLOB 2MB]);
-- ✓ Solution: store the original in object storage, transformations done on-the-fly via CDN
-- (Cloudflare Images, Cloudinary, or Imgix can resize based on URL parameters)

Production File Architecture Checklist #

DATABASE SCHEMA:
  □ No BLOB or MEDIUMBLOB columns in production tables?
  □ No TEXT columns storing Base64?
  □ All image references stored as URLs or storage keys?
  □ A separate image metadata table exists, apart from the main table?
  □ A deleted_at column exists for soft deletes?

UPLOAD FLOW:
  □ Files never pass through the application server (pre-signed URLs)?
  □ File type and maximum size validation exists?
  □ Upload confirmation happens before metadata is stored in the database?
  □ Storage keys unique and unpredictable?

DELIVERY:
  □ Images served via CDN, not directly from object storage?
  □ Cache-Control headers configured correctly?
  □ API-returned image URLs are already CDN URLs?
  □ A fallback exists if the CDN is unavailable?

CLEANUP:
  □ A soft delete mechanism exists?
  □ A cleanup worker deletes files from object storage?
  □ The worker runs after a delay (to avoid race conditions)?
  □ Cleanup errors logged with a retry mechanism?

Summary #

• Databases aren’t places to store files — databases are optimized for structured data, queries, and transactions. BLOBs and Base64 fight this fundamental design and break performance systemically.
• Base64 is worse than BLOB — besides all of BLOB’s problems, Base64 adds ~33% data size and encode/decode overhead on every read and write, with no benefit at all.
• BLOBs pollute the buffer pool — images entering the database cache evict far more useful indexes and small data, lowering cache hit rates and weighing down disk I/O.
• Pre-signed URLs are the correct upload pattern — clients upload directly to object storage, the server only stores metadata. Large files never pass through the application server.
• The database only stores metadata and URLs — storage_key, cdn_url, mime_type, size_bytes are the data to store in the database, not the file content.
• CDNs are a mandatory component for image delivery — object storage + CDN provide global caching, low latency, ETag, range requests, and on-the-fly image transformation that a database can’t offer.
• Soft delete + cleanup worker for consistency — don’t delete database metadata before the object storage file is successfully deleted. An async worker running with a delay avoids race conditions.
• The golden rule: databases for data, object storage for files, CDNs for delivery — each component used for its purpose makes the system faster, cheaper, and easier to scale.

← Previous: RAND()   Next: SQL Function Overuse →