PostgreSQL Partitioning: When Your Table Crosses 100M Rows

When PostgreSQL Starts Slowing Down

PostgreSQL handles tables up to 10-20 million rows beautifully. Indexes work, VACUUM keeps up, queries fly. But when a table crosses 100 million rows — problems begin.

The symptoms are familiar to anyone who's worked with a growing project: VACUUM can't keep up with INSERTs and the table bloats, indexes grow huge and no longer fit in shared_buffers, DELETE of old data locks the table for minutes, and a simple COUNT(*) with a date filter takes 30+ seconds.

Partitioning is a solution built into PostgreSQL. The table is physically split into parts (partitions), each being a separate table on disk. Queries automatically target only the relevant partitions (partition pruning), VACUUM works on small tables instead of one giant one, and deleting old data is an instant DROP partition instead of DELETE of millions of rows.

In my projects, partitioning was applied to three tables: analytics events (500M+ rows), API request logs (200M+), and change history (audit log, 150M+). Here's how it works in practice.

Types of Partitioning

Range Partitioning

The most common type. Data is split by a range of values — usually by date.

CREATE TABLE events (
    id          bigserial,
    event_time  timestamptz NOT NULL,
    user_id     bigint NOT NULL,
    event_type  text NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (event_time);

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');
CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE events_default PARTITION OF events DEFAULT;

A query with a date filter automatically targets only the relevant partition:

EXPLAIN SELECT count(*) FROM events
WHERE event_time >= '2026-05-01' AND event_time < '2026-06-01';
-- Only events_2026_05 is scanned

List Partitioning

Data is split by specific values — for example, by region.

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

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('DE', 'FR', 'IT', 'ES', 'NL', 'PL');
CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE orders_asia PARTITION OF orders
    FOR VALUES IN ('JP', 'KR', 'CN', 'IN', 'SG');
CREATE TABLE orders_other PARTITION OF orders DEFAULT;

Hash Partitioning

Data is distributed by hash value — evenly across N partitions.

CREATE TABLE user_activities (
    id          bigserial,
    user_id     bigint NOT NULL,
    action      text NOT NULL,
    metadata    jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY HASH (user_id);

CREATE TABLE user_activities_0 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);
-- ... through 7
CREATE TABLE user_activities_7 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 8, REMAINDER 7);

Hash partitioning doesn't support range pruning — only exact match: WHERE user_id = 12345. Used when there's no natural key for range/list but you need to split a large table for parallel VACUUM.

Composite Partitioning (Sub-partitioning)

CREATE TABLE logs (
    id          bigserial,
    log_time    timestamptz NOT NULL,
    level       text NOT NULL,
    service     text NOT NULL,
    message     text
) PARTITION BY RANGE (log_time);

CREATE TABLE logs_2026_05 PARTITION OF logs
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01')
    PARTITION BY LIST (service);

CREATE TABLE logs_2026_05_api PARTITION OF logs_2026_05
    FOR VALUES IN ('api');
CREATE TABLE logs_2026_05_worker PARTITION OF logs_2026_05
    FOR VALUES IN ('worker');
CREATE TABLE logs_2026_05_web PARTITION OF logs_2026_05
    FOR VALUES IN ('web');
CREATE TABLE logs_2026_05_other PARTITION OF logs_2026_05 DEFAULT;

Two-level partitioning: query WHERE log_time >= '2026-05-01' AND service = 'api' hits exactly one table — logs_2026_05_api.

Indexes on Partitioned Tables

-- Index is automatically created on EVERY partition
CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_type ON events (event_type);

-- Unique index MUST include partition key
CREATE UNIQUE INDEX idx_events_id_time ON events (id, event_time);

-- This does NOT work:
-- CREATE UNIQUE INDEX idx_events_id ON events (id);
-- ERROR: unique constraint must include all partitioning columns

Important constraint: a unique index on a partitioned table must include the partition key. For most scenarios this isn't a problem: the primary key becomes (id, partition_key).

Automatic Partition Creation

pg_partman

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'event_time',
    p_type := 'native',
    p_interval := 'monthly',
    p_premake := 3
);

-- Auto-maintenance (call via cron every hour)
SELECT partman.run_maintenance();

