How Partitioning Cut a 43‑Second PostgreSQL Query to 0.3 seconds

Introduction

The article describes a real‑world performance crisis in a SaaS user‑behavior analytics service where a single events table (≈45 billion rows, 1.2 TB) caused a typical P99 query latency of 43 seconds despite having several indexes.

Root causes include massive index size, low cache hit rate, huge heap page scans, MVCC visibility checks, and costly VACUUM/REINDEX operations. Adding more indexes would not solve the problem because the scan range remains huge.

Why Partitioning

PostgreSQL 10+ offers declarative range partitioning. By partitioning the table on the event_time column, the optimizer can prune irrelevant partitions during planning or execution, turning a full‑table scan into a scan of only the few partitions that overlap the requested time window.

Declarative Partitioning Example

CREATE TABLE events (
    event_id   bigint NOT NULL,
    tenant_id bigint NOT NULL,
    user_id   bigint NOT NULL,
    event_type text NOT NULL,
    event_time timestamptz NOT NULL,
    source    text,
    payload   jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (event_id)
);

CREATE INDEX idx_events_user_time ON events (user_id, event_time);
CREATE INDEX idx_events_tenant_time ON events (tenant_id, event_time);
CREATE INDEX idx_events_event_time ON events (event_time);

After migration the new table is defined as:

CREATE TABLE events_v2 (
    event_id   bigint NOT NULL,
    tenant_id  bigint NOT NULL,
    user_id    bigint NOT NULL,
    event_type text NOT NULL,
    event_time timestamptz NOT NULL,
    source     text,
    payload    jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (event_time, event_id)
) PARTITION BY RANGE (event_time);

Each day gets its own partition, e.g.:

CREATE TABLE events_20260701 PARTITION OF events_v2
FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-07-02 00:00:00+00');

CREATE TABLE events_20260702 PARTITION OF events_v2
FOR VALUES FROM ('2026-07-02 00:00:00+00') TO ('2026-07-03 00:00:00+00');

A default partition catches any rows that do not match an existing partition, preventing write failures.

Partition Pruning

When a query contains a condition on event_time, PostgreSQL discards all partitions outside the range, scanning only the relevant daily tables. The planner shows an Append node with many sub‑plans removed, confirming effective pruning.

Choosing the Partition Key and Granularity

Time is present in every analytical query and aligns with the natural write order of log‑style events.

Daily partitions keep each partition around 12 million rows (≈1 GB), a size that balances scan cost and metadata overhead.

Guidelines: high‑frequency filter, significant scan reduction, roughly even data distribution, and support for lifecycle operations.

Migration Strategy – Zero Downtime

Create the new partitioned table (as shown above) and a default partition.

Dual write : application writes to both the old events table and the new events_v2 table.

Backfill historical data in time‑window batches:

INSERT INTO events_v2 (event_id, tenant_id, user_id, event_type, event_time, source, payload, created_at)
SELECT event_id, tenant_id, user_id, event_type, event_time, source, payload, created_at
FROM events
WHERE event_time >= $1 AND event_time < $2
ON CONFLICT (event_time, event_id) DO NOTHING;

Consistency checks :

Row‑count per day comparison.

Random sample on tenant_id, user_id, event_type and time.

Run the exact business query on both tables and compare results.

Gray‑release reads : route a small set of users to the new table, monitor latency, CPU, I/O, autovacuum lag, and default‑partition usage.

Cut over : stop writes to the old table, keep it read‑only for a safety window, rename events_v2 to events, and drop the legacy table.

Application Code Changes

Java + Spring Boot + jOOQ example:

public List<EventTypeCount> queryEventStats(DSLContext dsl, long tenantId, long userId,
                                            OffsetDateTime start, OffsetDateTime end) {
    return dsl.select(EVENTS.EVENT_TYPE, DSL.count())
              .from(EVENTS)
              .where(EVENTS.TENANT_ID.eq(tenantId))
                .and(EVENTS.USER_ID.eq(userId))
                .and(EVENTS.EVENT_TIME.ge(start))
                .and(EVENTS.EVENT_TIME.lt(end))
              .groupBy(EVENTS.EVENT_TYPE)
              .fetch(r -> new EventTypeCount(r.get(EVENTS.EVENT_TYPE), r.get(DSL.count())));
}

Dual‑write service snippet:

@Transactional
public void saveEvent(Event event) {
    eventRepository.insertLegacy(event);      // writes to old table
    eventRepository.insertPartitioned(event); // writes to new partitioned table
}

Automation and Monitoring

Use pg_partman to create future daily partitions automatically and schedule maintenance with pg_cron:

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'event_time',
    p_type := 'native',
    p_interval := '1 day',
    p_premake := 7,
    p_start_partition := '2026-07-01'
);

SELECT cron.schedule('partman-maintenance', '15 * * * *', $$SELECT partman.run_maintenance()$$);

Key monitoring items:

Future partitions (next 3 days) existence.

Rows in the default partition – should stay at zero.

Hot‑partition size growth rate.

Autovacuum lag and n_dead_tup per partition.

Slow‑SQL plans that still scan many partitions.

Common Pitfalls

Missing future partitions → write errors ("no partition … found for row").

Timezone mismatch between application and DB causing rows to land in wrong partitions.

Assuming global uniqueness of event_id after partitioning; primary key must include the partition key.

Over‑fine granularity (hourly) leading to thousands of partitions and planner overhead.

Default partition becoming a write sink – indicates partition creation or timezone bugs.

Post‑Migration Operational Practices

Connection management : HikariCP pool + PgBouncer transaction pooling.

Read‑write splitting : primary for writes and recent reads; read replicas for reporting and long‑range analytics.

Caching & pre‑aggregation : short‑term result cache (30 s‑5 min) and daily/ hourly summary tables (e.g., event_daily_summary) to avoid full scans for dashboards.

Hot‑partition tuning : aggressive autovacuum settings for today’s partition, disable autovacuum on old read‑only partitions.

Rate limiting & degradation : per‑tenant throttling, circuit‑breakers for expensive queries, async export for very large time‑range requests.

End‑to‑End Migration Process Checklist

Research : collect slow‑SQL list, data growth patterns, decide on partition key and granularity.

Design : new schema, primary‑key strategy, dual‑write architecture, backfill plan, validation methodology, cut‑over steps.

Rehearsal : run the whole flow in a staging environment – create partitions, dual‑write load test, batch backfill, consistency checks, gray‑release.

Release : staged rollout – create partitions & automation, deploy dual‑write, start backfill, perform gray‑release, switch reads, rename tables, retire old table.

Observation : monitor for at least one full traffic peak, verify automatic partition creation, confirm expiration‑partition cleanup, ensure replica lag stays acceptable.

Evolution Roadmap

Stage 1: Single large table (baseline).

Stage 2: Native partitioning on a single PostgreSQL instance – solves storage‑lifecycle and query‑speed problems.

Stage 3: Add cache, summary tables, read replicas – moves system from "fast query" to "stable service".

Stage 4: When write throughput hits the single‑node ceiling, consider distributed extensions (Citus) or sharding by tenant.

Conclusion

The dramatic drop from 43 seconds to 0.3 seconds is not a magic index tweak but the result of a systematic methodology: identify the physical storage bottleneck, apply time‑range partitioning, execute a zero‑downtime migration (dual‑write → backfill → validation → gray‑release → cut‑over), automate partition maintenance, and finally complement the database with caching, summary tables, read‑replicas and proper throttling. For any workload with massive, time‑ordered data and queries that filter by time, native PostgreSQL partitioning is the most valuable single engineering investment.