yugabytedb

Can you shard a partitioned table in YugabyteDB?

[Question posted by a user on YugabyteDB Community Slack]

Can a partitioned table in YugabyteDB YSQL be sharded underneath?

Solution

  • Yes.

    If you use the sql on this page, you should be able to see it: https://docs.yugabyte.com/preview/explore/ysql-language-features/advanced-features/partitions/

    Example:

    yugabyte=# CREATE TABLE order_changes (
yugabyte(#   change_date date,
yugabyte(#   type text,
yugabyte(#   description text
yugabyte(# )
yugabyte-# PARTITION BY RANGE (change_date);
CREATE TABLE
yugabyte=# CREATE TABLE order_changes_2019_02 PARTITION OF order_changes
yugabyte-#   FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');
CREATE TABLE
yugabyte=# CREATE TABLE order_changes_2019_03 PARTITION OF order_changes
yugabyte-#   FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');
CREATE TABLE
yugabyte=# CREATE TABLE order_changes_2020_11 PARTITION OF order_changes
yugabyte-#   FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE
yugabyte=# CREATE TABLE order_changes_2020_12 PARTITION OF order_changes
yugabyte-#   FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');

CREATE TABLE
yugabyte=#
yugabyte=# CREATE TABLE order_changes_2021_01 PARTITION OF order_changes
yugabyte-#   FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE
yugabyte=#\q

    yb-admin --master_addresses 127.0.0.1:7100,127.0.0.2:7100,127.0.0.3:7100 list_tablets ysql.yugabyte order_changes_2019_02
Tablet-UUID                          Range                                                        Leader-IP           Leader-UUID
4ad7671bfcdf431eb7a3246ea9fd7480     partition_key_start: "" partition_key_end: "*\252"           127.0.0.2:9100      4d88502883f64cafbff2ba745c57b1fd
2f27e6a5f5fb4722abb23d133052293f     partition_key_start: "*\252" partition_key_end: "UT"         127.0.0.1:9100      d321a3d5fe444d78b5c2c8f519b65f1d
2ac7e3c873d74a46af2a80b2eb9589da     partition_key_start: "UT" partition_key_end: "\177\376"      127.0.0.1:9100      d321a3d5fe444d78b5c2c8f519b65f1d
795f1351d8664509af4da88744dd1229     partition_key_start: "\177\376" partition_key_end: "\252\250"     127.0.0.3:9100      db2e8e0df9fe4a96966aa6530123984c
4133638d308b419cb0326b442c5c0e86     partition_key_start: "\252\250" partition_key_end: "\325R"     127.0.0.3:9100      db2e8e0df9fe4a96966aa6530123984c
594086be6aa147d592e28270a0f1d220     partition_key_start: "\325R" partition_key_end: ""           127.0.0.2:9100      4d88502883f64cafbff2ba745c57b1fd