YugabyteDB supports PostgreSQL-style declarative table partitioning, including RANGE, LIST, and HASH partitioning.
That can be especially useful for large tables where data has a natural lifecycle, for example, retaining 12 months of history and regularly removing the oldest month.
However, there is an important YugabyteDB-specific distinction:
- ● YugabyteDB already distributes and shards tables automatically at the DocDB storage layer.
- ● YSQL declarative partitioning adds a second layer of partitioning on top of that distributed storage.
Two Different Kinds of Partitioning
It helps to separate two concepts that are sometimes both called “partitioning.”
Each YSQL partition is a separate table underneath the partitioned parent, and each of those tables is itself distributed across YugabyteDB tablets.
That means the two mechanisms work together:
A Simple Time-Based Example
Suppose we have a large table containing change history:
CREATE TABLE order_changes (
change_date DATE NOT NULL,
id BIGINT GENERATED BY DEFAULT AS IDENTITY,
type TEXT,
description TEXT,
PRIMARY KEY (change_date, id)
)
PARTITION BY RANGE (change_date);
We can create monthly partitions:
CREATE TABLE order_changes_2026_01
PARTITION OF order_changes
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE order_changes_2026_02
PARTITION OF order_changes
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE order_changes_2026_03
PARTITION OF order_changes
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
The lower boundary is inclusive and the upper boundary is exclusive.
Rows inserted into the parent automatically go to the appropriate child partition.
Why This Is Great for Purging
Without partitioning, removing millions or billions of old rows may require a large DELETE:
DELETE FROM order_changes
WHERE change_date < DATE '2025-01-01';
That is real DML. It creates distributed writes, transaction work, tombstones, and eventual compaction work.
With time-based partitioning, old data can instead be removed at the partition level:
ALTER TABLE order_changes
DETACH PARTITION order_changes_2025_01;
DROP TABLE order_changes_2025_01;
That is fundamentally different from deleting every row individually.
DELETE operations, you can detach and drop an entire old partition. Concern #1: Indexes Are Local to Each SQL Partition
This is probably the most important query-performance consideration.
Suppose we create an index on the partitioned parent:
CREATE INDEX order_changes_type_idx
ON order_changes (type);
YSQL creates corresponding indexes for the individual child partitions.
There is not one global index spanning all declarative partitions.
That distinction matters when executing queries.
Query That Includes the Partition Key
Consider:
SELECT *
FROM order_changes
WHERE change_date >= DATE '2026-03-01'
AND change_date < DATE '2026-04-01'
AND type = 'ADDRESS';
The planner knows that only the March partition can contain those rows.
Partition pruning can eliminate the other partitions before accessing their tables or indexes.
That is exactly what we want.
Query Without the Partition Key
Now consider:
SELECT *
FROM order_changes
WHERE type = 'ADDRESS';
There is no change_date predicate.
The planner cannot eliminate partitions based on the partition key, so it may need to search the type index belonging to every applicable partition.
With three partitions, that might be trivial. With 12 or 24 partitions, it may still be perfectly reasonable.
With hundreds of partitions, the query can become much more expensive because the SQL layer must coordinate work across many child relations and potentially many distributed tablets.
Verify Partition Pruning
Don’t assume that a query is pruning partitions.
Check it:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT *
FROM order_changes
WHERE change_date >= DATE '2026-03-01'
AND change_date < DATE '2026-04-01'
AND type = 'ADDRESS';
Then compare it with:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT *
FROM order_changes
WHERE type = 'ADDRESS';
For the second query, look for an Append or Parallel Append accessing multiple child partitions.
Also pay attention to YugabyteDB-specific metrics such as:
- ●
Storage Read Requests - ●
Storage Rows Scanned - ● Number of child scans
- ● Actual execution time
Those numbers tell you far more than simply looking at whether the plan says Index Scan.
Concern #2: No Global Unique Constraint Across Partitions
PostgreSQL declarative partitioning requires a PRIMARY KEY or UNIQUE constraint on the partitioned parent to contain all partition key columns.
For our example:
PRIMARY KEY (change_date, id)
is valid because change_date is the partition key.
But this:
UNIQUE (id)
cannot provide a global uniqueness constraint across all partitions.
The database would need a single global index spanning all child partitions to enforce that, and PostgreSQL-style declarative partitioning does not provide one.
UNIQUE(id) constraint spanning every partition.If strict global uniqueness on a non-partition column is required, you’ll need to model that separately.
Concern #3: Every Partition Adds Tablets
A declarative partition is not simply a logical boundary.
Each child partition is a separate distributed YugabyteDB table. Each secondary index on that partition is another distributed YugabyteDB relation.
So a rough way to think about the footprint is:
And with replication:
For example, imagine:
Before any automatic tablet splitting:
That is only an illustrative example… the actual starting tablet count depends on the YugabyteDB configuration, schema, and version, and tablets may automatically split as the data grows.
The important point is that partition count and index count multiply each other.
Don’t Over-Presplit the Partitions
YugabyteDB supports automatic tablet splitting, so for most workloads it makes sense to:
- ● Start with a conservative number of tablets.
- ● Let automatic tablet splitting grow large partitions.
- ● Use
SPLIT INTOwhen initial parallelism is actually required by a high-ingest workload. - ● Monitor real tablet distribution instead of calculating a large presplit count up front.
Check the Actual Tablet Count
On YugabyteDB versions that provide the yb_tablet_metadata view, you can see the tablets backing the leaf partitions.
For example:
SELECT
pt.relid::regclass AS partition_name,
COUNT(ytm.tablet_id) AS tablets
FROM pg_partition_tree('order_changes'::regclass) pt
LEFT JOIN yb_tablet_metadata ytm
ON ytm.oid = pt.relid
WHERE pt.isleaf
GROUP BY pt.relid
ORDER BY pt.relid::regclass::text;
You can also examine the secondary indexes belonging to each partition:
SELECT
child.relname AS partition_name,
idx.relname AS index_name,
COUNT(ytm.tablet_id) AS tablets
FROM pg_partition_tree('order_changes'::regclass) pt
JOIN pg_class child
ON child.oid = pt.relid
JOIN pg_index i
ON i.indrelid = child.oid
JOIN pg_class idx
ON idx.oid = i.indexrelid
LEFT JOIN yb_tablet_metadata ytm
ON ytm.oid = idx.oid
WHERE pt.isleaf
AND NOT i.indisprimary
GROUP BY child.relname, idx.relname
ORDER BY child.relname, idx.relname;
This is much better than guessing how many tablets the partition hierarchy has created.
Will Inserts Be Slower?
There is some additional SQL-layer routing involved because YugabyteDB first determines the appropriate declarative partition.
But once the partition has been identified, the row is written to the tablets backing that child table and its indexes just like other YugabyteDB writes. With a reasonable number of partitions, I would not assume that partitioning automatically means a significant INSERT performance penalty.
Benchmark the actual workload.
The bigger performance concern tends to be reads that cannot take advantage of partition pruning, not the act of routing an INSERT into one of a modest number of partitions.
How Many Partitions Should You Create?
There isn’t one magic number.
The better question is:
- What is the largest partition interval that still satisfies the lifecycle requirement?
If the requirement is:
- Keep one year of data and purge once per month.
Then monthly partitions are an obvious place to start:
There is usually no benefit to creating 365 daily partitions if the application only purges once per month.
Similarly, if quarterly retention boundaries work, four quarterly partitions may be even simpler.
Use the minimum number of declarative partitions needed to accomplish the operational goal, and let YugabyteDB tablets handle the scale within each partition.
Automating Time-Based Partitions
Manually creating the next month’s partition is easy to forget.
For environments with ongoing time-based partition maintenance, YugabyteDB also supports the pg_partman extension.
For example, pg_partman can pre-create future monthly partitions and automate retention maintenance.
There are YugabyteDB-specific considerations with pg_partman, so review its documented limitations before relying on it… particularly around default partitions, maintenance scheduling, and replicated environments.
Other Partitioning Details to Remember
Practical Recommendation
For a large YugabyteDB table where the primary motivation is purging historical data, I would absolutely consider declarative range partitioning by time.
But I would design it with a few rules:
- Partition for lifecycle… not for scale. Let DocDB tablets provide horizontal scale.
- Choose the coarsest useful interval. Monthly or quarterly partitions are often preferable to daily partitions when retention allows it.
- Make partition pruning part of the query design. High-frequency queries should include the partition key whenever practical.
- Identify queries that cannot include the partition key. Benchmark those carefully because they may need to search local indexes across every partition.
- Decide up front whether global uniqueness is required. A partitioned design may require changing how that constraint is modeled.
- Keep tablet counts conservative. Let automatic tablet splitting do its job rather than heavily presplitting every partition.
- Measure instead of guessing. Use
EXPLAIN (ANALYZE, DIST)andyb_tablet_metadatato see the actual distributed work being performed.
Have Fun!
We could not figure out what these tiny black dots all over the house were, so naturally we asked AI. 😂
Turns out our nice black wood mulch was growing artillery fungus… which literally shoots sticky little black spores at your house.
Yep. Our mulch was apparently armed. 🎯🍄
The good news: Magic Erasers actually work!
The bad news: there were approximately 47 billion spots, and cleaning them off took FOREVER. 😩
So we have now replaced the offending mulch with rubber mulch.
Consider this a public service announcement:
if mysterious black dots start appearing on your siding, check your mulch before your house becomes a fungal shooting range. 😂
⚠️ You have been warned!