Rails Postgres Partitioning: Managing Billion-Row Tables with pg_partman and Native Postgres Partitions

A logistics client called me last Thursday because their shipment_events table had crossed 1.4 billion rows and their nightly VACUUM was now running for eleven hours, sometimes spilling into the morning traffic. Queries that used to take 40ms were now taking 3 seconds, autovacuum could not keep up with the write volume, and the DBA had started muttering about buying a bigger server. I told them what I have told a dozen teams in the last three years: you do not need a bigger server, you need Rails Postgres partitioning. Two weeks later their same table was chunked into 60 monthly partitions, queries were back to 40ms, VACUUM finished in under twenty minutes per partition, and dropping a year of old data was now a single DETACH PARTITION instead of a two-day DELETE.

After nineteen years of Rails I have seen every generation of “how do we handle huge tables” advice cycle through: sharding gems, Octopus, moving hot data to Cassandra, denormalising into materialized views. Native Rails Postgres partitioning — declarative partitioning shipped in Postgres 10 and matured through 16 — makes almost all of it unnecessary for the tables that actually cause pain: events, logs, audit trails, telemetry, time-series measurements, anything append-heavy with a clean time or tenant dimension. This post is the exact recipe I use to introduce partitioning into an existing Rails app without downtime.

What Rails Postgres Partitioning Actually Solves

Rails Postgres partitioning is native Postgres declarative partitioning surfaced through Rails migrations and models. You take one enormous logical table and split it into many smaller physical tables (partitions) that Postgres treats as one. A SELECT ... WHERE occurred_at BETWEEN ... only reads the partitions whose ranges overlap the filter — partition pruning — so a query that used to scan 1.4 billion rows now scans 20 million. Vacuum, analyze, index rebuilds, and bulk deletion all operate one partition at a time, so the maintenance windows shrink from hours to minutes.

The three problems Rails Postgres partitioning fixes on real production workloads:

Vacuum churn. Autovacuum on a 500GB table is a nightmare because it has to walk the entire heap to find dead tuples. On a partitioned table, only the current partition sees heavy writes, so only that partition needs frequent vacuuming. Old partitions are read-only and effectively free to maintain.

Index bloat and rebuild time. A BTREE index on a billion-row table takes forever to REINDEX CONCURRENTLY. Per-partition indexes are 30-50x smaller and rebuild in seconds. When an index goes bloated after a migration, you fix the affected partition, not the whole table.

Deletion at scale. Retention is a real requirement in most SaaS — GDPR, cost, or just common sense. DELETE FROM events WHERE created_at < NOW() - INTERVAL '2 years' on a huge table generates gigabytes of WAL, blocks autovacuum, and takes hours. ALTER TABLE events DETACH PARTITION events_2024_01 is instant and generates almost no WAL.

Partitioning does not fix hot-partition problems, it does not turn a slow query into a fast one if you are not filtering by the partition key, and it does not replace good indexing. It is a maintenance and scaling tool, not a magic performance dust.

When to Reach for Rails Postgres Partitioning

I use a simple test: is the table over 100GB, is it append-heavy, and does every important query filter by a natural time or tenant dimension? If yes, partition it. If the table is small, or writes are spread across all rows, or your queries have no natural filter dimension, partitioning will make things worse, not better.

Good candidates I have partitioned in the last year:

• shipment_events (range on occurred_at, monthly)
• webhook_deliveries (range on created_at, weekly)
• audit_logs (range on created_at, monthly) — pairs nicely with the audit tooling I covered in Rails Audit Logging with PaperTrail
• llm_call_records (range on created_at, weekly) — where I put the per-tenant cost data from Rails LLM Cost Tracking
• iot_readings (range on measured_at, daily)
• analytics_page_views (list on tenant_id, one partition per big tenant)

Bad candidates: users, orders, products, most reference-data tables. If in doubt, run SELECT pg_size_pretty(pg_relation_size('your_table')) — under 50GB, do not bother yet.

Setting Up Native Declarative Partitioning in Rails

You cannot make an existing Postgres table partitioned in place — you have to create a new partitioned table and move data into it. Rails does not have a first-class DSL for partitioning yet, so I write the DDL directly in a migration. This is fine; it is boring SQL and it works everywhere.

class CreatePartitionedShipmentEvents < ActiveRecord::Migration[8.0]
  def up
    execute <<~SQL
      CREATE TABLE shipment_events_new (
        id BIGSERIAL,
        shipment_id BIGINT NOT NULL,
        event_type TEXT NOT NULL,
        payload JSONB NOT NULL DEFAULT '{}'::jsonb,
        occurred_at TIMESTAMPTZ NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (id, occurred_at)
      ) PARTITION BY RANGE (occurred_at);

      CREATE INDEX idx_shipment_events_new_shipment
        ON shipment_events_new (shipment_id, occurred_at DESC);
      CREATE INDEX idx_shipment_events_new_type
        ON shipment_events_new (event_type, occurred_at DESC);
    SQL
  end

  def down
    execute "DROP TABLE shipment_events_new"
  end
