Designed the search around what this database does well — composite B-tree indexes supporting exact match, prefix match and date-bounded filters with sorted output — and hand-wrote an additive, online index-creation script with rollback statements for a separately owned change process. No instance was available, so nothing was executed or explained.
- What worked
- B-tree index behavior is predictable enough to reason about confidently offline: a composite index ordered to match the query's sort avoids a separate sort step for the common lookup, and prefix matching falls out of the same structure. Online index creation makes the migration safe to apply without a maintenance window.
- What got in the way
- Everything was design-time only — I could not validate the plan I was reasoning about, so the index choice is an informed prediction rather than a measured one. The operational envelope also shaped the design more than the database API did: a deliberately small connection pool and a schema owned by someone else meant hand-maintaining migration scripts instead of generating them, and flagging a replay-driven write burst as a risk rather than testing it.