Rails Postgres Table Partitioning: Time-Based Partitions for Large Tables in Production

A fintech client called me on a Sunday because their events table had crossed four billion rows and the dashboard had stopped loading. Every query that touched the table was scanning weeks of cold history just to render the last hour of data. Autovacuum was constantly running, never finishing. Backups were taking eleven hours. They had been throwing bigger Postgres instances at it for two years and were now on the largest RDS box AWS would sell them.

We did not need a bigger box. We needed Rails Postgres table partitioning. Two weeks later that same table was responding in milliseconds, autovacuum was idle most of the day, and dropping a month of old data was a DETACH PARTITION instead of a multi-hour DELETE. After nineteen years of Rails I have done this migration enough times to know the rough edges, and this is the production playbook.

What Rails Postgres Table Partitioning Actually Does

Postgres declarative partitioning splits one logical table into many physical child tables based on a key — a date column for time-series data, an account id for multi-tenant data, or a hash for even distribution. Your application still queries the parent table. The Postgres planner reads the partition key from the WHERE clause and only touches the partitions it needs.

The wins compound. Indexes shrink because each partition has its own index over a smaller slice. Autovacuum has less to chew through per partition and finishes in minutes instead of hours. You can drop or detach old partitions instantly instead of issuing a DELETE that bloats the table. Backups can be tiered — hot partitions on fast storage, cold partitions on cheap storage. Sequential scans, when they happen, scan one partition rather than the whole monster.

The cost is real. Partitioned tables have constraints that ordinary tables do not. Foreign keys into a partitioned table from another table do not work in older Postgres versions and only partially work in newer ones. Unique constraints must include the partition key. The query planner needs the partition key in your WHERE clause to prune partitions, and ActiveRecord scopes that omit it will scan everything. Rails Postgres table partitioning is one of those features where the operational cost is higher than the schema cost, and you should not adopt it before you need it.

When Rails Postgres Table Partitioning Pays Off (and When It Doesn’t)

The rule of thumb I use: partition when the table is over 100 GB or over 500 million rows or when you regularly drop large slices by date. Below those numbers, fix your indexes, add a partial index for hot rows, run VACUUM ANALYZE, and revisit in a quarter.

Strong fit for time-based Rails Postgres table partitioning:

• Append-only event streams — analytics events, webhook receipts, audit logs, Sidekiq job records
• Time-series telemetry — application metrics, IoT readings, request logs persisted for compliance
• Notifications and outbox tables that grow forever and are queried mostly by recent window
• Soft-delete tables where most reads target rows from the last N days
• Per-tenant write-heavy tables that combine well with hash partitioning by account_id

Bad fit:

• Tables under 50 GB where queries already use indexes well
• Tables without a clear partition key in every hot query
• Tables with frequent updates spanning many partitions (the planner cannot prune writes)
• Tables that need foreign keys pointing into them from many other tables

If you are still on Postgres 12 or older, upgrade before you partition. Declarative partitioning before Postgres 13 is missing too many features (logical replication of partitioned tables, attaching partitions without long locks, partition-wise joins on by default) for a serious production rollout.

Setting Up Time-Based Rails Postgres Table Partitioning

Let’s partition a hypothetical events table by month. The schema looks like this for a fresh table:

class CreatePartitionedEvents < ActiveRecord::Migration[8.0]
  def up
    execute <<~SQL
      CREATE TABLE events (
        id          bigserial,
        account_id  bigint NOT NULL,
        kind        text   NOT NULL,
        payload     jsonb  NOT NULL,
        occurred_at timestamptz NOT NULL,
        created_at  timestamptz NOT NULL,
        updated_at  timestamptz NOT NULL,
        PRIMARY KEY (id, occurred_at)
      ) PARTITION BY RANGE (occurred_at);
    SQL

    execute <<~SQL
      CREATE INDEX index_events_on_account_id_and_occurred_at
        ON events (account_id, occurred_at DESC);
    SQL
  end

  def down
    drop_table :events
  end
end

Two things to call out. First, the primary key includes occurred_at because Postgres requires the partition key to be part of every unique constraint, including the primary key. ActiveRecord still treats id as the primary key for model purposes, but Postgres needs the composite. Second, indexes declared on the parent are automatically created on every child partition, present and future. Do not declare them per partition.

The matching ActiveRecord model:

class Event < ApplicationRecord
  self.primary_key = :id

  scope :for_account, ->(id) { where(account_id: id) }
  scope :in_window,   ->(range) { where(occurred_at: range) }
end

Now create monthly partitions. The simplest version up front:

