sql: document using RBR and Global tables as a snowflake pattern

awoods187 commented 3 years ago

Andy Woods (awoods187) commented:

We learned of a recent multi-region schema modeled as a standard “snowflake pattern”, but with a few cool additions with locality and data homing. First, data homing was pushed to the top of this hierarchy. All children inherited their parent’s homing using ON UPDATE CASCADE foreign key constraints. Second, all tables in the hierarchy except for the top were REGIONAL BY ROW. The top was GLOBAL, even though it contained a manual crdb_internal_region column. This looked like the following (which you can run in ./cockroach demo --global --empty --nodes=9 if you’d like to play around with it):

CREATE TABLE accounts (
    account_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        STRING NOT NULL,
    crdb_region crdb_internal_region NOT NULL,
    UNIQUE INDEX (account_id, crdb_region) -- this shouldn't be needed, but is. See https://github.com/cockroachdb/cockroach/issues/64619.
) LOCALITY GLOBAL;

CREATE TABLE tweets (
    tweet_id   UUID DEFAULT gen_random_uuid(),
    account_id UUID NOT NULL,
    message    STRING NOT NULL,
    crdb_region crdb_internal_region NOT NULL,
    PRIMARY KEY (crdb_region, tweet_id),
    FOREIGN KEY (account_id, crdb_region) REFERENCES accounts (account_id, crdb_region) ON DELETE CASCADE ON UPDATE CASCADE
) LOCALITY REGIONAL BY ROW;

INSERT INTO accounts VALUES (DEFAULT, 'starburst', 'us-east1') RETURNING account_id;

INSERT INTO tweets VALUES (
    DEFAULT,
    'ab887bbc-1a83-4324-8998-d18ebe448fa7',
    'this is tweeet 1',
    (SELECT crdb_region FROM accounts WHERE account_id = 'ab887bbc-1a83-4324-8998-d18ebe448fa7')
) RETURNING tweet_id;

INSERT INTO tweets VALUES (
    DEFAULT,
    'ab887bbc-1a83-4324-8998-d18ebe448fa7',
    'this is tweeet 2',
    (SELECT crdb_region FROM accounts WHERE account_id = 'ab887bbc-1a83-4324-8998-d18ebe448fa7')
) RETURNING tweet_id;
There are two very cool things that this setup provides. First, it allows all rows associated with a given account to be moved between regions with a single update.
demo@127.0.0.1:26257/defaultdb> select * from tweets;
                tweet_id               |              account_id              |     message      | crdb_region
---------------------------------------+--------------------------------------+------------------+--------------
  155420a6-9aff-419d-bf6c-e1757d0119f4 | ab887bbc-1a83-4324-8998-d18ebe448fa7 | this is tweeet 1 | us-east1
  b05ee724-d9cb-40c7-8098-74178ad32ee4 | ab887bbc-1a83-4324-8998-d18ebe448fa7 | this is tweeet 2 | us-east1
(2 rows)

Time: 68ms total (execution 67ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> update accounts set crdb_region = 'us-west1' WHERE account_id = 'ab887bbc-1a83-4324-8998-d18ebe448fa7';
UPDATE 1

Time: 758ms total (execution 758ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> select * from tweets;
                tweet_id               |              account_id              |     message      | crdb_region
---------------------------------------+--------------------------------------+------------------+--------------
  155420a6-9aff-419d-bf6c-e1757d0119f4 | ab887bbc-1a83-4324-8998-d18ebe448fa7 | this is tweeet 1 | us-west1
  b05ee724-d9cb-40c7-8098-74178ad32ee4 | ab887bbc-1a83-4324-8998-d18ebe448fa7 | this is tweeet 2 | us-west1
(2 rows)

We should document this pattern.

Jira Issue: DOC-1451