← Back to Glossary

Partitioning

Query Execution

What is partitioning?

Database partitioning is an architectural process of subdividing large tables, indexes and data into smaller partitions, enabling database performance and management benefits through parallel processing.

Partitioning breaks up data into smaller, more manageable chunks that can be spread across storage devices or nodes. Each partition forms an independent subset of the overall data that can be queried and manipulated efficiently in parallel. The partitions are consolidated transparently during query execution to produce complete results across the entire dataset.

Partitioning facilitates parallel execution within a server and distributed execution across a cluster. It works closely with the overall query execution engine to improve performance through divide-and-conquer parallelism. Partitioning also aids maintenance operations like backups and insert scalability.

How does it work?

Some ways partitioning is accomplished:

  • Range partitioning maps data to partitions by ranges of a column like date ranges.
  • List partitioning maps data based on discrete values in a column.
  • Hash partitioning maps data based on a hashing function on its key.
  • Composite partitioning uses a combination like range with hash.

    • Why does it matter?

    Partitioning provides benefits like:

  • Spreading IO across disks by striping partitions.
  • Dividing memory/CPU needs across servers.
  • Pruning queries by eliminating unneeded partitions.
  • Easy deletion of older historic data in range partitions.
  • Parallelizing operations by working on partitions concurrently.

    • FAQ

    When is partitioning useful?

    Partitioning helps for:

  • Taming large tables exceeding storage limits.
  • Parallelizing big data analytics through division of work.
  • Hardware scalability through spreading load.
  • Maintaining history by aging out old partitions.

    • What are some key partitioning challenges?

    Some downsides to partitioning:

  • Extra development and tuning work.
  • Joining partitioned tables can be complex.
  • Mandatory keys to map rows to partitions.
  • Rebalancing data as partitions grow.
  • Indexing considerations with locality.

    • What are some examples of partitioned databases?

    Some databases with partitioning capabilities:

  • Oracle - Range, hash, list, composite partitioning.
  • SQL Server - Partitioned tables and indexes using filegroups.
  • MySQL - Hash and key partitioning.
  • PostgreSQL - Declarative table partitioning.
  • Cassandra - Partitioning by row key for distribution.

    • References:

  • [Book] PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries, by Apress
  • [Article] Vertical partitioning algorithms for database design
  • [Post] Understanding Data Partitioning in Database design (Part 1)
  • [Post] Data Partitioning — System Design Concept

    • Related Topics

    Query Execution

    Query execution is the process of carrying out the actual steps to retrieve results for a database query as per the generated execution plan.

    Parallel Execution

    Parallel execution refers to techniques for speeding up database query processing by leveraging multiple CPUs, servers, or resources concurrently.

    Distributed Execution

    Distributed execution refers to techniques to execute database queries efficiently across clustered servers or nodes, dividing work to utilize parallel resources.

    Partitioning

    Database partitioning refers to splitting large tables into smaller, independent pieces called partitions stored across different filegroups, drives or nodes.

    CAP Theorem

    The CAP theorem states that distributed data systems can only support two of the three guarantees: consistency, availability and partition tolerance.

    © 2025 Synnada AI | All rights reserved.