← Back

Indexing vs Partitioning vs Sharding: A Problem-First Walkthrough

A progressive, problem-first guide to indexing, partitioning, and sharding in PostgreSQL — each technique introduced as the solution to a problem the previous one leaves behind, plus the new problems each one creates, when to combine them, and when to avoid them entirely.

Published on 16 jul 2026

Most engineers learn indexing, partitioning, and sharding as three separate topics. That makes it hard to see why all three exist. This guide takes the opposite approach: start with one problem, solve it, then show the next problem that solution can't solve, and bring in the next technique. By the end, you'll see indexing, partitioning, and sharding as one continuous story, not three competing tools.

The core question people ask: "If indexing makes queries fast, why do we need partitioning? And if partitioning makes queries fast, why do we need sharding?" The answer: each one solves a problem the previous one leaves behind.

Problem 1 — Finding a row is slow

You have a users table with 100 million rows:

SELECT *
FROM users
WHERE email = 'abc@gmail.com';

Without any help, PostgreSQL has to check rows one at a time:

100 Million Rows
↓
Check row 1
↓
Check row 2
↓
...
↓
Check row 100 Million

This takes something like 10 seconds — unacceptable for a login page.

Solution: Indexing

An index is like a library catalog: instead of walking every shelf, you look up the book number and go straight to it.

B-Tree
↓
abc@gmail.com
↓
Pointer
↓
Actual Row

Now the same query takes about 5 ms.

What indexing solves: fast lookups, fast sorting, fast joins, fast filtering — on columns it covers.

The cost: every INSERT must also update the index:

INSERT → Write Row → Update Index → Commit

More indexes on a table means more work per write. Five indexes = five updates per insert = slower writes.

Problem 2 — Indexing can't reduce how much data exists

Indexing found rows fast, so problem solved... until the table grows to orders with 100 million rows spanning 2020–2025, and:

• Backups take hours because the whole table must be read.
• VACUUM and maintenance operations crawl.
• Deleting old data (DELETE FROM orders WHERE order_date < '2023-01-01') locks the table and generates a huge amount of WAL.
• Even indexed queries that return many matching rows still have to read all of those rows — the index only found where they are, it didn't shrink how many there are.

An index can point you to matching rows instantly, but it cannot shrink the table itself. If a query matches 2 million rows, PostgreSQL still reads 2 million rows, index or not.

Solution: Partitioning

Split the table by a column that most queries filter on — usually a date:

Orders
↓
2020
2021
2022
2023
2024
2025

Now a query like:

WHERE order_date = '2025-07-01'

doesn't scan 100 million rows — PostgreSQL prunes irrelevant partitions and scans only the July 2025 partition (maybe 1 million rows). This is Partition Pruning.

What partitioning solves: smaller indexes per partition, faster backups, faster maintenance, instant archival (DROP TABLE orders_2020_01 instead of a slow DELETE), and faster date-range queries.

The catch: partitioning only helps if the partition key matches how you actually query. Partition by gender while querying WHERE customer_name = 'John', and partitioning gives you nothing — every partition still has to be scanned.

Problem 3 — Partitioning alone can't find a specific row inside a partition

Partitioning solved the "which chunk of data do I need" problem. But now:

SELECT *
FROM orders
WHERE customer_email = 'abc@gmail.com';

PostgreSQL knows this query has no date filter, so it may need to check every partition anyway. And even if you did filter by date, say WHERE order_date = '2025-07-15' AND customer_email = 'abc@gmail.com', the July partition alone might still have 5 million rows. Partitioning gets you to the right room in the library, but inside that room, you're back to a linear search unless something helps you find the exact shelf.

July Partition
↓
Scan 5 Million Rows   ← partitioning alone stops here

Solution: Combine Indexing + Partitioning

Add a local index on customer_email inside each partition:

July Index
↓
Directly find row

Now the query benefits from both techniques at once:

• Partitioning narrows the search to one partition (fewer rows to consider).
• Indexing narrows the search within that partition (no linear scan).
• Bonus: because the index only covers one partition's worth of data, it's much smaller than one giant index over the whole table — so it's also faster to use and cheaper to maintain.