class CreateEventPartitionsForApril2026 < ActiveRecord::Migration[8.0]
  def up
    execute <<~SQL
      CREATE TABLE events_2026_04 PARTITION OF events
        FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
    SQL
  end

  def down
    execute "DROP TABLE events_2026_04"
  end
end

That is it. Inserts into events for April land in events_2026_04. Queries with occurred_at in April only scan that partition. Queries that span two months scan two. Queries without occurred_at scan all of them — write your scopes accordingly.

Production Pattern 1: Append-Only Time-Series With Auto-Created Partitions

Creating partitions by hand is fine for week one. By month three you will forget, an insert will land outside any partition, and Postgres will raise no partition of relation "events" found for row. Your background jobs will start dying at midnight UTC on the first of the month. Do not let this happen.

There are two reasonable approaches. Either schedule a Rails job that creates partitions ahead of time, or install pg_partman and let the database do it. I run both in different shops and they both work. The Rails-side version reads more naturally to a Rails team and does not require an extra extension; pg_partman is more battle-tested at scale.

Here is the Rails-side version, which I prefer for teams under a hundred engineers:

class Partitions::EventsCreator
  LOOKAHEAD_MONTHS = 3

  def self.ensure_future_partitions
    base = Time.current.beginning_of_month
    LOOKAHEAD_MONTHS.times do |i|
      month = base + i.months
      create_partition(month)
    end
  end

  def self.create_partition(month)
    name  = "events_#{month.strftime('%Y_%m')}"
    start = month.strftime("%Y-%m-%d")
    finish = (month + 1.month).strftime("%Y-%m-%d")

    ActiveRecord::Base.connection.execute(<<~SQL)
      CREATE TABLE IF NOT EXISTS #{name}
        PARTITION OF events
        FOR VALUES FROM ('#{start}') TO ('#{finish}');
    SQL
  end
end

Schedule it daily with whichever scheduler you trust — I cover the cron-overlap traps in the Rails Postgres advisory locks post, and you should absolutely wrap this in one. Three months of lookahead means a runaway scheduler buys you a quarter to notice before inserts start failing.

Dropping old partitions is the second half of the operation:

class Partitions::EventsRetainer
  RETENTION_MONTHS = 12

  def self.detach_old_partitions
    cutoff = (Time.current - RETENTION_MONTHS.months).beginning_of_month
    partitions_older_than(cutoff).each do |name|
      ActiveRecord::Base.connection.execute(
        "ALTER TABLE events DETACH PARTITION #{name} CONCURRENTLY"
      )
      ActiveRecord::Base.connection.execute("DROP TABLE #{name}")
    end
  end

  def self.partitions_older_than(cutoff)
    sql = <<~SQL
      SELECT inhrelid::regclass::text AS name
      FROM pg_inherits
      WHERE inhparent = 'events'::regclass
    SQL
    ActiveRecord::Base.connection.select_values(sql).select do |name|
      name =~ /events_(\d{4})_(\d{2})$/ &&
        Date.new($1.to_i, $2.to_i, 1) < cutoff.to_date
    end
  end
end

DETACH PARTITION CONCURRENTLY (Postgres 14+) does the detach without blocking writers. After that the partition is just a normal table — you can DROP TABLE it instantly, no long DELETE, no autovacuum aftermath, no bloat. This single operation is often the entire reason teams adopt Rails Postgres table partitioning.

Production Pattern 2: Composite Partitioning by Tenant and Time

For a multi-tenant SaaS where one big customer can dominate your write volume, partition by account_id first (using LIST or HASH) and sub-partition each tenant by time. The big tenant gets isolation; the small tenants share. Combined with the Pundit authorization patterns I wrote about for multi-tenant SaaS and your tenancy story is solid end to end.

CREATE TABLE events (
  id          bigserial,
  account_id  bigint NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload     jsonb NOT NULL,
  PRIMARY KEY (id, account_id, occurred_at)
) PARTITION BY LIST (account_id);

CREATE TABLE events_acct_42 PARTITION OF events
  FOR VALUES IN (42)
  PARTITION BY RANGE (occurred_at);

CREATE TABLE events_acct_42_2026_04 PARTITION OF events_acct_42
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE events_default PARTITION OF events DEFAULT
  PARTITION BY RANGE (occurred_at);

CREATE TABLE events_default_2026_04 PARTITION OF events_default
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

The tradeoff: more partitions, more catalog overhead, more for the planner to consider. Keep total partition count under about ten thousand and keep your hot scopes scoped to a single tenant’s window. If you see the planner getting slow, raise enable_partition_pruning confidence with partition_pruning = on (default) and check that your WHERE includes both keys.

Gotchas in Rails Postgres Table Partitioning

