Database Partitioning
Subject: Computer and Technology noun /ˈdeɪ.təˌbeɪs pɑːrˈtɪʃ.ən.ɪŋ/ 1 definition
- database-systems
- storage-architecture
- query-optimization
- distributed-systems
-
Definition (noun)
Entry #308 · UpdatedDatabase Partitioning is a data-organization technique that divides a large logical database, table, or index into smaller, physically distinct segments called partitions, typically preserving a unified logical schema for applications. Depending on the strategy, a database engine distributes rows across segments using rules on a partition key—such as value ranges, discrete lists, or hashes—or separates columns into distinct physical structures, allowing the query optimizer to skip irrelevant segments via partition pruning. Isolating active or frequently queried records into smaller partitions reduces index depth and disk I/O while enabling targeted maintenance operations, such as archiving or dropping expired data without locking an entire table. Unlike database replication, which duplicates identical data across nodes for availability, partitioning splits a dataset into disjoint subsets—operating either within a single database server or across multiple servers in a distributed cluster.
Common Types
Horizontal Partitioning: Divides a table by rows so each partition holds a disjoint subset of records sharing the same schema, routing rows via range, list, or hash rules on a partition key and enabling distributed sharding when spread across nodes.
Vertical Partitioning: Splits a table by columns into narrower tables or column groups linked by a shared primary key, keeping frequently accessed attributes compact while isolating wide blobs or rarely queried fields.
Functional Partitioning: Separates schemas or tables into dedicated databases aligned with distinct application domains or workloads—such as isolating transactional billing from read-heavy product catalogs—to reduce contention and enable independent scaling.
Composite Partitioning: Layers two partitioning schemes hierarchically on the same dataset, such as partitioning rows first by monthly timestamp ranges and then subpartitioning each month by a hash of
tenant_idto prevent hotspots.
Example
An observability service storing 2 billion telemetry records configures declarative range partitioning on its PostgreSQL
eventstable usingPARTITION BY RANGE (created_at)to create monthly child tables such asevents_2026_10andevents_2026_11. When a dashboard queriesWHERE created_at >= '2026-11-01' AND created_at < '2026-11-15', the planner prunes all non-matching partitions to scan onlyevents_2026_11, while retention jobs purge expired logs instantly viaDROP TABLE events_2025_11instead of executing a full-tableDELETE.