This is the key insight: indexing and partitioning are not alternatives — they complement each other. Partitioning decides which partitions to search; indexing decides which rows inside those partitions to read.

Problem 4 — Indexing + Partitioning can't fix a server that's run out of hardware

Your orders table is now partitioned by month, every partition has a solid local index, and queries are fast. But the business keeps growing, and now you have a users table with 5 billion rows. No matter how well you partition or index it:

• The data still all lives on one PostgreSQL server.
• That one server has a fixed amount of CPU, RAM, disk, and network bandwidth.
• Write throughput is capped by that single machine — you can't add more partitions or indexes to get more raw capacity.
• Vertical scaling (bigger hardware) has diminishing returns and a hard ceiling.

Neither indexing nor partitioning can add more machines to the picture — they both operate within a single server.

Solution: Sharding

Split the data across multiple independent PostgreSQL servers:

Shard 1   1.25 Billion rows
Shard 2   1.25 Billion rows
Shard 3   1.25 Billion rows
Shard 4   1.25 Billion rows

Now CPU, RAM, disk, and network load are distributed across four machines instead of one.

What sharding solves: horizontal scaling, more total storage, more total throughput, and the ability to scale shards independently.

The cost: cross-shard joins and transactions become expensive or impossible to do atomically, routing logic becomes more complex (the application, or a proxy, must know which shard to query), and resharding — moving data when you add a new shard — is genuinely hard (this is exactly the problem consistent hashing is designed to reduce).

Problem 5 — Sharding alone doesn't make each shard fast internally

You've sharded users into 4 servers. Each shard now holds "only" 1.25 billion rows — still enormous. Sharding solved the distribution problem, but each individual shard still has:

• No partition pruning — a query on shard_2 for a date range still scans everything on that shard unless it's partitioned.
• No fast lookups — a query for a specific email still does a full scan of 1.25 billion rows on that shard unless it's indexed.

Sharding moves the "too much data on one server" problem down to "still too much data on one shard," which is the exact same problem Problems 1–3 already solved — just recursively, one level down.

Solution: Partitioning + Indexing, applied inside every shard

Shard 1
↓
Monthly Partitions
↓
Indexes

Every shard gets partitioned the same way a single-server database would be, and every partition gets its own indexes. The three techniques stack:

• Sharding decides which server holds the data.
• Partitioning decides which chunk on that server holds the data.
• Indexing decides which row inside that chunk is the one you want.

Different Problems, Different Combinations

Not every system needs all three — the right combination depends on which problem you actually have.

Notice each case starts from a different bottleneck:

• Amazon and banking care most about precise lookups at scale → indexing does the heavy lifting inside each partition.
• Uber cares about very high write volume concentrated by city → sharding is the primary lever, with daily (not monthly) partitions because trip volume per city is so high.
• PostHog/ClickHouse cares about aggregation over huge time-ordered datasets, not point lookups → partitioning plus columnar storage, without necessarily needing to shard.

The lesson: identify your actual bottleneck first (lookup speed? scan size? single-server capacity?) and reach for the technique that targets it — not all three by default.

The Library Analogy, End to End

1. Index = Book Catalog
   100 Million Books → Catalog → Book #456789 → Go directly to shelf
   Helps you find a book quickly. Does NOT reduce the number of books.

2. Partition = Split Library into Rooms
   Library → Room 2023 / Room 2024 / Room 2025
   Need a 2025 book? Go directly to Room 2025 — skip the other rooms.
   Still one library, one librarian.

3. Sharding = Multiple Libraries
   Library Hyderabad / Library Bangalore / Library Chennai
   Each library stores part of the books — the workload is now
   distributed across multiple buildings entirely.

               DATABASE
                  │
      ┌───────────┼────────────┐
      │           │            │
   Index      Partition     Sharding
 Find Fast    Read Less     Scale Out

Comparison Table

Production Evolution

A typical application's database architecture grows through these stages, adopting each technique exactly when the previous one stops being enough:

Stage 1 (Small Startup)
PostgreSQL → Index

Stage 2 (Millions of Rows)
PostgreSQL → Index → Partitioning

Stage 3 (Hundreds of Millions / Billions of Rows)
Shard 1 / Shard 2 / Shard 3 → each shard: Partitioning → Indexes

Rule of Thumb

The key takeaway: these are not competing technologies chosen instead of one another — in real production systems they're layered together, each solving the specific problem the others leave behind:

Sharding distributes data across multiple servers. Partitioning organizes large tables within each server. Indexing makes lookups inside each partition efficient.

The New Problems Each Solution Introduces

Every technique above fixed a problem — but every technique also creates a new problem of its own. A complete mental model includes both halves. This section walks through what breaks, for each technique alone and then for each combination, and how production systems work around it.

1. Indexing alone — new problems

Problem: Slower writes. Every INSERT, UPDATE, or DELETE has to update every index on the table. A table with 6 indexes pays that cost 6 times per write.

INSERT → write row → update index 1 → update index 2 → ... → update index 6 → commit

Alternative fixes (not partitioning/sharding):

• Drop unused indexes — audit with pg_stat_user_indexes and remove ones with near-zero scans.
• Use partial indexes (CREATE INDEX ... WHERE status = 'active') so only relevant rows are indexed, shrinking both write cost and index size.
• Batch writes and rebuild indexes afterward for large bulk loads (DROP INDEX → bulk COPY → CREATE INDEX) instead of paying the per-row index-update cost during ingestion.

Problem: Index bloat. Frequent updates/deletes leave dead index entries behind; the index grows larger than the data it indexes and read performance degrades over time.

Alternative fix: periodic REINDEX CONCURRENTLY, tuning autovacuum, or switching to HOT update-friendly table layouts (fewer indexed columns get updated).

Problem: Wrong or unused indexes silently cost you. An index that doesn't match the query's WHERE/ORDER BY pattern (wrong column order in a composite index, missing expression index for a function call, etc.) is dead weight — all the write cost, none of the read benefit.

Alternative fix: review query plans (EXPLAIN ANALYZE) and match index column order to actual filter/sort patterns, rather than reflexively indexing every column.

2. Partitioning alone — new problems

Problem: Cross-partition queries get slower, not faster, if the partition key is wrong or absent from the query. As noted above, a query without the partition key in its WHERE clause forces PostgreSQL to check every partition — and with many partitions, the planning overhead itself (deciding which partitions apply) can exceed the savings.

Alternative fix: choose the partition key based on your actual dominant query pattern, not the most "obvious" column. If two different query patterns need two different partition keys, consider sub-partitioning (partition by date, then list-partition each date partition by tenant, for example) instead of picking one key and hoping.

Problem: Uneven partition sizes ("hot partitions"). If one month has 10x the orders of another (holiday season spikes, for example), that partition becomes a mini version of the original "huge table" problem, while others sit nearly empty.

Alternative fix: use finer-grained partitions during high-volume periods (weekly instead of monthly for a known busy season), or partition by a more evenly-distributed key (hash) instead of a naturally skewed one (calendar date).

Problem: Too many partitions increases planner overhead. Thousands of partitions (e.g. partitioning by minute) makes the query planner itself slow, since it has to reason about which of thousands of children to prune.

Alternative fix: keep partition counts in the tens to low hundreds; use coarser partitions (monthly, not per-minute) and let indexes handle fine-grained lookups within a partition instead of trying to partition down to a tiny grain.

Problem: Migrating an existing table into a partitioned one is disruptive. PostgreSQL can't ALTER TABLE ... PARTITION BY an existing table — you must build a new partitioned table, backfill, and cut over, which is risky on a live system.

Alternative fix: use logical replication or a dual-write/backfill-then-swap strategy to migrate with minimal downtime, rather than a blocking one-shot copy.

3. Sharding alone — new problems

Problem: Cross-shard joins and transactions. A query joining orders (on shard 2) with customers (on shard 4) can't be done as a single SQL join — the application has to fetch from both and merge in code, and there's no cross-shard ACID transaction.

