Consistent Lookup vs Lookup Vindex

Both vindex families let you route by a non-sharding column, but they pay for the mapping table differently: lookup keeps it perfectly in sync using a two-phase commit on every write, while consistent_lookup drops the 2PC and accepts a narrow window where the mapping can lag the base row.

Context #

This page sits under vindex selection and tuning, and it assumes you have already decided you need a secondary lookup vindex — the mechanics of wiring one into a query so a cross-shard join stays targeted are in configuring lookup vindexes for cross-shard joins. The decision here is narrower: given that you are adding a lookup vindex, do you pay the 2PC cost for strict consistency, or take consistent_lookup for write throughput and manage the eventual-consistency edge. If your secondary column can be served by a functional vindex (a case-folded string, say), you do not need a lookup vindex at all — check the parent guide first.

The Comparison #

The single axis is the same one that runs through all lookup vindexes: strict consistency via 2PC, or higher throughput via an asynchronous mapping update with an eventual-consistency caveat.

How the Mapping Gets Maintained #

A plain lookup vindex is maintained transactionally. When you insert into orders, Vitess also inserts the order_id → keyspace_id row into the lookup table, and because those two rows usually live on different shards, the whole write becomes a two-phase commit: prepare on both shards, then commit on both. If either shard cannot prepare, the base insert rolls back too. That is what makes the mapping never wrong — but it also means every insert pays cross-shard commit latency and depends on 2PC being enabled (--transaction_mode=MULTI), and a slow lookup shard back-pressures your base writes.

consistent_lookup breaks that coupling. The base row commits on its own shard in a single-shard transaction; the mapping is updated through a separate path that Vitess drives so it converges without a distributed commit. The name is precise: it is consistent in that Vitess guarantees the mapping will reflect committed base rows (it reconciles rather than silently drifting), but not atomic — for a brief window after a base insert, a query on the secondary column may not yet find the new row, or may transiently see a stale entry that the vindex then repairs. The _unique variants add the constraint that each from value maps to exactly one row, which lets VTGate route straight to a single shard instead of reading a set.

The mechanism consistent_lookup uses to stay correct without 2PC is worth understanding, because it explains both the guarantee and its limit. When a base row is inserted, Vitess writes the lookup entry as part of the same session but not as a coordinated two-phase commit; on the rare path where the base commit succeeds and the lookup write does not land immediately, the vindex detects the missing or stale mapping on the next access and repairs it against the base table. That self-healing is what makes the “consistent” claim honest — the mapping cannot permanently diverge from committed base rows the way an unowned lookup can. What it cannot promise is timing: the repair happens on read or on a background pass, so a read that races the write can observe the pre-repair state. In exchange for that bounded window you shed the entire 2PC coordinator from the write hot path, which is the throughput unlock that makes the trade worthwhile at high QPS.

VSchema params, owned vs unowned #

The params block is where a lookup vindex is configured, and the fields are identical across both families:

{
  "sharded": true,
  "vindexes": {
    "order_id_lookup": {
      "type": "consistent_lookup_unique",
      "params": {
        "table": "commerce.order_id_lookup",
        "from": "order_id",
        "to": "keyspace_id",
        "write_only": "true"
      },
      "owner": "orders"
    }
  },
  "tables": {
    "orders": {
      "column_vindexes": [
        { "column": "customer_id", "name": "xxhash" },
        { "column": "order_id", "name": "order_id_lookup" }
      ]
    }
  }
}

• table — the fully-qualified backing table that stores the mapping.
• from — the source column(s) you want to route by (order_id). For a multi-column lookup this is a comma-separated list.
• to — the destination column, almost always keyspace_id for a non-unique/keyspace_id-target lookup, or the base table’s primary vindex column for a unique lookup.
• write_only — when "true", VTGate writes the mapping but does not read it for routing yet. This is the backfill state: you populate the table without letting queries depend on a half-built index. Externalizing the vindex flips this off.

Ownership is the other half. "owner": "orders" tells Vitess that the orders table is responsible for this mapping, so Vitess maintains it automatically on every insert, update, and delete. An unowned lookup vindex has no owner and is read-only from Vitess’s side — you must populate and reconcile the table yourself, and any drift silently mis-routes. For a consistent_lookup, ownership is effectively mandatory, because the asynchronous reconciliation that makes it “consistent” is exactly the maintenance that ownership authorizes.

The equivalent strict version is a one-word change — "type": "lookup_unique" — and the removal of the reconciliation guarantee in exchange for atomic 2PC updates. Nothing else in the definition differs, which is why the choice is easy to change on paper and consequential in production.

Choosing Between Them #

The decision reduces to two questions about the write path and one about the read path. First: what is the sustained write rate on the base table? Below a few hundred writes per second the 2PC overhead of lookup/lookup_unique is usually invisible against everything else the transaction does, and the strict-consistency guarantee is worth having for free. As the rate climbs into the thousands, the cross-shard commit becomes the dominant cost and the coordinator becomes a throughput ceiling — that is the regime consistent_lookup exists for.

