Operations & Scaling

Writing correct SQL is only half the job. The other half is keeping a single PostgreSQL server happy when it suddenly has to serve thousands of users, survive a crashed disk, hold tables with billions of rows, and refuse anyone who should not be there. That collection of concerns is what people mean when they say operations — the work of running a database in production rather than on your laptop.

This module steps away from query syntax and looks at the shape of a real deployment. None of it changes the SQL you already know; instead it surrounds that one server with the pieces that make it dependable at scale. Unlike the earlier modules, most of these lessons need more than one process, so they were run in throw-away Docker labs on PostgreSQL 18.6 (Docker Desktop on a Mac, September 2026): a primary and a replica on an internal network, a PgBouncer 1.26 pooler, a container with WAL archiving and a real point-in-time restore. Each lesson shows the real commands and output, names its containers postgresql-b3-*, and ends with the command that removes them.

The four production concerns

Almost every operational topic in PostgreSQL falls under one of four headings. Each one answers a different “what happens when…” question.

Concern	The question it answers	The usual answer
High availability	What if the primary server dies?	Replication and failover
Many clients	What if thousands of apps connect at once?	Connection pooling
Big tables	What if one table grows to billions of rows?	Partitioning
Durability & access	What if a disk fails or an attacker connects?	Backups and security

A small project may ignore all four. A serious production system eventually needs every one of them, and the four interact: replicas help availability and spread read load, pooling protects a primary that is also feeding replicas, and so on.

What a fresh PostgreSQL 18 gives you

Most of these features are one setting away, and several are already on. This is what a brand-new 18.6 server says about itself:

-- sql/defaults.sql
SELECT name, setting
FROM pg_settings
WHERE name IN ('max_connections', 'wal_level', 'max_wal_senders', 'archive_mode', 'ssl', 'listen_addresses', 'password_encryption', 'hot_standby')
ORDER BY name;

        name         |    setting
---------------------+---------------
 archive_mode        | off
 hot_standby         | on
 listen_addresses    | *
 max_connections     | 100
 max_wal_senders     | 10
 password_encryption | scram-sha-256
 ssl                 | off
 wal_level           | replica
(8 rows)

The lessons build on exactly these numbers. wal_level = replica and max_wal_senders = 10 mean streaming replication needs no server restart, just a role and a pg_hba.conf line. max_connections = 100 is the ceiling the pooling lesson runs into. archive_mode = off is why a fresh server has no point-in-time recovery until you switch it on. ssl = off shows that encryption is your decision, and listen_addresses = * is the Docker image’s choice (PostgreSQL’s own default is localhost, docs), so network exposure is another one, and password_encryption = scram-sha-256 is the modern default for stored passwords.

A production-shaped deployment

A laptop runs one PostgreSQL process and one client. A production setup spreads work across several pieces. Application servers do not talk to the database directly; they go through a connection pooler that keeps a small number of reusable server connections. One node is the primary that accepts writes, and one or more replicas receive a continuous copy of its changes and answer read-only queries.

flowchart TD
  App1[App server] --> Pool[Connection pooler]
  App2[App server] --> Pool
  App3[App server] --> Pool
  Pool --> Primary[(Primary - reads and writes)]
  Primary -->|streams changes| R1[(Replica - reads)]
  Primary -->|streams changes| R2[(Replica - reads)]
  Primary -->|base backup plus WAL| Backup[Backup and archive storage]

A production-shaped PostgreSQL deployment

Read the diagram from the top. Many app servers funnel through one pooler so the primary never sees a flood of raw connections. The primary streams every change to its replicas, which take read traffic off its back. And the primary’s data is continuously copied to backup storage so the whole thing can be rebuilt after a disaster.

What this module covers

• Replication — a real primary and replica: pg_basebackup, replication lag you can see, a synchronous commit that hangs when the standby is gone, pg_promote and timelines, and logical replication with its replica-identity, DDL and slot-retention traps.
• Connection pooling — why each PostgreSQL connection is an operating-system process (about 3 MB each in the lab), how PgBouncer served 200 clients with 10 server connections, what breaks in transaction mode, and how to size a pool.
• Partitioning — range, list and hash partitions, pruning proven with plans, the default-partition trap, and what partitioning does and does not speed up, measured on 3 million rows.
• Backup and security — pg_dump and pg_restore, a point-in-time recovery that stopped just before a DELETE, incremental backups, pg_hba.conf, TLS, GRANT, and row-level security.

Where this fits

You already know how to model data, query it, index it, and wrap changes in transactions. Operations is what lets that knowledge survive contact with real traffic. Nothing here is a different PostgreSQL; it is the same server, configured and surrounded so that a single machine’s limits and a single machine’s failures stop being your application’s limits and failures.

Mental Model

Think of the database server as one very busy chef. Replication gives the kitchen a second chef reading the same recipe book; pooling puts a maître d’ at the door so the chef is never mobbed; partitioning organises the pantry so one shelf can be cleared quickly; backups are the photographs of the kitchen you keep in a different building; and security is who is allowed in, which door they use, and what they may touch. None of these changes how the chef cooks.

Tips / gotchas

• Do not reach for replication or partitioning on day one. A single well-tuned server with good indexes handles a surprising amount of load; add complexity only when you can measure the need.
• Replicas are for availability and read scaling, not for taking writes — every write still goes to one primary. And a replica is not a backup.
• A backup you have never restored is a hope, not a backup. The only proof is a successful test restore.
• Security is not a final step you bolt on; least-privilege roles and restricted network access are cheapest to set up before anything depends on the loose version.

🐘 Same database, bigger stage

Operations does not replace what you have learned — it scales it. The primary in every diagram in this module is running the very same SQL engine you used in the earlier lessons. Replication, pooling, partitioning, and backups are the supporting cast that lets that engine perform in production.

Common Misconceptions

• “Operations means a different database.” It is the same server with a second process next to it (a replica, a pooler, an archive).
• “More connections means more throughput.” In the pooling lab 200 clients on 10 server connections ran at the same throughput as fewer; the extra connections only cost memory.
• “High availability and backups are the same thing.” Replication copies a mistaken DELETE to every replica; only a backup with archived WAL lets you rewind.

What is the main job of a connection pooler in front of PostgreSQL?

It encrypts the data stored on disk It multiplexes many client connections onto a small number of server connections It automatically writes SQL queries for you It replaces the need for backups

In a typical replicated setup, where do write statements go?

To any replica that has spare capacity Only to the primary server To the connection pooler, which stores them To the backup archive

Which production concern does partitioning primarily address?

Surviving a crashed primary Handling thousands of simultaneous clients Working efficiently with one extremely large table Encrypting traffic on the wire

A colleague says "we have a replica, so we do not need backups". What is the flaw?

Replicas are slower than backups A replica replays every change, including a mistaken DELETE, so it cannot rewind Replicas cannot be promoted Backups are required by PostgreSQL to start

What does a brand-new PostgreSQL 18 server have for wal_level and archive_mode?

wal_level = minimal and archive_mode = on wal_level = replica and archive_mode = off wal_level = logical and archive_mode = on Both are unset until you configure them

Submit

Mark as complete