yb_server_region()

Synopsis

yb_server_region() returns the region of the currently connected node.

Examples

Call yb_server_region()

yugabyte=# SELECT yb_server_region();

 yb_server_region
-----------------
 us-west-1
(1 row)

Usage in Row-level geo-partitioning

This function is primarily helpful while implementing Row-level geo-partitioning, as it can significantly simplify inserting rows from the user's connected node.

Use case examples

Setup

Do the following to create a 3-node multi-region cluster and a geo-partitioned table using tablespaces:

  1. Create a cluster spread across 3 regions us-west-1, us-east-1, us-east-2 using yugabyted:

    ./bin/yugabyted start                           \
  --base_dir=/home/yugabyte/<IP1>/yugabyte-data \
  --listen=<IP1>                                \
  --master_flags "placement_cloud=aws,placement_region=us-west-1,placement_zone=us-west-1c" \
  --tserver_flags "placement_cloud=aws,placement_region=us-west-1,placement_zone=us-west-1c"

./bin/yugabyted start                           \
  --base_dir=/home/yugabyte/<IP2>/yugabyte-data \
  --listen=<IP2>                                \
  --join=<IP1>                                  \
  --master_flags "placement_cloud=aws,placement_region=us-east-2,placement_zone=us-east-2c" \
  --tserver_flags "placement_cloud=aws,placement_region=us-east-2,placement_zone=us-east-2c"

./bin/yugabyted start                            \
  --base_dir=/home/yugabyte/<IP3>/yugabyte-data  \
  --listen=<IP3>                                 \
  --join=<IP1>                                   \
  --master_flags "placement_cloud=aws,placement_region=us-east-1,placement_zone=us-east-1a" \
  --tserver_flags "placement_cloud=aws,placement_region=us-east-1,placement_zone=us-east-1a"

  2. Use yb-admin to specify the placement configuration to be used by the cluster:

    ./bin/yb-admin -master_addresses <IP1>:7100 modify_placement_info aws.us-west-1.us-west-1c:1,aws.us-east-1.us-east-1a:1,aws.us-east-2.us-east-2c:1 3

  3. Create tablespaces corresponding to the regions used by the cluster created above using ysqlsh:

    CREATE TABLESPACE us_west_tablespace WITH (replica_placement=' {"num_replicas":1,"placement_blocks":[{"cloud":"aws","region":"us-west-1","zone":"us-west-1c","min_num_replicas":1}]}');
CREATE TABLESPACE us_east1_tablespace WITH (replica_placement=' {"num_replicas":1,"placement_blocks":[{"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1}]}');
CREATE TABLESPACE us_east2_tablespace WITH (replica_placement=' {"num_replicas":1,"placement_blocks":[{"cloud":"aws","region":"us-east-2","zone":"us-east-2c","min_num_replicas":1}]}');

    For more information on how to set up a cluster with yugabyted or YugabyteDB Anywhere with corresponding tablespaces, see tablespaces.

  4. Using the tablespaces, you can create a geo-partitioned table as follows. This is a partitioned table with 3 partitions, where each partition is pinned to a different location based on the regions. The geo_partition column value is default to be the currently connected region as in yb_server_region().

    CREATE TABLE users(user_id INTEGER NOT NULL,
                   user_info VARCHAR NOT NULL,
                   geo_partition VARCHAR DEFAULT yb_server_region(),
                   PRIMARY KEY(user_id, geo_partition))
   PARTITION BY LIST(geo_partition);

CREATE TABLE user_us_west PARTITION OF users FOR VALUES IN ('us-west-1') TABLESPACE us_west_tablespace;

CREATE TABLE user_us_east1 PARTITION OF users FOR VALUES IN ('us-east-1') TABLESPACE us_east1_tablespace;

CREATE TABLE user_us_east2 PARTITION OF users FOR VALUES IN ('us-east-2') TABLESPACE us_east2_tablespace;

  5. Insert data to the users table:

    If your server is connected to region us-west-1, you can insert rows into the users table without having to specify the geo_partition column value.

    INSERT INTO users VALUES(1, 'US West user');

    If your server is connected to region us-west-1 and you want to insert rows into another region's partitioned table, you can still insert the rows normally.

    INSERT INTO users VALUES(2, 'US East 1 user', 'us-east-1');

  6. In a partitioned setup, if there are no WHERE clause restrictions on the partition key, note that every query on a partitioned table gets fanned out to all of its child partitions:

    EXPLAIN (COSTS OFF) SELECT * FROM users;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Append
   ->  Seq Scan on user_us_east2
   ->  Seq Scan on user_us_east1
   ->  Seq Scan on user_us_west
(4 rows)

Using yb_server_region() on a partitioned table

Assuming that the client is in the us-west-1 region, using yb_server_region() in the WHERE clause causes YSQL to only scan the us_user_west table:

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE geo_partition=yb_server_region();

                           QUERY PLAN
-----------------------------------------------------------------
 Append
   Subplans Removed: 2
   ->  Seq Scan on user_us_west
         Filter: ((geo_partition)::text = (yb_server_region())::text)
(4 rows)

In other words, using yb_server_region() in the WHERE clause automatically returns only values from your current region.

SELECT * FROM users WHERE geo_partition=yb_server_region();

 user_id |  user_info   | geo_partition
---------+--------------+---------------
       1 | US West user | us-west-1
(1 row)

Note

If you didn't set the placement_zone flag at node startup, yb_server_zone() returns NULL.

See also