When you create an index on a partitioned table, what is physically created, and how does that differ from indexing an ordinary non-partitioned table?
answer
- one index per partition, parent = metadata
- local / aligned / partitioned index
- no pruning ⇒ N probes
- new partitions inherit the declared index
- uniqueness only within a partition
basics
~20 sOn a non-partitioned table you get one physical index over all rows. On a partitioned table, most engines build one physical index per partition, each covering only that partition's rows; the index you declared on the parent is just metadata tying them together.
solid answer
~50 sA partitioned table is one logical table made of many physical partitions. When I declare an index on the parent, the engine normally creates a **partition-local index**: a separate physical B-tree on every partition, each indexing only that partition's rows. The parent-level index object is metadata that groups them and makes new partitions inherit the index automatically. Consequences: each index is smaller and shallower, so it caches better and can be built or rebuilt per partition. But a lookup that cannot be pruned to one partition has to probe *every* partition's index and merge the results — N index probes instead of one. That is why predicates that include the partition key matter so much. It also means index-enforced uniqueness is only enforced *within* a partition, which is why unique keys must contain the partition key in engines that only support local indexes (PostgreSQL, MySQL). Oracle and SQL Server can additionally build a global/nonaligned index that spans all partitions.
code
sql · 15 linesCREATE TABLE events (
id bigint,
tenant_id bigint,
created_at date NOT NULL,
email text
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- declares one index object on the parent;
-- builds events_2026_01_email_idx and events_2026_02_email_idx
CREATE INDEX ON events (email);go deeper
Know that the engine builds one index per partition and that the parent index is a declaration new partitions inherit.
Add the consequences: smaller trees, per-partition maintenance, and N probes when the query cannot be pruned to one partition.
Connect it to real behaviour — regressed secondary-column lookups after partitioning, index count explosion, and the fact that uniqueness is per-partition.
Frame it as the core tradeoff of partitioning: you trade global structures for independent ones, buying lifecycle and maintenance locality at the cost of cross-partition access paths, and you choose the partition key so the hot access paths stay prunable.
## The shape of a partitioned table A partitioned table is a single logical table whose rows are physically stored in several separate structures called partitions, chosen by a partition key (a range of dates, a list of regions, a hash of an id). Queries and DML address the parent name; the engine routes rows to the right partition. ## What an index declaration produces On an ordinary table, `CREATE INDEX` produces exactly one physical structure — usually a B-tree — whose leaf entries point at every row in the table. On a partitioned table, the mainstream behaviour is a **local index** (Oracle's word), also called a **partitioned index**, an **aligned index** (SQL Server), or simply the default in PostgreSQL and MySQL: - One physical index is built per partition. - Each per-partition index contains entries only for the rows in that partition. - The index object you declared on the parent holds no data. It is a catalog entry that (a) names the set, (b) makes any partition attached or created later get its own matching index, and (c) reports the index as valid once every partition has its piece. So a table with 200 partitions and 3 declared indexes has 600 physical indexes plus 200 heaps. ## Why that is often good - **Smaller trees.** A B-tree over 5 million rows is shallower than one over 1 billion. Fewer levels means fewer random I/Os per probe and a much better chance the upper levels stay in cache. - **Bounded maintenance.** You can build, rebuild, reindex or drop the index for one partition without touching the rest, and you can do several partitions in parallel. - **Cheap partition lifecycle.** Detaching a partition takes its indexes with it as a metadata operation; nothing has to be deleted out of a shared structure. Attaching a partition that already carries matching indexes is likewise near-instant. - **Selective indexing.** You can index only the partitions that actually get queried — for example, skip an expensive index on ten-year-old archive partitions. ## Why it can hurt The cost lands on queries that cannot be narrowed to a small number of partitions. If a query filters only on `email` and the table is partitioned by `created_month`, the engine must probe the `email` index of every partition and combine the results. Each probe is cheap, but 200 of them is not; and if the query wants sorted output or a `LIMIT`, the engine has to merge or sort across partitions rather than walk one ordered structure. On a non-partitioned table that same lookup was one probe. This is the single most common surprise after partitioning: point lookups on a secondary column get *slower*, not faster, because the work is now proportional to partition count. The remedies are to include the partition key in the predicate so the planner can prune, to partition on something the hot queries actually filter on, or (on engines that support it) to build a global index on that column. ## Uniqueness follows from the same fact Because each per-partition index only sees its own partition's rows, an index-enforced unique constraint is only unique *within a partition*. Two rows with the same value can sit in different partitions and neither insert will see the other. That is why PostgreSQL and MySQL require every column of the partition key to appear in a `PRIMARY KEY` or `UNIQUE` constraint on a partitioned table — it guarantees any given key value can only ever land in one partition, so local enforcement equals global enforcement. ## Engines that offer an alternative Oracle supports **global indexes**: one index (optionally partitioned on a different key) spanning all partitions. SQL Server calls the equivalent a **nonaligned index**. These restore single-probe lookups and global uniqueness, but they re-couple the partitions: dropping, truncating or exchanging a partition invalidates or forces maintenance of the global index, which destroys the cheap-lifecycle benefit unless you explicitly ask for the index to be updated during the operation. PostgreSQL and MySQL have no global indexes at all. ## What to say in an interview "Declaring an index on a partitioned table normally creates one index per partition; the parent index is metadata. That makes each index small and makes partition attach/detach and per-partition rebuilds cheap, but any lookup that isn't pruned costs one probe per partition, and uniqueness is only enforced per partition — which is why the partition key has to be part of any unique key."
- If a query filters only on an indexed column that is not the partition key, what does the engine do and how does the cost scale?It cannot prune, so it opens the local index on every partition, probes each one, and appends or merges the results. Cost grows roughly linearly with partition count, so a lookup that was one index probe becomes N probes plus a merge. If the query also needs ordering or a small LIMIT, the engine may have to gather rows from all partitions before it can answer, which is far worse than a single ordered walk.
- What happens to indexes when you attach or detach a partition?Local indexes belong to the partition, so detach is a metadata operation that takes the indexes with it. On attach, the incoming table must already have an index matching each parent index, otherwise the engine builds one while holding a lock. That is why the standard pattern is to load a standalone table, build all its indexes and constraints offline, and only then attach it.
It is the difference between one giant card catalogue for the whole library and a small catalogue drawer bolted to each room. The drawers are quicker to search and easy to wheel in or out, but if you don't know which room the book is in you must open every drawer.
saying these in an interview costs you the question
- Saying an index on a partitioned table is one big B-tree over all partitions in every engine.
- Assuming partitioning automatically makes every indexed lookup faster.
- Forgetting that without pruning the engine probes every partition's index.
- Believing a UNIQUE index on a partitioned table enforces uniqueness across the whole table by default.
- Thinking you must create the index separately on each new partition — the parent declaration is inherited.