Partitioning

Hook: Interviewers love this question because partitioning can make a giant table feel tiny — but only if you split it the right way.

Question: What is partitioning in SQL, and when would you use it?

Answer: Partitioning means splitting one logical table into smaller physical pieces called partitions. The application still queries one table, but the database can read only the relevant pieces when the query matches the partition key, such as a date range or region. It is most useful for very large tables that are queried, archived, or maintained in slices.

Interview-Ready Answer: I’d say partitioning is a way to split one large table into smaller physical chunks while keeping one logical table for the application. I use it when access is naturally limited by a column like date, tenant, or region, because the optimizer can prune partitions and scan much less data. It helps with read performance, maintenance, and archiving, but it is not a replacement for indexes, and the partition key has to match real query patterns.

What partitioning is

Partitioning is a storage and planning technique. A partition key is the column the database uses to decide where each row lives. A row with created_at = 2024-01-15 may go into a January partition, while a March row goes elsewhere. The big win is partition pruning — pruning means the optimizer skips partitions that cannot possibly match the query.

How it works under the hood

1. You define a parent table and choose a partitioning method, usually RANGE, LIST, or HASH.
2. Rows are routed into a partition when you insert them. The database checks the partition key and sends the row to the matching child table.
3. When you query with a filter on the partition key, the planner removes irrelevant partitions before scanning data.
4. Each partition can have its own indexes, statistics, and maintenance work like VACUUM or archival.
5. If your query does not filter on the partition key, the database may need to scan many or all partitions, so the benefit drops fast.

Common partitioning styles

Partitioning vs other tools

When and why to use it

Use partitioning when the table is large enough that scanning, vacuuming, or archiving is painful, and when most queries naturally target one slice of the data. A common pattern is monthly partitions for events or orders. If a table is only a few million rows, a good index is usually simpler and faster to operate.

Performance notes and edge cases

• Partition pruning can turn a scan of millions of rows into a scan of one partition, which can change a query from seconds to milliseconds.
• Too many tiny partitions hurt planning and maintenance; in practice, dozens or low hundreds is often comfortable, while thousands need careful testing.
• Partitioning does not remove the need for indexes. You still index the columns used inside each partition.
• Some databases require the partition key to be part of a unique constraint; in PostgreSQL, a unique or primary key on a partitioned table must include the partition key columns.
• Updating the partition key can move a row to another partition, which is more expensive than a simple in-place update.

Memory model: one front door, many drawers. The application opens one table, but the database only pulls the drawer that matches the key.

Real-World Story: Imagine a checkout service storing every payment event for three years. The team partitions by month on created_at, so yesterday’s fraud checks hit only the current partition and old data can be archived by detaching a whole month. One outage happened when a developer wrote WHERE DATE(created_at) = ... instead of a plain range filter; the function hid the partition key from the planner, partition pruning stopped working, and the query began scanning every partition. The symptoms were high CPU, many sequential scans in EXPLAIN, slow dashboard loads, and angry users seeing timeouts during peak traffic.

Follow-up & Tricky Questions:

• How is partitioning different from indexing? Indexing helps find rows inside a table; partitioning reduces how much of the table the database has to touch. In practice, you often need both: partitioning for coarse skipping, indexes for fast lookup inside each partition.
• What is partition pruning? It is the planner’s ability to ignore partitions that cannot match the query. If your filter uses the partition key directly, pruning can be dramatic; if you wrap the key in a function, pruning may disappear.
• When would you choose RANGE vs LIST vs HASH? RANGE is best for ordered values like time, LIST is best when the value comes from a small set like region or status, and HASH is useful when you mainly want even distribution rather than human-friendly grouping.
• Can partitioning help writes? Sometimes indirectly, because smaller partitions are easier to maintain, vacuum, and archive. But the insert path can be a little more expensive because the database must route each row to the right partition.
• How many partitions are too many? There is no magic number, but hundreds are often manageable and thousands need testing. The real cost shows up in planning time, metadata, and operational complexity.
• Can you put a unique constraint on a partitioned table? Yes, but in PostgreSQL the unique or primary key must include the partition key columns. That surprises many candidates because uniqueness is checked per partition unless the key is designed to span the partitioning rule.
• Does partitioning replace sharding? No. Partitioning keeps data inside one database and one logical table; sharding splits data across multiple servers. Sharding is about horizontal scale-out, while partitioning is about managing large tables more efficiently.
• Tricky: Will WHERE DATE(created_at) = '2024-01-10' always prune partitions? Not reliably. A function around the partition key can prevent pruning because the planner cannot use the raw key range directly, so the safer pattern is an explicit half-open range on the key.
• Tricky: Is a partitioned table automatically faster? No. If the table is not large enough, or if most queries do not filter by the partition key, partitioning can add overhead without giving much back.

Common Mistakes:

• Choosing the wrong key: partitioning by a column that rarely appears in queries gives little benefit; pick the column that matches real filters.
• Expecting partitioning to replace indexes: partitioning skips chunks, but it does not make row lookups free; keep useful indexes on partitions.
• Making too many partitions: tiny partitions increase planning and maintenance overhead; aim for a shape that is easy to manage, not just easy to explain.
• Hiding the key in functions: expressions like DATE(created_at) can block pruning; use plain range predicates on the partition column.

Memory Hook: One table, many drawers. Partitioning is a filing cabinet: the label on the drawer matters, and the librarian only saves time if your request includes that label.

Cheat Sheet:

• Partitioning splits one logical table into smaller physical pieces.
• Best for huge tables with natural slices like time, tenant, or region.
• Pruning is the big win: the planner skips irrelevant partitions.
• Partitioning helps maintenance and archiving, not just query speed.
• Still use indexes inside partitions.
• Too many partitions or the wrong key can make things worse.

Practice Tasks:

• Create a small monthly partitioned log table and verify which partition each row lands in.
• Run the same query with and without a filter on the partition key and compare the plan.
• Add a default partition, then insert an out-of-range row and observe how it is handled.