--- title: "14 — Scaling and Performance: Partition, Plan, Profile" uid: scaling-performance tags: ["explain", "roadmap:postgresql-dba", "normalization", "sharding", "postgresql", "profiling", "partitioning", "capacity-planning", "performance"] excerpt: "Scaling splits data — partitioning divides one table within a cluster, sharding divides across clusters — and performance tuning is a measure-find-change loop driven by EXPLAIN and the Golden Signals, not guesswork." date: 2026-08-13T03:27:50+0000 source: https://www.aveshina.my.id/en/blog/scaling-performance --- "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. ```figure ① measure EXPLAIN · pg_stat_statements ② find bottleneck scan · join · lock · bloat ③ change ONE thing index · ANALYZE · config ④ re-measure did it help? never change two things at once ``` 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](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](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/](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](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/](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](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](https://blog.paradedb.com/pages/introducing_analytics) [8] B. Gregg, "The USE Method," 2024. [Online]. Available: [https://www.brendangregg.com/usemethod.html](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](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](https://www.prisma.io/dataguide/postgresql/5-ways-to-host-postgresql) ```quiz Q: What is the difference between partitioning and sharding? - Partitioning splits a table across multiple clusters; sharding splits it within one cluster - Partitioning splits one table into smaller physical pieces within a single cluster; sharding splits data across multiple database instances correct: 1 explain: Partitioning keeps data in one Postgres cluster (one big logical table, many physical pieces). Sharding distributes data across separate clusters/instances. Sharding is the heavier operation, reserved for when partitioning is exhausted. Q: A query plan shows a Seq Scan on a 50-million-row table with a row estimate wildly different from actual rows. What are the two likely fixes? - Add more RAM and restart - Add or fix an index, and run ANALYZE to refresh the stale statistics driving the bad estimate correct: 1 explain: A Seq Scan on a huge table suggests no usable index; a wide estimate-vs-actual gap means planner statistics are stale. ANALYZE refreshes stats so the planner picks a better plan, and an index removes the Seq Scan. Q: Why change only one thing per iteration of the performance loop? - Postgres locks the config after each change - So you can attribute the measured effect to a single cause; changing two things confounds which one helped or hurt correct: 1 explain: Tuning is an experiment. One change per cycle lets you know exactly what produced the result. Two changes at once leaves you guessing which mattered. Q: Which workload type favors heavy indexing, tight transactions, and low work_mem, and which favors sequential scans, parallelism, and high work_mem? - Both favor the same settings - OLTP favors the first set; OLAP favors the second correct: 1 explain: OLTP is many short transactions needing fast single-row lookups (indexes, low work_mem). OLAP is few long aggregations needing scans and parallelism (high work_mem). Tuning them is nearly opposite. Q: The Four Golden Signals for a Postgres service are… - CPU, memory, disk, network - Latency, Traffic, Errors, Saturation correct: 1 explain: The Golden Signals (from Google SRE) are latency, traffic, errors, saturation. USE covers resources (CPU/mem/disk/net) via the saturation lens, but the four-signal checklist is the service-health framing. ```