PostgreSQL as Your Backend Database: The Decisions That Bite Later

Postgres is the correct default for almost every backend database decision, and that is the least interesting thing about it. What determines whether your backend holds up at a few hundred concurrent users is not the choice of engine. It is connection pooling, migration discipline, and whether anyone looked at a query plan before shipping.

Search for backend Postgres advice and you find two kinds of page: the project homepage telling you it is powerful and mature, and a tutorial wiring Express to a local database. Both are true and neither prepares you for the four things that actually go wrong. This is the gap between the tutorial and the incident.

Connections are the resource you will run out of first
Migrations: the part that decides whether deploys are boring
Indexes: three that fix most slow queries
Transactions, isolation, and the bug that only appears under load
Backups, and the only test that counts
Where the managed version changes the calculation
How this fits the rest of the stack
FAQ

Connections are the resource you will run out of first

This is the single most common way a Postgres-backed backend falls over, and it surprises people because the database is barely under load when it happens.

Postgres allocates a process per connection. Each one costs memory before it has done anything. max_connections is a hard ceiling, and it is not large by default — a hundred on many installations, and managed instances tie it to the plan size. Exceed it and new connections are refused outright:

FATAL: sorry, too many clients already

The arithmetic that catches people out: if your API runs four instances and each opens a pool of twenty connections, that is eighty before a single background worker, cron job, migration runner, or psql session joins in. Autoscale to eight instances during a traffic spike and you are at a hundred and sixty, which means the scale-up event is what takes the database down.

The fixes, in the order you should apply them:

Set an explicit pool size per instance and multiply it out by hand. Instances times pool size, plus workers, plus headroom for a migration and an admin session, must sit under the ceiling.
Put a pooler in front for high instance counts. PgBouncer in transaction mode lets many client connections share few server connections. In transaction mode you lose session-level features — prepared statements across transactions, advisory locks, LISTEN/NOTIFY — so check your ORM’s behaviour before switching.
Set idle timeouts. A pool that holds twenty connections open through the night is holding twenty connections you cannot use elsewhere.
Give background jobs their own smaller pool. Then a runaway worker exhausts its own budget rather than the API’s.

Migrations: the part that decides whether deploys are boring

Schema changes are the highest-risk routine operation a backend performs, and Postgres makes most of them safe if you know which ones are not.

Adding a nullable column is instant. Adding a column with a non-volatile default is also fast on modern Postgres, because the default is stored in the catalogue rather than written to every row. Dropping a column is instant. Renaming is instant. Those are the easy ones.

The ones that hurt:

Adding an index without CONCURRENTLY takes a lock that blocks writes to the table for the duration of the build. On a large table during business hours, that is an outage. CREATE INDEX CONCURRENTLY avoids it at the cost of taking longer and being unable to run inside a transaction — which means your migration tool needs to support running it outside one.
Adding a NOT NULL constraint rewrites and validates the whole table. Add the column nullable, backfill in batches, add a validated check constraint, then set NOT NULL.
Changing a column type may rewrite the table. Some conversions are safe, most are not. Check before assuming.
Any migration that waits on a lock behind a long transaction queues every subsequent query on that table. A migration blocked behind a five-minute analytics query blocks writes for five minutes. Set lock_timeout on migration sessions so it fails fast instead.

The discipline that makes this work is expand-then-contract. Deploy the schema change that is compatible with both the old and new code, deploy the code, then deploy the cleanup that removes the old shape. Three deploys instead of one, and no window where the running code and the schema disagree.

Indexes: three that fix most slow queries

Most backend performance problems are one missing index, and the query plan says which one. Learn to read EXPLAIN (ANALYZE, BUFFERS) and you will diagnose in minutes what otherwise takes an afternoon of guessing.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Seq Scan on a large table with a selective filter is the signal. Three index shapes cover the majority of real cases:

Composite, in the right order. For the query above, (customer_id, status, created_at DESC) serves the filter and the sort from one index. Column order matters: equality columns first, then the range or sort column.
Partial. If ninety-five percent of rows are status = 'complete' and you only ever query the rest, CREATE INDEX ... WHERE status <> 'complete' is a fraction of the size and stays in memory.
GIN for JSONB and full text. A jsonb column queried with containment operators needs a GIN index; a B-tree does nothing for it.