These are the mistakes I have personally watched cause production incidents.

Unique constraints on non-partition columns silently break. A unique_by: :external_id on a partitioned events table compiles fine and enforces uniqueness within each partition, not globally. If you need globally unique non-key columns, you cannot enforce that in a partitioned table — move that uniqueness to a separate non-partitioned reference table.

Foreign keys pointing into a partitioned table were impossible before Postgres 12 and are still awkward. If comments.event_id references events.id, and events is partitioned, Postgres has to look across every partition for the existence check on insert. Avoid this design where you can. For audit-style data, denormalise the parent reference instead of using a real FK.

ActiveRecord migrations do not roundtrip cleanly. Partitioned tables use SQL features the schema dumper cannot represent. Switch your repo to config.active_record.schema_format = :sql so db/structure.sql captures the partition definitions correctly. If you forget this, your test database will be a regular table while production is partitioned. Ask me how I know.

COPY and bulk insert ignore partition routing performance. They work, but for very large loads, copy directly into the target partition. The router adds tens of percent of overhead at scale.

Autovacuum settings are per-partition. A noisy partition might need different settings than a sleepy historical one. The good news: you can ALTER TABLE events_2026_04 SET (autovacuum_vacuum_scale_factor = 0.05) and tune just the hot one. I went deep on this in the Postgres autovacuum tuning guide.

Migrating an Existing Large Table to Partitioned Without Downtime

The migration itself is the scariest part. You cannot simply ALTER TABLE events PARTITION BY RANGE (occurred_at) on a populated table. Postgres requires the partitioned parent to be empty.

The pattern I use, which has worked on tables up to about a billion rows without downtime:

1. Create a new partitioned table events_partitioned with the same columns and the partitioning scheme.
2. Create partitions covering the entire range of existing data and a few months ahead.
3. Backfill in batches with a Rails job that copies rows from events to events_partitioned, ordered by occurred_at, with INSERT ... SELECT and a LIMIT. Use the Rails Postgres advisory lock pattern so two pods do not run the backfill at once.
4. Add a trigger on the original events table to mirror new inserts and updates into events_partitioned. This is the riskiest piece — write characterisation tests first.
5. Once the backfill catches up and the trigger has been quiet (rows match in both for a day), inside a single transaction: RENAME events to events_old, RENAME events_partitioned to events, drop the trigger.
6. Sanity-check counts and a few queries, then drop events_old after a week.

For tables over a few billion rows, take the maintenance window. The chart-topping fintech client I opened with took a four-hour Saturday window with read-only mode rather than risk a live cutover. Sometimes the boring choice is the right choice.

The detailed cousin to this pattern is the zero-downtime database migration playbook — same principles, different schema operation.

FAQ

How many partitions can a Postgres table have before performance degrades?

In Postgres 14 and later, planning stays fast through the low thousands of partitions per table thanks to runtime pruning. I treat 1,000 partitions as a comfortable ceiling and 10,000 as a warning sign. Beyond that, query planning time itself becomes the bottleneck. If you need more, partition by a coarser grain (quarterly instead of monthly) or shard at the application layer.

Should I use pg_partman or write my own partition manager in Rails?

Both work. pg_partman is the right choice if your operations team already runs Postgres extensions, you want premade retention and pre-creation logic, and you do not want partitioning concerns in your Rails repo. Roll your own if you are a small team that prefers Ruby code and existing observability. The Rails version is fifty lines and easier to reason about; pg_partman handles edge cases you have not thought of yet.

Does Rails Postgres table partitioning work with Active Record associations and joins?

Yes, transparently — you query the parent table and Active Record does not know or care that it is partitioned. The thing to check is that every hot query includes the partition key in its WHERE so Postgres can prune. Event.where(account_id: 42).where(occurred_at: 1.day.ago..) prunes to one partition. Event.where(account_id: 42) alone scans every partition. Add Bullet or strict loading for hygiene, but partition pruning is checked with EXPLAIN.

Can I add partitioning to a table that already has foreign keys pointing to it?

It is painful. The migration above (rename-and-swap) needs to recreate every inbound FK against the new partitioned table, and events.id is no longer unique on its own — the unique constraint includes the partition key. In practice, denormalise inbound FKs into business-key references (an event_uuid column with an application-level invariant) before partitioning. This is one of the strongest reasons to design new high-write tables with partitioning in mind from the start, even if you do not enable it on day one.

Need help with a Rails Postgres performance migration without taking the system down? TTB Software specialises in Rails infrastructure work like this — partitioning, replication, vacuum tuning, the whole stack. We’ve been doing this for nineteen years.