The word partitioning carries so much baggage from distributed systems that the engine-level feature is often misunderstood before it is examined, so it is worth establishing precisely what it is, which is a mapping from one logical table onto several physical ones inside a single engine.

What the feature actually does

It declares that a table's rows are divided among several separate physical tables according to a rule applied to one or more of its columns, while queries continue to address one name.

Starting from the mechanism is right because the feature is simpler than its reputation. Several tables, one name, and a rule deciding which row goes where.

What stays the same

Each physical piece is an ordinary table with its own pages, its own free space and its own indexes, built from the same structures as any other table.

Establishing this early matters because it bounds how much there is to learn. The pieces are not a new kind of object.

What the rule looks like

Usually a range of values, an explicit list of values, or a hash of a column, with every row's destination determined entirely by its own column values.

Giving the three forms is necessary because the choice between them governs everything later. The rule is also the entire interface to the feature.

Why the destination must depend only on the row

The engine has to know where to put a row from the row alone, and where to look for one from the query's conditions alone, which rules out any rule needing other data.

Explaining the constraint is useful because it excludes tempting ideas. A rule depending on a count or on another table cannot work.

What it is not

It is not distribution across machines, because every piece lives in the same engine, uses the same buffer pool and participates in the same transactions.

Drawing the distinction explicitly is necessary because the confusion is so widespread. The feature does nothing about capacity beyond one machine.

Why that distinction keeps mattering

Nothing about this feature improves the throughput ceiling of the server, so a table partitioned for performance on an overloaded machine remains on an overloaded machine.

Reinforcing the point is worth the space because the mistake is expensive. The feature solves specific problems and this is not one of them.

Why it is nonetheless not merely cosmetic

Having the rows in separate physical tables makes operations possible that are not possible on one table, which is where the real value lies.

Setting up the positive case matters after two paragraphs of denial. The feature earns its place through operations rather than through speed.

What the engine stores about the arrangement

A description of the rule and the bounds of each piece, held as metadata the planner consults before deciding which pieces a query needs to touch.

Introducing the metadata is necessary because the planner's use of it is the main read benefit. The bounds are what allow pieces to be skipped.

Why the bounds must be known rather than discovered

The planner must be able to exclude a piece without reading it, which requires the bounds to be declared in the metadata rather than inferred from its contents.

Explaining this is useful because it is why the rule is declarative. A rule the engine can only evaluate by looking is no use for planning.

Why each piece has its own indexes

An index spanning all pieces would defeat the point, because detaching one piece would require rewriting the shared index, so indexes are created per piece.

Covering the index arrangement matters because it has many consequences. Per-piece indexes are what make the cheap operations cheap.

What a declared index on the whole table means

A declaration that every piece should carry an index of that shape, which the engine creates and maintains separately on each, with the declaration as bookkeeping.

Clarifying the abstraction is useful. The whole-table index is a convenience over per-piece indexes rather than a structure of its own.

Why that makes the index smaller per piece

Each index covers only its own piece's rows, so it is shallower and more of it stays resident, which can make a lookup cheaper even without any pruning.

Noting the incidental benefit is worthwhile. A shallower tree is a real if modest gain.

Why that benefit is usually small

Tree height grows so slowly with size that splitting one index into ten rarely removes a level, so the saving is often nothing at all.

Being honest about the size of the gain is important. The shallower index is a real effect and rarely the reason to partition.

How a row is routed on insert

The rule is evaluated against the incoming row, the destination piece is identified from the metadata, and the insert proceeds as an ordinary insert into that table.

Covering the write path is necessary for completeness. Routing is a cheap computation and the write itself is unchanged.

What happens when no piece accepts a row

The insert fails, because there is nowhere for the row to go, unless a catch-all piece has been declared to receive everything the rule does not place.

Marking this is essential because it is the feature's most common operational failure. A missing piece turns inserts into errors.

Why a catch-all is a mixed blessing

It prevents the failure and accumulates rows that belong elsewhere, so it grows without bound and cannot be pruned usefully by any condition.

Covering the trade is practical. The safety net has a cost that accrues quietly.

Why updates can move a row between pieces

Changing a column the rule depends on changes where the row belongs, so the engine must remove it from one piece and insert it into another.

Introducing row movement is necessary because it is surprisingly expensive. An update becomes a delete and an insert.

Why that is more expensive than it looks

Every index on both pieces must be updated, the transaction touches two physical tables, and some engines refuse the update rather than performing the move.

Explaining the cost is useful because it affects key choice. A rule over a column that changes is a poor rule.

Why the arrangement is hard to change later

Changing the rule means redistributing every row, which is a rewrite of the whole table, so the choice is close to permanent once there is data.

Warning about permanence is important. The decision should be made with more care than most schema decisions.

Why that argues for simplicity

A simple rule over a stable column with a granularity matching how the data is retained will usually outlast a clever rule designed around current query shapes.

Giving the design posture is the right close to the mechanics. Durability matters more than optimality here.

Why the feature is worth learning despite the caveats

The operations it enables are ones there is otherwise no good way to perform, and the data patterns it suits are extremely common.

Ending on the positive case is correct. The caveats are real and the feature is genuinely valuable where it fits.

What to take from the mechanism

Three things. Partitioning maps one logical table onto several ordinary physical tables by a declarative rule over the row's own columns, with the bounds held as metadata so the planner can exclude a piece without reading it, and with indexes created per piece rather than spanning the whole. It is not distribution: every piece shares one engine, one buffer pool and one transaction scope, so it does nothing for a server's throughput ceiling. And the arrangement is close to permanent, because changing the rule rewrites every row, which argues for a simple rule over a stable column at a granularity matching retention.