Scaling PostgreSQL: replicas, caches, shards

One slice of an API’s traffic made PostgreSQL touch 168,717 pages of its shared buffers in the run below, and one statement of the three was 96.2 % of them. One index cut that statement from 162,300 pages to 15,618, which is more relief than a second server would have given.

Every further step relieves a different limit and adds parts of its own: read replicas add read capacity, caches save repeated work at the price of freshness, and shards spread writes over several servers. This lesson orders the steps and shows on a recorded replica why a read can miss a fresh write.

Measure first: one statement often does most of the work

The place to start is pg_stat_statements, an extension that ships with PostgreSQL. It must be loaded at server start through shared_preload_libraries, and it then groups statements by their shape, so that 300 page loads for 300 customers count as one statement with 300 calls. The session below sends 2,000 lookups by id, 300 pages of a customer’s orders and 100 new orders, and ranks the statements by the pages they touched; then it adds one index and sends the same traffic again:

"""Measure before scaling, on PostgreSQL 18.6: api_traffic.py (psycopg 3.3.6) sends the same slice of traffic twice,
and pg_stat_statements ranks its statements by the shared buffers they touched, before and after one index. Buffers
are counted instead of times, because times change from run to run on a shared computer and buffer counts do not."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "shared_preload_libraries": "'pg_stat_statements'",
            "default_statistics_target": "1000", "max_parallel_workers_per_gather": "0", "jit": "off"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.silent("CREATE EXTENSION pg_stat_statements")
    pg.psql("measure_setup.sql", flags=("-q",))
    pg.silent("SELECT pg_stat_statements_reset()")
    pg.app("python3", "api_traffic.py")
    pg.psql("top_statements.sql", note="before: which statement costs the most")
    pg.psql("add_index.sql", note="one index on the column the slow statement filters on")
    pg.app("python3", "api_traffic.py")
    pg.psql("top_statements.sql", note="after: the same traffic again")

$ psql -X -q -f measure_setup.sql

$ python3 api_traffic.py
sent: 2,000 lookups, 300 customer pages, 100 inserts

-- before: which statement costs the most
$ psql -X -a -f top_statements.sql
-- The application's statements, ranked by the shared buffers each touched (hits plus reads).
SELECT regexp_replace(query, '\s+', ' ', 'g') AS statement, calls, rows,
       shared_blks_hit + shared_blks_read AS blocks,
       round(100.0 * (shared_blks_hit + shared_blks_read)
             / sum(shared_blks_hit + shared_blks_read) OVER (), 1) AS pct_of_blocks
FROM pg_stat_statements
WHERE query LIKE '% orders %' AND query NOT LIKE '%pg_stat_statements%'
ORDER BY blocks DESC;
                           statement                           | calls | rows  | blocks | pct_of_blocks 
---------------------------------------------------------------+-------+-------+--------+---------------
 SELECT id, total_cents FROM orders WHERE customer_id = $1     |   300 | 15000 | 162300 |          96.2
 SELECT id, total_cents FROM orders WHERE id = $1              |  2000 |  2000 |   6003 |           3.6
 INSERT INTO orders (customer_id, total_cents) VALUES ($1, $2) |   100 |   100 |    414 |           0.2
(3 rows)

-- one index on the column the slow statement filters on
$ psql -X -a -f add_index.sql
CREATE INDEX orders_customer_id ON orders (customer_id);
CREATE INDEX
SELECT pg_stat_statements_reset() IS NOT NULL AS statistics_reset;
 statistics_reset 
------------------
 t
(1 row)

$ python3 api_traffic.py
sent: 2,000 lookups, 300 customer pages, 100 inserts

-- after: the same traffic again
$ psql -X -a -f top_statements.sql
-- The application's statements, ranked by the shared buffers each touched (hits plus reads).
SELECT regexp_replace(query, '\s+', ' ', 'g') AS statement, calls, rows,
       shared_blks_hit + shared_blks_read AS blocks,
       round(100.0 * (shared_blks_hit + shared_blks_read)
             / sum(shared_blks_hit + shared_blks_read) OVER (), 1) AS pct_of_blocks
FROM pg_stat_statements
WHERE query LIKE '% orders %' AND query NOT LIKE '%pg_stat_statements%'
ORDER BY blocks DESC;
                           statement                           | calls | rows  | blocks | pct_of_blocks 
---------------------------------------------------------------+-------+-------+--------+---------------
 SELECT id, total_cents FROM orders WHERE customer_id = $1     |   300 | 15016 |  15618 |          70.2
 SELECT id, total_cents FROM orders WHERE id = $1              |  2000 |  2000 |   6003 |          27.0
 INSERT INTO orders (customer_id, total_cents) VALUES ($1, $2) |   100 |   100 |    612 |           2.8
(3 rows)

"""One slice of an API's traffic: 2,000 order lookups by id, 300 "orders of a customer" pages and 100 new orders."""
import psycopg

