Duplicate Indexes Topology

On this page

In a multi-region deployment, the duplicate indexes pattern is a good choice for tables with the following requirements:

• Read latency must be low, but write latency can be much higher.
• Reads must be up-to-date for business reasons or because the table is referenced by foreign keys.
• Rows in the table, and all latency-sensitive queries, cannot be tied to specific geographies.
• Table data must remain available during a region failure.

In general, this pattern is suited well for immutable/reference tables that are rarely or never updated.

Prerequisites

Fundamentals

• Multi-region topology patterns are almost always table-specific. If you haven't already, review the full range of patterns to ensure you choose the right one for each of your tables.
• Review how data is replicated and distributed across a cluster, and how this affects performance. It is especially important to understand the concept of the "leaseholder". For a summary, see Reads and Writes in CockroachDB. For a deeper dive, see the CockroachDB Architecture documentation.
• Review the concept of locality, which makes CockroachDB aware of the location of nodes and able to intelligently place and balance data based on how you define replication controls.
• Review the recommendations and requirements in our Production Checklist.
• This topology doesn't account for hardware specifications, so be sure to follow our hardware recommendations and perform a POC to size hardware for your use case.
• Adopt relevant SQL Best Practices to ensure optimal performance.

Cluster setup

Each multi-region topology pattern assumes the following setup:

Hardware

• 3 regions

• Per region, 3+ AZs with 3+ VMs evenly distributed across them

• Region-specific app instances and load balancers

  • Each load balancer redirects to CockroachDB nodes in its region.
  • When CockroachDB nodes are unavailable in a region, the load balancer redirects to nodes in other regions.

Cluster

Each node is started with the --locality flag specifying its region and AZ combination. For example, the following command starts a node in the west1 AZ of the us-west region:

$ cockroach start \
--locality=region=us-west,zone=west1 \
--certs-dir=certs \
--advertise-addr=<node1 internal address> \
--join=<node1 internal address>:26257,<node2 internal address>:26257,<node3 internal address>:26257 \        
--cache=.25 \
--max-sql-memory=.25 \
--background

Configuration

Summary

Using this pattern, you tell CockroachDB to put the leaseholder for the table itself (also called the primary index) in one region, create 2 secondary indexes on the table, and tell CockroachDB to put the leaseholder for each secondary index in one of the other regions. This means that reads will access the local leaseholder (either for the table itself or for one of the secondary indexes). Writes, however, will still leave the region to get consensus for the table and its secondary indexes.

Steps

Assuming you have a cluster deployed across three regions and a table like the following:

> CREATE TABLE postal_codes (
    id INT PRIMARY KEY,
    code STRING
);

1. If you do not already have one, request a trial Enterprise license.

2. Create a replication zone for the table and set a leaseholder preference telling CockroachDB to put the leaseholder for the table in one of the regions, for example us-west:

   > ALTER TABLE postal_codes
       CONFIGURE ZONE USING
         num_replicas = 3,
         constraints = '{"+region=us-west":1}',
         lease_preferences = '[[+region=us-west]]';
   

3. Create secondary indexes on the table for each of your other regions, including all of the columns you wish to read either in the key or in the key and a STORING clause:

   > CREATE INDEX idx_central ON postal_codes (id)
       STORING (code);
   

   > CREATE INDEX idx_east ON postal_codes (id)
       STORING (code);
   

4. Create a replication zone for each secondary index, in each case setting a leaseholder preference telling CockroachDB to put the leaseholder for the index in a distinct region:

   > ALTER INDEX postal_codes@idx_central
       CONFIGURE ZONE USING
         num_replicas = 3,
         constraints = '{"+region=us-central":1}',
         lease_preferences = '[[+region=us-central]]';
   

   > ALTER INDEX postal_codes@idx_east
       CONFIGURE ZONE USING
         num_replicas = 3,
         constraints = '{"+region=us-east":1}',
         lease_preferences = '[[+region=us-east]]';
   

5. New in v19.2: To confirm that replication zones are in effect, you can use the SHOW CREATE TABLE:

   > SHOW CREATE TABLE postal_codes;
   

      table_name  |                              create_statement
   +--------------+----------------------------------------------------------------------------+
     postal_codes | CREATE TABLE postal_codes (
                  |     id INT8 NOT NULL,
                  |     code STRING NULL,
                  |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
                  |     INDEX idx_central (id ASC) STORING (code),
                  |     INDEX idx_east (id ASC) STORING (code),
                  |     FAMILY "primary" (id, code)
                  | );
                  | ALTER TABLE defaultdb.public.postal_codes CONFIGURE ZONE USING
                  |     num_replicas = 3,
                  |     constraints = '{+region=us-west: 1}',
                  |     lease_preferences = '[[+region=us-west]]';
                  | ALTER INDEX defaultdb.public.postal_codes@idx_central CONFIGURE ZONE USING
                  |     num_replicas = 3,
                  |     constraints = '{+region=us-central: 1}',
                  |     lease_preferences = '[[+region=us-central]]';
                  | ALTER INDEX defaultdb.public.postal_codes@idx_east CONFIGURE ZONE USING
                  |     num_replicas = 3,
                  |     constraints = '{+region=us-east: 1}',
                  |     lease_preferences = '[[+region=us-east]]'
   (1 row)
   

Characteristics

Latency

Reads

Reads access the local leaseholder and, therefore, never leave the region. This makes read latency very low.

For example, in the animation below:

1. The read request in us-central reaches the regional load balancer.
2. The load balancer routes the request to a gateway node.
3. The gateway node routes the request to the relevant leaseholder. In us-west, the leaseholder is for the table itself. In the other regions, the leaseholder is for the relevant index, which the cost-based optimizer uses due to the leaseholder preferences.
4. The leaseholder retrieves the results and returns to the gateway node.
5. The gateway node returns the results to the client.

Writes

The replicas for the table and its secondary indexes are spread across all 3 regions, so writes involve multiple network hops across regions to achieve consensus. This increases write latency significantly. It's also important to understand that the replication of extra indexes can reduce throughput and increase storage cost.

For example, in the animation below:

1. The write request in us-central reaches the regional load balancer.
2. The load balancer routes the request to a gateway node.
3. The gateway node routes the request to the leaseholder replicas for the table and its secondary indexes.
4. While each leaseholder appends the write to its Raft log, it notifies its follower replicas.
5. In each case, as soon as one follower has appended the write to its Raft log (and thus a majority of replicas agree based on identical Raft logs), it notifies the leaseholder and the write is committed on the agreeing replicas.
6. The leaseholders then return acknowledgement of the commit to the gateway node.
7. The gateway node returns the acknowledgement to the client.

Resiliency

Because this pattern balances the replicas for the table and its secondary indexes across regions, one entire region can fail without interrupting access to the table:

Alternatives

• If reads from a table can be historical (48 seconds or more in the past), consider the Follower Reads pattern.
• If rows in the table, and all latency-sensitive queries, can be tied to specific geographies, consider the Geo-Partitioned Leaseholders pattern. Both patterns avoid extra secondary indexes, which increase data replication and, therefore, higher throughput and less storage.

Tutorial

For a step-by-step demonstration of how this pattern gets you low-latency reads in a broadly distributed cluster, see the Low Latency Multi-Region Deployment tutorial.

See also

• Topology Patterns Overview

  • Single-region
    • Development
    • Basic Production
  • Multi-region
    • Geo-Partitioned Replicas
    • Geo-Partitioned Leaseholders
    • Duplicate Indexes
    • Follow-the-Workload

Was this helpful?