How to create range partioned, date partitioned, geo partioned tables in yugabyte-db?

Question

It is understood that yugabyte creates hash partitioned tables by default.

Please guide me in creating range partioned, date partitioned, geo partioned tables in yugabyte-db with multiple tablets.

Answer 1

Hash partitioned table is specified as CREATE TABLE sample(k1 int, k2 int, PRIMARY KEY (k1, k2));

Setting the first column of the primary key as ASC/DESC will use range partitioning like:

CREATE TABLE sample(k1 int, k2 int, PRIMARY KEY (k1 ASC, k2 DESC));

Docs page for more info: https://docs.yugabyte.com/latest/api/ysql/commands/ddl_create_table/

Geo partitioned tables are not yet possible. You can subscribe to this issue: https://github.com/yugabyte/yugabyte-db/issues/1958

Answer 2

@dh-yb 's explanation of YugabyteDB's Hash Sharded and Range Sharded tables is on point.

The terms Sharding and Partitioning are used interchangeably nowadays. In a distributed database like YugabyteDB which is fully compatible with a single-node DB like Postgres, there are some subtle differences between the two terms. Sharding is for data distribution while Partitioning is for data placement for management/maintenance. More details @ Marco's blog on Sharding vs Partitioning

YugabyteDB has supported Row Level Geo-Partitioning of tables for some time now. It leverages two Postgres features(which YugaByteDB fully supports) - Table Partitioning and TableSpaces. When we create the table, we specify on what field(s) we want the table to be partitioned and then specify where you want the partition to be located via TableSpaces. Let's say we have a user table and you want the data for US users to be in the US and the data for EU users to be in Europe.

First, create the TableSpaces in US & EU to place the data partition.

--- Table Space for Europe
CREATE TABLESPACE eu_tablespace WITH (
  replica_placement='{
      "placement_blocks": [{"cloud":"aws","region":"eu-central-1","zone":"eu-central-1a"}]
  }'
);

--- Table Space for US
CREATE TABLESPACE us_tablespace WITH (
  replica_placement='{
      "placement_blocks": [{"cloud":"aws","region":"us-west-2","zone":"us-west-2a"}]
  }'
);

Let's create the actual table. We specify a geo_partition field with values in ['EU', 'US'] which will decide where the user data will reside in

--- User table
CREATE TABLE user_info (
    user_id   INTEGER NOT NULL,
    account_id INTEGER NOT NULL,
    geo_partition VARCHAR
) PARTITION BY LIST (geo_partition);

Now create the table partitions and attach them to the geo-specific table spaces we created above.

--- Europe Table partitions  
CREATE TABLE user_info_eu 
    PARTITION OF user_info 
      (user_id, account_id, geo_partition,
       PRIMARY KEY (user_id HASH, account_id, geo_partition))
    FOR VALUES IN ('EU') TABLESPACE eu_tablespace;

--- for US users 
CREATE TABLE user_info_us 
    PARTITION OF user_info 
      (user_id, account_id, geo_partition,
       PRIMARY KEY (user_id HASH, account_id, geo_partition))
    FOR VALUES IN ('US') TABLESPACE us_tablespace;

That's it !! Now if you insert a row with geo_partition='EU', that row will be placed only in Europe and rows with geo_partition='US' will be in the US.

More details @ Yugabyte DB's Row-Level GeoPartitioning