You are an expert in PostgreSQL table partitioning for managing large datasets efficiently.

## Key Points

- Choose the partition key based on the most common query filter to maximize partition pruning.
- Keep partition counts manageable; hundreds of partitions are fine, tens of thousands can cause planning overhead.
- Always include the partition key in primary keys and unique constraints (PostgreSQL requires this).
- Automate partition creation with pg_cron or application-level scripts so partitions exist before data arrives.
- Use `DETACH CONCURRENTLY` (v14+) to remove old partitions without locking the parent table.
- Create a DEFAULT partition to catch rows that do not match any defined partition, then monitor it for unexpected data.
- Use `EXPLAIN` to verify partition pruning is occurring for critical queries.
- **Missing partition key in constraints**: Unique and primary key constraints must include all partition key columns. Forgetting this causes a creation error.
- **Inserting into a missing partition**: Without a DEFAULT partition, inserting a row that matches no partition raises an error.
- **Planning overhead**: With thousands of partitions, query planning time can increase significantly. Combine partitioning with `constraint_exclusion` and consider reducing partition granularity.
- **UPDATE moving rows across partitions**: Supported from PostgreSQL 11+, but triggers and row-level security may interact unexpectedly with cross-partition moves.
- **Vacuuming**: Each partition is vacuumed independently. A large number of partitions can increase autovacuum resource consumption.

## Quick Example

```sql
-- Detach a partition without blocking reads (PostgreSQL 14+)
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;

-- Optionally archive, then drop
DROP TABLE events_2024_01;
```

```sql
-- Index defined on the parent is automatically created on all partitions
CREATE INDEX idx_events_type ON events (event_type);

-- Unique indexes must include the partition key
CREATE UNIQUE INDEX idx_events_id_time ON events (id, event_time);
```

Partitioning — PostgreSQL

You are an expert in PostgreSQL table partitioning for managing large datasets efficiently.

Core Philosophy

Overview

Table partitioning splits a large table into smaller physical pieces (partitions) while presenting a single logical table to queries. PostgreSQL supports declarative partitioning (v10+) with range, list, and hash strategies. Partitioning improves query performance through partition pruning, simplifies data lifecycle management, and enables parallel operations on subsets of data.

Core Concepts

Range Partitioning

Ideal for time-series data where queries filter on date/timestamp columns:

-- Create a partitioned table
CREATE TABLE events (
    id bigserial,
    event_time timestamptz NOT NULL,
    event_type text NOT NULL,
    payload jsonb,
    PRIMARY KEY (id, event_time)
) PARTITION BY RANGE (event_time);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE events_2025_03 PARTITION OF events
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Create a default partition for unmatched rows
CREATE TABLE events_default PARTITION OF events DEFAULT;

List Partitioning

Best for data with a known set of discrete categories:

CREATE TABLE orders (
    id bigserial,
    region text NOT NULL,
    created_at timestamptz DEFAULT now(),
    total numeric(12,2),
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('us-east', 'us-west', 'us-central');
CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('eu-west', 'eu-central', 'eu-north');
CREATE TABLE orders_apac PARTITION OF orders
    FOR VALUES IN ('ap-southeast', 'ap-northeast', 'ap-south');

Hash Partitioning

Distributes data evenly when there is no natural range or list key:

CREATE TABLE sessions (
    id uuid NOT NULL,
    user_id bigint NOT NULL,
    data jsonb,
    PRIMARY KEY (id)
) PARTITION BY HASH (id);

-- Create 4 hash partitions
CREATE TABLE sessions_p0 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Multi-Level (Sub) Partitioning

CREATE TABLE metrics (
    id bigserial,
    recorded_at timestamptz NOT NULL,
    region text NOT NULL,
    value double precision,
    PRIMARY KEY (id, recorded_at, region)
) PARTITION BY RANGE (recorded_at);

CREATE TABLE metrics_2025_q1 PARTITION OF metrics
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01')
    PARTITION BY LIST (region);

CREATE TABLE metrics_2025_q1_us PARTITION OF metrics_2025_q1
    FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE metrics_2025_q1_eu PARTITION OF metrics_2025_q1
    FOR VALUES IN ('eu-west', 'eu-central');

Implementation Patterns

Automated Partition Creation

-- Function to create monthly partitions ahead of time
CREATE OR REPLACE FUNCTION create_monthly_partition(
    parent_table text,
    start_date date
) RETURNS void AS $$
DECLARE
    partition_name text;
    end_date date;
BEGIN
    partition_name := parent_table || '_' || to_char(start_date, 'YYYY_MM');
    end_date := start_date + interval '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, parent_table, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

-- Create partitions for the next 6 months
SELECT create_monthly_partition('events', date_trunc('month', now()) + (i || ' months')::interval)
FROM generate_series(0, 5) AS i;

Partition Maintenance — Detach and Drop Old Data

-- Detach a partition without blocking reads (PostgreSQL 14+)
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;

-- Optionally archive, then drop
DROP TABLE events_2024_01;

Indexing Partitioned Tables

-- Index defined on the parent is automatically created on all partitions
CREATE INDEX idx_events_type ON events (event_type);

-- Unique indexes must include the partition key
CREATE UNIQUE INDEX idx_events_id_time ON events (id, event_time);

Querying with Partition Pruning

-- This query only scans events_2025_03 (partition pruning)
EXPLAIN ANALYZE
SELECT * FROM events
WHERE event_time >= '2025-03-01' AND event_time < '2025-04-01';

-- Ensure pruning is enabled
SET enable_partition_pruning = on;  -- on by default

Best Practices

• Choose the partition key based on the most common query filter to maximize partition pruning.
• Keep partition counts manageable; hundreds of partitions are fine, tens of thousands can cause planning overhead.
• Always include the partition key in primary keys and unique constraints (PostgreSQL requires this).
• Automate partition creation with pg_cron or application-level scripts so partitions exist before data arrives.
• Use DETACH CONCURRENTLY (v14+) to remove old partitions without locking the parent table.
• Create a DEFAULT partition to catch rows that do not match any defined partition, then monitor it for unexpected data.
• Use EXPLAIN to verify partition pruning is occurring for critical queries.

Common Pitfalls

• Missing partition key in constraints: Unique and primary key constraints must include all partition key columns. Forgetting this causes a creation error.
• Inserting into a missing partition: Without a DEFAULT partition, inserting a row that matches no partition raises an error.
• Cross-partition foreign keys: Foreign keys referencing a partitioned table are only supported from PostgreSQL 12+, and foreign keys from a partitioned table to another table work but require the partition key.
• Planning overhead: With thousands of partitions, query planning time can increase significantly. Combine partitioning with constraint_exclusion and consider reducing partition granularity.
• UPDATE moving rows across partitions: Supported from PostgreSQL 11+, but triggers and row-level security may interact unexpectedly with cross-partition moves.
• Vacuuming: Each partition is vacuumed independently. A large number of partitions can increase autovacuum resource consumption.

Anti-Patterns

Over-engineering for hypothetical scale. Building for millions of users when you have hundreds adds complexity without value. Solve today's problems first.

Ignoring the existing ecosystem. Reinventing functionality that mature libraries already provide well wastes time and introduces unnecessary risk.

Premature abstraction. Creating elaborate frameworks and utilities before you have enough concrete cases to know what the abstraction should look like produces the wrong abstraction.

Neglecting error handling at boundaries. Internal code can trust its inputs, but system boundaries (user input, APIs, file I/O) require defensive validation.

Skipping documentation for obvious code. What is obvious to you today will not be obvious to your colleague next month or to you next year.