PostgreSQL Table Partitioning Strategies for Billion-Row Tables

When your PostgreSQL table hits a billion rows, every operation becomes expensive. Sequential scans that once took seconds now take minutes. VACUUM can't keep up with dead tuple accumulation. Index bloat pushes your working set beyond available memory. We hit this wall on our analytics events table — 1.2 billion rows, growing at 15 million/day, with queries degrading 3% weekly.

Partitioning isn't optional at this scale. It's the difference between a database that performs predictably and one that slowly drowns. Here's how we implemented partitioning on RDS PostgreSQL for three different billion-row tables, each requiring a different strategy.

The Problem: Monolithic Tables at Scale

Our three largest tables were all exhibiting the same symptoms:

Table	Row Count	Size	Daily Growth	Avg Query Time	VACUUM Duration
analytics_events	1.2B	890GB	15M rows	4.2s → 8.1s	6+ hours
audit_logs	2.1B	1.4TB	8M rows	2.8s → 5.4s	8+ hours
iot_readings	3.4B	2.1TB	45M rows	1.1s → 3.8s	12+ hours

The degradation pattern was consistent: as tables grew, index scans started hitting increasingly cold buffer cache pages, VACUUM couldn't complete before the next run was needed, and query planner estimates became increasingly inaccurate with stale statistics on multi-billion-row tables.

Strategy 1: Range Partitioning by Time (analytics_events)

Range partitioning on timestamp is the natural choice for time-series append-only data. 95% of queries include a time range predicate, making partition pruning highly effective:

-- Create partitioned table structure
CREATE TABLE analytics_events (
    id              BIGINT GENERATED ALWAYS AS IDENTITY,
    event_time      TIMESTAMPTZ NOT NULL,
    tenant_id       UUID NOT NULL,
    event_type      TEXT NOT NULL,
    properties      JSONB,
    session_id      UUID,
    user_id         UUID,
    page_url        TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (event_time);

-- Monthly partitions for current + future months
CREATE TABLE analytics_events_2026_01
    PARTITION OF analytics_events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

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

-- ... (automated via pg_partman)

-- Partition-local indexes (created on each partition independently)
CREATE INDEX ON analytics_events (tenant_id, event_time DESC);
CREATE INDEX ON analytics_events (event_type, event_time DESC);
CREATE INDEX ON analytics_events (session_id) WHERE session_id IS NOT NULL;

Automating Partition Management with pg_partman

-- Install and configure pg_partman
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.analytics_events',
    p_control := 'event_time',
    p_type := 'range',
    p_interval := '1 month',
    p_premake := 3,               -- Create 3 months ahead
    p_start_partition := '2024-01-01'
);

-- Configure retention: automatically drop partitions older than 18 months
UPDATE partman.part_config
SET retention = '18 months',
    retention_keep_table = false,
    retention_keep_index = false
WHERE parent_table = 'public.analytics_events';

-- Schedule maintenance (runs via pg_cron on RDS)
SELECT cron.schedule(
    'partman_maintenance',
    '0 3 * * *',  -- Daily at 3 AM
    $$SELECT partman.run_maintenance('public.analytics_events')$$
);

Performance After Range Partitioning

Query Pattern	Before (1 table)	After (monthly partitions)	Improvement
Last 24h, single tenant	4.2s	120ms	35x
Last 7d, aggregation	18s	1.8s	10x
Last 30d, full scan	82s	8.4s	10x
VACUUM (per partition)	6+ hours	12 min/partition	Parallelizable
Index rebuild	4.5 hours	8 min/partition	Non-blocking rotation

Strategy 2: List Partitioning by Tenant (audit_logs)

Audit logs are queried exclusively by tenant. A compliance request means "show me all actions for tenant X in date range Y." List partitioning by tenant ensures these queries scan only the relevant partition:

