is enables range partitioning and aligns with retention policies.

• Multi-Tenancy: Use a tenant identifier. This enables list partitioning for data isolation and per-tenant QoS.
• High-Cardinality Unstructured Data: Use a hash of a primary key or identifier to distribute writes evenly.

2. Strategy Determination

• Range: Best for time-series or sequential data. Allows efficient range scans and easy expiration of old data.
• List: Best for categorical data with a known set of values, such as regions or tenant IDs.
• Hash: Best for distributing load evenly when no natural range or list exists, mitigating write hotspots.

3. Declarative Schema Definition

Use the database's native partitioning syntax. This ensures the query planner automatically handles routing and pruning.

SQL Implementation (PostgreSQL):

-- Parent table definition with partition strategy
CREATE TABLE user_events (
    event_id BIGINT GENERATED ALWAYS AS IDENTITY,
    tenant_id UUID NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    payload JSONB,
    PRIMARY KEY (event_id, tenant_id, event_time)
) PARTITION BY RANGE (event_time);

-- Create partitions (Automation recommended for production)
CREATE TABLE user_events_y2024m01 PARTITION OF user_events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE user_events_y2024m02 PARTITION OF user_events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Constraint Note: Unique constraints on partitioned tables must include all partition key columns. A global unique constraint on event_id alone is not possible without including event_time in the constraint.

4. Application Integration

The application must include the partition key in queries to enable partition pruning. Omitting the key forces a sequential scan across all partitions.

TypeScript Query Pattern:

import { Pool } from 'pg';

const pool = new Pool();

// ❌ Anti-pattern: Query omits partition key (event_time).
// Planner must scan all partitions.
const badQuery = `
  SELECT * FROM user_events 
  WHERE tenant_id = $1 AND payload->>'type' = $2
`;

// ✅ Best practice: Include partition key in WHERE clause.
// Planner prunes irrelevant partitions.
const optimizedQuery = `
  SELECT * FROM user_events 
  WHERE tenant_id = $1 
    AND event_time BETWEEN $2 AND $3
    AND payload->>'type' = $4
`;

async function getEvents(tenantId: string, type: string, start: Date, end: Date) {
  const res = await pool.query(optimizedQuery, [
    tenantId, 
    start.toISOString(), 
    end.toISOString(), 
    type
  ]);
  return res.rows;
}

5. Architecture Decisions

• Automation: Partition creation must be automated. Manual DDL is error-prone and does not scale. Use extensions like pg_partman or application-level cron jobs to create partitions ahead of time.
• Granularity: Balance partition size against catalog overhead. Partitions should ideally range from 1GB to 10GB. Too many partitions (e.g., daily partitions over 10 years) can bloat the system catalog and increase query planning time.
• Indexing: Indexes are created on the parent table and automatically propagated to new partitions. However, index size per partition is smaller, improving index cache efficiency.

Pitfall Guide

Production partitioning introduces specific failure modes. Avoid these common mistakes to ensure stability and performance.

1. Partition Pruning Failure:

   • Mistake: Queries do not include the partition key or use non-sargable expressions.
   • Impact: The planner scans all partitions, resulting in worse performance than a monolithic table due to overhead.
   • Mitigation: Always verify execution plans with EXPLAIN ANALYZE. Ensure partition keys are in the WHERE clause and not wrapped in functions.

2. Catalog Bloat:

   • Mistake: Creating excessive partitions (e.g., hourly partitions for multi-year data).
   • Impact: Query planning time increases linearly with partition count. Memory usage for the system catalog grows.
   • Mitigation: Limit total partition count to <1,000 where possible. Use coarser granularity (monthly) unless daily granularity is strictly required for retention.

3. Data Skew in Hash Partitioning:

   • Mistake: Using a partition key with non-uniform distribution in hash partitioning.
   • Impact: One partition becomes a hotspot, negating distribution benefits.
   • Mitigation: Analyze data distribution before choosing hash. If skew exists, consider list partitioning or composite keys.

4. The Unique Constraint Trap:

   • Mistake: Attempting to create a unique index on a column not included in the partition key.
   • Impact: Database rejects the constraint. Application logic assumes uniqueness that cannot be enforced.
   • Mitigation: Design primary keys to include partition columns. If global uniqueness is required, implement application-level checks or use a distributed ID generator.

5. Missing Partition Automation:

   • Mistake: Relying on manual creation or default partitions without monitoring.
   • Impact: Write failures when a new time period arrives, or silent data routing to default partitions, defeating partitioning benefits.
   • Mitigation: Implement robust automation. Alert on default partition growth. Use tools that proactively create partitions days in advance.

