# Postgres in production: connections, backups

A postgres production checklist: the connection limit, long transactions, missing indexes, vacuum, and a restore you have run.

Canonical URL: https://unbroker.cloud/blog/postgres-in-production
Published: 2026-10-30
Reviewed: 2026-10-30
Author: Unbroker

## The short answer

Before real users arrive, fit the app's connections under the server's limit, cut transactions that sit open, give tables a primary key and index the foreign keys you change through, watch vacuum, and restore a backup at least once.

Those are the first things that break a Postgres database. They are not a missing CPU size. The checks below are the same list Unbroker's managed Postgres health card reads from Postgres statistics. How to connect the app is on [Connect an API to PostgreSQL](https://unbroker.cloud/solutions/api-with-postgres) and [connection details](https://docs.unbroker.cloud/databases/connect).

## Self-operated Postgres vs managed Postgres

Someone still has to operate the host, and someone still has to own the schema. Those are not the same job. [Running Postgres on your own Linux server](https://unbroker.cloud/cloud-servers) means you patch that machine and you own recovery of that disk. If you are leaving Railway, the [Railway alternative](https://unbroker.cloud/alternatives/railway) is the comparison.

### A database node you operate

An Apps Postgres node is a private service: a database image, a volume, and a connection other services in the project use. You handle engine upgrades and recovery of that volume. The Postgres template is one of these. New databases from that template use the official Postgres 16 image with pgvector. You run `CREATE EXTENSION vector;` yourself. It is not a managed cluster.

### A provider-managed cluster

A managed cluster is a PostgreSQL the provider operates. You still own schemas, queries, credentials, and the recovery plan you test. On Unbroker it requires Pro. The smallest catalogue plan is 1 vCPU, 1 GB of RAM, and 10 GB of storage, up to 30 GB on that plan, at $21.21 per month. Extra storage on a plan is $0.30 per GB-month. Pro itself is $25 per month with $25 of usage credit, and the cluster price is separate. Free does not include managed databases. The 1 GB RAM plan is a single node. Standby nodes require a plan with at least 2 GB of RAM.

<aside class="plan-box">
<p><strong>On Unbroker.</strong> The create flow is where you pick managed Postgres or an Apps node. Read the quoted price before you confirm. Plans and the node rule are on <a href="/managed-databases/postgres">managed Postgres</a>.</p>
</aside>

## 1. Connections: the limit you hit first

PostgreSQL uses one backend process per client connection. The postmaster spawns that process when the client connects. `max_connections` is the cap on concurrent connections. The default is typically 100, and it can be less if the kernel could not support 100 when the data directory was initialized. You can change it only at server start.

Ordinary clients do not get every slot. `superuser_reserved_connections` defaults to 3. When active connections reach `max_connections` minus that reserve, new connections are accepted only for superusers.

Each running copy of the app opens its own clients. Multiply the connections one copy holds by the number of copies that run at once. Add migrations, admin sessions, and a pooler if you use one. That sum has to stay inside the limit. This post does not pick a pool size for you. The right number is the one that still fits. The Node.js guide's Express example sets the pool max to 5 and says three replicas with that pool can open fifteen connections, before jobs and admin tools. The process around that pool is [Express in production](https://unbroker.cloud/blog/express-in-production).

### When to add PgBouncer

Add a pooler when many app processes would each hold a server connection for the whole time they stay connected. [PgBouncer](https://www.pgbouncer.org/features.html) has three modes. Session pooling, the [default](https://www.pgbouncer.org/config.html), keeps a server connection until the client disconnects and supports PostgreSQL session features. Transaction pooling hands the server connection back when the transaction ends, and it breaks session features such as `SET`, `LISTEN`, `PREPARE`, session advisory locks, and temp tables that preserve rows. Statement pooling also disallows transactions that span more than one statement. Use transaction pooling only if the app does not need those session features.

<aside class="plan-box">
<p><strong>On Unbroker.</strong> The health card shows connections in use against the maximum. It reads Postgres statistics, not your rows. It does not set <code>max_connections</code> for you.</p>
</aside>

## 2. Long-running transactions

An `UPDATE` or `DELETE` leaves the old row version in place until no transaction can still see it. Vacuum is what reclaims that space. A transaction that stays open keeps old row versions interesting, so vacuum cannot treat them as garbage yet. PostgreSQL's wraparound guidance says to end long-running open transactions, and to find them in `pg_stat_activity` where `age(backend_xid)` or `age(backend_xmin)` is large.

`xact_start` is when the current transaction began, or null if none is active. `state` of `idle in transaction` means the session is inside a transaction and is not running a query.

```sql
SELECT pid, state, xact_start
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
```

`statement_timeout` aborts a statement that runs longer than the limit. `idle_in_transaction_session_timeout` ends a session that sits idle inside an open transaction. Both default to 0, which disables the timeout. PostgreSQL says not to set `statement_timeout` in `postgresql.conf`, because that value would apply to every session.

<aside class="plan-box">
<p><strong>On Unbroker.</strong> The health card flags long-running transactions. It does not choose a timeout for you. Self-hosted Postgres also has a read-only Data tab: you can page through rows, and browsing cannot lock or change them.</p>
</aside>

## 3. Schema mistakes that hurt later

A missing key does not stop the first insert. It shows up when something has to find the row again, or when a delete has to search the other table.

### Tables without a primary key

Declaring a primary key creates a unique B-tree index on those columns and forces them `NOT NULL`. PostgreSQL uses that key as the default target of foreign keys that point at the table. A client that edits rows usually needs it to tell the rows apart. The database does not require you to declare one.

### Foreign keys without an index

A foreign key must point at a primary key, a unique constraint, or columns of a non-partial unique index, so the referenced side already has an index. The referencing columns do not get an index automatically. A `DELETE` or an `UPDATE` of the referenced row scans the referencing table for matches. Index those columns when that scan matters. The declaration skips the index because it is not always needed, and because you choose the index.

<aside class="plan-box">
<p><strong>On Unbroker.</strong> The health card flags tables without a primary key and foreign keys without an index. Fixing them is a schema change you run. The card does not create the index.</p>
</aside>

## 4. Vacuum falling behind

Vacuum reclaims dead row versions so the table does not grow without a bound. Plain `VACUUM` marks space for reuse. It does not hand that space back to the operating system except in a narrow case. `VACUUM FULL` rewrites the table and needs an exclusive lock. Autovacuum never runs `VACUUM FULL`. It tries to vacuum often enough that you do not need one. The docs say it is unwise to turn the daemon off unless the workload is extremely predictable, because a surprise burst of updates can bloat a table until `VACUUM FULL` is the way to get the space back.

`n_dead_tup` is an estimate of dead rows. `last_autovacuum` is the last time the autovacuum daemon vacuumed that table. Use both. A quiet table can have an empty `last_autovacuum` without being in trouble.

```sql
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
```

<aside class="plan-box">
<p><strong>On Unbroker.</strong> The health card flags tables that are behind on vacuum. It uses these statistics. It does not change autovacuum settings.</p>
</aside>

## 5. Backups you have restored

`pg_dump` writes a dump that can recreate the database as of when the dump started. A text dump is reloaded with `psql`. A custom-format dump is restored with `pg_restore`. The dump is a snapshot from the start of `pg_dump`, and it does not block ordinary work, except operations that need an exclusive lock. It is not a file-level copy of the data directory. After a SQL restore, PostgreSQL says to run `ANALYZE` so the planner has statistics.

Put the dump somewhere other than the only disk the database is on. Then load it once before launch. A file you have never restored is a hope.

A code rollback does not put rows back. That split is the point of [backups and rollback for AI-built apps](https://unbroker.cloud/blog/ai-built-app-to-production). Where the migration runs, and why a rollback leaves the schema in place, is [database migrations on deploy](https://unbroker.cloud/blog/database-migrations-on-deploy). What changes when that app is Django, including `DEBUG`, `ALLOWED_HOSTS`, and a production server instead of `runserver`, is [Django in production](https://unbroker.cloud/blog/django-in-production).

<aside class="plan-box">
<p><strong>On Unbroker.</strong> For a managed cluster, backup and restore depend on the engine and the plan. There is no single recovery promise for every cluster. Test a restore before you treat it as production. An Apps node keeps data on its volume. Volume backups go to an S3-compatible bucket you control, on a schedule you set, and they require Pro. Images recognized as Postgres, including <code>pgvector/pgvector</code>, <code>postgis/postgis</code>, and <code>timescale/timescaledb</code>, get <code>pg_dump</code> backups. The volume path is on <a href="/solutions/persistent-files">volume backups</a>.</p>
</aside>

## Credentials and connection strings

The connection string is a secret. It does not belong in the repository.

On Unbroker, each variable is All, Production, or Preview. A value scoped to an environment overrides All for that key, so a preview can use a different database without changing production. A new preview copies All and Preview variables, and it does not receive Production-only values. Put the production database credential in Production, not in All, unless you mean for previews to see it.

## Pre-launch Postgres checklist

Do these before real users write rows. Each line is one check.

1. Read `max_connections` on the server you will actually use.
2. Multiply one app copy's connections by the copies that run at once, and add every other client.
3. Leave the superuser reserve free. Do not plan to use every slot.
4. Add a pooler only if that product still does not fit, and pick session or transaction mode on purpose.
5. Find sessions where `xact_start` is old, and end them.
6. Decide whether `idle_in_transaction_session_timeout` should be non-zero. The default is off.
7. Give each table that clients update a primary key.
8. Index referencing columns on foreign keys you delete or update through.
9. Read `n_dead_tup` and `last_autovacuum` on tables that change.
10. Store a dump somewhere other than the database disk.
11. Restore that dump once, then run `ANALYZE`.
12. Keep the production connection string out of All if previews must not see it.

## FAQ

### What should I configure in Postgres before going to production?

Set how connections are used against max_connections, end transactions that sit open, give tables a primary key, index foreign-key columns you delete or update through, watch autovacuum, and restore a backup once. A page that queries the database is not that list.

### Why does Postgres say too many connections?

The server refuses new non-superuser connections once active connections reach max_connections minus the slots reserved for superusers. The default of max_connections is typically 100, and it can be lower if the kernel could not support that at initdb. Each client connection is its own backend process.

### Do I need PgBouncer for a small app?

Not if the connections one copy of the app holds, times the copies that run at once, still fit under max_connections with room for other clients. Add a pooler when many app processes would each hold a server connection for the whole session. PgBouncer's default mode is session pooling.

### How do I find long-running transactions in Postgres?

Query pg_stat_activity. xact_start is when the current transaction began, and it is null when no transaction is active. The state idle in transaction means the session is inside a transaction and is not running a query.

### Should every Postgres table have a primary key?

PostgreSQL does not require one, and adding one creates a unique B-tree index and marks the columns NOT NULL. The docs also say a client that edits rows usually needs that key to identify them. Unbroker's health card flags tables that do not have one.

### Do foreign keys need an index in Postgres?

The foreign key itself does not create an index on the referencing columns. PostgreSQL says a delete or update of the referenced row has to scan the referencing table, so indexing those columns is often a good idea. It is not automatic, because it is not always needed.

### How do I know if autovacuum is falling behind?

Read pg_stat_user_tables. n_dead_tup is the estimated number of dead rows, and last_autovacuum is the last time the autovacuum daemon vacuumed that table. A growing dead-row estimate on a table that keeps changing, with an old or empty last_autovacuum, is the signal. There is no single cutoff in the docs.

### How often should I test a Postgres restore?

At least once before you call the database production, and again when the backup path changes. A dump you have never loaded is not a plan. The Postgres docs also say to run ANALYZE after restoring a SQL dump so the planner has statistics.

### Is managed Postgres worth it compared to running it myself?

Use a managed cluster when you want the provider to operate the database host. Use an Apps Postgres node when you will operate the image, the volume, and recovery yourself. Either way you still own the schema, the queries, the credentials, and a restore you have tested. This is not a price comparison.

## Related reading

- [Managed Postgres](https://unbroker.cloud/managed-databases/postgres)
- [Connect an API to PostgreSQL](https://unbroker.cloud/solutions/api-with-postgres)
- [Backups and rollback for AI-built apps](https://unbroker.cloud/blog/ai-built-app-to-production)
- [Volume backups](https://unbroker.cloud/solutions/persistent-files)
- [Django in production](https://unbroker.cloud/blog/django-in-production)
- [Express in production](https://unbroker.cloud/blog/express-in-production)
- [Pricing](https://unbroker.cloud/pricing)
