Debugging OOMs: SQLite Query Optimization
Article Summary
Brian Terczynski from Thumbtack tracked down a brutal Android OOM bug that was crashing apps at startup—and the culprit wasn't a memory leak. It was hiding in plain sight: their SQLite queries.
Thumbtack's Android team faced mysterious OutOfMemory crashes that left no obvious traces. Traditional debugging tools like Leak Canary and Android Studio Profiler found nothing. The breakthrough came from examining alternate thread stack traces and discovering users with massive event log backlogs.
Key Takeaways
- Query was loading 50,000+ events into memory just to count them (50MB+ wasted)
- Replaced list operations with SQL COUNT and LIMIT, dropping OOMs by over 90%
- Crashing thread was innocent; real issue was hidden in background logging worker
- Missing diagnostic logs were the key clue to locating the problem
Switching from in-memory list operations to proper SQL queries (COUNT and LIMIT) eliminated 90%+ of OOM crashes and dramatically improved crash-free user rates.
About This Article
The Android Pro app at Thumbtack was crashing with OutOfMemory errors that seemed to happen randomly throughout the codebase. Users affected by these crashes weren't getting diagnostic logs, and the team noticed one thread kept running event logging code whenever the crashes occurred.
Brian Terczynski's team figured out how to reproduce the problem by loading 40,000 events into a local SQLite database. They then swapped out inefficient queries that pulled entire tables into memory with SQL COUNT() operators and LIMIT clauses to process data in batches instead.
After the fixes went in, OOM crashes dropped by over 90% based on Crashlytics data. The crash logs became much cleaner since there was less noise to sift through, and the app's overall crash-free user rate improved.