with psycopg.connect(autocommit=True) as conn:
    for n in range(2000):
        # ids from 40,001 up: psycopg sends a Python int as the smallest type that holds it, and pg_stat_statements
        # keeps a statement apart for each parameter type, so one range keeps the lookups in one line
        conn.execute("SELECT id, total_cents FROM orders WHERE id = %s", (40001 + (n * 7919) % 60000,)).fetchone()
    for n in range(300):
        conn.execute("SELECT id, total_cents FROM orders WHERE customer_id = %s", ((n * 13) % 2000 + 1,)).fetchall()
    for n in range(100):
        conn.execute("INSERT INTO orders (customer_id, total_cents) VALUES (%s, %s)", ((n * 17) % 2000 + 1, 1000 + n))
print("sent: 2,000 lookups, 300 customer pages, 100 inserts")

-- 100,000 orders of 2,000 customers, with no index on customer_id yet.
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL,
  total_cents integer NOT NULL
);
INSERT INTO orders (customer_id, total_cents)
SELECT n % 2000 + 1, (n * 37) % 9000 + 100 FROM generate_series(1, 100000) AS n;
ANALYZE orders;

-- The application's statements, ranked by the shared buffers each touched (hits plus reads).
SELECT regexp_replace(query, '\s+', ' ', 'g') AS statement, calls, rows,
       shared_blks_hit + shared_blks_read AS blocks,
       round(100.0 * (shared_blks_hit + shared_blks_read)
             / sum(shared_blks_hit + shared_blks_read) OVER (), 1) AS pct_of_blocks
FROM pg_stat_statements
WHERE query LIKE '% orders %' AND query NOT LIKE '%pg_stat_statements%'
ORDER BY blocks DESC;

CREATE INDEX orders_customer_id ON orders (customer_id);
SELECT pg_stat_statements_reset() IS NOT NULL AS statistics_reset;

The customer page ran 300 times and touched 162,300 pages, 541 a call: the whole table, because nothing told PostgreSQL where one customer’s rows were. With the index the same 300 calls touched 15,618 pages, about 52 a call, the index and the 50 rows of each customer. The 2,000 lookups by id stayed at 6,003 pages, three a call.

A replica would have run the same 541-page scan, and a cache would only have hidden it. So fix the statements and indexes the measurement points at, pool short connections and move to a bigger machine first; only then add servers or stored results.

Replicas, caches and shards each relieve a different limit

Once a tuned server on the biggest machine is not enough, the limit decides the step. Read replicas are standbys that answer read-only queries, long reports among them, while they replay the primary’s WAL: the hot standbyshot standby: A standby server that answers read-only queries while it replays the primary's write-ahead log. The setting hot_standby is on by default, so a standby built with pg_basebackup is a hot standby. More in “Streaming replication: standbys, slots and lag” of the streaming replication lesson. Replicas add capacity for reads only: every write still goes to the one primary, and each replica answers from what it has replayed so far.

Caches keep a computed result so that it is read instead of computed again, in the application or inside PostgreSQL as a materialized viewmaterialized view: A view whose result PostgreSQL stores like a table. It shows the data from its last REFRESH MATERIALIZED VIEW, not the current state of the tables it reads. or a summary table. Caches trade freshness for work, because a stored result is as old as its last update.

Shards split the rows of a table across several servers by a shard keyshard key: The column whose value decides which server, or shard, holds a row, such as the tenant id when each tenant's rows are kept together., such as the tenant a row belongs to. Shards scale writes as well as reads, but a query or a transaction that needs rows from several shards becomes the application’s job, or an extension’s.

Partitioning splits one table into smaller ones on the same server. Each option comes with methods of its own:

• Read-your-writesread-your-writes: A guarantee that a user who has just changed something sees that change on the next read, even when reads normally go to a replica that may be behind. routing reads from the primary after a user’s write, or waits for the replica.
• REFRESH MATERIALIZED VIEW CONCURRENTLY recomputes a view without locking out its readers.
• Incremental view maintenanceincremental view maintenance: Keeping a stored query result current by applying each change of its base tables to it, instead of computing the whole query again; the pg_ivm extension does it for PostgreSQL. with pg_ivm updates a summary as the change happens.
• Two-phase commit prepares a change on every shard before committing it on any.
• Citus is an extension that spreads tables over several PostgreSQL servers.

Answer four questions about your own database, and the table rules out the steps that cannot help yet:

A replica answers from what it has replayed

The manual’s chapter on hot standby says that data takes time to reach a standby, so the same query on the primary and a standby can give different results at nearly the same moment. The session below makes that delay hold still. It pauses replay on the replica with pg_wal_replay_pause(), takes an order on the primary, asks the replica, and then lets it catch up:

"""A read replica that lags, on PostgreSQL 18.6: a primary and one streaming hot standby, its replica. Replay on the
replica is paused with pg_wal_replay_pause(), so the lag that real replicas have now and then holds still long
enough to look at; the primary takes an order, and the replica is asked about it before and after replay resumes.
The session waits (unprinted) until the replica has replayed the setup, and later the new order, before it reads."""

from pg_session import Server

