14 — Scaling and Performance: Partition, Plan, Profile

"Add an index and hope" was my performance strategy, and it worked exactly once. The framing that organized it: scaling is about splitting data — partitioning divides one table within a cluster, sharding divides data across clusters — and performance tuning is a measure-find-change loop driven by EXPLAIN, pg_stat_statements, and the Golden Signals, not guesswork [1][2][3]. Once I separated the structural splits from the diagnostic loop, "the database is slow" became a methodical sequence instead of a panic.

Partitioning: splitting one big table

A partition divides a large table into smaller physical pieces based on a rule, while keeping it queryable as one logical table [1]. The win is that queries with a partition-key filter scan only the relevant partitions (partition pruning), and maintenance operations (archiving old data, reindexing) act on a partition instead of the whole table.

Postgres supports three partitioning strategies:

• Range — by a range of values, typically a timestamp. Monthly partitions of an events table is the classic case.
• List — by discrete values, like region or tenant.
• Hash — by a hash of a key, distributing rows evenly for load balancing.

CREATE TABLE events (
  id BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

The rule I follow: partition when a table grows without bound (logs, events, telemetry) and queries naturally filter by time or another key. Partitioning is not free — query planning has overhead, and a wrong partition key gives no pruning — so I don't partition every table, only the ones that will get genuinely large.

Sharding: splitting across clusters

Sharding goes further: it splits data across multiple database instances (shards), each holding a portion [4]. This is the path when one cluster is saturated and vertical scaling has run out. Sharding in Postgres is typically done with extensions like Citus (which turns Postgres into a distributed database) or by hand-rolling with foreign data wrappers and consistent hashing.

The cost is high. Cross-shard joins, distributed transactions, and referential integrity all become hard problems. The roadmap's guidance — explore partitioning first, shard only when partitioning within one cluster is exhausted — is the right ordering. Most workloads never need to shard; partitioning plus read replicas and connection pooling carries a Postgres cluster a very long way.

Schema design: normalization and its tradeoffs

Underneath scaling is schema design. Normalization organizes tables to minimize redundancy via the normal forms [5]:

• 1NF — atomic values, unique rows.
• 2NF — non-key attributes depend on the whole primary key (no partial dependency).
• 3NF — non-key attributes depend on nothing but the primary key (no transitive dependency).
• Higher forms (BCNF, 4NF, 5NF) address rarer anomalies.

Normalized schemas minimize redundancy and protect integrity — the right default for transactional data. Denormalization trades that for read speed by duplicating data to avoid joins, appropriate for read-heavy analytics. For data warehousing, the star schema (a central fact table surrounded by dimension tables) and the snowflake schema (normalized dimensions) are the established patterns [6]. The choice is workload-driven: OLTP favors normalization; OLAP favors star/snowflake.

Workload shapes: OLTP, OLAP, HTAP

The roadmap distinguishes workload types, and the distinction drives every tuning decision [7]:

• OLTP (Online Transaction Processing) — many short transactions, heavy inserts/updates, small result sets. Needs fast single-row lookups, tight transactions, lots of indexes. The "app database" workload.
• OLAP (Online Analytical Processing) — few long queries aggregating large volumes. Needs sequential scans, parallelism, columnar-friendly storage, materialized views. The "data warehouse" workload.
• HTAP (Hybrid) — both at once. Postgres handles it via its general architecture plus extensions (columnar storage via extensions like Citus or ParadeDB) [7].

Tuning for OLTP (low work_mem, many connections pooled, heavy indexing) is nearly opposite to OLAP (high work_mem, parallelism, fewer wider indexes). Knowing which workload I'm tuning prevents fighting the wrong lever.

The performance loop: EXPLAIN first

When a query is slow, the first move is always EXPLAIN (ANALYZE, BUFFERS) [2]. EXPLAIN shows the plan the planner chose; ANALYZE actually runs the query and shows real timings; BUFFERS shows cache hit/miss counts. Reading the plan is the skill:

• Seq Scan on a large table — often a missing index, or a query that can't use one.
• Index Scan vs. Bitmap Scan vs. Seq Scan — the planner picks based on selectivity; aSeq Scan isn't always bad if the table is small or most rows match.
• Nested Loop vs. Hash Join vs. Merge Join — join strategies, chosen by row counts and whether inputs are sorted.
• Rows estimate vs. actual — a big gap means stale statistics; running ANALYZE fixes the plan.

Tools that complement EXPLAIN: pg_stat_statements for the slowest queries across the whole workload [3], pg_stat_activity for what's running right now, PEV2 / depesz / explain.dalibo.com for visualizing plans, PgBadger for log-based analysis, and OS-level tools (top, iotop, perf, iostat/sysstat, eBPF) for resource saturation. The diagnostic frameworks — USE for resources, RED for requests, Golden Signals as the unified checklist — give the structure for turning "slow" into a specific cause [8][9].

Capacity planning

The forward-looking complement is capacity planning [10]: forecasting resource needs based on workload growth. The factors:

• Workload — query rate, transaction rate, expected growth.
• Data size — current size, growth rate, retention policy.
• Resources — CPU, memory, disk I/O, network; where is the next saturation point.
• Provisioning — vertical (bigger box) vs. horizontal (read replicas, partitioning, eventually sharding) vs. high availability (replicas for failover).

The practical output is knowing the next bottleneck before it hits — "at current growth, disk fills in 8 months; connection count saturates CPU in 3 months unless we add PgBouncer." Capacity planning is monitoring projected forward.

How I use this

The loop is the habit. When something is slow, I EXPLAIN (ANALYZE, BUFFERS) the specific query, check pg_stat_statements for the workload-wide slow list, and consult the Golden Signals dashboard for whether the problem is latency, errors, or saturation. I change one thing at a time — an index, an ANALYZE, a config knob — and re-measure. Structurally, I partition tables that grow without bound by a time key, and I normalize for transactional workloads while reserving star schemas for analytics. I reserve sharding for the case where partitioning and read replicas are genuinely exhausted, because the operational cost is steep. And capacity planning is a quarterly exercise against the monitoring data, so the next bottleneck is a planned event, not an incident.

References

[1] PostgreSQL Global Development Group, "Table Partitioning," 2024. [Online]. Available: https://www.postgresql.org/docs/current/ddl-partitioning.html

[2] PostgreSQL Global Development Group, "Using EXPLAIN," 2024. [Online]. Available: https://www.postgresql.org/docs/current/using-explain.html

[3] Timescale, "Using pg_stat_statements to optimize queries," 2024. [Online]. Available: https://www.timescale.com/blog/using-pg-stat-statements-to-optimize-queries/

[4] G. Valler, "Exploring effective sharding strategies with PostgreSQL," Medium, 2023. [Online]. Available: https://medium.com/@gustavo.vallerp26/exploring-effective-sharding-strategies-with-postgresql-for-scalable-data-management-2c9ae7ef1759

[5] Cybertec, "A guide to data normalization in PostgreSQL," 2024. [Online]. Available: https://www.cybertec-postgresql.com/en/data-normalization-in-postgresql/

[6] Timescale, "How to design your PostgreSQL database: two schema examples," 2024. [Online]. Available: https://www.timescale.com/learn/how-to-design-postgresql-database-two-schema-examples

[7] ParadeDB, "Transforming Postgres into a fast OLAP database," 2024. [Online]. Available: https://blog.paradedb.com/pages/introducing_analytics

[8] B. Gregg, "The USE Method," 2024. [Online]. Available: https://www.brendangregg.com/usemethod.html

[9] Google SRE, "The Four Golden Signals," 2024. [Online]. Available: https://sre.google/sre-book/monitoring-distributed-systems/#xref_monitoring_golden-signals

[10] Prisma, "5 ways to host PostgreSQL databases," 2024. [Online]. Available: https://www.prisma.io/dataguide/postgresql/5-ways-to-host-postgresql