Optimizing Images and Blobs

Decide whether to store large binary files in the database versus the file system.

In this lesson, you will learn how to decide whether to store large binary objects (BLOBs) directly inside your database or as external files on a filesystem.

The BLOB Dilemma

When building applications—especially for embedded systems like mobile or IoT devices—you will eventually need to handle binary data like profile pictures, logs, or reports. While SQL databases allow you to store this data in BLOB columns, it is rarely the most performant choice for large files.

The Case for Filesystem Storage

Storing BLOBs in the database increases your database file size significantly. Because SQLite (our common embedded database) is often stored in a single file, adding large binary data can lead to:

Increased Backup Times: Every time you back up your database, you are backing up the heavy images, even if they haven't changed.
Fragmentation: Large binary chunks can cause the database page file to become fragmented, slowing down queries that have nothing to do with the images.
Memory Pressure: Querying a row that contains a 5MB image forces the engine to handle that memory overhead, which can be brutal for constrained devices.

Instead, use a "Pointer Pattern." Save the file to the disk (e.g., /app/data/images/user_123.jpg) and store only the file path (or a relative URI) in your SQL table:

-- Instead of storing the binary blob directly:
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT,
    profile_picture_path TEXT -- Store the file system path here
);

When to Keep it in the Database

Only store BLOBs inside the database if:

Security/Encryption: You need the data to be covered by the same database-level encryption you implemented in lesson 21.
Atomicity: You need the image and the metadata to be transactionally bound (i.e., if the row is deleted, the image must be guaranteed to be gone).
Small Files: If your blobs are tiny (under 100KB), the overhead of managing separate filesystem pointers might actually be more complex than just keeping the data in the table.

Key Takeaway: For large binary files, prioritize the file system for performance and scalability, using the database only to store file paths and associated metadata.