Database Partitioning: Strategies and Pitfalls
Database partitioning is the technique of splitting one logical table into multiple physical pieces. The pieces can be queried as one but are stored and indexed separately. The pattern fits very large tables where queries naturally filter by the partition key. The pattern hurts tables where the queries do not naturally filter. The right key and the right time are the two decisions that matter most.
Written by Yashveer Singh, founder of Yashveer Labs.
What you actually need to know
- Partitioning fits very large tables where queries filter by a natural key.
- The wrong partition key produces overhead without benefit.
- Hundreds of partitions is fine. Many thousands is usually over partitioning.
- Migrating a live table to partitions is a real project.
- Preemptive partitioning is the most common mistake.
| Workload | Partition recommendation |
|---|---|
| Time series with date range queries | Range partition by date |
| Multi tenant with tenant scoped queries | Hash or list partition by tenant_id |
| Event logs you regularly drop | Range partition by date |
| Generic CRUD without natural filter | Do not partition |
| Geographic data with region queries | List partition by region |
| Very small to moderate sized tables | Do not partition |
The core argument
Partitioning is one of those database techniques that founders read about and want to use before they need it. The pattern sounds powerful. The talks at conferences are excellent. The reality is that partitioning helps a specific shape of problem and hurts most others.
The shape that partitioning helps is large tables where most queries filter by a natural partition key. The query planner uses the key to skip partitions that cannot have matching rows. A query that touches one month of data only reads that month's partition. The other eleven months are skipped. The performance gain is real.
The shape that partitioning hurts is large tables where queries do not naturally filter by a partition key. The query has to touch every partition because the planner cannot prune. The overhead of the multi partition query is paid. The performance is worse than the unpartitioned table.
The decision is mostly about the workload. The team that has time series data with date range queries should partition by date. The team that has multi tenant data with tenant scoped queries should partition by tenant. The team that has generic CRUD without a natural key should not partition.
The timing matters. Partitioning preemptively for a table that is not yet large enough is a common mistake. The overhead is paid without the benefit. The right time is when the table is approaching the scale where unpartitioned operations become painful.
The partition key decision
| Workload pattern | Partition key |
|---|---|
| Most queries filter by created_at range | Date range |
| Most queries filter by tenant_id | Tenant hash or list |
| Most queries filter by region | Region list |
| Most queries filter by status | Often not a good partition key |
| Most queries filter by user_id | User hash if very many users |
| Generic application data | Often not a candidate |
How much does this cost
| Phase | Cost |
|---|---|
| Initial design | A few weeks |
| Migration from unpartitioned | A quarter for a large table |
| Ongoing operations | Modest. Partition management. |
| Application changes | Often minimal if the partition key is in queries |
| Monitoring | Modest |
Features the partitioning setup must have
- A clear partition key justified by query patterns.
- A reasonable partition count.
- A management plan for adding new partitions.
- A management plan for dropping old partitions.
- Monitoring on partition sizes and query plans.
- Indexes on each partition appropriate to the queries.
- A documented operational runbook.
- A test that verifies partition pruning is working.
Expert opinion
Partitioning is a tool that solves a specific class of problem. The teams that adopt it for the right problem get the scaling capability they need. The teams that adopt it for the wrong problem pay the complexity tax without the corresponding benefit. The discipline is to recognize which class of problem you have before reaching for the tool. The right time is when the unpartitioned table is genuinely struggling.
>
Yashveer Singh, founder of Yashveer Labs
How this played out on a real project
A client SaaS had an events table that had grown to roughly 2 billion rows. Queries on recent events were slow. The team was considering partitioning. We measured first.
The queries that were slow were filtering by date and by tenant. Both columns were good candidates for partitioning. The events table also had a regular operation to drop events older than ninety days. The drop was taking hours.
We partitioned by month. The events older than ninety days could be dropped by dropping the partition, which is instant. Queries on recent events touched only the recent partitions. The performance improved by an order of magnitude on the slow queries.
The migration took six weeks of careful work. The team has run the partitioned table for two years without significant issues. The decision to partition was right for this specific workload. A different team's table with the same size but different query patterns would have been worse with partitioning.
For more on the related work, see scaling from one thousand to one hundred thousand users the invisible database bottlenecks and sharding strategies for SaaS when to start and when to stop avoiding it.
Common mistakes teams make
- Partitioning preemptively before the table is large enough.
- Wrong partition key for the query patterns.
- Too many partitions. Planner overhead.
- No partition management for new and old partitions.
- No verification that partition pruning is working.
- Migration without planning. Outages follow.
- Treating partitioning as a substitute for indexes.
- Treating partitioning as a substitute for sharding.
A decision framework
- Step one. Measure the current table size and growth rate.
- Step two. Analyze the query patterns. Identify the natural partition key.
- Step three. Confirm partition pruning would apply to most queries.
- Step four. Plan the migration carefully.
- Step five. Migrate. Verify. Adopt the operational practice.
For more on the related work, read sharding strategies for SaaS when to start and when to stop avoiding it and PostgreSQL performance at scale the tweaks that move the needle. On the broader scaling side, CockroachDB and TiDB when distributed SQL pays off is the natural next read.
Frequently asked
Why you should hire Yashveer Singh for this
The kind of work this article describes is the kind of work I do every week. Production deployments, scaling decisions, the architecture choices that compound over years. I am Yashveer Singh, founder of Yashveer Labs. If you need this done, I do not need to be sold on the brief. Send me what you have and I will tell you what it actually takes.
Posts that line up with this one.
- Backend, APIs, and System Design
Database Indexes: A Practical Primer for SaaS Engineers
Most database performance problems in SaaS are missing indexes. The fix is mechanical. The discipline is to know which indexes to add and which to skip. Here is the primer.
- Backend, APIs, and System Design
Full Text Search in PostgreSQL: Practical Patterns
Postgres full text search is good enough for most SaaS. The setup is small. The performance is solid. Most teams reach for Elasticsearch when Postgres would have served them for years.
- Backend, APIs, and System Design
Geospatial Data Done Right: PostGIS and Beyond
PostGIS turns Postgres into a real geospatial database. The setup is small. The performance is excellent. Most teams that need location queries should start with PostGIS rather than reaching for specialized stores.
- Backend, APIs, and System Design
CockroachDB and TiDB: When Distributed SQL Pays Off
Distributed SQL solves problems most SaaS does not have. When it solves yours, it pays back richly. When it does not, the operational tax is brutal. Here is the honest decision.