Engineering notes — 2026-11-03

Postgres Performance Tuning on 100M+ Rows: A Playbook Beyond Partitioning

When a Postgres table crosses 100 million rows, the usual advice jumps straight to sharding or partitioning. That skips the layers where most speedups actually live. This is the playbook I ran on a 109M-row production system, in the order that pays — and where partitioning finally entered, it needed hardware-aware design, not the date-sliced default.

This is the companion to my case study on partitioning a 109M-row telemetry table 720x faster. That article covers the layout decision in depth. This one covers everything that came before it: the checklist that finds most classic database pain before anyone talks about splitting tables. If your Postgres reports run minutes instead of seconds, this playbook finds it faster and safer than a rewrite.

Layer 1: read the workload before touching anything

Postgres already recorded most of the diagnosis; the job is reading it. Enable pg_stat_statements and look at total time, calls, and hit rate for the top offenders. Around 20% of queries usually account for the bulk of database load. Also collect: table and index bloat, cache hit ratio, autovacuum status on the hot tables, and the hardware you actually run on. This step is half a day and it prevents the most expensive tuning mistake — optimizing a query that barely matters, or heroically fixing something memory would fix.

Layer 2: cheap wins from plans and quirks

With the workload ranked, most rescues find these in the first week:

• Wrong plans from stale statistics. One ANALYZE on a hot table can flip a sequential scan back to an index scan in minutes.
• Queries that cannot use an index because of a shape problem: functions on the predicate column, implicit casts, leading wildcards. Each is a rewrite, not an infra project.
• N+1 patterns from the application: hundreds of identical point queries in one request. Nagging until fixed, but a one-line loop change on the app side.
• Missing covering indexes. A POC index on the real workload data beats any theoretical index debate.
• Overly broad queries: SELECT * plus jsonb mega-columns bloating buffers and cache. Most fixes here are coordinator-side, not database-side.

Layer 3: configuration and hardware honesty

After workload and plans, check whether the instance's configuration matches the instance's job. The recurring offenders: shared_buffers and effective_cache_size far from the actual RAM, work_mem defaults producing on-disk sorts on aggregation-heavy reports, and checkpoint/autovacuum settings tuned for a machine the database outgrew years ago. None of these changes are exciting, and together they routinely buy another order of magnitude before redesigning anything.

Layer 4: only now, the physical layout

If the top use-case queries still scan large fractions of the table after layers 1–3, then a layout change is on the table — and this is what the Perseus case was about. The defining rule: the partition key must match the predicate of your dominant queries, or pruning never fires. Partitioning by date and hoping is the classic failure mode of a tuning project that spends a quarter and ships nothing. For the 109M-row table, hardware tiers of monitoring devices were the real access pattern; enterprise-tier clients read their own history over long ranges, so the two-level layout (time first, device tier second) let pruning eliminate everything else before the planner touched a heap page.

-- The check before any layout migration: is pruning even possible?
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
WHERE read_at >= '2026-10-01' AND read_at < '2026-11-01'
  AND device_id IN (SELECT id FROM enterprise_devices);

-- If the plan touches N partitions for a query that logically
-- needs 1, fix the predicate shape first, not the layout.

The order in one paragraph

Measure the real workload, fix plans and application quirks, align the instance config with the hardware it runs on — and only then talk about partitioning, with a partition key driven by actual predicates. That order turned a 1-hour critical report into a sub-5-second one on 109M+ rows without a downtime window. If your reports are in the wrong unit of time, the profiling step is worth a day before any bigger commitment: check the case study at /work/perseus, or reach out directly by email or Telegram.