Database Indexes
How Meridian structures indexes for fast recipe lookups, filtering, and full-text search across millions of rows.
Why Indexes Matter
Without indexes, every recipe query would scan the entirerecipestable. With millions of rows, that means seconds of latency. Meridian uses a layered indexing strategy to keep p95 query times under 12ms.
Primary Index: B-Tree on ID
Every recipe row has a UUIDv7 primary key. The B-tree index onidgives O(log n) point lookups. UUIDv7 is time-sortable, so range scans on creation order are free.
Composite Index: Category + Created
The most common user query is “show me recent recipes in a category.” A composite index on(category, created_at DESC)serves this without a filesort. The leading column handles equality filtering; the trailing column handles ordering.
Full-Text: GIN Trigram
For ingredient and title search, Meridian uses a GIN index withgin_trgm_ops. This enables LIKE and ILIKE queries with fuzzy matching. The trigram index breaks text into overlapping 3-character chunks, so partial-word searches hit the index instead of scanning.
Covering Index: User Favorites
The favorites join is hot. A covering index on(user_id, recipe_id, saved_at)includes all columns the query needs, so the planner never touches the heap. Index-only scans eliminate random I/O entirely.
Maintenance
Meridian runsREINDEX CONCURRENTLYweekly during low-traffic windows. Bloat monitoring viapgstattupletriggers alerts if any index exceeds 30% dead tuples. Autovacuum is tuned aggressively on indexed columns.