A relational engine lets you declare one logical table as a set of physical child tables using RANGE, LIST or HASH. Explain what table partitioning is and what each of the three strategies is for.

## What partitioning is A partitioned table is one logical table whose rows are physically stored in several separate child tables, called partitions. You declare the split when you create the table by naming a **partition key** — one or more columns — and a **strategy** that says how key values map to partitions. From then on, an insert into the parent is automatically routed to the partition whose bounds accept its key, and a query against the parent transparently reads whichever partitions can contain matching rows. Crucially this all happens inside **one database server**. The partitions share the same process, the same buffer pool, the same write-ahead log, the same CPU. Partitioning reorganises storage and metadata; it does not add hardware. That distinction — partitioning is within a server, sharding is across servers — is the single most common confusion on this topic. ## RANGE partitioning Each partition owns a contiguous, non-overlapping interval of the key, checked as lower bound inclusive, upper bound exclusive. ```sql CREATE TABLE events ( id bigint, occurred_at timestamptz NOT NULL, payload jsonb ) PARTITION BY RANGE (occurred_at); CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'); ``` This is the dominant real-world case, because most enormous tables are event-shaped: rows are inserted with a timestamp, queried by a time window, and eventually discarded by age. Range partitioning by time aligns all three. Inserts concentrate in the newest partition, whose indexes stay small and hot in cache; window queries touch a handful of partitions; and expiring old data becomes a metadata operation on a whole partition rather than a mass delete. Range also works on numeric keys (id blocks) and on multi-column keys, where the comparison is lexicographic across the listed columns. ## LIST partitioning Each partition enumerates the key values it accepts. ```sql CREATE TABLE accounts (id bigint, region text NOT NULL) PARTITION BY LIST (region); CREATE TABLE accounts_eu PARTITION OF accounts FOR VALUES IN ('DE','FR','ES'); CREATE TABLE accounts_us PARTITION OF accounts FOR VALUES IN ('US','CA'); ``` Use LIST when the key is categorical, the set of values is small and stable, and the categories genuinely matter to how you query or manage the data — for example keeping one region's rows on separate storage, or isolating a category you archive independently. Its weakness is exactly its explicitness: a new value that no partition lists is rejected (or lands in a default partition if you declared one), so LIST needs governance whenever the domain can grow. ## HASH partitioning ```sql CREATE TABLE sessions (user_id bigint NOT NULL, ...) PARTITION BY HASH (user_id); CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0); -- ... p1..p7 ``` Hash gives you N roughly equal pieces without any semantic meaning. Choose it when the table is too big to manage comfortably but has no natural time or category axis — a large user-keyed table, for instance. The benefits are narrower than range: index maintenance and vacuum/statistics work per partition, and equality lookups on the key prune to one partition. What you do *not* get is the ability to drop a partition to expire data (no partition corresponds to "old"), or cheap range pruning, and changing the partition count means rewriting the table. ## Choosing the strategy Derive it from three things, in this order: 1. **How you delete.** If data expires by age, range on time, full stop — this benefit usually dwarfs the others. 2. **How you query.** The key must appear in the predicates of your important queries; otherwise the engine reads every partition and you have added overhead without benefit. 3. **How data is shaped.** Continuous ordered value → RANGE. Small fixed category set → LIST. Neither, just too big → HASH. The key must be part of the primary key and of every unique constraint on the table, because the engine can only enforce uniqueness within a partition's local index. ## What partitioning is *not* It is not a performance button. A well-indexed 50-million-row table on decent hardware does not need it and will typically get slower with it, since the planner now has more relations to consider and every query that does not carry the key touches every partition. It also does not increase write throughput — same server, same WAL. And it does not replace indexes: each partition still needs its own indexes for lookups within it. ## How to answer Define the parent/child model and the partition key, give the three strategies with a one-line canonical use for each (range = time, list = category, hash = even split of a big table), and volunteer the boundary: this is one server, unlike sharding, and the key must be in your queries for it to pay off.