Managing Large SQLite Database File Sizes and Splitting Strategies

Database File Size Limitations and Tool Compatibility Challenges

The core challenge revolves around managing SQLite database files which have grown too large for third-party tools like QGIS to handle effectively. While SQLite natively supports databases up to 281 terabytes, practical limitations emerge when interacting with external software not optimized for large single-file databases. The primary symptoms include system crashes during operations and difficulties in performing routine maintenance tasks like backups. This situation requires addressing two distinct but related aspects: optimizing existing database size and implementing sustainable data partitioning strategies without breaking relational integrity or application functionality.

Key technical constraints compound these challenges:

• Cross-database foreign key enforcement limitations
• Trigger scope restrictions between attached databases
• Full-Text Search (FTS5) implementation peculiarities
• Schema namespace management inconsistencies
• Backup/restore operational complexities

These factors create a multidimensional problem space where solutions must balance file size reduction with maintained data accessibility and system performance. The technical debt incurred by improper partitioning can manifest as query performance degradation, data integrity risks, and increased development overhead.

Database Bloat Sources and Cross-Schema Operational Constraints

Three primary factors contribute to excessive SQLite file sizes and management challenges:

1. Storage Engine Fragmentation and Vacuuming

• Page reuse inefficiencies from deleted/updated records
• Freelist accumulation in non-WAL (Write-Ahead Logging) mode
• Index fragmentation in tables with frequent writes
• Unreclaimed space from DROP TABLE/COLUMN operations

2. BLOB Storage Patterns

• Embedded media files or geospatial data in database columns
• Non-optimized BLOB chunking strategies
• Duplicate binary asset storage

3. Schema Design Limitations

• Monolithic table architectures without partitioning
• Over-normalized relationship graphs
• Unused index accumulation
• Full-text search implementation choices

The operational constraints surface when attempting to split databases:

• Foreign Key Integrity: SQLite’s FOREIGN KEY pragma only validates relationships within the same database connection’s attached schemas
• Trigger Scope Limitations: Triggers created in the main database cannot directly modify attached databases without TEMP schema workarounds
• FTS5 External Content: Full-text search tables configured with external content require the source table to reside in the same schema
• View Qualification: Object references in views require explicit schema prefixes when querying attached databases
• Transaction Boundaries: DML operations spanning multiple attached databases require careful transaction management

These technical realities necessitate specific architectural patterns when implementing database splitting while maintaining application functionality.

Database Optimization and Partitioning Implementation Strategy

Phase 1: Database Health Assessment and Baseline Metrics
Execute PRAGMA statements to establish current database state:

PRAGMA main.page_count;  -- Total database pages
PRAGMA main.page_size;   -- Current page size (1-65536 bytes)
PRAGMA main.freelist_count;  -- Unused pages
PRAGMA main.quick_check; -- Lightweight integrity check
PRAGMA main.integrity_check; -- Full verification

Calculate actual data payload versus overhead:

SELECT 
  name AS table_name,
  SUM(pgsize) AS total_bytes
FROM dbstat
GROUP BY name;

For BLOB-heavy tables, analyze storage patterns:

SELECT 
  typeof(blob_column) AS type,
  avg(length(blob_column)) AS avg_size,
  count(*) AS row_count 
FROM bloat_table;

Phase 2: Database File Size Reduction
Implement incremental vacuuming for active databases:

PRAGMA main.auto_vacuum = INCREMENTAL;
PRAGMA main.incremental_vacuum(1000); -- Vacuum 1000 pages

For BLOB optimization, consider external storage with URI filenames:

INSERT INTO documents(file) VALUES (
  'file:attachments/contract.pdf?immutable=1'
);

Restructure FTS5 tables using contentless models:

CREATE VIRTUAL TABLE search USING fts5(
  title, 
  content,
  content='documents',
  content_rowid='rowid'
);

Phase 3: Horizontal Partitioning Implementation
Create partitioned schema architecture:

main.db       - Core tables, metadata
shard1.db     - Partition 1 data
shard2.db     - Partition 2 data
temp.db       - Cross-shard operations

Attach databases with alias namespaces:

ATTACH DATABASE 'shard1.db' AS s1;
ATTACH DATABASE 'shard2.db' AS s2;

Implement partitioned view with UNION ALL:

CREATE TEMP VIEW combined_data AS
SELECT * FROM s1.data
UNION ALL
SELECT * FROM s2.data;

Handle cross-shard foreign keys via trigger emulation:

CREATE TEMP TRIGGER cross_shard_fk
AFTER INSERT ON main.orders
BEGIN
  INSERT INTO s1.inventory_updates(item_id, qty)
  VALUES (NEW.item_id, -NEW.qty);
END;

Phase 4: FTS5 External Content Workaround
Store content table in main database:

-- In main.db
CREATE TABLE documents(
  id INTEGER PRIMARY KEY,
  title TEXT,
  content TEXT
);

Attach shard database and create FTS5 index:

ATTACH 'shard_fts.db' AS fts;
CREATE VIRTUAL TABLE fts.search USING fts5(
  title,
  content,
  content='main.documents',
  content_rowid='id'
);

Phase 5: Backup Strategy Implementation
Use .dump command for partial backups:

sqlite3 main.db ".dump some_table" > some_table.sql

Implement incremental backup using WAL:

cp main.db main.backup
cp main.db-wal main.backup-wal

Phase 6: Query Optimization for Attached Databases
Use explicit schema qualification:

SELECT m.name, s1.address 
FROM main.customers m
JOIN s1.addresses s1 ON m.id = s1.customer_id;

Create covering indexes across shards:

CREATE INDEX s1.idx_address_cust ON addresses(customer_id);
CREATE INDEX s2.idx_address_cust ON addresses(customer_id);

Phase 7: Testing and Validation
Verify cross-database integrity:

PRAGMA foreign_key_check;
PRAGMA s1.foreign_key_check;

Stress test with composite queries:

BEGIN;
INSERT INTO main.orders (...) VALUES (...);
INSERT INTO s1.inventory (...) VALUES (...);
COMMIT;

Phase 8: Tool-Specific Optimization for QGIS
Export spatial subsets using views:

CREATE VIEW qgis_export AS
SELECT * FROM s1.geodata
WHERE Within(geom, MakePoint(-71, 42), 1000);

Use QGIS’s virtual layers to combine shards:

SELECT * FROM s1.geodata
UNION ALL
SELECT * FROM s2.geodata

Phase 9: Long-Term Maintenance
Implement shard rotation strategy:

DETACH DATABASE s_old;
ATTACH DATABASE 's_new.db' AS s3;

Schedule periodic vacuuming:

PRAGMA main.auto_vacuum = FULL;
VACUUM;

Phase 10: Alternative Approaches Evaluation
Consider SQLite Archive Mode:

sqlite3 -A main.db "SELECT * FROM geodata"

Evaluate sqlean extensions:

.load sqlean/fileio
SELECT file_write('backup.db', readfile('main.db'));

This comprehensive approach balances immediate file size reduction with sustainable architectural patterns. Each phase builds upon the previous while maintaining operational integrity. The strategy emphasizes SQLite’s native capabilities while acknowledging third-party tool limitations through targeted optimization and schema design. Implementation requires careful testing at each stage, particularly regarding transaction management across attached databases and query performance validation.