PostgreSQL Partitioning: When We Used It and When We Wish We Hadn’t

A candid look at the gains we got, the complexity we didn’t expect, and the benchmarks that changed our minds.

There’s a moment in every backend engineer’s career when a table crosses some invisible threshold and everything starts to slow down. Queries that used to return in 8ms suddenly take 300ms. Your DBA (or in our case, the Slack channel #db-ops) starts sending ominous messages. Someone drops the word partitioning in a meeting and everyone nods like they understand exactly what that means.

We lived through that moment — twice. Once where partitioning genuinely saved us, and once where we added complexity that made us question our own judgment.

This is that story.

The Setup

We were running a transaction ledger table on a fintech product. By the time we got to this problem, the table had around 180 million rows and was growing at roughly 4–6 million rows per day. The schema looked something like this:

CREATE TABLE transactions (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(18, 4) NOT NULL,
    currency    VARCHAR(3) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata    JSONB
);

CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_transactions_created_at ON transactions(created_at);
CREATE INDEX idx_transactions_status ON transactions(status);

Most of our queries were time-bounded. Something like “get all transactions for this user in the last 30 days” or “summarize today’s settlement batch.” But EXPLAIN ANALYZE kept showing us index scans that touched more pages than they should have, and autovacuum was choking every morning.

The diagnosis was straightforward: we needed range partitioning on created_at.

Setting Up Partitioning in PostgreSQL

We migrated to a declarative partitioned table. Here’s a simplified version of what we did:

CREATE TABLE transactions (
    id          BIGSERIAL NOT NULL,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(18, 4) NOT NULL,
    currency    VARCHAR(3) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata    JSONB,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE transactions_2024_01
    PARTITION OF transactions
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE transactions_2024_02
    PARTITION OF transactions
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- ... and so on

And since we didn’t want to manually create partitions every month, we wrote a small Go utility that ran as a scheduled job to create the next month’s partition in advance:

package partitionmgr

import (
 "context"
 "fmt"
 "time"

 "github.com/jackc/pgx/v5/pgxpool"
)

type Manager struct {
 db *pgxpool.Pool
}

func New(db *pgxpool.Pool) *Manager {
 return &Manager{db: db}
}

// EnsureNextMonthPartition creates the next month's partition if it doesn't exist.
// Safe to call idempotently — uses IF NOT EXISTS under the hood.
func (m *Manager) EnsureNextMonthPartition(ctx context.Context, table string) error {
 next := time.Now().AddDate(0, 1, 0)
 start := time.Date(next.Year(), next.Month(), 1, 0, 0, 0, 0, time.UTC)
 end := start.AddDate(0, 1, 0)

 partitionName := fmt.Sprintf(
  "%s_%d_%02d",
  table,
  start.Year(),
  start.Month(),
 )

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

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

 return nil
}

We ran this as a cron job on the 25th of every month. Simple, reliable, no drama.

The Query Side: Where Go Meets Partitions

One thing that caught us off guard was how partition pruning interacts with parameterized queries in Go. PostgreSQL prunes partitions at planning time — which means it needs to know the literal value (or a stable expression) to decide which partitions to scan.

This works great:

func (r *TransactionRepo) GetByUserAndPeriod(
   ctx context.Context,
   userID int64,
   from, to time.Time,
) ([]Transaction, error) {
   rows, err := r.db.Query(ctx, `
    SELECT id, user_id, amount, currency, status, created_at
    FROM transactions
    WHERE user_id = $1
      AND created_at >= $2
      AND created_at < $3
    ORDER BY created_at DESC
   `, userID, from, to)
   if err != nil {
    return nil, fmt.Errorf("query transactions: %w", err)
   }
   defer rows.Close()

   var result []Transaction
   for rows.Next() {
    var t Transaction
    if err := rows.Scan(&t.ID, &t.UserID, &t.Amount, &t.Currency, &t.Status, &t.CreatedAt); err != nil {
     return nil, err
    }
    result = append(result, t)
   }
   return result, rows.Err()
}

PostgreSQL sees the $2 and $3 parameters and — because pgx sends typed parameters — prunes to just the relevant partitions. You only touch 1–3 monthly partitions instead of the full table. The difference shows up immediately in EXPLAIN ANALYZE:

-- Before partitioning
Seq Scan on transactions (cost=0.00..4820000.00 rows=180000000)
  Filter: (created_at >= '2024-03-01' AND created_at < '2024-04-01')
  Rows Removed by Filter: 165000000

-- After partitioning
Append (cost=0.00..98000.00 rows=4500000)
  ->  Index Scan on transactions_2024_03
        Index Cond: (created_at >= '2024-03-01' AND created_at < '2024-04-01')

Roughly 50x fewer rows examined. That’s not a typo.

The Benchmarks

We ran a series of benchmarks comparing a non-partitioned table (rebuilt from a snapshot) against the partitioned version. All tests were on a c2-standard-8 GCP instance, PostgreSQL 15, with shared_buffers = 4GB and work_mem = 64MB.

The test covered three query patterns we use most in production:

• Point query — single user, current month
• Range query — single user, last 90 days (crosses 3 partitions)
• Aggregation — daily totals for a single user over 6 months

(See benchmark chart below)

The point and range queries improved dramatically. The aggregation query — crossing 6 monthly partitions — was where things got interesting.

Where It Got Complicated

The aggregation case exposed something we hadn’t fully appreciated: partition overhead is real when you cross many boundaries.

A query like this:

func (r *TransactionRepo) DailySummary(
    ctx context.Context,
    userID int64,
    from, to time.Time,
) ([]DailySummary, error) {
    rows, err := r.db.Query(ctx, `
        SELECT
            DATE_TRUNC('day', created_at) AS day,
            COUNT(*) AS tx_count,
            SUM(amount) AS total_amount
        FROM transactions
        WHERE user_id = $1
          AND created_at >= $2
          AND created_at < $3
        GROUP BY 1
        ORDER BY 1
    `, userID, from, to)
    // ...
}

…performs worse when it spans many partitions because the planner has to spawn a parallel Append node for each one, merge the results, and coordinate memory for each partition-level aggregate. At 6 partitions the overhead was negligible. At 24 (2 years of data for annual reports), it started showing.

The fix wasn’t removing partitioning — it was accepting that some queries belong in a separate reporting path, backed by pre-aggregated materialized views, not raw partition scans.

When We Wish We Hadn’t

The second case is harder to write about, because it was mostly our fault.

We applied the same pattern to a reference table — about 2 million rows, low write volume, queried constantly from multiple services. Someone argued it would “future-proof” the schema. The table was partitioned by a status enum, which in hindsight makes no sense for range-based pruning.

What we got: JOINs that suddenly needed to span all partitions (because you can't prune an enum range), query plans that looked like someone stapled 8 identical plans together, and a planner that spent more time figuring out what to ignore than actually doing work.

The lesson: partition pruning only helps if your queries actually filter on the partition key. A table partitioned on status where half your queries don't filter on status is just a more complicated table.

We ended up reverting. The migration back took longer than the migration in.

What We Learned

A few things that would have saved us time if someone had written them down:

Do partition when:

• Your most frequent queries are naturally time-bounded
• You need efficient bulk deletion by time range (DROP PARTITION is O(1) compared to DELETE)
• The table grows indefinitely and autovacuum is struggling to keep up
• You have clear hot/cold data separation — recent partitions stay in buffer cache, old ones get pushed to cold storage

Don’t partition when:

• Your access patterns don’t filter on the partition key
• The table is small or grows slowly — the complexity isn’t worth it under ~50M rows in most cases
• You’re partitioning on a low-cardinality column like status or boolean flags
• You have many ad-hoc cross-partition queries that can’t be rewritten

Operational things that matter:

• Always create partitions in advance — inserting into a table with no matching partition throws an error, not a graceful fallback
• Unique constraints have to include the partition key, which changes your primary key shape
• Partition pruning with IN (...) lists works, but only up to a certain size — large IN lists can disable pruning entirely
• Monitor partition sizes independently; a single anomalous month of data won’t drag the whole table, but it will drag that partition

The Honest Summary

Partitioning is one of those features that can feel like magic when applied correctly, and like self-inflicted punishment when applied wrong. It’s not a silver bullet for slow queries — it’s a tool with a very specific use case: large, time-series or range-oriented tables where you can reliably filter on the partition key.

The 180-million-row ledger table? We’re happy we partitioned it. Our settlement reports run faster, monthly archival is trivially easy, and the DBA channel has been quiet for months.

The reference table with the status enum? We learned from that one. Probably more than from the success.

If you’re sitting in front of a slow table right now, reach for EXPLAIN (ANALYZE, BUFFERS) before you reach for partitioning. Sometimes the problem is a missing index. Sometimes it's an N+1 hiding in your Go service layer. Partitioning is the right answer less often than we want it to be.

But when it is — it’s really the right answer.