SQLite Optimization Techniques | Android System Design

Room uses SQLite under the hood. Understanding SQLite internals lets you write faster queries, design better schemas, and avoid common performance pitfalls that only surface at scale.

Indexes: The Most Important Optimization

Without an index, SQLite does a full table scan for every query — O(n). With an index, lookups are O(log n).

// Add indexes for columns you filter or sort by frequently
@Entity(
    tableName = "articles",
    indices = [
        Index("author_id"),                    // for: WHERE author_id = ?
        Index("published_at"),                 // for: ORDER BY published_at
        Index(value = ["category", "published_at"]),  // for: WHERE category = ? ORDER BY published_at
        Index(value = ["title"], unique = true)        // uniqueness constraint
    ]
)
data class ArticleEntity(
    @PrimaryKey val id: String,
    val title: String,
    val authorId: String,
    val category: String,
    val publishedAt: Long
)

When NOT to index:

Columns with low cardinality (boolean, status with 2-3 values)
Columns you never filter or sort by
Tables with < 1000 rows (full scan is faster than index lookup overhead)

Explain Query Plan

Check if your query uses indexes:

// In a debug test
@Test
fun verifyQueryUsesIndex() {
    val cursor = db.query("EXPLAIN QUERY PLAN SELECT * FROM articles WHERE author_id = ?", arrayOf("u1"))
    cursor.moveToFirst()
    val plan = cursor.getString(3)
    assertThat(plan).contains("USING INDEX")  // ← confirms index is used
    cursor.close()
}

Transactions for Batch Writes

Each individual INSERT is a separate transaction (journaling overhead). Wrap bulk inserts in a transaction:

// ❌ 1000 separate transactions
articles.forEach { dao.insert(it) }  // ~2 seconds for 1000 rows

// ✅ Single transaction
db.withTransaction {
    articles.forEach { dao.insert(it) }  // ~50ms for 1000 rows
}

// Or: @Insert with a List parameter — Room wraps in a transaction automatically
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertAll(articles: List<ArticleEntity>)

Avoiding N+1 Queries

// ❌ N+1: one query for articles, N queries for authors
val articles = dao.getAllArticles()
articles.map { article ->
    val author = dao.getAuthor(article.authorId)  // N queries!
    ArticleWithAuthor(article, author)
}

// ✅ JOIN: one query for all data
@Query("""
    SELECT a.*, u.name as author_name
    FROM articles a
    LEFT JOIN authors u ON a.author_id = u.id
""")
suspend fun getArticlesWithAuthors(): List<ArticleWithAuthorRow>

Write-Ahead Logging (WAL)

WAL mode allows concurrent reads while a write is in progress — critical for performance when using Flow (background reads) and foreground writes:

Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .setJournalMode(RoomDatabase.JournalMode.WRITE_AHEAD_LOGGING)  // enabled by default
    .build()

WAL is the default in Room 2.x+. Don't disable it unless you have a specific reason.

Prepared Statements (Room Does This for You)

Room compiles SQL queries into prepared statements at compile time. These are reused across calls — faster than parsing SQL on every execution. This is one reason Room's @Query methods are fast even without explicit caching.

VACUUM: Reclaim Space

After many deletes, SQLite doesn't automatically shrink the file. VACUUM rebuilds the database:

// Run periodically (expensive — don't run on main thread or during active use)
db.openHelper.writableDatabase.execSQL("VACUUM")

Schedule with WorkManager during charging + idle:

val vacuumRequest = PeriodicWorkRequestBuilder<VacuumWorker>(7, TimeUnit.DAYS)
    .setConstraints(Constraints.Builder().setRequiresCharging(true).build())
    .build()

Key Takeaways

Technique	Impact
Index on filtered/sorted columns	10–1000× faster reads
Batch inserts in transaction	40× faster writes
JOINs instead of N+1	N times fewer queries
WAL mode	Concurrent read/write; enabled by default in Room
`EXPLAIN QUERY PLAN`	Verify queries use indexes in tests