Skip to main content
Definition Website Computer and Technology

Database Partitioning

Subject: Computer and Technology noun /ˈdeɪ.təˌbeɪs pɑːrˈtɪʃ.ən.ɪŋ/ 1 definition

  1. Definition (noun)

    Entry #308 · Updated

    Database 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_id to prevent hotspots.

    Example

    An observability service storing 2 billion telemetry records configures declarative range partitioning on its PostgreSQL events table using PARTITION BY RANGE (created_at) to create monthly child tables such as events_2026_10 and events_2026_11. When a dashboard queries WHERE created_at >= '2026-11-01' AND created_at < '2026-11-15', the planner prunes all non-matching partitions to scan only events_2026_11, while retention jobs purge expired logs instantly via DROP TABLE events_2025_11 instead of executing a full-table DELETE.