You are converting a very large table to a partitioned one and must commit to a partition key and granularity. How do you choose the column, and what makes a partition key a bad one?

## The decision, framed properly A partition key is close to irreversible: changing it means recreating the table and rewriting every row. It deserves the same care as a primary key. There are exactly three inputs. ## 1. How the data dies Start here, because it is the benefit that no index can replicate. If your retention policy says "keep 13 months", then partitioning by month turns nightly expiry from a `DELETE` that scans, writes tombstones/undo, bloats indexes and generates enormous WAL, into a metadata operation that removes one child table. On a table doing hundreds of millions of rows a month, this alone justifies the project. If the data never expires, this input is silent and you fall through to the next. ## 2. What the queries filter on Partitioning only helps reads when the engine can eliminate partitions, and it can only eliminate them when the query constrains the partition key with a usable predicate. So the key must be a column that is present, as a literal or a parameter, in the predicates of your high-volume queries. Two traps here. First, choosing a column the application does not carry: if a query knows `order_id` but the table is partitioned on `customer_id`, that query reads every partition. Second, choosing a column that queries constrain only through a function or a join — the engine needs a direct comparison on the key to reason about bounds. When queries split between two axes (say, by tenant *and* by time), that is a signal to consider sub-partitioning rather than to compromise on one. ## 3. How big the pieces come out Granularity is a balance between two failure modes. *Too coarse*: yearly partitions on a table doing a billion rows a year means each partition is still enormous, its indexes still too large to cache, and retention still drops a whole year at a time — you have gained almost nothing. *Too fine*: hourly partitions on a five-year retention window is 43,800 child tables. Every query's planning must consider them (even with pruning, the planner and the executor pay per-relation costs), the system catalog grows, each partition needs its own indexes and statistics, locks multiply during DDL, and connections consume more memory holding relation metadata. Most practitioners keep the count in the tens to low hundreds, and treat a few thousand as the point where the overhead starts to hurt. A workable heuristic: pick the interval such that one partition's hot indexes fit comfortably in memory and a maintenance operation on a single partition completes in a time you would be willing to wait — often that lands on monthly or daily for time-series. ## What makes a key actively bad - **Not in the predicates.** The commonest mistake. It converts partitioning into pure overhead: more relations, more planning, more index objects, no elimination. - **Mutable.** If the partition key can be updated, an update that changes it must delete the row from one partition and insert it into another (engines do this for you, but it is a physical row move, it invalidates index entries, and it can fail if no partition accepts the new value). Choose keys that are set at insert and never change — timestamps of the event, tenant of the record. - **Skewed for RANGE or LIST.** If 80% of rows fall in one interval or one listed value, that partition is the table again and the rest are noise. - **Too many distinct values used directly.** Partitioning per customer id with 50,000 customers means 50,000 partitions. Use HASH on that column instead, or partition by a coarser attribute. - **Nullable without a plan.** A NULL key has no home under RANGE/LIST unless a default partition exists, and a default partition that collects everything unmatched can quietly become the biggest one. - **Excluded from the primary key.** Not allowed by the engine for good reason: uniqueness is enforced by per-partition indexes. ## Composite keys and sub-partitioning The partition key can be several columns. Under RANGE they are compared lexicographically, which is useful for `(tenant_id, occurred_at)` when tenants are few and large. More often the cleaner expression is a two-level layout: range by month, then hash or list within each month by tenant. Add the second level only when a real query or maintenance pattern demands it, because it multiplies the partition count. ## Deciding, in practice Write down your top ten queries and your retention rule before choosing. If retention is by time and most queries carry a time window, the answer is range on time at a granularity that yields tens to low hundreds of partitions, and you are done. If retention is unbounded and queries are point lookups by an identifier, hash on that identifier — but ask first whether you need partitioning at all, since a well-indexed table may serve those lookups fine. ## How to answer Give the three inputs in priority order (lifecycle, predicates, size), commit to a concrete key and granularity for a stated table, and name the failure modes: a key absent from predicates, a mutable key, and a partition count in the thousands.