Colocation Gotcha: Creating Hash-Partitioned Tables with Range Partitions in YugabyteDB

🟢 Update (Nov 2025): The colocation issue described in this tip has been fixed in YugabyteDB 2025.1.1.2. You can now create hash-partitioned tables with range partitions in colocated databases without needing the workaround mentioned in this post. 💪🔥

Example:

				
					yugabyte=# SELECT split_part(version(), '-', 3) yb_version;
 yb_version
------------
 2025.1.1.2
(1 row)

yugabyte=# CREATE DATABASE demo WITH COLOCATION = TRUE;
CREATE DATABASE

yugabyte=# CREATE TABLE sales_orders (
yugabyte(#      region       VARCHAR,
yugabyte(#      order_id     BIGINT NOT NULL,
yugabyte(#      customer_id  BIGINT NOT NULL,
yugabyte(#      product_id   BIGINT NOT NULL,
yugabyte(#      quantity     INT NOT NULL,
yugabyte(#      order_date   TIMESTAMP DEFAULT NOW(),
yugabyte(#      status       VARCHAR NOT NULL
yugabyte(#  ) PARTITION BY LIST (region) WITH (colocation = false);
CREATE TABLE

yugabyte=# CREATE TABLE sales_orders_us
yugabyte-#      PARTITION OF sales_orders
yugabyte-#        (region, order_id, customer_id, product_id,
yugabyte(#         quantity, order_date, status,
yugabyte(#         PRIMARY KEY (customer_id HASH, order_id, region))
yugabyte-#      FOR VALUES IN ('US') WITH (colocation = false);
CREATE TABLE

Intro

YugabyteDB’s colocation feature is a powerful tool that helps reduce storage and performance overhead by allowing multiple tables to share the same tablet. This is ideal for workloads with many small tables or use cases like multi-tenancy.

However, not all combinations of features work seamlessly, especially when colocation meets range partitioning and hash-based primary keys.

Let’s walk through a scenario where we hit a limitation when trying to create a non-colocated parent table with a primary key and partitioning and how to work around it.

Background: Colocation and Table Partitioning

In a colocated database, user tables (unless explicitly marked otherwise) are colocated by default. While this is beneficial for many cases, creating non-colocated hash-partitioned tables within a colocated database currently requires extra steps, especially when the table is also range-partitioned. This limitation is expected to be addressed in a future release, but until then, there’s a reliable workaround.

Reproducing the Issue

First, let’s create a new colocated database:

				
					CREATE DATABASE retail_data WITH COLOCATION=true;
\c retail_data;

SELECT yb_is_database_colocated(); -- returns 't'

Let’s now try to create a range-partitioned, non-colocated parent table with a composite primary key using hash and range:

				
					CREATE SCHEMA orders;

CREATE TABLE orders.store_sales_partition (
    sale_meta jsonb DEFAULT NULL,
    sale_amount decimal(16,2) NOT NULL,
    sale_id varchar(24) COLLATE "C" NOT NULL,
    region_code varchar(24) COLLATE "C" NOT NULL,
    PRIMARY KEY (sale_id HASH, region_code ASC)
) PARTITION BY RANGE (region_code) WITH (COLOCATION = false);

This will result in an error.

Example:

				
					retail_data=# CREATE TABLE orders.store_sales_partition (
retail_data(#     sale_meta jsonb DEFAULT NULL,
retail_data(#     sale_amount decimal(16,2) NOT NULL,
retail_data(#     sale_id varchar(24) COLLATE "C" NOT NULL,
retail_data(#     region_code varchar(24) COLLATE "C" NOT NULL,
retail_data(#     PRIMARY KEY (sale_id HASH, region_code ASC)
retail_data(# ) PARTITION BY RANGE (region_code) WITH (COLOCATION = false);
ERROR:  unrecognized parameter "colocation"

Even trying without the WITH (COLOCATION = false) fails if the PK includes hash partitioning:

				
					retail_data=# CREATE TABLE orders.store_sales_partition (
retail_data(#     sale_meta jsonb DEFAULT NULL,
retail_data(#     sale_amount decimal(16,2) NOT NULL,
retail_data(#     sale_id varchar(24) COLLATE "C" NOT NULL,
retail_data(#     region_code varchar(24) COLLATE "C" NOT NULL,
retail_data(#     PRIMARY KEY (sale_id HASH, region_code ASC)
retail_data(# ) PARTITION BY RANGE (region_code);
ERROR:  cannot colocate hash partitioned table

Workaround: Create Parent Table Without a PK

The current workaround is to:

Create the parent table without a primary key.
Define child partitions that are explicitly non-colocated and define the primary key at the child level.

Step 1: Create parent table

				
					CREATE TABLE orders.store_sales_partition (
    sale_meta jsonb DEFAULT NULL,
    sale_amount decimal(16,2) NOT NULL,
    sale_id varchar(24) COLLATE "C" NOT NULL,
    region_code varchar(24) COLLATE "C" NOT NULL
) PARTITION BY RANGE (region_code);

Step 2: Add child partition with PK and non-colocation

				
					CREATE TABLE orders.store_sales_partition_east
    PARTITION OF orders.store_sales_partition (sale_meta, sale_amount, sale_id, region_code,
     PRIMARY KEY (sale_id HASH, region_code ASC))
    FOR VALUES FROM ('A') TO ('Z')
    WITH (COLOCATION = false);

Confirming Colocation Behavior

We can inspect the colocation status of each table using yb_table_properties:

				
					SELECT * FROM yb_table_properties('orders.store_sales_partition'::regclass);
SELECT * FROM yb_table_properties('orders.store_sales_partition_east'::regclass);

Example:

				
					retail_data=# SELECT * FROM yb_table_properties('orders.store_sales_partition'::regclass);
 num_tablets | num_hash_key_columns | is_colocated | tablegroup_oid | colocation_id
-------------+----------------------+--------------+----------------+---------------
           1 |                    0 | t            |          16400 |    1576751817
(1 row)

retail_data=# SELECT * FROM yb_table_properties('orders.store_sales_partition_east'::regclass);
 num_tablets | num_hash_key_columns | is_colocated | tablegroup_oid | colocation_id
-------------+----------------------+--------------+----------------+---------------
           1 |                    1 | f            |                |
(1 row)

As expected:

The parent table is colocated (default for the database).
The child table is not colocated, and it is hash-partitioned on sale_id.

Recap and Takeaways

Creating a non-colocated range-partitioned parent table with a primary key isn’t currently supported directly.
The workaround is to omit the PK on the parent, and define it in each child partition.
You can mark child partitions as non-colocated using WITH (COLOCATION = false).
Use yb_table_properties() to verify colocation and hash distribution status.

Looking Ahead

This limitation is expected to be resolved in a future release of YugabyteDB. Until then, this pattern provides a clean and reliable workaround for building hybrid-partitioned, non-colocated data models inside colocated databases.

Have Fun!