Solving Write Hotspots in YugabyteDB with YSQL Range Partitioning

In a distributed database, a monotonically increasing primary key—such as a timestamp or an auto-incrementing ID—often creates a "hotspot." Because YugabyteDB stores data in sorted tablets, all new inserts land on the same tablet range, overloading a single node while the rest of the cluster remains idle. Range partitioning allows you to break this bottleneck by splitting a logical table into multiple physical child tables based on a key value.

How Partitioning Distributes the Load

Partitioned tables in YSQL (available in version 8.0+) allow you to define a parent table that acts as a logical interface. By using PARTITION BY RANGE, you direct data to specific child partitions based on the value of a partition key.

Crucially, each child partition is not a single file; it is a standard YugabyteDB table. This means every child partition is internally sharded into its own set of tablets across the cluster. By spreading writes across multiple child partitions, you distribute the ingestion load across more nodes, effectively multiplying your write throughput.

Mapping the Storage Layer

It is important to note that partitioning is a YSQL-specific feature; YCQL does not support it. When a query arrives at the YSQL endpoint, the planner uses the partition key to perform "partition pruning." If the query includes the partition key in the WHERE clause, YugabyteDB routes the request only to the relevant child partition. If the key is omitted, the database must scan all partitions, which can severely degrade performance.

To monitor the effectiveness of your distribution, you can use the pg_partitions system view to verify boundaries and yb_table_stats to inspect how data is distributed across tablets.

Worked Example: Time-Based Order Partitioning

To implement this, the partition key must be part of the primary key. In this example, we partition an orders table by order_date to prevent a single date's traffic from bottlenecking a single node.

Run these commands via psql connected to the YSQL endpoint. You will need CREATE privileges on the database.

-- Create the logical parent table
CREATE TABLE orders (
  id BIGINT,
  order_date DATE NOT NULL,
  amount NUMERIC,
  PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

-- Create child partitions for specific date ranges
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Verification: To confirm the partitions are active, run:

SELECT schemaname, tablename, partition_key FROM pg_partitions WHERE tablename LIKE 'orders%';

Expected Result: The output should list both orders_2024 and orders_2025 with their respective range bounds. Risk: If you attempt to insert a row with a date outside these ranges (e.g., '2023-12-31'), the insert will fail because no matching partition exists.

Trade-offs and Operational Limits

Partitioning is a structural decision, not a magic performance switch. There are three primary limitations to consider:

• Data Skew: If your partition boundaries are too wide (e.g., one partition for five years), you may still experience hotspots within that child table. Conversely, too many small partitions increase catalog overhead and slow down query planning.
• Primary Key Constraint: Because the partition key must be part of the primary key, you cannot have a simple id as the sole primary key if you want to partition by date.
• Cross-Partition Overhead: While YugabyteDB maintains full ACID compliance across partitions, transactions that update rows in multiple partitions incur higher coordination costs than those within a single partition.

To roll back a partitioning strategy, you must drop the child partitions and the parent table, as changing a table from partitioned to non-partitioned requires a full table recreation.

Actionable Summary

Use range partitioning when your write traffic is time-skewed and your primary access patterns filter by that same time column. Ensure your partition key is integrated into the primary key to enable pruning, and proactively create future partitions to avoid insert failures.