DATA & INDEXING
Incremental indexing: keeping an index fresh
An index is a copy, so it has to be refreshed. The question is how, how often, and what to tell users.
- Full import
- Delta import · new and changed records only
Why there is a refresh at all
An index is a copy, not a live view. That is the source of its speed: the records are stored in the shape the queries need, which is not the shape the database keeps them in, and converting between the two on every read would cost what the index is there to save. So the conversion happens once per change, in the background, and reads hit the finished structure. The price is that the copy and the original drift apart between refreshes. Managing that drift - how it is detected, how often it is closed, what users are told - is the subject of this article.
Full import
A full import reads every record in the source and rebuilds the collection from nothing. It is the right move three times: the first load, when there is nothing to update yet; after a schema change, when the shape of every record is different; and after a large backfill or correction in the source, when more rows changed than an incremental run would handle gracefully. Its cost is proportional to the whole collection - minutes for hundreds of thousands of records, longer for tens of millions - and while it runs the previous index keeps serving, so the switch is a cutover, not a gap.
Incremental import
An incremental import brings only what changed since the last run. It needs a way to tell. An updated-at timestamp on each row is the common one: fetch everything modified after the last watermark. A change flag works where the source can mark rows dirty and clear the mark once they are indexed. A rising id catches inserts, not updates, and is enough for append-only tables such as events or invoices.
Deletes are the awkward case, because a row that is gone has no timestamp to find it by. The options are a soft delete the import can see, a deleted-ids feed from the source, or a periodic full import that drops what no longer exists. Decide this before the first incremental run, not after a customer finds a product that was deleted last week.
Push or pull
- Your systemERP · CRM · app
- Index API
- Collection
- Fits systems that already emit events
- Changes arrive as they happen
- Your code has to send every change, including deletes
- Database
- Indexon a schedule
- Collection
- Nothing to build in the source system
- Works from an updated-at column or id
- Fresh only as of the last run
Push fits systems that already emit events: an order service that publishes every change can send the record to the index API in the same breath, and the index is seconds behind the source. The cost is that your code owns the feed, including deletes. Pull fits the common case where the database is the only reliable record of what changed: the index runs a query on a schedule, takes everything past the watermark, and nothing in the source system has to be built. Many deployments use both - push for the collection that must be close to live, pull for the rest.
Choosing a schedule
Hourly, every few hours, daily or weekly, and independently per collection. The question for each one is: how stale can this screen be before someone makes a wrong decision from it? Worked across the four collections in the lead visual: orders hourly, because the operations desk watches them through the day; customers every four hours, because contact details change slowly and nothing urgent depends on them; products daily, because the master is edited in batches and a catalog can lag a night; invoices weekly, because they are issued in a cycle and read in reports, not consoles. The busy collection stays fresh without rebuilding the quiet ones, and the schedule can tighten later without touching anything else. Start looser than feels comfortable and tighten where someone actually notices; a schedule nobody asked for is cost without a customer.
Freshness lag
Whatever the schedule, say so on the screen. As of 09:00 next to the grid turns the lag from a surprise into a setting, and users calibrate to it quickly. Which screens tolerate it: reports, listings, catalogs, dashboards, anything where the number is read rather than acted on to the second. Which do not: a stock check at the till, a balance before a withdrawal, an approval that depends on the state right now. Those keep reading the database, and the index serves everything else. The saved grids that an operations manager opens each morning - Urgent orders 14, Rejected orders 36 - are the first kind.
What never happens
Nothing is written back. Data flows from your systems into the index, by push or by pull, and stops there. Applications read from the index; they do not change it, and the index never changes the source. If the two disagree the source is right and the next import fixes the copy. This is the reason an indexed layer is easy to add and easy to remove: it is a copy on the side, and the systems it copies from do not know it exists.