Alternative fixes:

• Co-locate related data — shard both orders and customers by the same key (e.g. customer_id) so related rows always land on the same shard, making "joins" local again.
• Use a saga pattern (a sequence of local transactions with compensating actions) instead of a distributed transaction when an operation must span shards.
• Maintain a denormalized read model (e.g. via CDC into a search index or analytics store) for queries that inherently need to cross shard boundaries, rather than trying to force a live cross-shard join.

Problem: Hot shards. If sharding by a skewed key (e.g. one huge enterprise customer vs. thousands of small ones), one shard can receive disproportionate load while others are idle.

Alternative fix: use consistent hashing with virtual nodes to spread any single large key's neighbors thinly across many shards, or split a single oversized tenant onto its own dedicated shard as a special case.

Problem: Resharding pain. Adding a 5th shard to a modulo-sharded (user_id % 4) system moves ~80% of keys.

Alternative fix: this is precisely what consistent hashing was introduced to solve earlier in this series — adopt it from the start if you expect to add shards over time, rather than retrofitting it under pressure.

Problem: Operational complexity multiplies. Instead of one database to back up, monitor, patch, and upgrade, you now have N. Schema migrations must run identically across every shard.

Alternative fix: invest early in migration tooling that applies schema changes to all shards atomically (or safely in sequence), and centralize monitoring/alerting across shards rather than per-shard dashboards.

4. Indexing + Partitioning combined — new problems

Problem: Global uniqueness becomes hard to enforce. Because a unique constraint on a partitioned table must include the partition key, you can no longer have a simple globally-unique id primary key on its own — PRIMARY KEY (id, order_date) allows the same id to exist in two different partitions.

Alternative fix: generate IDs that are unique by construction (UUIDs, or a Snowflake-style ID generator that encodes a timestamp/shard component), rather than relying on the database alone to enforce global uniqueness.

Problem: Local indexes must be recreated on every new partition. Since indexes are per-partition, forgetting to add an index to a newly-created partition silently degrades performance for that slice of data (queries on last month look fine; queries on the new month are suddenly slow).

Alternative fix: automate partition creation with a template/migration script that always creates the accompanying indexes in the same transaction, rather than creating partitions and indexes as separate manual steps.

5. Partitioning + Sharding combined — new problems

Problem: Two-dimensional routing complexity. The application (or middleware) now must decide which shard, and then PostgreSQL decides which partition on that shard — debugging "why is this query slow" requires reasoning about both layers at once.

Alternative fix: keep the two dimensions using different, non-overlapping keys where possible (e.g. shard by tenant_id, partition by created_at) so each layer's routing logic stays independent and easier to reason about in isolation, and add tooling/logging that surfaces both the resolved shard and resolved partition for any given query during debugging.

Problem: Cross-shard analytics still require fan-out, now across partitioned tables. An aggregate query now fans out to every shard, and on each shard, potentially every partition — multiplying the fan-out cost.

Alternative fix: this is the same lesson as before — don't try to serve large aggregations from the OLTP cluster at all. Stream to an OLAP store (ClickHouse, Redshift, BigQuery) via CDC/Kafka instead of running fan-out aggregate queries live against every shard/partition combination.

6. Indexing + Sharding combined — new problems

Problem: No single global index. A query like "find the user with this email, regardless of which shard they're on" has no single index to consult — you must either query every shard (fan-out) or maintain a separate lookup structure.

Alternative fix: maintain a small global lookup table or cache (e.g. email → shard_id in Redis or a dedicated lookup database) that's updated on write, so reads can jump straight to the correct shard instead of fanning out.

7. All three combined (Sharding + Partitioning + Indexing) — new problems

Problem: Total system complexity and failure surface. You now have multiple servers, each with multiple partitions, each with multiple indexes — multiplying the number of moving parts that can fail, drift out of sync, or need coordinated schema changes.

Alternative fix: invest in infrastructure-as-code and automated tooling (migration frameworks that apply changes across every shard/partition consistently, centralized monitoring, and chaos/failure testing) rather than managing this topology by hand. In many cases, the honest alternative is to ask whether you need all three at all — see the next section.

