SQLite Database File Size Not Decreasing After Delete Operations

Understanding SQLite Delete Operations and File Size Behavior

When records are deleted from an SQLite database, the database file size does not automatically decrease. Instead, SQLite marks the space previously occupied by deleted records as "free" for future reuse. This design choice optimizes performance by avoiding frequent file resizing, which can be resource-intensive. However, this behavior often leads to confusion when users expect immediate file size reduction after delete operations.

The database file consists of fixed-size pages (default: 4 KB). When a record is deleted, its associated pages are marked as free but remain allocated to the file. Subsequent insert operations reuse these free pages, preventing unnecessary file growth. If no new data is added, the free pages remain part of the file, causing the file size to persist at its maximum historical value. This is analogous to a hard drive retaining file system slack space after file deletion.

SQLite’s VACUUM command rebuilds the entire database file, discarding free pages and reducing the file size. However, VACUUM is not triggered automatically by default and requires explicit execution. Additionally, orphaned references (e.g., unenforced foreign keys or residual index entries) can contribute to retained space if not properly managed.

Root Causes of Persistent File Size Post-Delete

1. Free Page Retention Without Reuse

SQLite prioritizes performance over immediate space reclamation. Deleted records free pages internally, but these pages are retained in the file for future writes. If the application does not perform subsequent insert or update operations, free pages remain unused, creating the illusion of "bloat."

2. Unenforced Foreign Key Constraints

SQLite does not enforce foreign key constraints unless explicitly enabled via PRAGMA foreign_keys = ON;. If foreign keys are not enforced, deleting a parent record may leave child records orphaned. These orphaned records occupy space and are not automatically removed, contributing to file size growth.

3. Autovacuum Configuration

SQLite supports incremental autovacuum (PRAGMA auto_vacuum = FULL/INCREMENTAL;), which reclaims free pages after transactions. However, autovacuum is disabled by default (auto_vacuum = NONE). Without autovacuum, free pages are never returned to the operating system unless VACUUM is manually executed.

4. Index and Schema Overhead

Indexes, triggers, and other schema objects may retain references to deleted data. For example, if a table has an index, deleting records updates the index structure but does not immediately shrink it. Over time, fragmented indexes can occupy significant space.

5. Write-Ahead Log (WAL) Mode

In WAL mode, SQLite uses a separate write-ahead log file for transactions. While WAL improves concurrency, it can temporarily increase storage usage. However, this is unrelated to delete operations and resolves automatically during checkpointing.

Resolving File Size Growth and Orphaned References

Step 1: Verify Actual File Size and Free Pages

Use the sqlite3_analyzer tool or execute the following SQL to inspect free pages:

PRAGMA page_count;          -- Total pages in the database  
PRAGMA freelist_count;      -- Number of unused pages  
PRAGMA page_size;           -- Size of each page in bytes  

Calculate the theoretical minimum size:

Minimum Size = (page_count - freelist_count) * page_size  

Compare this with the actual file size. A large discrepancy indicates reclaimable space.

Step 2: Execute the VACUUM Command

Run VACUUM; in an SQLite shell or via application code:

VACUUM;

This rebuilds the database, removes free pages, and defragments indexes. For large databases, ensure sufficient disk space, as VACUUM creates a temporary copy.

Step 3: Enable and Enforce Foreign Key Constraints

Add foreign key clauses to table definitions:

CREATE TABLE parent (
    id INTEGER PRIMARY KEY
);

CREATE TABLE child (
    id INTEGER PRIMARY KEY,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

Enable foreign key enforcement:

PRAGMA foreign_keys = ON;

The ON DELETE CASCADE clause ensures child records are automatically deleted when parent records are removed.

Step 4: Configure Autovacuum

Enable autovacuum before creating the database:

PRAGMA auto_vacuum = FULL;   -- Or INCREMENTAL  

For existing databases, autovacuum can only be enabled after a VACUUM:

VACUUM;  
PRAGMA auto_vacuum = FULL;  

Incremental autovacuum reclaims space in smaller chunks, reducing latency spikes.

Step 5: Rebuild Indexes and Optimize Schema

Recreate fragmented indexes:

REINDEX table_name;          -- Rebuild all indexes for a table  
REINDEX index_name;          -- Rebuild a specific index  

Remove unused schema objects (e.g., triggers, unused indexes) to reduce overhead.

Step 6: Monitor Application Write Patterns

If the application deletes large volumes of data without subsequent writes, free pages will accumulate. Schedule periodic VACUUM operations during off-peak hours or after bulk deletions.

Step 7: Use In-Memory Databases for Temporary Data

For transient data, consider using :memory: databases or temp_store = MEMORY; to avoid file bloat:

PRAGMA temp_store = MEMORY;  

Step 8: Leverage Partial Vacuum for Large Databases

For databases where a full VACUUM is impractical, use PRAGMA incremental_vacuum(N); to free N pages per transaction.

Step 9: Validate Orphaned Record Cleanup

After enabling foreign keys, audit tables for orphaned records:

-- Example: Find orphaned child records  
SELECT * FROM child  
WHERE parent_id NOT IN (SELECT id FROM parent);  

Delete orphaned records manually or via triggers.

Step 10: Adjust Page Size and Reserved Bytes

Recreate the database with a larger page size (e.g., 8192 bytes) to reduce fragmentation:

PRAGMA page_size = 8192;  
VACUUM;  

Adjust reserved bytes per page (PRAGMA schema.reserve_bytes = N;) to align with data patterns.

By systematically addressing free page retention, enforcing referential integrity, and configuring autovacuum, SQLite databases can maintain optimal file sizes while preserving performance. Regular maintenance and schema optimization are critical for long-term efficiency.