SQLite Database File Size Growth Due to BLOB Overflow Page Rewrites

BLOB Overflow Page Rewrites During Non-Binary Column Updates

When working with SQLite databases, particularly those containing large binary objects (BLOBs), a common issue arises when updating non-binary columns in a table that also contains BLOBs. Specifically, setting non-binary columns to NULL in such tables can lead to unexpected database file size growth. This behavior is rooted in how SQLite manages overflow pages for BLOBs and the internal mechanics of row updates.

Consider a table schema like the following:

CREATE TABLE Data1 (
    Data BLOB,
    Items_id INTEGER,
    Key TEXT
);

When a row in this table contains a large BLOB and non-binary columns are updated to NULL, the database file size can double, and the number of pages on the freelist can increase significantly. This occurs even after running the VACUUM command, which is typically used to reclaim unused space. The root cause lies in how SQLite handles overflow pages for BLOBs during updates.

Overflow Pages and BLOB Storage in SQLite

SQLite stores large BLOBs in overflow pages. Each row in a table is stored as a cell, which includes a manifest (a list of data types for the row) and a payload (the actual data). For BLOBs, the first portion of the data (up to a certain size) is stored directly in the cell, while the remainder is stored in overflow pages. When a row is updated, SQLite may rewrite the entire cell, including the overflow pages, even if the BLOB data itself hasn’t changed.

For example, consider a row with the following data:

• Data: A very large BLOB (e.g., 1 MB)
• Items_id: 1
• Key: "MyKey"

When you issue an update like:

UPDATE Data1 SET Items_id = NULL, Key = NULL WHERE Items_id = 1;

SQLite rewrites the entire row, including the BLOB overflow pages, even though the BLOB data remains unchanged. This results in the original overflow pages being marked as free, and new overflow pages being allocated, leading to an increase in the database file size.

Manifest and Payload Changes During Updates

The manifest of a row in SQLite includes information about the data types and sizes of each field. When non-binary columns are set to NULL, the manifest changes to reflect the new state of the row. Specifically, the manifest no longer includes the data for the NULL fields, but the BLOB data remains in the payload. This change in the manifest can trigger a rewrite of the entire cell, including the overflow pages.

For instance, the original row might have a manifest like:

• Header length
• BLOB size (X)
• INTEGER (1)
• TEXT ("MyKey")

After the update, the manifest changes to:

• Header length
• BLOB size (X)
• NULL
• NULL

The payload, however, still contains the BLOB data. Because the manifest has changed, SQLite rewrites the entire cell, including the overflow pages, even though the BLOB data itself hasn’t changed.

Impact of BLOB Position in the Schema

The position of the BLOB column in the table schema can also affect the behavior of overflow page rewrites. If the BLOB is the first column in the table, SQLite must access the overflow pages to read or write the BLOB data, even if the other columns are being updated. This can lead to unnecessary rewrites of the overflow pages.

However, even if the BLOB is the last column in the table, the overflow pages may still be rewritten if the payload length changes. This is because SQLite treats the entire cell as a single unit, and any change to the manifest or payload can trigger a rewrite of the entire cell, including the overflow pages.

Schema Design and BLOB Overflow Page Management

To mitigate the issue of database file size growth due to BLOB overflow page rewrites, it is recommended to separate large BLOBs into their own table and reference them from the main table. This approach reduces the likelihood of overflow page rewrites during updates to non-binary columns.

Separating BLOBs into a Separate Table

One effective strategy is to create a separate table for BLOBs and reference them from the main table. For example:

CREATE TABLE Data1A (
    Data_id INTEGER NOT NULL UNIQUE REFERENCES Data1B(Data_id),
    Items_id INTEGER,
    Key TEXT
);

CREATE TABLE Data1B (
    Data_id INTEGER PRIMARY KEY,
    Data BLOB
);

CREATE VIEW Data1 AS
    SELECT Data, Items_id, Key
    FROM Data1A, Data1B
    WHERE Data1A.Data_id = Data1B.Data_id;

CREATE TRIGGER Data1Insert INSTEAD OF INSERT ON Data1
BEGIN
    INSERT INTO Data1B (Data) VALUES (NEW.Data);
    INSERT INTO Data1A VALUES (last_insert_rowid(), NEW.Items_id, NEW.Key);
