Scaling PostgreSQL: the order of moves

conceptThe concept

A single PostgreSQL primary on modern hardware handles tens of thousands of transactions per second and many terabytes. Before adding nodes, work down this list:

Partitioning is a manageability tool first; it speeds up queries only when pruning removes most partitions. Replicas scale reads, never writes. Sharding is the only move that scales writes, and it changes your application, your transactions and your operations forever.

whyWhy it matters

Each step up the list multiplies operational complexity. Teams that shard early spend years paying for it: cross-shard queries, rebalancing, distributed backups and transactions that no longer span the whole dataset. Teams that measure first usually find a missing index.

Use caseReal-world scenario

An e-commerce database hits 90% CPU at peak. pg_stat_statements shows one search query using 60% of total time. An expression index brings CPU to 35%. A year later, reporting queries slow down checkout; moving them to a replica fixes it. Sharding never becomes necessary.

Try itExamples

Run and edit these in the interactive lesson.

SELECT relname, n_live_tup, seq_scan, idx_scan,
       n_tup_ins + n_tup_upd + n_tup_del AS rows_written
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

SELECT pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS data_and_indexes,
       current_setting('shared_buffers') AS shared_buffers
FROM pg_class c
WHERE c.relnamespace = 'public'::regnamespace AND c.relkind IN ('r', 'p');

SELECT date_trunc('quarter', created_at)::date AS quarter,
       count(*) AS orders,
       pg_size_pretty(sum(pg_column_size(o.*))) AS approx_size
FROM orders o
GROUP BY 1
ORDER BY 1;

SELECT pg_is_in_recovery() AS is_replica,
       current_setting('transaction_read_only') AS read_only;

internalsUnder the hood

Vertical scaling works because PostgreSQL performance is dominated by whether the hot pages are in shared_buffers or the OS page cache. When the working set fits in RAM, reads are memory reads; when it does not, every cache miss is a disk read. That is why "data size versus RAM" and the cache hit ratio are the first numbers to look at.

Avoid / preferDo this, not that

-- CPU is at 90%, so add shards
SELECT create_distributed_table('orders', 'customer_id');

SELECT left(query, 60), calls, round(total_exec_time) AS ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;

-- writes are slow, so add read replicas

-- replicas scale reads only; for write load look at
-- batching, fewer indexes, HOT updates, faster storage, then sharding

PitfallsCommon mistakes

• Raising max_connections to thousands instead of adding a pooler; memory and lock contention get worse, not better.
• Partitioning a table expecting faster queries when the queries do not filter on the partition key.
• Adding read replicas without handling replication lag, so users do not see their own writes.

LimitsWhen not to use it

Do not follow the list blindly when the requirement is explicit: a dataset that is already 50 TB and growing 1 TB a month, or a legal requirement to keep EU and US data in separate regions, may need sharding or a functional split from day one.

SecuritySecurity notes

Every new node is a new attack surface: replicas need the same TLS, pg_hba.conf and patching discipline as the primary, and caches can leak data that row-level security would have filtered. Apply the same access rules to every copy of the data.

ChallengeTry it yourself

SELECT relname,
       seq_tup_read + coalesce(idx_tup_fetch, 0) AS rows_read,
       n_tup_ins + n_tup_upd + n_tup_del AS rows_written,
       round((n_tup_ins + n_tup_upd + n_tup_del)::numeric
             / nullif(seq_tup_read + coalesce(idx_tup_fetch, 0), 0), 3) AS write_ratio
FROM pg_stat_user_tables
ORDER BY write_ratio DESC NULLS LAST;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Hot standby
• Table partitioning
• Resource consumption