Some workloads query a huge table along two axes — for example by time window and by tenant. Explain sub-partitioning (composite partitioning), when a second level genuinely pays off, and what it costs.
answer
- Second strategy inside each partition; only leaves store rows
- RANGE(time) then HASH(tenant) is the classic
- Both keys must do work
- Counts multiply: 36 x 8 = 288 leaves
- Unique keys must contain both partition keys
basics
~20 sSub-partitioning partitions each partition again by a second key — typically RANGE by month at the top and HASH or LIST by tenant underneath. It pays off only when both keys appear in queries or maintenance. The cost is multiplicative: levels multiply into total partition count.
solid answer
~60 sA composite layout declares a second strategy inside each first-level partition: range by `occurred_at` monthly, then hash by `tenant_id` into 8 sub-partitions, giving 8 physical tables per month. It is worth it when **both** levels do work: - the top level matches the lifecycle (drop a whole month) and the dominant filter (time window); - the second level meaningfully narrows a query that already constrains time (a tenant's rows in that month), or splits an otherwise unmanageably large monthly partition into pieces you can index and maintain individually. It is not worth it when the second key rarely appears in predicates — you then get the partition count without the pruning. Costs are multiplicative and hit fast: 36 months x 8 hash = 288 tables, each with its own indexes and statistics; planning cost, catalog size, lock counts and automation complexity all grow. Partition-creation automation must now generate a whole sub-tree per period, and a mistake leaves an incomplete month. Rule of thumb: reach for level two only after level one is in place and demonstrably insufficient.
code
sql · 15 linesCREATE TABLE events (
tenant_id bigint NOT NULL,
occurred_at timestamptz NOT NULL,
id bigint NOT NULL,
PRIMARY KEY (tenant_id, occurred_at, id)
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
PARTITION BY HASH (tenant_id);
CREATE TABLE events_2026_01_p0 PARTITION OF events_2026_01
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_2026_01_p1 PARTITION OF events_2026_01
FOR VALUES WITH (MODULUS 4, REMAINDER 1);go deeper
Know that a partition can itself be partitioned again by a second key, and give the range-by-time then hash-by-tenant example.
State the test for whether the second level pays off and note that leaf counts multiply along with indexes and statistics.
Weigh sub-partitioning against a local index, discuss automation and locking costs, and size both levels together against a total leaf-count budget.
Treat it as an operability decision over the table's whole life: retention units, backup and storage tiering, migration path, and the point at which the answer stops being partitioning and becomes a separate cluster.
## What sub-partitioning is Sub-partitioning, also called composite partitioning, applies a second partitioning strategy inside each first-level partition. The parent is partitioned by one key; each of its partitions is itself declared as a partitioned table with its own key and strategy; only the leaves store rows. Common combinations: - **RANGE(time) then HASH(tenant)** — keep the time axis for retention and window queries, and split each period into equal pieces so no single month is unmanageably large. - **RANGE(time) then LIST(region)** — when regions must be stored, backed up or retained differently. - **LIST(category) then RANGE(time)** — when a few categories have very different volumes and lifecycles. Syntactically it is just the same declaration applied one level down: the first-level child is created `PARTITION OF parent ... PARTITION BY HASH (tenant_id)`, and its own children carry hash bounds. ## When the second level earns its place The honest test is: *does something concrete get better that a single level cannot deliver?* Three cases qualify. **Two-axis pruning.** The important query constrains both keys — "this tenant's events in March". With a single time level, that query scans or index-probes all of March; with a hash sub-level on tenant, it touches one eighth of March. That matters only when March is genuinely large and the per-tenant slice is a small share of it. **Managing an oversized first-level partition.** Sometimes the natural lifecycle interval is monthly but a month is 400 GB. Going to daily partitions would explode the count and misalign with retention. Sub-partitioning the month into equal hash pieces keeps the retention unit at a month while making each physical table small enough to index, vacuum, rewrite and back up independently. **Different physical treatment per category.** A list sub-level lets one region's data live on different storage, be excluded from a backup set, or be retained on a different schedule, while the range level continues to govern time. ## When it does not If the second key is absent from your predicates, you gain nothing at query time and pay everything at management time. If the first-level partitions are already comfortably sized, a second level is pure overhead. And if what you actually want is *capacity* rather than manageability, no amount of sub-partitioning helps — it is still one server, and the answer is sharding or a bigger machine. A frequent mistake is reaching for a second level to compensate for a poor first-level key. If queries mostly filter by tenant and only sometimes by time, the answer may be to partition by tenant (hash) at the top and range by time underneath — or to reconsider whether partitioning is the right tool at all. ## The costs, which are multiplicative **Partition count.** Levels multiply. 36 monthly partitions x 8 hash sub-partitions = 288 leaf tables; make it daily and you are at thousands. Since per-relation overheads in planning, catalog access and connection-local caches scale with leaf count, this is the constraint that usually decides the granularity of both levels together, not each in isolation. **Index and statistics objects.** Every index exists once per leaf. Three indexes over 288 leaves is 864 index objects to create, monitor for bloat, and rebuild. Statistics are collected per leaf, and per-leaf row counts get small enough that estimates can degrade. **Automation complexity.** Creating next month is no longer one `CREATE TABLE`; it is a partitioned child plus all its sub-partitions plus their indexes, as one atomic-ish operation. Partial failure leaves a month that accepts some tenants and rejects others. Whatever job pre-creates partitions must be idempotent and must be monitored, because a missing leaf means failed inserts. **Locking and DDL.** Schema changes touch far more relations; adding a column or an index acquires locks across the whole tree, and the operation's duration grows with leaf count. **Constraint rules compound.** Every unique constraint must include *both* partition keys, which frequently forces primary keys like `(tenant_id, occurred_at, id)` — usable, but it changes index design and it means a globally unique `id` alone is not enforceable. ## A pragmatic sequence 1. Partition on the lifecycle key at a granularity that gives tens to low hundreds of partitions. Ship it. Measure. 2. If individual partitions are still too big to maintain, or a two-key query is demonstrably dominant and slow, add the second level — sized so total leaf count stays in the hundreds, not thousands. 3. Automate creation of the full sub-tree and alert on missing leaves and on the default partition growing. Also consider the alternatives before adding a level: a well-chosen local index on the second column inside each first-level partition often delivers most of the query benefit at a fraction of the operational cost. Sub-partitioning wins over an index when the benefit is *physical* — separate maintenance, separate storage, separate retention — not merely selective lookup. ## How to answer Define the two-level model with a concrete pairing, state the test for whether level two earns its place (both keys do work, or a first-level partition is unmanageably large), emphasise that partition counts multiply, and mention the honest alternative of a local index on the second column.
- When would a local index on the second column be a better choice than adding a sub-partition level?Whenever the benefit you want is selective lookup rather than physical separation. An index on tenant_id inside each monthly partition gives fast access to one tenant's rows in that month without multiplying relations, indexes, statistics or automation. Sub-partitioning wins only when you need the sub-slices to be separately maintainable, separately stored or separately retained — for example rebuilding or archiving one slice at a time — or when the monthly partition is too large to maintain as one object.
- How does sub-partitioning change the primary key you can declare?Every unique constraint must contain all partitioning columns from every level, because uniqueness is enforced by leaf-local indexes and rows sharing a value must be guaranteed to land in the same leaf. With range on occurred_at and hash on tenant_id, the primary key must include both, giving something like (tenant_id, occurred_at, id). A bare UNIQUE(id) is not enforceable, so global identity has to come from a generator such as a UUID or a sequence you trust.
Monthly volumes of a ledger, each volume split into eight tabbed sections. Useful only if you routinely need one section of one month — otherwise you have bought 288 bindings.
saying these in an interview costs you the question
- Adding a second level when the second key is rarely in query predicates
- Ignoring that partition counts multiply across levels and landing in the thousands
- Expecting sub-partitioning to add write capacity on the same server
- Forgetting that unique constraints must include both partitioning columns
- Automating creation of the top-level partition only, leaving months with missing sub-partitions