Partitioning Patterns

What it is

Database partitioning divides a large table into smaller physical segments (partitions) while presenting a single logical table to queries. Unlike sharding — which distributes data across separate database servers — partitioning operates within a single database instance. Each partition is stored as a separate physical file or segment but participates in the parent table's queries transparently. When a query includes a filter on the partition key, the optimizer can skip entire partitions that cannot contain matching rows, a technique called partition pruning.

PostgreSQL (since version 10) supports declarative partitioning with range, list, and hash strategies. Oracle pioneered the feature decades earlier and supports composite strategies. MySQL supports range and list partitioning at the engine level. Partitioning is especially powerful for time-series data, audit logs, and event tables where data access patterns are strongly time-correlated, making old partitions cold and droppable without expensive DELETE operations.

Why it exists

A 10-billion-row events table is problematic even on powerful hardware: index scans become expensive, VACUUM operations stall, archival requires copying billions of rows, and adding a new column locks the entire table for an extended period. Partitioning solves all of these problems. Old partitions can be detached and dropped in milliseconds (a metadata-only operation), index maintenance is scoped to individual partitions, and ALTER TABLE on a partition does not block queries on other partitions. The query planner benefits immediately from partition pruning, often scanning only 1/N of the data for time-bounded queries.

Partitioning also enables tiered storage strategies: keep the recent (hot) partition on NVMe SSDs, move 90-day-old partitions to cheaper SATA SSDs, and archive one-year-old partitions to object storage or detach them entirely. This dramatically reduces storage costs for high-volume data without any application code changes.

When to use

• Tables with billions of rows where queries almost always filter by a date range or other range-bounded key.
• Data that has a natural lifecycle — archival, deletion, or tiering of old data is a regular operational requirement.
• Large tables with maintenance pain: slow VACUUM, long index builds, or ALTER TABLE blocking production traffic.
• Multi-tenant tables where each tenant's data can be isolated into a separate partition for better query isolation and easy tenant offboarding.
• High-volume write tables (events, logs, metrics) where new data is always inserted in a predictable pattern (e.g., always the "latest" partition).
• Analytics workloads with partition-wise aggregate queries, where PostgreSQL can execute aggregations in parallel across partitions.

When not to use

• Small tables where the overhead of partition management and planner complexity outweighs any benefit.
• Tables with queries that almost never filter by the partition key — partition pruning will not trigger and all partitions will be scanned.
• Workloads with frequent cross-partition joins on non-partition-key columns, which can be slower than a non-partitioned table due to planning overhead.
• When you need unique constraints or foreign keys that span the partition key — these are difficult or impossible with most partitioning implementations.

Typical architecture

  PostgreSQL Declarative Partitioning (Range by month):

  events (parent table — logical only, no data)
  ├── events_2025_01  (Jan 2025: created_at >= 2025-01-01 AND < 2025-02-01)
  ├── events_2025_02  (Feb 2025)
  ├── events_2025_03  (Mar 2025)
  │   ...
  └── events_2025_12  (Dec 2025)

  Query: SELECT * FROM events WHERE created_at BETWEEN '2025-03-01' AND '2025-03-31'
  → Planner prunes: scans ONLY events_2025_03
  → Cost: 1/12 of full table scan

  DDL:
  CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    payload JSONB
  ) PARTITION BY RANGE (created_at);

  CREATE TABLE events_2025_03
    PARTITION OF events
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

  -- Archive: detach old partition (instant, metadata-only)
  ALTER TABLE events DETACH PARTITION events_2024_01;
  -- Then DROP TABLE events_2024_01; (or move to archive DB)

  Composite Partitioning (range + hash):
  orders PARTITION BY RANGE (order_date)
  └── orders_2025_q1 PARTITION BY HASH (customer_id)
      ├── orders_2025_q1_h0
      ├── orders_2025_q1_h1
      └── orders_2025_q1_h2
          

Pros and cons

Implementation notes

Automate partition creation. For time-range partitioning, new partitions must be pre-created before data arrives. Use a scheduled job (cron, pg_cron, or a dedicated maintenance service) to create next-period partitions ahead of time. A common pattern is to always have the current month and the next 2 months pre-created. If a partition does not exist when a row is inserted, the insert will fail (PostgreSQL) or go to a default partition (if configured). Use CREATE TABLE IF NOT EXISTS in your maintenance script to make it idempotent.

Partition key selection: The partition key must be immutable (or rarely updated) — updating a row's partition key requires deleting it from one partition and inserting into another, which is a complete row-level operation. Choose a column that is always present in your most frequent queries' WHERE clauses. For time-series data, created_at is canonical. For multi-tenant tables, tenant_id works well with list partitioning. The granularity of range partitions (daily, weekly, monthly, yearly) should be calibrated so that each partition is manageable (not too many rows per partition, not too many partitions for the planner). A practical rule: aim for 10–500 partitions for range-partitioned tables; each partition storing 1–50 GB is typical.

Common failure modes

• Missing partition: Automated partition creation fails silently; the next INSERT attempt hits a missing partition and fails or lands in the default partition, breaking all queries that assume partition pruning.
• Partition pruning disabled: Query uses a date calculation that the planner cannot evaluate at planning time (e.g., WHERE created_at > NOW() - INTERVAL '30 days'); PostgreSQL may not prune partitions and scans all of them.
• Global index invalidation: PostgreSQL global indexes on partitioned tables require maintenance when partitions are attached or detached; failing to rebuild them causes stale results.
• Too many partitions: A daily partition table with 5 years of history has 1,825 partitions; query planning time increases measurably. Archive and detach old partitions regularly.
• Default partition accumulation: Rows that don't match any partition land in the default partition; if left unmanaged, it grows unbounded and degrades all query performance.

Decision checklist

• The partition key is always present in the WHERE clause of the most frequent queries to enable partition pruning.
• Automated partition pre-creation is implemented and monitored with alerting if it fails.
• Partition archival/detachment policy is defined (e.g., detach partitions older than 12 months).
• Unique constraint requirements have been reviewed; if cross-partition uniqueness is needed, an alternative (application-level check, UUID keys) is planned.
• The number of partitions is bounded; a maintenance plan prevents unbounded growth.
• Partition granularity (daily/monthly/yearly) is calibrated to expected data volume per partition.

Example use cases

• Audit log table: A compliance system stores 50 million audit events per day. Monthly range partitioning allows instant archival of months beyond the 7-year retention policy and enables partition pruning for compliance queries scoped to specific date ranges.
• Multi-tenant SaaS metrics: A SaaS platform stores per-tenant metrics in a list-partitioned table. Each tenant partition can be independently maintained, and large tenants can be moved to dedicated tablespaces without affecting others.
• E-commerce orders: An orders table is range-partitioned by year. End-of-year financial reporting queries only touch the current-year partition, reducing query time from minutes to seconds.

Related patterns

• Sharding Strategies — extends partitioning across multiple database nodes when single-node limits are reached.
• Hot Partition Mitigation — strategies when a particular partition receives disproportionate write load.
• Materialized Views — partition-wise aggregates can be further optimized by pre-computing summaries per partition.

Further reading

• PostgreSQL Documentation — Table Partitioning
• MySQL 8.0 — Partitioning Reference
• Use The Index, Luke — Partitioning and Indexes