Database Partitioning
Database Table Partitioning and Sharding · Also known as: table partitioning, sharding
Database partitioning is a technique for dividing large tables across multiple physical storage units or servers to improve performance and scalability. Developed in the context of distributed databases, partitioning allows individual queries to access smaller subsets of data, reducing I/O and enabling horizontal scaling as data grows.
Read the full method
Sign in with a free account to read this section.
When to use it
Implement partitioning when tables exceed physical disk capacity, when performance degrades due to table size, or when you need to scale writes across multiple servers. Partitioning is most effective when queries naturally align with partition boundaries. Assumptions include identifiable partitioning keys and acceptable performance trade-offs from distributed execution.
Strengths & limitations
- Enables horizontal scaling by distributing data across multiple servers
- Partition pruning eliminates unnecessary data access, improving query performance
- Allows independent maintenance of partitions (backup, compression, archival)
- Queries spanning multiple partitions require distributed execution and result aggregation
- Partitioning key selection is critical; poor choices limit query optimization
- Adding or removing partitions can be complex and require data redistribution
Frequently asked
What is the difference between partitioning and sharding?
Partitioning divides a table into independent chunks, possibly stored on the same server. Sharding distributes partitions across different servers. Sharding adds distributed coordination but enables horizontal scaling beyond a single machine.
How do I choose a good partitioning key?
Select keys based on: (1) natural access patterns (queries that filter by the key), (2) balanced distribution (avoiding skewed partitions), (3) temporal or geographic relevance. Avoid low-cardinality columns that create too many rows per partition.
What happens when I query across multiple partitions?
The database executes the query against all relevant partitions independently, then aggregates results. This is slower than a single-partition query but still faster than querying the entire unpartitioned table.
How do I handle growing data in a partition?
Use time-based partitioning for growing tables, creating new partitions periodically (daily, monthly). Archive old partitions to slower storage. Some databases support partition splitting to divide overgrown partitions.
Sources
- Stonebraker, M., & Schloss, G. A. (1986). Distributed INGRES to homogeneous and heterogeneous computer systems. Proceedings of the ACM SIGMOD International Conference on Management of Data, 64-77. link ↗
- Johnson, B. (2000). Distributed systems and databases (2nd ed.). New York: Morgan Kaufmann. link ↗
- Garcia-Molina, H., Ullman, J. D., & Widom, J. (2009). Database Systems: The Complete Book (2nd ed.). Pearson Education. link ↗
How to cite this page
ScholarGate. (2026, June 3). Database Table Partitioning and Sharding. ScholarGate. https://scholargate.app/en/information-systems/database-partitioning