Scale a service you just built: schema trade-offs, new features, indexes, and search

Earlier in this onsite loop you extended a small service; this 60-minute design round takes the same service and asks how it would hold up and grow in production. The conversation goes deep on data modeling: you propose schemas, the interviewer probes the pros and cons of each proposal, and then keeps adding features and asking how the database changes, how the queries perform, where a search-oriented datastore helps, and which indexes you would add. The real service from the interview was not reported. For practice, use this stand-in: a catalog service storing items (`id`, `name`, `description`, `category`, `price`, `createdAt`), with endpoints to fetch one item, list items filtered by category and price range with sorting and pagination, create items, and let users save favorites. Treat the features in Part 3 as the interviewer's additions. ### Constraints and Clarifications - Assume the workload is read-heavy: listings and item pages dominate traffic, while writes come from catalog admins, favorites and, later, reviews. - Scale numbers were not given. State your own assumptions and show how the design would change if they were off by an order of magnitude. - Prefer a design that evolves from the current single-database service over a rewrite. ### Clarifying Questions - How many items and users, and what peak read and write rates should the design target? - How fresh must listings and search results be after an item is created or edited? - Do listings need exact total counts, or is an indication that more results exist enough? - Are item attributes the same across categories, or does each category carry its own fields? ### Part 1 — High-level design for scale Sketch the production architecture for the service as it stands today: its components, the read and write paths, and how each tier scales. Name the first bottleneck you expect as traffic grows and how you would spot it. ```hint Follow one request Trace a filtered listing request and an item creation from the client to storage and back, and note where each would queue up under load. ``` #### What This Part Should Cover - Requirements and back-of-the-envelope estimates that drive the design - The service tier, storage and caching choices, each with a justification - Distinct read and write paths - The first bottleneck and how it would be detected ### Part 2 — Schema design and alternatives Propose a schema for items, categories and favorites. Then present at least one credible alternative, for example for storing category-specific attributes, and compare the options on query flexibility, write cost, integrity and ease of change. ```hint Let the queries choose List the queries every endpoint runs before choosing tables, then judge each schema by what those queries look like against it. ``` #### What This Part Should Cover - Tables, keys, constraints and data types for the core entities - At least two alternatives with explicit pros and cons, and a decision - How the chosen schema serves each endpoint's query - Which integrity guarantees exist and where they are enforced ### Part 3 — Grow the service The interviewer adds two features. First, users can write a review with a star rating for an item, and listings must show each item's average rating and support sorting by it. Second, users can search items by keywords in the name and description. Describe the schema changes, how you would roll them out without downtime, and how the read paths change. ```hint Read cost versus write cost Decide which values are computed when a review is written and which are computed when a listing is read. ``` #### What This Part Should Cover - New tables and constraints for reviews - Where and when aggregate ratings are computed, and the consistency trade-offs - An online migration and backfill plan with a rollback path - How the new features affect caching and the read path ### Part 4 — Query performance, indexes and search For the main queries (filtered and sorted listings, a user's favorites, an item's reviews, keyword search), explain how they perform with and without indexes, which indexes you would add, and at what point you would move search into a dedicated search engine. Explain how that engine would stay consistent with the primary database. ```hint Match the index to the predicate For each query, write out its filter and sort columns in order, and ask which index could satisfy both without an extra sort step. ``` #### What This Part Should Cover - Specific indexes tied to specific queries, with their write cost - Pagination that stays fast deep into a result set - Why ordinary relational indexes struggle with keyword search and what a search engine adds - A synchronization approach for the search index and its failure modes ### What a Strong Answer Covers - Choices justified by stated assumptions and query patterns rather than habit - Schema trade-offs argued in both directions, ending in a clear decision - Evolution of a live system: migrations, backfills and rollback without downtime - Concrete index and query-plan reasoning, not just "add an index" - Consistency, failure handling and observability across the database, cache and search index ### Follow-up Questions - Review volume spikes on a handful of very popular items. What breaks first, and how do you fix it? - The search index falls hours behind the database. How do you notice, and how do you recover? - When would you shard the primary database, and which key would you choose for each table? - Product wants search results personalized per user. How does the design change?