Full-text search in Room: adding instant search to a local-first Android app in 2026

Search is the feature nobody notices until it’s slow. Type “yog” into a pantry with three hundred items and expect the list to filter as you type — if it takes even 200ms to respond, the app feels broken. Most Room-backed apps handle this with a LIKE '%query%' clause, and it works fine for small tables. It stops working the moment the table grows, the query has more than one word, or you want typo tolerance. SQLite has had a real answer to this since forever: full-text search, exposed in Room as the @Fts4 annotation. Here’s how it actually works, where it breaks, and why FTS5 — the version most tutorials assume you’re using — isn’t something Room gives you for free.

Why LIKE doesn’t scale

SELECT * FROM pantry_items WHERE name LIKE '%yogurt%' can’t use an index. SQLite has to scan every row and run a substring match on each one. On a few hundred rows that’s invisible. On a few thousand — which a pantry app with barcode scanning and receipt history reaches faster than you’d think — it becomes a visible stutter on every keystroke, especially if the query also has to OR across multiple columns (name, brand, category).

Full-text search flips this. Instead of scanning rows, SQLite builds an inverted index at write time: every word maps to the rows that contain it. A search becomes an index lookup, not a scan, so performance stays flat as the table grows.

What Room actually gives you: FTS4, not FTS5

This is the part that trips people up, because most FTS content on the web assumes SQLite’s newer FTS5 module. Room’s @Fts4 annotation wires up an FTS4 virtual table — it does not have an @Fts5 equivalent. FTS4 and FTS5 differ enough that this matters: FTS5 has a saner query syntax, built-in bm25() ranking, and better handling of prefix queries, none of which Room hands you automatically with FTS4.

If you need FTS5, you can still get it — you just do it manually, by creating the virtual table yourself inside a Migration with raw SQL (CREATE VIRTUAL TABLE ... USING fts5(...)) instead of an annotated entity, and mapping it to a plain @DatabaseView or raw query for reads. For most local search use cases — pantry names, subscription names, note titles — FTS4 is plenty, and it’s the version that costs you nothing beyond an annotation. Start there; reach for the manual FTS5 route only if you actually need phrase queries or built-in ranking.

Wiring an FTS4 table into Room

An FTS table needs a companion “content” entity — the normal table you already query for everything else — plus a virtual table that indexes the searchable columns:

@Entity(tableName = "pantry_items")
data class PantryItem(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val name: String,
    val brand: String,
    val category: String,
)

@Fts4(contentEntity = PantryItem::class)
@Entity(tableName = "pantry_items_fts")
data class PantryItemFts(
    val name: String,
    val brand: String,
    val category: String,
)

contentEntity tells Room to treat pantry_items as the source of truth and store only the index in the FTS table, not a duplicate copy of every row. The DAO query looks almost identical to a normal one, except it joins against the FTS table’s rowid:

@Query("""
    SELECT pantry_items.* FROM pantry_items
    JOIN pantry_items_fts ON pantry_items.id = pantry_items_fts.rowid
    WHERE pantry_items_fts MATCH :query
""")
fun search(query: String): Flow<List<PantryItem>>

MATCH is the FTS operator — it’s what turns the query into an index lookup instead of a scan. Passing "yog*" instead of "yog" gives you prefix matching, which is what you want for search-as-you-type.

Keeping the index in sync

With contentEntity, Room generates the triggers that keep pantry_items_fts synchronized with pantry_items on insert, update, and delete — you don’t write those by hand. The one thing to watch: this sync only covers writes that go through Room’s generated insert/update/delete methods. A raw SQL UPDATE executed outside Room’s DAO layer, or a bulk import done with execSQL, bypasses the trigger-backed content entity mapping in ways that are easy to get subtly wrong. Route pantry mutations — including the barcode-scan insert path — through the same DAO methods that Room’s schema validation already accounts for, rather than a separate raw-SQL shortcut for “fast” imports.

Ranking: FTS4’s real limitation

FTS4 gives you correct matches but no relevance ranking beyond match order. If someone searches “milk” and both “Whole Milk” and “Milk Chocolate Bar” match, FTS4 won’t tell you which one the user probably meant — you get rows in table order, not relevance order. For a few hundred pantry items this rarely matters in practice; the list is short enough to scan. If it starts to matter — a larger catalog, or search across a wider set of fields — the fix without jumping to FTS5 is a simple client-side reorder: pull the matches, then sort by whether the query matches the start of the name before anything else. It’s a few lines of Kotlin, not a database migration, and it fixes the case that actually annoys users: exact and prefix matches buried below unrelated partial matches.

The takeaway

Room’s FTS4 support turns a linear scan into an index lookup for the cost of one annotation and a slightly different DAO query — no server, no third-party search SDK, no network round trip for something that should feel instant. It’s the right default for search inside any local-first Android app. The two things worth remembering: Room speaks FTS4, not FTS5, so don’t design around ranking features that only exist in the newer module, and every write path that’s supposed to be searchable has to go through Room’s DAO layer, or the index quietly falls out of sync with the table it’s supposed to describe. Get those two things right and search stops being a feature you worry about.

If you want to see this pattern in a shipped app, Stocky uses it to search a pantry that can grow into the hundreds of items — barcode scans, receipt imports, and manual entries all landing in the same searchable table.