PostgreSQL for DevOps: Backups, Replication, and Performance Basics

Most DevOps engineers aren't DBAs, but almost every production system has a database sitting behind it, and Postgres is the one you'll run into most often. This isn't a full SQL course - it assumes you can already write a basic SELECT with a WHERE clause and a simple JOIN. What it covers instead is the operational side: the handful of concepts that matter when you're the one keeping a Postgres instance healthy, not just querying it.

Connections aren't free

Every Postgres connection is a real operating system process on the server, with real memory overhead - Postgres doesn't use lightweight threads for connections the way some databases do. The max_connections setting (default 100) exists because of this, and it's lower than people expect the first time they hit it.

This is why connection pooling tools like PgBouncer exist. Instead of every application instance opening its own direct connection to Postgres, they connect to PgBouncer, which maintains a small pool of real Postgres connections and multiplexes application requests across them. If your app scales to 50 instances each holding 10 connections open "just in case," you've used 500 connections for workloads that might only need 20 active at once - PgBouncer is usually the fix, not raising max_connections further (which just pushes the memory problem onto the server).

Backups: three different tools for three different needs

• pg_dump - a logical backup: exports the schema and data as SQL (or a custom binary format) you can restore into any compatible Postgres version. Good for smaller databases and portability, slow to restore for large ones since it's rebuilding data row by row.
• pg_basebackup - a physical backup: copies the actual data files. Much faster for large databases, but the restore target needs a compatible Postgres version and architecture.
• Continuous archiving (WAL) - Postgres writes every change to a Write-Ahead Log before applying it, primarily for crash recovery. Archiving these WAL files continuously (to S3 or similar) lets you restore to any point in time, not just your last backup - this is what "point-in-time recovery" means in practice.

A single pg_dump on a schedule is a reasonable starting point for a side project. Anything handling real user data in production usually wants WAL archiving, specifically so "we accidentally deleted the wrong rows at 2pm" doesn't mean "we lose everything since last night's backup."

Replication: not just for scaling reads

Streaming replication continuously ships WAL changes from a primary to one or more replicas, which stay nearly in sync in real time. Two reasons this matters operationally:

1. Read scaling - route read-heavy queries (reports, analytics) to a replica so they don't compete with write traffic on the primary.
2. Failover - if the primary goes down, a replica can be promoted to take over, which is a lot faster than restoring from a backup.

The tradeoff to understand: replication is asynchronous by default, meaning there's a small delay before a replica has the latest writes ("replication lag"). If your application reads from a replica immediately after writing to the primary, it can read stale data. Synchronous replication removes this gap but adds latency to every write, since the primary now waits for a replica to confirm. That's a real availability-vs-consistency tradeoff, not a free upgrade.

Two queries that matter more than most

1. Finding out why a query is slow:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

EXPLAIN shows the query plan Postgres intends to use; ANALYZE actually runs the query and shows real timing per step. The output tells you whether Postgres is doing a fast Index Scan or a slow Seq Scan (scanning the entire table row by row). If you see a Seq Scan on a large table for a query like this, the fix is usually an index:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

2. Checking what's actually using your connections right now:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

This one query answers "are we actually near max_connections, and are those connections doing anything (active) or just sitting there (idle)?" This is usually the first thing worth checking when an app starts throwing connection errors.

A couple of things that trip people up in interviews

VACUUM vs VACUUM FULL: Postgres doesn't immediately reclaim space when rows are deleted or updated (it uses MVCC, keeping old row versions around briefly for consistency). Regular VACUUM marks that space reusable without locking the table. VACUUM FULL actually rewrites the table to reclaim disk space, but it takes an exclusive lock. That's fine for a maintenance window, not something to run on a live production table without thinking about it first.

Why WAL matters beyond backups: the Write-Ahead Log is also what makes Postgres crash-safe: changes are written to WAL and confirmed before being applied to the actual data files, so if the server crashes mid-write, Postgres can replay the log on restart instead of ending up with corrupted data.

Where to go next

You don't need to memorize every tuning parameter Postgres has. You need to know which of these four things (connections, backups, replication, slow queries) is actually the problem when something breaks, and that's mostly a matter of having seen the shape of each one before. Check out our Terraform vs Ansible guide next if you're provisioning the infrastructure this database runs on.