with Server(settings={"autovacuum": "off"}) as pg:
    pg.up()
    pg.psql("lag_setup.sql", flags=("-q",))
    replica = pg.standby()
    pg.wait_for("SELECT count(*) FROM orders", "100000", port=replica)
    pg.psql("lag_pause.sql", port=replica, note="the replica (port 5433)")
    pg.wait_for("SELECT pg_get_wal_replay_pause_state()", "paused", port=replica)
    pg.psql("lag_write.sql", note="the primary (port 5432)")
    pg.psql("lag_read.sql", port=replica, note="the replica, while it is behind")
    pg.psql("lag_resume.sql", port=replica, note="the replica applies the WAL it has received")
    pg.wait_for("SELECT count(*) FROM orders", "100001", port=replica)
    pg.psql("lag_read.sql", port=replica, note="the replica, after it has caught up")

$ psql -X -q -f lag_setup.sql

-- the replica (port 5433)
$ psql -X -a -f lag_pause.sql
-- On the replica: stop applying WAL for a moment, which is how a replica that lags behind looks from outside.
SELECT pg_wal_replay_pause();
 pg_wal_replay_pause 
---------------------
 
(1 row)

-- the primary (port 5432)
$ psql -X -a -f lag_write.sql
-- On the primary: a customer places an order.
INSERT INTO orders (customer_id, total_cents) VALUES (42, 1999) RETURNING id;
   id   
--------
 100001
(1 row)

INSERT 0 1
SELECT count(*) AS orders_on_primary FROM orders;
 orders_on_primary 
-------------------
            100001
(1 row)

-- the replica, while it is behind
$ psql -X -a -f lag_read.sql
-- On the replica: the same questions, answered from what it has replayed so far.
SELECT count(*) AS orders_on_replica FROM orders;
 orders_on_replica 
-------------------
            100000
(1 row)

SELECT count(*) AS new_order_visible FROM orders WHERE id = 100001;
 new_order_visible 
-------------------
                 0
(1 row)

SELECT pg_is_in_recovery() AS is_replica, pg_is_wal_replay_paused() AS replay_paused;
 is_replica | replay_paused 
------------+---------------
 t          | t
(1 row)

-- the replica applies the WAL it has received
$ psql -X -a -f lag_resume.sql
SELECT pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

-- the replica, after it has caught up
$ psql -X -a -f lag_read.sql
-- On the replica: the same questions, answered from what it has replayed so far.
SELECT count(*) AS orders_on_replica FROM orders;
 orders_on_replica 
-------------------
            100001
(1 row)

SELECT count(*) AS new_order_visible FROM orders WHERE id = 100001;
 new_order_visible 
-------------------
                 1
(1 row)

SELECT pg_is_in_recovery() AS is_replica, pg_is_wal_replay_paused() AS replay_paused;
 is_replica | replay_paused 
------------+---------------
 t          | f
(1 row)

CREATE TABLE orders (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id integer NOT NULL, total_cents integer NOT NULL);
INSERT INTO orders (customer_id, total_cents) SELECT n % 2000 + 1, 500 FROM generate_series(1, 100000) AS n;

-- On the replica: stop applying WAL for a moment, which is how a replica that lags behind looks from outside.
SELECT pg_wal_replay_pause();

-- On the primary: a customer places an order.
INSERT INTO orders (customer_id, total_cents) VALUES (42, 1999) RETURNING id;
SELECT count(*) AS orders_on_primary FROM orders;

-- On the replica: the same questions, answered from what it has replayed so far.
SELECT count(*) AS orders_on_replica FROM orders;
SELECT count(*) AS new_order_visible FROM orders WHERE id = 100001;
SELECT pg_is_in_recovery() AS is_replica, pg_is_wal_replay_paused() AS replay_paused;

SELECT pg_wal_replay_resume();

The primary counted 100,001 orders and the replica 100,000, and order 100001 was not there; after pg_wal_replay_resume() the replica showed it. Nothing was wrong with the replica. It answered correctly for the point in the WAL it had reached, and the answer carried no sign that it was old. Step through the same order, server by server:

A user who saves a profile and is then shown the old one has met this replication lagreplication lag: How far a standby is behind its primary: in bytes, as the gap between two WAL positions, or in time, from a commit on the primary until the standby has replayed it. More in “Streaming replication: standbys, slots and lag”, so the reads that follow a user’s own write need a rule of their own.

Reading your own writes from a replica, on 18 and on 19 Pro: opens with a Pro, Premium or Ultimate pass

Summary tables kept current: REFRESH, CONCURRENTLY and pg_ivm Pro: opens with a Pro, Premium or Ultimate pass

Sharding by tenant: routing, fan-out queries and two-phase commit Pro: opens with a Pro, Premium or Ultimate pass

Practise: choosing the next step

These questions use this lesson’s measurements and its replica, starting with the idea you have shown least:

Interview questions

Three design questions follow: a cart that empties once replicas arrive, a dashboard that times out during each refresh, and tenants that outgrow one primary.

Key takeaways

• Measure with pg_stat_statements before adding servers: one statement was 96.2 % of the pages touched, and one index cut it from 162,300 pages to 15,618.
• Replicas scale reads, caches trade freshness for work, and shards spread writes as well as reads, at the price of queries across shards.
• A replica answers correctly for the WAL it has replayed, so a read right after a write can show the old row.