-- Retention: auto-drop partitions older than 12 months
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

Manual Creation via SQL Function

CREATE OR REPLACE FUNCTION create_monthly_partitions(
    table_name text,
    months_ahead int DEFAULT 3
) RETURNS void AS $$
DECLARE
    start_date date;
    end_date date;
    partition_name text;
    i int;
BEGIN
    FOR i IN 0..months_ahead LOOP
        start_date := date_trunc('month', current_date + (i || ' months')::interval);
        end_date := start_date + '1 month'::interval;
        partition_name := format('%s_%s', table_name, to_char(start_date, 'YYYY_MM'));

        IF NOT EXISTS (
            SELECT 1 FROM pg_class WHERE relname = partition_name
        ) THEN
            EXECUTE format(
                'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
                partition_name, table_name, start_date, end_date
            );
            RAISE NOTICE 'Created partition: %', partition_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Deleting Old Data: DROP vs DELETE

The main partitioning advantage for retention — instant data removal.

DELETE: Slow and Painful

DELETE FROM events WHERE event_time < now() - interval '1 year';
-- Time: 30-60 minutes
-- Locks: row-level locks for entire duration
-- WAL: generates gigabytes of WAL
-- VACUUM: needs VACUUM FULL after (another 30-60 min)

DROP PARTITION: Instant

DROP TABLE events_2025_01;
-- Time: < 1 second
-- Locks: minimal (DDL lock for a fraction of a second)
-- WAL: almost none
-- VACUUM: not needed

Or safely with DETACH:

ALTER TABLE events DETACH PARTITION events_2025_01 CONCURRENTLY;
-- Then later:
DROP TABLE events_2025_01;

DETACH CONCURRENTLY (PostgreSQL 14+) doesn't block reads from the main table.

VACUUM and Maintenance

On a monolithic 500M-row table, VACUUM can run for hours. On a partitioned table — each partition is vacuumed separately:

-- VACUUM one partition (milliseconds to seconds)
VACUUM (VERBOSE) events_2026_05;

Per-partition autovacuum tuning

-- "Hot" current partition: aggressive autovacuum
ALTER TABLE events_2026_05 SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 2
);

-- "Cold" archive partition: lazy autovacuum
ALTER TABLE events_2025_01 SET (
    autovacuum_enabled = false
);

Partition Pruning and Query Optimization

How Partition Pruning Works

SET enable_partition_pruning = on;  -- default

EXPLAIN (ANALYZE) SELECT * FROM events
WHERE event_time >= '2026-05-01' AND event_time < '2026-06-01'
  AND event_type = 'purchase';
-- Only events_2026_05 participates. Other 11+ partitions aren't even opened.

When Pruning Does NOT Work

-- 1. Function on partition key
SELECT * FROM events WHERE date_trunc('month', event_time) = '2026-05-01';
-- NO pruning. Rewrite as range:
SELECT * FROM events
WHERE event_time >= '2026-05-01' AND event_time < '2026-06-01';

-- 2. Query without partition key
SELECT * FROM events WHERE user_id = 12345;
-- Scans ALL partitions. Always add partition key filter:
SELECT * FROM events
WHERE user_id = 12345 AND event_time >= now() - interval '30 days';

Integration with Rails

Migration

class CreatePartitionedEvents < ActiveRecord::Migration[8.0]
  def up
    execute <<-SQL
      CREATE TABLE events (
        id          bigserial,
        event_time  timestamptz NOT NULL,
        user_id     bigint NOT NULL,
        event_type  text NOT NULL,
        payload     jsonb,
        created_at  timestamptz NOT NULL DEFAULT now(),
        PRIMARY KEY (id, event_time)
      ) PARTITION BY RANGE (event_time);

      CREATE TABLE events_2026_05 PARTITION OF events
        FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
      CREATE TABLE events_2026_06 PARTITION OF events
        FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
      CREATE TABLE events_default PARTITION OF events DEFAULT;

      CREATE INDEX idx_events_user_id ON events (user_id);
      CREATE INDEX idx_events_type ON events (event_type);
    SQL
  end

  def down
    drop_table :events, force: :cascade
  end
end

Model with Scopes