When NOT to Use Indexing, Partitioning, or Sharding

Just as important as knowing how to apply these techniques is knowing when not to reach for them. Using the wrong tool adds complexity and cost without a matching benefit.

When NOT to add an index

• Small tables. A table with a few thousand rows is often faster to sequentially scan than to look up through an index — and PostgreSQL's planner usually knows this already.
• Columns rarely used in WHERE, JOIN, or ORDER BY. Indexing a column just because it "might be searched someday" adds write overhead for no realized benefit.
• Write-heavy, read-light tables (e.g. an audit/event log that's rarely queried but constantly appended to) — extra indexes slow down every write for reads that rarely happen.
• Low-cardinality columns (e.g. a boolean is_active flag) on their own — a plain B-Tree index on a column with only 2–3 distinct values often isn't selective enough to beat a sequential scan; a partial index scoped to the interesting subset is usually better than indexing the whole column.

When NOT to use partitioning

• Small or medium tables (well under a few million rows). Partitioning adds planning overhead and operational complexity that isn't repaid until a table is large enough that pruning meaningfully reduces work.
• No natural, query-aligned partition key. If your queries don't consistently filter on one dominant column, partitioning either doesn't help (you still scan every partition) or actively hurts (extra planning overhead for no pruning benefit).
• Tables that need frequent, flexible ad-hoc queries across arbitrary columns — analytics-style tables that are queried in unpredictable ways benefit more from a columnar OLAP store than from range/list/hash partitioning in an OLTP database.
• Tables under heavy schema churn. Partitioned tables make certain schema changes (especially changing the partition key itself) painful; a frequently-evolving schema is easier to manage unpartitioned until it stabilizes.

When NOT to shard

• The single-server ceiling hasn't actually been reached. If a bigger instance (more CPU/RAM/faster disks), read replicas, partitioning, and indexing haven't been tried or have already solved the problem, sharding is premature — it's the most expensive and hardest-to-reverse of the three techniques.
• Your workload needs strong cross-entity transactions or joins. If the application constantly needs atomic multi-entity transactions or ad-hoc joins across what would become shard boundaries, sharding will fight your data model at every turn; consider whether the real fix is better indexing, caching, or a different single-server scaling approach first.
• Team size and operational maturity don't support it yet. Sharding multiplies operational surface area (N databases to patch, monitor, and migrate). A small team without mature automation will likely spend more time fighting the infrastructure than building the product.
• The data or traffic doesn't actually require horizontal scale. Many applications never reach the hundreds-of-millions-to-billions-of-rows range where sharding pays for itself; reaching for it "to be safe" early on is a common over-engineering trap.

When NOT to combine all three

• When you haven't identified which specific problem you have. As shown throughout this guide, each technique targets a distinct symptom (slow lookup, too much data scanned, single-server ceiling). Applying all three without first confirming the corresponding problem exists just adds every technique's downsides with none of the matching upside.
• Early-stage products with uncertain access patterns. Partitioning and sharding both lock in assumptions about how data will be queried and grown. Committing to a partition key or shard key before your query patterns have stabilized often means an expensive migration later when those assumptions turn out to be wrong.
• When a simpler alternative solves the immediate problem just as well — e.g. a cache (Redis) in front of a hot read path, a read replica for read-scaling, or an OLAP store for analytics, can each solve what looks like a "we need to partition/shard" problem without touching the primary OLTP schema at all.

Rule of thumb for restraint: reach for indexing first, partitioning only once a table is large and has a clear dominant query filter, and sharding only once a single well-indexed, well-partitioned server has demonstrably run out of hardware headroom — not before.

Recommended Articles

Docker + PostgreSQL Clusters: Sharding, Replication, and Read/Write Routing

PostgreSQL Indexing Deep Dive: B-Tree, GIN, GiST, BRIN, and Index Maintenance

PostgreSQL Horizontal Partitioning: Range, List, and Hash Partitioning in Practice