-- List partitioning for multi-tenant audit logs
CREATE TABLE audit_logs (
    id              BIGINT GENERATED ALWAYS AS IDENTITY,
    tenant_id       TEXT NOT NULL,
    actor_id        UUID NOT NULL,
    action          TEXT NOT NULL,
    resource_type   TEXT NOT NULL,
    resource_id     TEXT NOT NULL,
    changes         JSONB,
    ip_address      INET,
    performed_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY LIST (tenant_id);

-- High-volume tenants get dedicated partitions
CREATE TABLE audit_logs_tenant_acme
    PARTITION OF audit_logs
    FOR VALUES IN ('acme-corp');

CREATE TABLE audit_logs_tenant_globex
    PARTITION OF audit_logs
    FOR VALUES IN ('globex-inc');

-- Small tenants share a "default" partition with sub-partitioning
CREATE TABLE audit_logs_shared
    PARTITION OF audit_logs
    DEFAULT
    PARTITION BY RANGE (performed_at);

-- Sub-partition the shared partition by month
CREATE TABLE audit_logs_shared_2026_06
    PARTITION OF audit_logs_shared
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

This hybrid approach gives large tenants partition-level isolation (faster VACUUM, targeted backups, independent retention) while keeping operational overhead manageable for hundreds of smaller tenants.

Strategy 3: Hash Partitioning for Even Distribution (iot_readings)

IoT readings are queried by device_id across arbitrary time ranges. Neither time nor device alone provides ideal distribution, so we use hash partitioning on device_id for even data spread:

-- Hash partitioning for uniform distribution
CREATE TABLE iot_readings (
    device_id       UUID NOT NULL,
    reading_time    TIMESTAMPTZ NOT NULL,
    metric_name     TEXT NOT NULL,
    value           DOUBLE PRECISION NOT NULL,
    quality_flag    SMALLINT DEFAULT 0
) PARTITION BY HASH (device_id);

-- 32 hash partitions for balanced distribution
CREATE TABLE iot_readings_p00 PARTITION OF iot_readings FOR VALUES WITH (MODULUS 32, REMAINDER 0);
CREATE TABLE iot_readings_p01 PARTITION OF iot_readings FOR VALUES WITH (MODULUS 32, REMAINDER 1);
-- ... through p31

-- Composite index per partition
CREATE INDEX ON iot_readings (device_id, reading_time DESC);
CREATE INDEX ON iot_readings (reading_time DESC, device_id)
    WHERE reading_time > NOW() - INTERVAL '7 days';

Hash Partitioning Tradeoffs

Hash partitioning guarantees even distribution but sacrifices one advantage: you can't prune by range on the partition key. Queries for "all devices in region X" must scan all 32 partitions. We mitigate this with composite indexes that make per-partition scans efficient.

Migration: Zero-Downtime Partition Conversion

Converting an existing billion-row table to partitioned format without downtime requires careful orchestration:

-- Step 1: Create new partitioned table with identical schema
CREATE TABLE analytics_events_new (...) PARTITION BY RANGE (event_time);

-- Step 2: Create partitions covering all existing data
-- Step 3: Set up logical replication from old → new
CREATE PUBLICATION events_migration FOR TABLE analytics_events;
-- (On new table's logical replica)
CREATE SUBSCRIPTION events_migration_sub
    CONNECTION 'dbname=prod host=...'
    PUBLICATION events_migration;

-- Step 4: Wait for sync, then perform atomic rename
BEGIN;
  ALTER TABLE analytics_events RENAME TO analytics_events_old;
  ALTER TABLE analytics_events_new RENAME TO analytics_events;
COMMIT;
-- Total lock time: <100ms

Maintenance Automation

// Automated partition health monitoring
interface PartitionHealth {
  tableName: string;
  partitionCount: number;
  largestPartition: { name: string; size: string; rows: number };
  vacuumLag: { partition: string; deadTuples: number };
  upcomingPartitions: number; // Pre-created future partitions
}

async function checkPartitionHealth(pool: Pool): Promise<PartitionHealth[]> {
  const query = `
    SELECT
      parent.relname AS parent_table,
      child.relname AS partition_name,
      pg_size_pretty(pg_total_relation_size(child.oid)) AS size,
      pg_stat_get_live_tuples(child.oid) AS live_tuples,
      pg_stat_get_dead_tuples(child.oid) AS dead_tuples,
      pg_stat_get_last_autovacuum_time(child.oid) AS last_vacuum
    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 IN ('analytics_events', 'audit_logs', 'iot_readings')
    ORDER BY parent.relname, child.relname;
  `;

  const result = await pool.query(query);
  return aggregateHealthMetrics(result.rows);
}

Performance Summary

Metric	Before Partitioning	After Partitioning	Method
analytics_events query P95	8.1s	180ms	Range (monthly)
audit_logs compliance export	45 min	90s	List (tenant)
iot_readings device history	3.8s	400ms	Hash (device_id)
VACUUM total duration	26 hours	45 min (parallelized)	All
Index rebuild time	12 hours	20 min (per partition)	All
Storage reclaim (DROP)	Hours (DELETE)	Instant (DROP PARTITION)	Range/List

Key Takeaways

1. Match partition strategy to query pattern — range for time-series, list for tenant isolation, hash for even distribution.
2. pg_partman automates the tedious parts — partition creation, retention, and maintenance scheduling.
3. VACUUM parallelization is the hidden win — instead of one 6-hour job, you get 18 parallel 12-minute jobs.
4. Plan the migration as a separate project — converting live tables requires logical replication and careful cutover orchestration.
5. 32 partitions is a good hash starting point — enough for parallelism without excessive planning overhead. You can't change the modulus later without rebuilding.

Partitioning transforms PostgreSQL from "we need to migrate to a distributed database" into "PostgreSQL handles this fine for the next 3 years." That buys you time to make architectural decisions from a position of stability rather than crisis.