Second: is 2PC even enabled on the fleet? Many production Vitess deployments deliberately run --transaction_mode=SINGLE to force cross-shard writes to fail fast and keep the write path single-shard by policy. On such a fleet a lookup vindex simply cannot function — its mapping update requires the distributed commit that policy forbids — so consistent_lookup is not a preference but the only option that fits.

Third, on the read side: can the workflow tolerate a read-after-write miss on the secondary key? If code inserts a row and then immediately reads it back by the secondary column, consistent_lookup can return nothing for a short window and the strict lookup_unique cannot. Where that pattern exists and matters — an insert followed by an immediate confirmation lookup by order_id — either keep the strict vindex or reroute that specific read through the primary vindex, which is always immediately consistent. In practice most teams reach for consistent_lookup on high-volume write tables and reserve lookup_unique for lower-volume tables where a unique secondary key must never, even transiently, appear absent.

Edge cases and gotchas #

• Backfill via write_only. Never hand-populate a lookup table and flip it live in one step. Create it with write_only: "true" (or let CreateLookupVindex do it), run the VReplication backfill until it catches up, then ExternalizeVindex to start serving reads. Reading a half-built lookup returns missing rows that look exactly like data loss.
• Orphaned lookup rows. A base row deleted while the lookup shard is briefly unreachable can leave a mapping entry pointing at a keyspace ID with no row. For consistent_lookup the reconciliation cleans most of these, but a lookup under a partial 2PC failure can strand one — periodically anti-join the lookup table against the base table and delete unmatched from values.
• consistent_lookup read-after-write. Immediately after inserting an order, a query by order_id may not find it for a short window. If your workflow inserts and then instantly reads back by the secondary key, either route that read by the primary vindex (customer_id) instead, or accept the retry. This is the defining caveat and the reason lookup_unique still exists.
• 2PC must be on for lookup. A lookup/lookup_unique needs --transaction_mode=MULTI at VTGate; if the fleet runs SINGLE, base writes that would trigger a 2PC mapping update fail. consistent_lookup has no such dependency, which is part of its appeal on fleets that keep 2PC off for cross-shard write hygiene.
• Non-unique lookups return a set. A plain lookup (non-unique) can map one from value to several keyspace IDs, so VTGate targets several shards, not one. Only the _unique variants guarantee a single-shard route. Pick the unique variant whenever the secondary column is actually unique.
• Lookup-table shard skew. The lookup table is itself sharded (by its from column). If that column has poor entropy, you have simply moved the hotspot into the lookup keyspace. Give the lookup table a high-cardinality from or it becomes its own bottleneck.
• Do not mix strict and consistent variants on the same column. Switching a live vindex from lookup_unique to consistent_lookup_unique (or back) changes the maintenance contract, not the stored data, but doing it under load leaves in-flight writes straddling two consistency models. Drain or quiesce writes to the table, switch the type, re-externalize, and confirm the mapping is clean before resuming full write traffic.
• consistent_lookup under a rolling restart. Because reconciliation runs on read and on background passes, a VTGate/tablet restart mid-burst can lengthen the eventual-consistency window until the reconciler catches up. During planned restarts, expect a transient rise in secondary-key read misses and avoid scheduling them against a write spike on that table.

Verification #

Confirm both that the vindex routes reads to a single shard and that maintenance is actually keeping the mapping in step. First, plan-check the read path:

# A query on order_id must resolve to ONE shard via the lookup, not scatter
vtexplain --vschema-file vschema.json --schema-file schema.sql \
  --shards 4 --sql "SELECT * FROM orders WHERE order_id = 90218"

A correct result shows SelectEqualUnique (unique lookup) or a narrow SelectEqual (non-unique), never SelectScatter. If it scatters, the vindex is still write_only and not yet externalized. Then verify the mapping has no drift by anti-joining it against the base table:

# Rows in the base table with no lookup entry = drift; should return nothing
vtctldclient ExecuteFetchAsDBA <lookup-shard-primary> \
  "SELECT o.order_id FROM commerce.orders o
     LEFT JOIN commerce.order_id_lookup l ON o.order_id = l.order_id
    WHERE l.order_id IS NULL LIMIT 10"

For a freshly externalized consistent_lookup, a handful of rows here right after a write burst is the expected eventual-consistency window and should drain within seconds; a count that grows and never clears is genuine drift and means the vindex is unowned or the reconciliation stream is stalled.

Related #

• Configuring Lookup Vindexes for Cross-Shard Joins — using a lookup vindex so a join stays targeted rather than scattering.
• Vindex Selection and Tuning — where lookup vindexes fit in the full functional-versus-lookup decision.
• Optimizing Vindex Performance for High QPS — reducing lookup cost on hot write paths.

← Back to Vindex Selection and Tuning