SQLite Tips

SQLite VACUUM

In SQLite, pages may need to be resized when data is changed or deleted. Over time, the database file can become fragmented with free pages scattered throughout. The VACUUM statement repaginates the underlying database file, moving all data closer together on the disk and reclaiming unused pages.

-- Rebuild the database and reclaim space
VACUUM;

-- Vacuum a specific table
VACUUM orders;

-- Check database page size and count
PRAGMA page_count;
PRAGMA page_size;

When to Run VACUUM

• After significant deletions
• After many updates to variable-length columns
• When database size seems larger than expected
• Periodically for maintenance (monthly/quarterly)

⚠️ VACUUM requires free disk space equal to the size of the original database during operation.

Auto-VACUUM

SQLite supports auto-vacuum mode:

-- Enable auto-vacuum
PRAGMA auto_vacuum = INCREMENTAL;  -- Options: NONE, FULL, INCREMENTAL

-- Check current setting
PRAGMA auto_vacuum;

• NONE: No auto-vacuum (default, most efficient)
• FULL: Vacuum after each transaction (slower)
• INCREMENTAL: Vacuum using incremental_vacuum pragma

SQLite Performance Notes

COUNT(*) Performance

For SQLite specifically, COUNT(*) is generally faster than COUNT(column_name) because SQLite can optimize it differently.

Index Usage in SQLite

SQLite uses a cost-based query planner. Check your query plans:

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE status = 'pending';

Write Performance

For bulk inserts, use transactions:

BEGIN TRANSACTION;
-- Insert thousands of rows
COMMIT;

This is much faster than individual auto-committed inserts.

Index Statistics (ANALYZE)

Run ANALYZE after creating indexes to update statistics and help the query planner make better decisions. Some databases have a feature that allows the query planner to recompute statistical parameters to enhance query efficiency.

-- SQLite
ANALYZE;

-- PostgreSQL
ANALYZE;

-- MySQL
ANALYZE TABLE tablename;

When to Run ANALYZE

• After creating new indexes
• After significant data changes (large inserts, updates, or deletes)
• When query performance seems to degrade
• Periodically for maintenance (weekly/monthly)

💡 For SQLite specifically, run ANALYZE every time indexes are created to ensure the query planner has accurate statistics.