Example of non-prefixed local index on a geo distributed table

[robert-s-lee]

When a index is partitioned, there is concept of local and global index. Each type can have prefixed and non-prefixed. A secondary index tends to be global non-prefix. Some examples include secondary index for email id, address, phone numbers.

Read performance of global non-prefixed index on a globally distributed CockroachDB is highly dependent on network latency. In order to provide predictable read performance, multiple local non-prefixed index should be created and used. This trade write performance when that field changes against predictable good read performance. A secondary index that is frequently used but seldom changes are a good candidate for this technique.

CREATE TABLE IF NOT EXISTS customers (
  customer_id UUID,
  email string,
  PRIMARY KEY (customer_id),
  index orders_email_america (email),
  index orders_email_europe (email),
  index orders_email_asia (email)
) PARTITION BY RANGE (customer_id) (
    PARTITION americas VALUES FROM (b'\x10\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00') to (b'\x20\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00')
    ,PARTITION europe VALUES FROM (b'\x20\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00') to (b'\x30\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00')
    ,PARTITION asia VALUES FROM (b'\x30\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00') to (b'\x40\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00')
);

ALTER PARTITION americas OF TABLE customers EXPERIMENTAL CONFIGURE ZONE 'constraints: {"+r=americas": 2}';
ALTER PARTITION europe OF TABLE customers EXPERIMENTAL CONFIGURE ZONE 'constraints: {"+r=americas": 2}';
ALTER PARTITION asia OF TABLE customers EXPERIMENTAL CONFIGURE ZONE 'constraints: {"+r=americas": 2}';

ALTER INDEX customers@orders_email_america EXPERIMENTAL CONFIGURE ZONE 'constraints: {"+r=americas": 2}';
ALTER INDEX customers@orders_email_europe EXPERIMENTAL CONFIGURE ZONE 'constraints: {"+r=europe": 2}';
ALTER INDEX customers@orders_email_asia EXPERIMENTAL CONFIGURE ZONE 'constraints: {"+r=asia": 2}';

ALTER INDEX customers@orders_email_america EXPERIMENTAL CONFIGURE ZONE 'experimental_lease_preferences: [[+r=americas]]';
ALTER INDEX customers@orders_email_europe EXPERIMENTAL CONFIGURE ZONE 'experimental_lease_preferences: [[+r=europe]]';
ALTER INDEX customers@orders_email_asia EXPERIMENTAL CONFIGURE ZONE 'experimental_lease_preferences: [[+r=asia]]';


insert into customers (customer_id,email) values (b'\x10\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00', '[email protected]');

SELECT * FROM customers@orders_email_america WHERE email = '[email protected]';
SELECT * FROM customers@orders_email_europe WHERE email = '[email protected]';
SELECT * FROM customers@orders_email_asia WHERE email = '[email protected]';

[robert-s-lee]

@drewdeally

[awoods187]

Closing this since we have duplicate index documented