Partitioning vs Indexing: Two Layers, Two Problems, One Common Confusion

Partitioning and indexing get confused because both make queries faster, but they solve different problems and live at different layers.

Partitioning is a physical decision. It splits a logical table into smaller chunks so a query can ignore irrelevant chunks entirely. The goal is to bound the working set: instead of scanning two years of data, scan one month. The unit of optimization is the partition.

Indexing is an access-path decision. Given the rows you have to look at, an index turns a sequential scan into a targeted lookup. The mechanics of B+ trees and how they enable point and range lookups are covered in database-indexes-how-they-work. The unit of optimization is the row.

You usually need both. A common shape is a logs table partitioned by month with a B+ tree index on request_id inside each partition. The partition bounds the scan to one month, the index finds the specific row, and the query finishes in milliseconds.

The complication is that an index on a partitioned table can be local or global. A local index is one B+ tree per partition. Each tree only knows about rows in its own partition. Cheap to maintain, easy to drop along with the partition. The cost is that lookups not constrained by the partition key have to consult every local index.

A global index is one B+ tree spanning all partitions. Lookups that ignore the partition key stay cheap because there is only one tree to traverse. The cost is maintenance: every write has to update the global index, and dropping a partition leaves orphan index entries that have to be cleaned up asynchronously.

The production failure I watched: a team partitioned an orders table by month for retention purposes. Their primary lookup was WHERE order_id = ? from the order detail page. After the migration, that page got slower, not faster. The reason was that order_id had been the primary key on the unpartitioned table, but the partition migration silently made the index local. Every order detail lookup now scanned 24 local indexes, one per partition, because the planner could not narrow the partition set from order_id alone.

The fix had two viable paths. Path one: add a global secondary index on order_id. The detail page lookup returned to single-digit milliseconds, at the cost of a heavier write path. Path two: change the partition key to align with the dominant lookup. Partitioning by order_id hash instead of month would have made the lookup fast, at the cost of losing cheap monthly retention drops.

The team picked path one because retention was load-bearing. The lesson: a partitioned table is not automatically a fast table. Decide which queries get the partition key and which get a global index, and accept the maintenance cost of whatever you choose.