As a table grows into hundreds of millions of rows, even well-indexed operations slow down: indexes get huge, vacuum takes longer, and dropping old data means an expensive DELETE. Partitioning splits one large logical table into many smaller physical tables, called partitions, while queries still treat it as a single table. Done well, it keeps a very large dataset manageable and fast.
PostgreSQL supports declarative partitioning: you define a parent table partitioned by a key, then create child partitions that each hold a slice of the data. The planner uses partition pruning to skip partitions that cannot contain matching rows, so a query for last week's data touches only the relevant partitions instead of the whole table.
There are three strategies. Range partitioning splits by value ranges, classically by date — perfect for time-series data. List partitioning splits by discrete values, such as region or tenant. Hash partitioning distributes rows evenly across a fixed number of partitions by a hash of the key, useful for spreading load when there is no natural range or list. Choosing the right strategy depends entirely on how the data is queried and aged.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.