6. Cross-Partition Joins:

   • Mistake: Joining large partitioned tables on non-partition keys.
   • Impact: Nested loop joins across partitions become prohibitively expensive.
   • Mitigation: Co-partition tables on the join key where possible. If not, ensure join conditions allow for pruning on at least one side.

7. Migration Complexity:

   • Mistake: Converting a live monolithic table to partitioned without downtime strategy.
   • Impact: Long locks, data inconsistency, or rollback difficulties.
   • Mitigation: Use the "shadow table" pattern. Create the partitioned table, backfill data, sync writes via triggers, then switch over atomically.

Production Bundle

Action Checklist

• Audit Query Patterns: Identify top 20 queries and verify they filter on potential partition keys.
• Estimate Partition Count: Calculate total partitions based on granularity and retention. Ensure count < 1,000.
• Define Retention Policy: Map retention requirements to partition boundaries (e.g., drop monthly partitions after 12 months).
• Implement Automation: Deploy partition creation automation (e.g., pg_partman, custom cron, or IaC) with alerting on failure.
• Validate Pruning: Run EXPLAIN ANALYZE on critical queries to confirm Partitions Pruned in the output.
• Review Constraints: Audit unique indexes and foreign keys for compatibility with partition keys.
• Test Maintenance: Simulate partition drop and verify impact on backup size and WAL generation.
• Monitor Skew: Check partition sizes regularly; investigate significant deviations.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Time-series logs (>50GB)	Range by Timestamp	Enables instant retention, efficient range scans, aligns with backup windows.	Low (Storage optimization)
Multi-tenant SaaS	List by TenantID	Data isolation, per-tenant maintenance, prevents noisy neighbor issues.	Medium (Complexity)
High-write events	Hash by UserID	Even write distribution, prevents WAL hotspots on single nodes.	Low
Reference data (<10GB)	None	Partitioning overhead exceeds benefits; monolithic is faster for small tables.	N/A
Global analytics	None / Materialized Views	Partitioning hinders full-table scans; use OLAP or aggregated tables instead.	Low

Configuration Template

PostgreSQL Range Partitioning with Automation Script:

-- 1. Create Parent Table
CREATE TABLE audit_logs (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL,
    action TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 2. Create Index on Parent (Propagates to partitions)
CREATE INDEX idx_audit_logs_user_id ON audit_logs (user_id);

-- 3. Automation SQL (Run via pg_cron or external scheduler)
-- Creates partition for next month if not exists
DO $$
DECLARE
    partition_date DATE := DATE_TRUNC('month', NOW() + INTERVAL '1 month');
    partition_name TEXT := 'audit_logs_' || TO_CHAR(partition_date, 'YYYY_MM');
    start_date DATE := partition_date;
    end_date DATE := partition_date + INTERVAL '1 month';
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = partition_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF audit_logs FOR VALUES FROM (%L) TO (%L)',
            partition_name, start_date, end_date
        );
        RAISE NOTICE 'Created partition: %', partition_name;
    END IF;
END $$;

-- 4. Retention Automation (Drop partitions older than retention period)
-- Example: Drop partitions older than 90 days
DO $$
DECLARE
    cutoff_date DATE := NOW() - INTERVAL '90 days';
BEGIN
    FOR rec IN 
        SELECT c.relname 
        FROM pg_class c 
        JOIN pg_inherits i ON c.oid = i.inhrelid 
        JOIN pg_class p ON i.inhparent = p.oid 
        WHERE p.relname = 'audit_logs' 
        AND c.relname ~ 'audit_logs_\d{4}_\d{2}'
    LOOP
        -- Extract date from partition name and compare
        -- Implementation depends on naming convention
        -- EXECUTE format('DROP TABLE %I', rec.relname);
    END LOOP;
END $$;

Quick Start Guide

1. Analyze Table Size: Run \dt+ in PostgreSQL or sp_spaceused in SQL Server. If table > 20GB with high write volume, proceed.
2. Select Key: Choose a column that appears in 80%+ of queries and correlates with data age or tenant.
3. Create Parent Table: Execute CREATE TABLE ... PARTITION BY [Strategy] (key). Migrate schema constraints.
4. Seed Partitions: Create initial partitions covering current and future data ranges.
5. Migrate Data: Backfill existing data into partitions or switch traffic to the new table using a dual-write strategy. Verify EXPLAIN output shows pruning.