The counterweight: every index slows writes and consumes disk. Indexes that no query uses are pure cost, and pg_stat_user_indexes will tell you which ones have never been scanned. Auditing that once a quarter is one of the highest-value database chores there is.

Transactions, isolation, and the bug that only appears under load

Postgres defaults to READ COMMITTED, which is the right default and is weaker than most people assume. Under it, two concurrent transactions can both read a value, both decide to act on it, and both write — the classic lost update. It is invisible in testing because testing is sequential.

The read-modify-write pattern is where this lives. Reading a balance, computing a new one in application code, and writing it back is a race. Two safe shapes:

-- Let the database do the arithmetic atomically
UPDATE accounts SET balance = balance - 50 WHERE id = 7;

-- Or lock the row for the duration of the transaction
BEGIN;
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
-- application logic here
UPDATE accounts SET balance = 120 WHERE id = 7;
COMMIT;

SELECT ... FOR UPDATE serialises access to that row, which is correct and also a throughput ceiling, so keep the transaction short. And keep transactions off the far side of network calls entirely: a transaction held open while waiting for a payment API holds its locks for the duration of somebody else’s outage.

The related failure is long idle-in-transaction sessions. They hold locks, they prevent autovacuum from cleaning up dead rows, and the table bloats. pg_stat_activity filtered on state = 'idle in transaction' finds them, and a idle_in_transaction_session_timeout prevents them.

Backups, and the only test that counts

There are two kinds of Postgres backup and they answer different questions. A logical dump from pg_dump is portable, restorable table by table, and gets slow on large databases. Physical backups with WAL archiving give you point-in-time recovery, which is what you want when the problem is a DELETE without a WHERE clause at 3pm rather than a lost disk.

The measure that matters is not whether backups exist. It is how long a restore takes, and whether anyone has ever done one. A backup nobody has restored is a hypothesis.

Run a restore into a scratch database once. Time it. That number is your actual recovery time, and it is usually a surprise. Then write it down, because it is the number that determines whether the incident plan is realistic.

Where the managed version changes the calculation

Everything above is your problem on a self-managed instance: the connection ceiling and its tuning, max_connections against available memory, the WAL archive, the retention policy, the minor version upgrades, the network rules that keep the database off the public internet.

On RunxBuild, Postgres is a managed instance with connection limits, backups, user management and private networking, on the same plan ladder as the services that talk to it — $6 for a Basic instance up through the ladder as the working set grows. That does not remove the design decisions in this article. The pool arithmetic is still yours to get right, the migration discipline is still yours, and no platform will add the index for you. What it removes is the operational surface: the archive that silently stopped, the upgrade you postponed, the instance that was reachable from the internet because a security group was written on a Friday.

That is the honest split. Managed hosting takes the operations. The four decisions in this article stay with the person writing the queries.

How this fits the rest of the stack

Postgres is rarely the wrong choice and frequently the wrong size. Model the instance and the services that connect to it together, because the connection ceiling is a property of the pair rather than the database alone. The RunxBuild hosting calculator shows the database, the services and the storage as separate line items, which makes the scale-up conversation a number rather than a guess.

Useful related references:

FAQ

Why do I get too many clients already from PostgreSQL?

Total open connections have hit max_connections. Multiply your instance count by the per-instance pool size, add background workers and admin sessions, and compare against the ceiling. Reduce pool sizes, add idle timeouts, or put PgBouncer in front.

Do I need PgBouncer for a backend on PostgreSQL?

Not until instance count times pool size approaches your connection ceiling. When it does, PgBouncer in transaction mode is the standard answer, but it disables session-level features like advisory locks and LISTEN/NOTIFY, so verify your ORM first.

How do I add an index without locking the table?

Use CREATE INDEX CONCURRENTLY. It does not block writes, takes longer, and cannot run inside a transaction, so your migration tool needs to support statements outside a transaction block.

What isolation level does PostgreSQL use by default?

READ COMMITTED. It prevents dirty reads but allows lost updates in read-modify-write patterns. Either do the arithmetic in a single UPDATE statement or lock the row with SELECT FOR UPDATE.

What is the safe way to add a NOT NULL column to a large table?

Add it nullable, backfill in batches, add a CHECK constraint and validate it separately, then set NOT NULL. Adding NOT NULL directly rewrites and validates the whole table under a lock.