DATA & INDEXING
Connecting your database: from tables to collections
Six decisions, in order: which screens, which collections, which fields, push or pull, what schedule, and which screen moves first.
- Pick the screensStart with the slow ones: search, listings with filters and totals, reports.catalog search · orders listing
- Define collectionsOne per entity the screens ask about.products · orders · customers · invoices
- Map the fieldsText to search, keywords to filter, numbers and dates to range and total.name: text · brand: keyword · price: number
- Push or pullYour systems send records, or the index reads the database on a schedule.orders: push · products: pull
- Set a schedulePer collection, from hourly to weekly, by how fresh each screen must be.orders hourly · products daily
- Import, then connectOne full import, incremental from then on; point the first screen at the API.first screen live
Before you start
You need three things: the list of screens that are slow or that you want to build, a way to read from the database or a stream of change events from the systems that own the data, and a decision about how fresh each screen has to be. You do not need to change the database, add indexes to it, or touch the applications that write to it. The whole exercise is additive.
Step 1: pick the screens
Do not start from the schema. Start from the catalog search that cannot forgive a typo, the orders listing with six filters and a running total, the report that runs for minutes. Each one tells you what it needs: which records, which fields searched, which filtered, which counted, which totalled. Write that down per screen. It is the specification for everything that follows, and it keeps the first version small.
Step 2: define collections
One collection per entity the screens ask about: products, orders, customers, invoices. A collection is a flat set of records, one per row of the screen, so the joins happen here rather than at query time. An order record carries the customer name and the product category it needs for filtering, copied in at import. Resist modelling the database; model the screens. If two screens need orders shaped differently, that is usually still one collection with a few extra fields, not two.
Step 3: map the fields
Each field gets a job. Text fields - name, description - are tokenized and searched. Keyword fields - brand, status, region, category - are filtered and counted exactly. Numeric and date fields - price, order date, quantity - take range facets and totals and sort. A field that will never be searched, filtered or shown does not need to be in the collection at all, and leaving it out keeps the record small and the import fast. Decide the fields from the screen specification in step 1, not from the table's columns.
Step 4: push or pull
Two ways to get records in. Your systems push them through the index's API whenever something changes, which suits services that already emit events and gives the freshest data. Or the index pulls from the database on a schedule, which suits the common case where the database is the only reliable record of what changed and nothing needs to be built in the source system. Many deployments use both: push for the collection that must stay close to live, pull for the rest. Deletes need a decision either way - a soft-delete flag the import can see, or a periodic full import that drops what is gone. Incremental indexing goes through the options.
Step 5: set a schedule
Per collection, from hourly to weekly, by how fresh each screen must be. Orders that an operations desk watches: hourly. Customers, whose details change slowly: every four hours. The product master, edited in batches: daily. Invoices, issued in a cycle and read in reports: weekly. Say the freshness on the screen - as of 09:00 - and tighten a schedule only when somebody actually needs it tighter.
| Collection | Source | Push or pull | Schedule | Change detection |
|---|---|---|---|---|
| Orders | Order tables, joined to customer and product | Push from the order service, or pull | Hourly | Updated-at timestamp |
| Customers | CRM and account tables | Pull | Every 4 hours | Updated-at timestamp |
| Products | Product master, variants flattened | Pull | Daily | Updated-at, with a weekly full import |
| Invoices | Accounting export | Pull | Weekly | Rising invoice id (append-only) |
Step 6: import, then connect
Run one full import per collection: every record read once, indexed once. From then on the schedule brings only new and changed records. Then point the first screen at the API - the catalog search, say - and leave every other screen exactly where it is. The request it sends names the collection and what it needs; the response carries the records, the counts, the ranges and the totals for that screen in one call. For the products request used across this site - text jaket, category Clothing, facets on brand and size, price bands, an average, fuzzy 1 - that is 531 records found, Polo 302 · Levis 144 · Nike 85, Small 412 · Medium 88 · Large 31, price bands 0–25, 25–50 and 50–100 holding 120, 245 and 166 records, an average price of 42.60, and suggestions, in 624 ms.
- Denim jacket
- Rain jacket
- + 5 more
- Polo302
- Levis144
- Nike85
- Small412
- Medium88
- Large31
- 0–25120
- 25–50245
- 50–100166
Move the next screen when the first one has earned trust. The orders listing, then the report, then the dashboard that sits on the same collections. Each one is a change to where a screen reads from, not to the application around it.
What does not change
The database schema. The applications that write. The reporting indexes you can now remove. The checkout, the record-detail page, and every screen that reads one exact row. The flow is one way, from your systems into the index, and nothing comes back, which is what makes this safe to start and easy to stop. When not to use an index server is the list of screens to leave alone.