Partitioning splits a table into separate chunks by a column value so queries can skip whole chunks. An index is an extra lookup structure that helps find specific rows fast. One removes data from consideration, the other points to the data.
How each works
A table events partitioned by event_date stores each day in its own piece. A query with WHERE event_date = '2025-03-01' reads one day and ignores the rest. The skipping is cheap because it uses metadata, not the data.
An index on events(user_id) is a sorted structure (a B-tree in most OLTP databases) that maps each user_id to row locations. A query for one user reads a few index pages and then a few rows, without scanning the table.
Which environment uses what
- OLTP databases (Postgres, MySQL, SQL Server): rely on B-tree indexes, because queries fetch a few rows by key and the data is stored in rows.
- Analytical warehouses (BigQuery, Snowflake, Redshift, Databricks): scan columns in bulk and rely on partitioning plus clustering (sorting data inside partitions so min/max statistics prune more). Most do not offer ordinary B-tree indexes.
Combining them
Partition by the column nearly every query filters on, usually a date. Cluster, or index, by the columns used for selective filters and joins inside it, such as customer_id.
Mistakes
- Partitioning by a high-cardinality column such as
user_idcreates millions of tiny partitions. Metadata grows, small files appear, and each query touches too many pieces. Pick a column with limited distinct values and a clear filter pattern. - Some engines have hard limits on the number of partitions, so check yours.
- Assuming partitioning helps a query that does not filter on the partition column. It will not. It might even make it slower.
A one-line answer: partitioning cuts how much data you look at, indexing cuts how long it takes to find a row within it.