Mapping E-commerce Workloads to Range Partition Keys

This guide walks through aligning a high-velocity e-commerce orders table to range partitioning strategies so that write hotspots are eliminated, partition pruning fires on every OLTP query, and quarterly archival runs without downtime — all within the broader context of use case mapping for partition strategies.

Prerequisites

• PostgreSQL 14 or later (required for DETACH PARTITION CONCURRENTLY)
• Superuser or role with CREATE TABLE and ALTER TABLE privileges on the target schema
• Baseline metrics on order insert rate (orders/sec) and peak multiplier (flash-sale factor)
• Confirmed TIMESTAMPTZ column exists (or planned) on the orders table
• pg_partman extension available if automating lifecycle beyond the example script
• Read replica or materialized view provisioned for cross-partition reporting queries

Step 1 — Analyse Workload Velocity and Choose Boundary Granularity

Map the four key e-commerce metrics to the boundary granularity they demand before writing any DDL.

SRE tip: Calculate total partition count against catalog overhead before committing. PostgreSQL stores one row in pg_class per partition; schemas with thousands of sub-daily partitions experience measurable planner overhead. Keep live partitions under 500 for general OLTP workloads.

Step 2 — Create the Partitioned Parent Table

Declare PARTITION BY RANGE on a TIMESTAMPTZ column. Include created_at in the primary key so PostgreSQL can enforce uniqueness across all child partitions without a global index.

