Engineering notes — 2026-10-27

PostgreSQL Partitioning at 109M Rows: What Actually Made Queries 720x Faster

Partitioning a 109M-row PostgreSQL table took a critical report from about one hour to under 5 seconds. The speedup did not come from splitting the table into pieces; it came from matching the partition layout to the queries that actually run, so pruning removes most data before the planner reads a page.

The nearest large table I have worked on is Perseus, an industrial IoT analytics system for water treatment telemetry. Continuous readings from facility device fleets had accumulated past 109 million rows, and the nightly critical report, the one operations staff actually depended on, took about an hour to build. This is the story of how that query time was cut by a factor of 720, from a strategy on paper that mostly did not survive contact with the data.

Profile first, partition second

The instinctive move is to shard or partition by date the moment a table gets large. For this table that would have been wrong. Reading the actual query workload showed two very different access profiles:

• Enterprise-tier clients with high-value monitoring devices who read their own history almost exclusively, over long time ranges.
• Operations staff who scan recent windows across the entire fleet for alarms and anomalies.

Partitioning only by time would serve the operational scans well and do nothing for the enterprise clients: every one of their queries would still fan out across every time partition. The partition key has to serve the dominant access path, and there were two.

The hardware-aware partition key

The insight that unlocked the design was in the hardware itself. Monitoring devices are not all equal: enterprise-tier clients run expensive, high-frequency monitors, while the rest of the fleet reports more cheaply and in bursts. So the partition layout followed the hardware tiers: enterprise-grade devices went to dedicated partitions, and the operational time-window scans got their own layout aligned with the ingest pattern.

With that layout, partition pruning eliminates the rest of the fleet for enterprise queries before the planner touches a single heap page. That is where the 720x came from: not from faster reads of 109M rows, but from not reading most of them.

Migrate without an outage window

A schema change on a live system is rarer than it sounds, because the old table keeps serving reads. The migration ran on the existing PHP 8 / Symfony services, executing incrementally while the legacy table remained the source of truth. Queries were flipped to the partitioned tables only after validation, in a controlled cutover rather than a weekend freeze. The dashboards built on React and TypeScript meant the improvement was visible to non-engineers the same day, not just in EXPLAIN plans.

A minimal pruning-friendly example

The general shape of a pruning-friendly layout, simplified for illustration: ensure your query predicate includes the partition key, or the planner has no reason to skip partitions.

CREATE TABLE telemetry (
  device_id     bigint      NOT NULL,
  read_at       timestamptz NOT NULL,
  metrics       jsonb,
  PRIMARY KEY (device_id, read_at) -- must include BOTH partition keys
) PARTITION BY RANGE (read_at);

-- level 1: monthly partitions serve the operational scans.
-- The partition is declared partitioned itself (multi-level):
CREATE TABLE telemetry_2026_10
  PARTITION OF telemetry
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01')
  PARTITION BY RANGE (device_id);

-- level 2: enterprise-tier devices get their own leaf, so client
-- queries prune everything else away
CREATE TABLE telemetry_2026_10_enterprise
  PARTITION OF telemetry_2026_10
  FOR VALUES FROM (900000) TO (MAXVALUE);

CREATE TABLE telemetry_2026_10_rest
  PARTITION OF telemetry_2026_10
  FOR VALUES FROM (MINVALUE) TO (900000);

Note the primary key includes the partition key. PostgreSQL requires partition keys to be part of any unique constraint, and finding this out mid-migration is expensive.

What partitioning will not fix

• Missing indexes. A sequential scan in one partition is still a sequential scan.
• Too many partitions. Thousands of tiny partitions slow planning and can regress both load and queries; the cost curve is real and documented in the PostgreSQL community.
• Application-level inefficiency. No schema layout rescues N+1 query loops.
• Wrong partition key. This whole article exists because by-date was not the right key.

Results, in numbers: 109M+ rows queried in under 5 seconds, a 720x faster critical path, and a migration with no downtime window. I write about real production systems at safesync.dev. If your reports are measured in the wrong unit of time, this is exactly the kind of engagement I take on: reach out by email or Telegram.