PostgreSQL Partitioning: Managing Billions of Rows Efficiently

snippet-2.sql

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

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

-- Each partition gets its own indexes. PostgreSQL 11+ propagates these
-- automatically if you define them on the parent *after* partitions exist,
-- but explicit creation gives you control over fill factor and concurrency.
CREATE INDEX CONCURRENTLY idx_events_2026_03_user_created
    ON events_2026_03 (user_id, created_at DESC);

snippet-3.go

func EnsureMonthPartition(ctx context.Context, db *pgxpool.Pool, month time.Time) error {
    start := time.Date(month.Year(), month.Month(), 1, 0, 0, 0, 0, time.UTC)
    end := start.AddDate(0, 1, 0)

    partitionName := fmt.Sprintf("events_%d_%02d", start.Year(), start.Month())
    tableName := "events"

    query := fmt.Sprintf(`
        CREATE TABLE IF NOT EXISTS %s PARTITION OF %s
        FOR VALUES FROM ('%s') TO ('%s')
    `,
        pgx.Identifier{partitionName}.Sanitize(),
        pgx.Identifier{tableName}.Sanitize(),
        start.Format("2006-01-02"),
        end.Format("2006-01-02"),
    )

    _, err := db.Exec(ctx, query)
    if err != nil {
        return fmt.Errorf("creating partition %s: %w", partitionName, err)
    }

    // Create covering index on the new partition
    indexQuery := fmt.Sprintf(`
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_%s_user_created
        ON %s (user_id, created_at DESC)
    `,
        partitionName,
        pgx.Identifier{partitionName}.Sanitize(),
    )

    _, err = db.Exec(ctx, indexQuery)
    return err
}

snippet-4.sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT user_id, count(*) AS event_count
FROM   events
WHERE  created_at >= '2026-03-01'
  AND  created_at <  '2026-04-01'
  AND  event_type = 'purchase'
GROUP  BY user_id
ORDER  BY event_count DESC
LIMIT  100;

snippet-5.sql

-- Detach cleanly: the partition becomes a standalone table, data intact.
-- Useful if you need to archive to cold storage before deletion.
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;

-- Then export to S3 or another store via COPY, if needed:
COPY events_2024_01 TO '/tmp/events_2024_01.csv' WITH (FORMAT csv, HEADER);

-- Finally drop it. This is instant regardless of row count.
DROP TABLE events_2024_01;

snippet-6.sql

SELECT
    child.relname                                  AS partition,
    pg_size_pretty(pg_relation_size(child.oid))    AS table_size,
    pg_size_pretty(pg_indexes_size(child.oid))     AS index_size,
    pg_stat_get_live_tuples(child.oid)             AS live_rows,
    pg_stat_get_dead_tuples(child.oid)             AS dead_rows
FROM   pg_inherits
JOIN   pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN   pg_class child  ON pg_inherits.inhrelid  = child.oid
WHERE  parent.relname = 'events'
ORDER  BY pg_relation_size(child.oid) DESC;

snippet-7.txt

# pgbouncer.ini — transaction mode is critical for partitioned workloads
# to avoid per-partition planning overhead accumulating across long sessions.
[databases]
app_db = host=127.0.0.1 port=5432 dbname=production

[pgbouncer]
pool_mode            = transaction
max_client_conn      = 1000
default_pool_size    = 25
reserve_pool_size    = 5
reserve_pool_timeout = 3
server_idle_timeout  = 600
log_connections      = 0

snippet-8.txt

# Allow parallel workers to scan different partitions simultaneously.
max_parallel_workers_per_gather = 4
max_parallel_workers            = 8
enable_partition_pruning        = on

# With many partitions, the planner spends time evaluating each one.
# Raise this if you have 500+ partitions and see slow planning times.
# Default is 100.
constraint_exclusion = partition

snippet.sql

CREATE TABLE events (
    id          BIGSERIAL,
    user_id     BIGINT       NOT NULL,
    event_type  TEXT         NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Indexes must be created on each partition, but you can template them.
-- Create a default partition to catch rows that don't fit any range.
CREATE TABLE events_default PARTITION OF events DEFAULT;