DataTerminology.comNEW
Also known as: Table Partitioning, Database Partitioning
A database design technique that splits large tables and indexes into smaller, more manageable pieces to improve performance and manageability.
DESCRIPTION
Partitioning divides a logical table or index into smaller segments based on partition keys such as date ranges or hash values. It improves query performance via partition pruning and simplifies maintenance tasks like archiving, purging, and rebalancing. Localized indexes and parallelism increase throughput. Partitioning differs from sharding: it is typically within a single database, while sharding distributes across servers. Proper key choice, balanced sizes, and aligned predicates are essential for benefits.
ALTERNATIVE DEFINITIONS
Oracle Database VLDB and Partitioning Guide
Partitioning is a database design technique that divides large tables and indexes into smaller, more manageable pieces.
Highlights manageability alongside performance benefits.
PostgreSQL Documentation
Table partitioning splits a large table into smaller pieces, called partitions, which are stored separately but queried as one table.
Emphasizes unified querying with physically separate partitions.
Microsoft Learn
Partitioned tables and indexes divide data into units that can be managed and accessed separately for improved performance and maintenance.
Focuses on operational manageability and performance gains in SQL Server.
RELATED TERMS
Range Partitioning Hash Partitioning List Partitioning Partition Pruning Local Index Materialized View Query Optimization Data Lifecycle Information Lifecycle Management
COMPETING TERMS
RELATIONS
KNOWLEDGE AREAS
Data Storage & Operations Data Engineering Data Architecture Real-time & Streaming Data
TARGET AUDIENCE
Data Engineer Data Architect Software Developer BI Specialist Data Modeler
EXTERNAL SOURCES
- Wikipedia - Partition (database)
- Oracle - Partitioning concepts and methods
- PostgreSQL - Declarative partitioning reference
- Microsoft - Create partitioned tables/indexes
TOP QUESTIONS
Why use Partitioning?
It enhances performance via partition pruning, simplifies maintenance, and supports large-scale data lifecycle operations.
Category: Relevancy
How does Partitioning differ from Sharding?
Partitioning is within a database instance; sharding spreads data across servers for horizontal scale-out.
Category: Difference
What are common Partitioning strategies?
Range, hash, list, and composite partitioning are common, chosen based on query patterns and data distribution.
Category: Basics
What’s a mistake when choosing partition keys?
Picking low-selectivity or unstable keys that don’t align with query predicates, reducing pruning benefits.
Category: Mistakes
What are Partitioning risks?
Too many small partitions increase overhead; skewed data causes hotspots and uneven performance.
Category: Risks
How does Partitioning affect indexing?
Local indexes per partition speed maintenance and pruning; global indexes may require more complex maintenance.
Category: Interaction
How to manage data lifecycle with Partitioning?
Use partition exchange, detach/attach, and rolling windows to archive or purge efficiently.
Category: Operations
What metrics indicate Partitioning success?
Reduced scan IO, faster queries with aligned predicates, and efficient maintenance operations.
Category: Metrics
Do all queries benefit from Partitioning?
No. Queries that don’t filter on partition keys may see little or no improvement.
Category: Misunderstanding