Backfill Lookup Vindexes

Creating a Lookup Vindex after the main table already contains rows does not automatically backfill the lookup table for the existing entries. Only newer inserts cause automatic population of the lookup table.

This backfill can be set up using the LookupVindex create command covered below.

Manual Backfill Checklist

Creating a unique Lookup Vindex is an elaborate process. It is good to use the following checklist if this is done manually:

  • Create the lookup table as sharded or unsharded. Make the from column the primary key.
  • Create a VSchema entry for the lookup table. If sharded, assign a Primary Vindex for the from column.
  • Create the lookup vindex in the VSchema of the sharded keyspace:
    • Give it a distinct name
    • Specify the type from one of predefined vindexes
    • Under params: specify the properties of the lookup table
    • Specify the Owner as the main table
  • Associate the column of the owner table with the new Vindex.

Creating a Lookup Vindex

vtctldclient supports the LookupVindex create command that can perform all the above steps as well as the backfill.

This will not work against the vtcombo based demo app because it does not support vreplication. You can only try this against a real Vitess cluster.

The workflow automatically infers the schema and vschema for the lookup table and creates it. It also sets up the necessary VReplication streams to backfill the lookup table.

After the backfill is done, you should clean up the workflow. More detailed instructions are available in the Creating a Lookup Vindex Guide

To create such a lookup vindex on a real Vitess cluster, you can use the following instructions:

Unique Lookup Vindex Example

Continued from Unique Lookup Vindex Page

Save the following json into a file, say corder_keyspace_idx.json:

  1. {
  2.   "sharded": true,
  3.   "vindexes": {
  4.     "corder_keyspace_idx": {
  5.       "type": "consistent_lookup_unique",
  6.       "params": {
  7.         "table": "product.corder_keyspace_idx",
  8.         "from": "corder_id",
  9.         "to": "keyspace_id"
  10.       },
  11.       "owner": "corder"
  12.     }
  13.   },
  14.   "tables": {
  15.     "corder": {
  16.       "column_vindexes": [{
  17.           "column": "corder_id",
  18.           "name": "corder_keyspace_idx"
  19.       }],
  20.     }
  21.   }
  22. }

And issue the vtctldclient command:

  1. $ vtctldclient --server <vtctld_grpc_address> CreateLookupVindex -- --tablet_types=REPLICA customer "$(cat corder_keyspace_idx.json)"

The workflow will automatically create the necessary Primary Vindex entries for vindex table corder_keyspace_idx knowing that it is sharded.

Non-unique Lookup Vindex Example

Continued from Non-unique Lookup Vindex Page

Save the following json into a file, say oname_keyspace_idx.json:

  1. {
  2.   "sharded": true,
  3.   "vindexes": {
  4.     "oname_keyspace_idx": {
  5.       "type": "consistent_lookup",
  6.       "params": {
  7.         "table": "customer.oname_keyspace_idx",
  8.         "from": "oname,corder_id",
  9.         "to": "keyspace_id"
  10.       },
  11.       "owner": "corder"
  12.     }
  13.   },
  14.   "tables": {
  15.     "corder": {
  16.       "column_vindexes": [{
  17.         "columns": ["oname", "corder_id"],
  18.         "name": "oname_keyspace_idx"
  19.       }]
  20.     }
  21.   }
  22. }

And issue the vtctldclient command:

  1. $ vtctldclient --server <vtctld_grpc_address> CreateLookupVindex -- --tablet_types=REPLICA customer "$(cat oname_keyspace_idx.json)"

The workflow will automatically create the necessary Primary Vindex entries for vindex table oname_keyspace_idx knowing that it is sharded.