class Event < ApplicationRecord
  self.primary_key = [:id, :event_time]

  scope :in_month, ->(date) {
    start_of_month = date.beginning_of_month
    end_of_month = date.end_of_month + 1.day
    where(event_time: start_of_month...end_of_month)
  }

  scope :recent, -> { where(event_time: 30.days.ago..) }
  scope :by_type, ->(type) { where(event_type: type) }
end

# Partition pruning works automatically
Event.in_month(Date.today).by_type('purchase').count

Rake Task for Partition Management

namespace :partitions do
  desc "Create monthly partitions for next N months"
  task create: :environment do
    months_ahead = (ENV['MONTHS'] || 3).to_i
    table = ENV['TABLE'] || 'events'

    months_ahead.times do |i|
      date = Date.today.beginning_of_month + (i + 1).months
      partition_name = "#{table}_#{date.strftime('%Y_%m')}"
      start_date = date.strftime('%Y-%m-%d')
      end_date = (date + 1.month).strftime('%Y-%m-%d')

      ActiveRecord::Base.connection.execute(<<-SQL)
        DO $$ BEGIN
          IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = '#{partition_name}') THEN
            EXECUTE format(
              'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
              '#{partition_name}', '#{table}', '#{start_date}', '#{end_date}'
            );
          END IF;
        END $$;
      SQL
      puts "#{partition_name}: #{start_date} -> #{end_date}"
    end
  end

  desc "Drop partitions older than N months"
  task drop_old: :environment do
    months = (ENV['MONTHS'] || 12).to_i
    table = ENV['TABLE'] || 'events'
    cutoff = Date.today.beginning_of_month - months.months

    partitions = ActiveRecord::Base.connection.select_values(
      "SELECT tablename FROM pg_tables WHERE tablename LIKE '#{table}_%' AND tablename != '#{table}_default'"
    )

    partitions.each do |part|
      match = part.match(/_(\d{4})_(\d{2})$/)
      next unless match
      part_date = Date.new(match[1].to_i, match[2].to_i, 1)
      if part_date < cutoff
        puts "Dropping #{part}"
        ActiveRecord::Base.connection.execute("DROP TABLE #{part}")
      end
    end
  end
end

Benchmarks: With and Without Partitioning

Table: 200M rows, 50GB, PostgreSQL 16, 8 cores, 32GB RAM.

Common Pitfalls

1. Foreign Keys to Partitioned Tables

PostgreSQL < 17 doesn't support foreign keys referencing partitioned tables. Use application-level validation or triggers. PostgreSQL 17+ supports this natively.

2. Too Many Partitions

Daily partitions × 3 years = 1095 partitions. Planning time grows linearly. Recommendation: no more than 100-200 partitions per table.

3. Queries Without Partition Key

-- Scans ALL partitions
SELECT * FROM events WHERE user_id = 12345;

-- Always add partition key filter
SELECT * FROM events
WHERE user_id = 12345 AND event_time >= now() - interval '30 days';

Final Checklist

Before Partitioning

• [ ] Table > 50M rows or growing > 5M/month
• [ ] Identify partition key: usually timestamp
• [ ] Determine granularity: month (most cases), week (very active), year (slow growth)
• [ ] Verify queries: 80%+ should filter by partition key

Creation

• [ ] PARTITION BY RANGE for temporal data
• [ ] DEFAULT partition — mandatory to avoid data loss
• [ ] PRIMARY KEY includes partition key
• [ ] Indexes on parent table — auto-created on all partitions

Automation

• [ ] pg_partman or rake task for pre-creating partitions
• [ ] Cron: daily creation of new partitions (premake 3 months)
• [ ] Retention: automatic DROP of old partitions
• [ ] Monitoring: partition count, sizes, dead tuples

Migration

• [ ] Create new partitioned table
• [ ] Transfer data in batches (100K-500K rows at a time)
• [ ] Rename tables in a transaction
• [ ] Verify EXPLAIN — pruning works
• [ ] Drop old table one week after migration

Production Monitoring

• [ ] Partition sizes: shouldn't grow unevenly
• [ ] Dead tuples: hot partition needs more frequent vacuum
• [ ] Planning time: if > 10ms — too many partitions
• [ ] Partition pruning: verify EXPLAIN for key queries