END;

CREATE TRIGGER Data1Update INSTEAD OF UPDATE ON Data1
BEGIN
    UPDATE Data1B SET Data = NEW.Data
    WHERE Data_id = (SELECT Data_id FROM Data1A WHERE Items_id IS OLD.Items_id AND Key IS OLD.Key)
      AND NEW.Data IS NOT OLD.Data;
    UPDATE Data1A SET Items_id = NEW.Items_id, Key = NEW.Key
    WHERE Items_id IS OLD.Items_id AND Key IS OLD.Key;
END;

CREATE TRIGGER Data1Delete INSTEAD OF DELETE ON Data1
BEGIN
    DELETE FROM Data1B
    WHERE Data_id = (SELECT Data_id FROM Data1A WHERE Items_id IS OLD.Items_id AND Key IS OLD.Key);
    DELETE FROM Data1A
    WHERE Items_id IS OLD.Items_id AND Key IS OLD.Key;
END;

This schema design separates the BLOB data into its own table (Data1B), while the main table (Data1A) contains only the non-binary columns. The Data1 view provides a unified interface for querying and manipulating the data, and the triggers handle the necessary operations to keep the data consistent.

Benefits of Separating BLOBs

By separating BLOBs into their own table, you can avoid the overhead of rewriting overflow pages when updating non-binary columns. This approach also makes it easier to manage large BLOBs, as they are stored in a dedicated table that can be optimized independently of the main table.

Additionally, this design reduces the likelihood of database file size growth due to overflow page rewrites, as the BLOB data is only rewritten when it is actually updated. This can be particularly beneficial in applications where large BLOBs are frequently updated or deleted.

Practical Steps to Mitigate Database File Size Growth

To address the issue of database file size growth due to BLOB overflow page rewrites, consider the following practical steps:

1. Optimize Schema Design

As discussed earlier, separating BLOBs into their own table can significantly reduce the likelihood of overflow page rewrites. This approach not only mitigates the issue of file size growth but also improves overall database performance by reducing the overhead associated with managing large BLOBs.

2. Use VACUUM Command

The VACUUM command can be used to reclaim unused space in the database file. However, it is important to note that VACUUM may not always fully resolve the issue of file size growth caused by overflow page rewrites. In some cases, the file size may still increase after running VACUUM, particularly if the database contains a large number of BLOBs.

3. Monitor Database File Size

Regularly monitor the database file size to identify any unexpected growth. If you notice significant increases in file size after updating non-binary columns, consider reviewing your schema design and optimizing it to reduce the likelihood of overflow page rewrites.

4. Consider Using PRAGMA auto_vacuum

The PRAGMA auto_vacuum setting can be used to automatically reclaim unused space in the database file. When auto_vacuum is enabled, SQLite will automatically free up space when rows are deleted or updated, reducing the likelihood of file size growth. However, this setting may not fully resolve the issue of overflow page rewrites, particularly in databases with large BLOBs.

5. Evaluate Application Logic

Review your application logic to ensure that updates to non-binary columns are necessary and cannot be avoided. In some cases, it may be possible to minimize the number of updates to non-binary columns, thereby reducing the likelihood of overflow page rewrites.

6. Consider Using a Different Database Engine

If the issue of database file size growth due to BLOB overflow page rewrites is particularly problematic for your application, consider using a different database engine that may be better suited to handling large BLOBs. For example, some database engines are specifically designed to handle large binary data more efficiently than SQLite.

Conclusion

The issue of database file size growth due to BLOB overflow page rewrites in SQLite is a complex one, rooted in the internal mechanics of how SQLite manages overflow pages and row updates. By understanding the underlying causes and implementing appropriate schema design and optimization strategies, you can mitigate the impact of this issue and ensure that your database remains efficient and manageable.

Separating BLOBs into their own table, using the VACUUM command, and monitoring database file size are all effective strategies for addressing this issue. Additionally, reviewing your application logic and considering alternative database engines may be necessary in some cases.

By taking a proactive approach to managing BLOB data in SQLite, you can avoid the pitfalls of database file size growth and ensure that your application remains performant and scalable.