end

Two things people trip on. First, the primary key on a partitioned table must include the partition key. PRIMARY KEY (id, occurred_at) is not optional — Postgres cannot enforce global uniqueness across partitions without indexing the partition key. Second, indexes declared on the parent table are automatically created on every partition, current and future. This is what you want; it means you never have to remember to add an index to a new partition.

Now you need actual partitions. You can create them by hand:

class CreateShipmentEventPartitions < ActiveRecord::Migration[8.0]
  def up
    (Date.new(2024, 1, 1)..Date.new(2026, 12, 1)).step(30) do |start|
      month = start.strftime("%Y_%m")
      finish = start.next_month
      execute <<~SQL
        CREATE TABLE shipment_events_#{month}
          PARTITION OF shipment_events_new
          FOR VALUES FROM ('#{start.iso8601}') TO ('#{finish.iso8601}');
      SQL
    end
  end
end

This works, but it is a maintenance burden — someone has to remember to run a migration every month to add the next partition, or writes will start failing when occurred_at falls outside every existing range. That is why pg_partman exists.

Automating Rails Postgres Partitioning with pg_partman

pg_partman is a Postgres extension that automates partition creation, retention, and maintenance. You register a parent table with pg_partman once, tell it “keep six months of future partitions and drop anything older than three years,” and a background maintenance job takes care of the rest. It is the piece I install on every Rails Postgres partitioning project after the first week.

Install it as a normal Postgres extension (on RDS it is on the extension whitelist; on self-hosted just apt install postgresql-16-partman). Then in a Rails migration:

class EnablePgPartman < ActiveRecord::Migration[8.0]
  def up
    execute "CREATE SCHEMA IF NOT EXISTS partman"
    execute "CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman"
  end
end

Register the parent table with pg_partman. This one function call is the whole configuration:

class RegisterShipmentEventsWithPartman < ActiveRecord::Migration[8.0]
  def up
    execute <<~SQL
      SELECT partman.create_parent(
        p_parent_table => 'public.shipment_events_new',
        p_control      => 'occurred_at',
        p_type         => 'native',
        p_interval     => '1 month',
        p_premake      => 6
      );

      UPDATE partman.part_config
      SET retention                = '3 years',
          retention_keep_table     = false,
          infinite_time_partitions = true
      WHERE parent_table = 'public.shipment_events_new';
    SQL
  end
end

p_premake => 6 keeps six months of empty partitions ahead of today so a burst of “future-dated” writes never hits a gap. retention => '3 years' marks anything older for automatic dropping. retention_keep_table => false means dropped partitions are removed entirely — flip to true if you want to detach and archive to S3 first.

pg_partman has a maintenance function that must run periodically. In every Rails Postgres partitioning setup I ship this as a Solid Queue recurring job:

class PgPartmanMaintenanceJob < ApplicationJob
  queue_as :maintenance

  def perform
    ActiveRecord::Base.connection.execute(
      "CALL partman.run_maintenance_proc()"
    )
  end
end

# config/recurring.yml
production:
  pg_partman_maintenance:
    class: PgPartmanMaintenanceJob
    schedule: "every hour"

Once an hour is plenty. This job creates new partitions before they are needed, applies retention, and reindexes if configured. If you are still on cron, use bin/rails runner "PgPartmanMaintenanceJob.perform_now" — either way, it must run somewhere.

Migrating Existing Data Into a Rails Postgres Partitioned Table

The trick with Rails Postgres partitioning is moving billions of existing rows without a maintenance window. The pattern I use every time is: create the partitioned table alongside the old one, dual-write from the application, backfill in batches, then swap the names in a single transaction.

Dual-write from a model concern is the safest approach. Every write goes to both tables until we cut over:

class ShipmentEvent < ApplicationRecord
  self.table_name = "shipment_events"

  after_create :mirror_to_partitioned_table

  private

  def mirror_to_partitioned_table
    return unless Feature.enabled?(:partitioned_shipment_events_dual_write)

    self.class.connection.exec_insert(<<~SQL, "MirrorShipmentEvent", [
      [nil, id], [nil, shipment_id], [nil, event_type],
      [nil, payload.to_json], [nil, occurred_at], [nil, created_at]
    ])
      INSERT INTO shipment_events_new
        (id, shipment_id, event_type, payload, occurred_at, created_at)
      VALUES ($1, $2, $3, $4::jsonb, $5, $6)
      ON CONFLICT DO NOTHING
    SQL
  end
end

The ON CONFLICT DO NOTHING matters because the backfill job will race with the dual-write, and you want the winner to be idempotent. Backfill in batches, low priority, aiming for a few thousand rows at a time:

class BackfillShipmentEventsJob < ApplicationJob
  queue_as :low

  BATCH_SIZE = 5_000

  def perform(last_id: 0)
    rows = ShipmentEvent
      .where("id > ?", last_id)
      .order(:id)
      .limit(BATCH_SIZE)
      .to_a

    return if rows.empty?

    values = rows.map do |e|
      "(#{e.id}, #{e.shipment_id}, #{ActiveRecord::Base.connection.quote(e.event_type)}, " \
        "#{ActiveRecord::Base.connection.quote(e.payload.to_json)}::jsonb, " \
        "#{ActiveRecord::Base.connection.quote(e.occurred_at)}, " \
        "#{ActiveRecord::Base.connection.quote(e.created_at)})"
    end.join(",")

    ActiveRecord::Base.connection.execute(<<~SQL)
      INSERT INTO shipment_events_new
        (id, shipment_id, event_type, payload, occurred_at, created_at)
      VALUES #{values}
      ON CONFLICT DO NOTHING
    SQL

    BackfillShipmentEventsJob.set(wait: 200.milliseconds).perform_later(last_id: rows.last.id)
  end
end

The wait: 200.milliseconds between batches is what keeps replication lag under control and lets other workloads breathe. On a 1.4-billion-row table this finished in about 40 hours, running continuously in the background while the app served traffic. If you are on Postgres 16 or later you can also use pg_dump --data-only piped through COPY for the historical data and dual-write only recent rows, which is faster but requires a bit more coordination.

Once the counts match (SELECT COUNT(*) FROM shipment_events vs shipment_events_new), swap in a single transaction:

class SwapShipmentEventsTables < ActiveRecord::Migration[8.0]
  def up
    execute <<~SQL
      BEGIN;
      ALTER TABLE shipment_events RENAME TO shipment_events_legacy;
      ALTER TABLE shipment_events_new RENAME TO shipment_events;
      COMMIT;
    SQL
  end
end

Keep shipment_events_legacy around for a week in case you need to roll back, then drop it. If you are especially cautious, layer a Strong Migrations guard on it — I covered that pattern in Rails Strong Migrations.

Querying a Partitioned Table Correctly

The most common Rails Postgres partitioning mistake I see is queries that do not filter by the partition key. If you write ShipmentEvent.where(shipment_id: 42).first without an occurred_at bound, Postgres has to scan every partition. It will still work, but you lose the entire benefit.

Two fixes. First, add a default scope that requires the time bound:

class ShipmentEvent < ApplicationRecord
  scope :in_window, ->(from:, to:) {
    where(occurred_at: from..to)
  }

  scope :recent, -> { in_window(from: 30.days.ago, to: Time.current) }
end

Now callers write ShipmentEvent.recent.where(shipment_id: 42) and pruning kicks in. Second, verify pruning with EXPLAIN:

puts ShipmentEvent
  .in_window(from: 30.days.ago, to: Time.current)
  .where(shipment_id: 42)
  .explain

You want to see Append over just the recent partitions, not Seq Scan on all of them. If you see the latter, something in your scope is defeating the constant-folding pruner — usually a computed timestamp inside a subquery instead of a bind parameter.

FAQ: Rails Postgres Partitioning

When should I partition a Postgres table in Rails?

Partition when the table is over 100GB, is append-heavy, and every important query filters by a natural time or tenant dimension. Below 100GB, the operational cost of partitioning exceeds the maintenance savings. Above 100GB, VACUUM, REINDEX, and retention deletes all start to hurt in ways partitioning fixes cleanly.

Does Rails Postgres partitioning require pg_partman?

No, native Postgres declarative partitioning works without it — you just have to create new partitions manually every period. pg_partman automates partition creation, retention, and maintenance, so I install it on every real project. On managed services like AWS RDS and Google Cloud SQL, pg_partman is on the extension whitelist and installs like any other extension.

Can I partition an existing table in place with Rails?

Not directly. Postgres does not support turning a regular table into a partitioned table in place. The pattern is to create a new partitioned table alongside the old one, dual-write from the application, backfill in batches, and swap names in a single transaction. This is doable with zero downtime and is what I walk through in the migration section above.

How does Rails Postgres partitioning interact with foreign keys?

Foreign keys pointing from a partitioned table to another table work fine. Foreign keys pointing into a partitioned table are supported from Postgres 12 onwards but come with caveats — the referencing table needs an index that matches the partition key, and cascading deletes across partitions can be slow. For very hot append-heavy tables I usually skip inbound foreign keys entirely and enforce referential integrity in the application layer.

Need help partitioning a large Postgres table in your Rails app, or planning a database scaling strategy that will actually hold up? TTB Software specialises in Rails performance and Postgres at scale. We have been doing this for nineteen years.