-- PostgreSQL 14+ declarative range partitioning
CREATE TABLE orders (
  id          UUID        NOT NULL DEFAULT gen_random_uuid(),
  customer_id INT         NOT NULL,
  total_amount DECIMAL(10,2),
  region_code CHAR(2),
  created_at  TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

Operational note: The parent table itself holds no rows — all storage is in child partitions. Attempting VACUUM or ANALYZE directly on the parent cascades to all children; that is intentional and desirable for statistics accuracy.

DBA tip: Add a CHECK constraint on region_code values later via list sub-partitioning if GDPR isolation becomes a requirement. Range and list compose cleanly — see list partitioning techniques for the composite DDL pattern.

Step 3 — Attach Range Partitions and a Default Fallback

Create child partitions using FOR VALUES FROM ... TO bounds and always attach a default partition to catch out-of-bound writes without failing transactions.

-- Quarterly partitions for 2024
CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE orders_2024_q3 PARTITION OF orders
  FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE orders_2024_q4 PARTITION OF orders
  FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

-- Safe fallback: catches late-arriving or misdated inserts
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Operational note: Bounds are half-open intervals: FROM '2024-01-01' TO '2024-04-01' includes January 1 and excludes April 1. This convention avoids off-by-one errors and aligns exactly with date_trunc('quarter', ...) expressions in reporting queries.

SRE tip: Omitting the default partition during high-traffic periods causes immediate INSERT failures when timestamps drift due to clock skew, NTP corrections, or application bugs. Route default partition traffic to a reconciliation queue for background re-routing.

Step 4 — Validate Partition Pruning with EXPLAIN

Application queries must include the partition key in WHERE clauses to trigger partition elimination. A missing predicate forces PostgreSQL’s planner to scan every child table via an Append node.

-- Query enforcing strict range predicates for partition elimination
SELECT id, total_amount, created_at
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-04-01'
  AND customer_id = 1042;

Validate with:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, total_amount, created_at
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-04-01'
  AND customer_id = 1042;

Expected output (pruning active):

Append  (cost=0.00..8.30 rows=1 width=36)
  ->  Seq Scan on orders_2024_q1  (cost=0.00..8.29 rows=1 width=36)
        Filter: ((customer_id = 1042) AND ...)

If you see an Append scanning multiple partitions, check for:

• Implicit type casting — comparing TIMESTAMPTZ to a bare DATE literal without an explicit cast disables pruning.
• Timezone mismatches — the session TimeZone setting affects how bare timestamp literals are interpreted.
• Stale statistics — run ANALYZE orders after bulk loads so the planner has accurate row estimates per partition.

DBA tip: Enable enable_partition_pruning = on (the default) and verify it has not been disabled at the session level with SHOW enable_partition_pruning;.

Step 5 — Mitigate Write Hotspots During Flash Sales

Monotonic timestamps concentrate writes on the latest partition. During a flash sale this means one partition absorbs all inserts while others sit idle.

Option A — hash sub-partitioning on customer_id:

-- Replace the monthly partition with hash sub-partitions
CREATE TABLE orders_2024_11 PARTITION OF orders
  FOR VALUES FROM ('2024-11-01') TO ('2024-12-01')
  PARTITION BY HASH (customer_id);

CREATE TABLE orders_2024_11_h0 PARTITION OF orders_2024_11
  FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE orders_2024_11_h1 PARTITION OF orders_2024_11
  FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE orders_2024_11_h2 PARTITION OF orders_2024_11
  FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE orders_2024_11_h3 PARTITION OF orders_2024_11
  FOR VALUES WITH (modulus 4, remainder 3);

Option B — daily boundaries during known high-traffic events:

-- Swap from monthly to daily for Black Friday week
CREATE TABLE orders_2024_11_29 PARTITION OF orders
  FOR VALUES FROM ('2024-11-29') TO ('2024-11-30');
CREATE TABLE orders_2024_11_30 PARTITION OF orders
  FOR VALUES FROM ('2024-11-30') TO ('2024-12-01');

Operational note: Option A preserves temporal pruning for reporting. Option B simplifies archival by keeping partitions single-purpose but increases catalog size. Choose based on whether your reporting queries scan by date or by customer.

SRE tip: For consistent hash routing between the application tier and sub-partitions, read the hash routing algorithms guide before adding application-side shard keys.

Step 6 — Automate Partition Lifecycle Management

Pre-create the next period’s partition before it becomes active and detach the oldest for archival once the retention window closes.

#!/usr/bin/env bash
# rotate_partitions.sh — zero-downtime quarterly rotation
# Usage: ./rotate_partitions.sh 2024-10-01 2025-01-01
set -euo pipefail

NEXT_START="${1:?Usage: $0 NEXT_START NEXT_END}"
NEXT_END="${2:?Usage: $0 NEXT_START NEXT_END}"
PARTITION_NAME="orders_$(date -d "$NEXT_START" +%Y_%m)"

# 1. Create next partition (idempotent via IF NOT EXISTS)
psql -c "
  CREATE TABLE IF NOT EXISTS ${PARTITION_NAME}
    PARTITION OF orders
    FOR VALUES FROM ('${NEXT_START}') TO ('${NEXT_END}');"

# 2. Find the oldest dated partition (skip the default)
OLDEST_PART=$(psql -t -c "
  SELECT c.relname
  FROM   pg_inherits i
  JOIN   pg_class c ON c.oid = i.inhrelid
  JOIN   pg_class p ON p.oid = i.inhparent
  WHERE  p.relname = 'orders'
    AND  c.relname <> 'orders_default'
  ORDER  BY c.relname
  LIMIT  1;" | tr -d ' \n')

# 3. Detach and archive if past retention window
if [ -n "$OLDEST_PART" ]; then
  echo "Detaching ${OLDEST_PART} for archival..."
  psql -c "ALTER TABLE orders DETACH PARTITION ${OLDEST_PART} CONCURRENTLY;"
  pg_dump -t "${OLDEST_PART}" | gzip > "/archive/${OLDEST_PART}.sql.gz"
  echo "Archived to /archive/${OLDEST_PART}.sql.gz"
fi

Operational note: DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) acquires only a brief ShareUpdateExclusiveLock instead of the heavier AccessExclusiveLock taken by a plain DETACH. Active OLTP transactions continue uninterrupted during the detach.

SRE tip: Schedule this script via pg_cron or an external orchestrator (Airflow, Temporal) to run 7 days before the boundary transitions. Monitor pg_stat_user_tables on orders_default row counts — a growing default partition is an early signal that boundary creation has fallen behind inserts.

Verification

Confirm the partition tree, row distribution, and default partition health with these catalog queries:

-- List all partitions and their boundary ranges
SELECT
  c.relname           AS partition_name,
  pg_get_expr(c.relpartbound, c.oid) AS bounds,
  pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class p
JOIN pg_inherits i ON i.inhparent = p.oid
JOIN pg_class c    ON c.oid = i.inhrelid
WHERE p.relname = 'orders'
ORDER BY c.relname;

Expected output shape:

  partition_name   |               bounds                | size
-------------------+-------------------------------------+-------
 orders_2024_q1    | FOR VALUES FROM ('2024-01-01') ...  | 1240 MB
 orders_2024_q2    | FOR VALUES FROM ('2024-04-01') ...  | 980 MB
 orders_default    | DEFAULT                              | 0 bytes

-- Confirm default partition is empty (non-zero signals a boundary gap)
SELECT COUNT(*) FROM orders_default;

-- Verify pruning fires: must reference only one child partition
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';

Failure Mode Table

FAQ

-- Move misdated rows into the correct partition
INSERT INTO orders_2024_q1
SELECT * FROM orders_default
WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';

DELETE FROM orders_default
WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';

Related

• Use Case Mapping for Partition Strategies — parent: maps workload access patterns to the right partitioning strategy
• Range Partitioning Strategies — boundary design, pruning optimisation, and lifecycle management in depth
• Automating Monthly Partition Creation in MySQL 8.0 — parallel lifecycle automation approach for MySQL workloads