Running PostgreSQL in Production on EC2: Tuning, PgBouncer and Backups

PostgreSQL's default configuration is designed to start successfully on a machine with very little memory. That is a sensible goal for a package default and a terrible one for a database server, because it means an untouched postgresql.conf on a 16 GB instance is using a fraction of what it has been given.

This guide covers the settings that actually matter on an EC2 instance, when to introduce a connection pooler, and how to set up backups you can restore from. It assumes PostgreSQL 16 or 17 on Ubuntu, though nearly all of it applies from version 12 onward.

Storage and filesystem, before anything else

Database performance on EC2 is decided by the volume more often than by the configuration file. Put the data directory on a dedicated EBS volume, not the root volume — it lets you snapshot the data independently, resize it without touching the OS, and detach it if the instance dies.

Use gp3 rather than gp2. gp3 lets you provision IOPS and throughput independently of size, so you are not forced to over-allocate 500 GB of storage just to get the IOPS you need. A gp3 volume starts at 3,000 IOPS and 125 MB/s regardless of size, which gp2 only reaches at around 1 TB.

sudo mkfs.ext4 -m 0 /dev/nvme1n1
sudo mkdir -p /var/lib/postgresql/data
echo '/dev/nvme1n1 /var/lib/postgresql/data ext4 defaults,noatime 0 2' | sudo tee -a /etc/fstab
sudo mount -a

noatime stops the kernel writing an access timestamp on every read, which is pure overhead for a database. The -m 0 disables the 5 percent root reserve, which exists to keep a system usable when a root filesystem fills up and is meaningless on a dedicated data volume — on 500 GB it silently reclaims 25 GB.

The configuration settings that matter

There are roughly 350 settings in postgresql.conf. Fewer than a dozen make a difference for most workloads. These are the ones, with sizing for a hypothetical 16 GB instance dedicated to the database.

Two of these are commonly misunderstood. effective_cache_size does not allocate anything — it is purely a hint that tells the planner how much data it can expect to find cached, which changes whether it chooses an index scan or a sequential scan. And random_page_cost at its default of 4.0 encodes the assumption that random reads are four times more expensive than sequential ones, which was true for spinning disks and is not remotely true for SSDs. Leaving it at 4.0 on gp3 storage makes the planner avoid index scans it should be using.

Apply changes through a drop-in file rather than editing the packaged postgresql.conf, so a package upgrade cannot silently revert them:

sudo tee /etc/postgresql/17/main/conf.d/tuning.conf > /dev/null <<'EOF'
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 32MB
maintenance_work_mem = 1GB
random_page_cost = 1.1
effective_io_concurrency = 200
wal_compression = on
max_wal_size = 4GB
log_min_duration_statement = 500ms
EOF

sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

log_min_duration_statement = 500ms is the highest-value line in that file. It logs any query taking longer than half a second, which turns performance work from guesswork into reading a list. Start there before touching anything else.

Connection pooling with PgBouncer

Every PostgreSQL connection is a separate operating system process with its own memory. A few hundred idle connections consume gigabytes and add scheduling overhead to the whole machine. Meanwhile a typical web application opens a connection pool per worker process, so ten Gunicorn workers with a pool size of ten is a hundred connections from one small app.

PgBouncer sits between the application and PostgreSQL and multiplexes many client connections onto a small number of real server connections. In transaction pooling mode, a server connection is assigned to a client only for the duration of a transaction, so a few dozen real connections can serve thousands of clients.

sudo apt install -y pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 600

Point your application at port 6432 instead of 5432 and nothing else changes — until it does. Transaction pooling breaks anything that assumes session state persists between transactions, because the next transaction may land on a different server connection.

The prepared statement issue is the one that catches Python and Node applications. SQLAlchemy with asyncpg uses protocol-level prepared statements by default, and against an older PgBouncer in transaction mode this produces intermittent errors about statements already existing. Either set max_prepared_statements in PgBouncer 1.21 or later, or disable statement caching in the driver.

# SQLAlchemy + asyncpg through PgBouncer, older versions
engine = create_async_engine(
    DATABASE_URL,
    connect_args={"statement_cache_size": 0, "prepared_statement_cache_size": 0},
    pool_size=5,
    max_overflow=5,
)

Backups: the part people get wrong

There are two distinct kinds of backup and they solve different problems. A logical backup with pg_dump produces a portable file that can be restored into a different PostgreSQL version — good for moving data, slow to restore at size, and it captures only the moment it ran. A physical backup plus WAL archiving lets you restore to any point in time, which is what you want when someone runs a DELETE without a WHERE clause at 14:32.

For a small database, a nightly pg_dump to S3 is genuinely adequate:

#!/usr/bin/env bash
set -euo pipefail

STAMP=$(date -u +%Y%m%dT%H%M%SZ)
DEST=/var/backups/postgres
mkdir -p "$DEST"

pg_dump --format=custom --compress=9 --dbname=myapp \
        --file="$DEST/myapp-$STAMP.dump"

aws s3 cp "$DEST/myapp-$STAMP.dump" \
          "s3://my-backups/postgres/myapp-$STAMP.dump" \
          --storage-class STANDARD_IA

find "$DEST" -name 'myapp-*.dump' -mtime +7 -delete

Use --format=custom rather than plain SQL. The custom format is compressed, and it lets pg_restore run in parallel with -j and restore individual tables selectively, both of which matter enormously when you are restoring under pressure.

Schedule it with a systemd timer rather than cron, so failures land in the journal alongside everything else and the unit can declare its dependencies:

# /etc/systemd/system/pg-backup.timer
[Unit]
Description=Nightly PostgreSQL backup

[Timer]
OnCalendar=*-*-* 03:17:00
RandomizedDelaySec=600
Persistent=true

[Install]
WantedBy=timers.target

The odd minute is deliberate. Everything in the world runs at 03:00, which means S3 uploads, snapshot APIs and your own monitoring all contend at the same instant. Persistent=true runs a missed job after a reboot rather than skipping the night entirely.

For point-in-time recovery, use pgBackRest rather than assembling it from archive_command and rsync yourself. It handles parallel compressed backups, retention, encryption and — critically — verification.

sudo apt install -y pgbackrest

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-bucket=my-pgbackrest
repo1-s3-region=ap-south-1
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
process-max=4
start-fast=y

[myapp]
pg1-path=/var/lib/postgresql/17/main

# The drill, in full
aws s3 cp s3://my-backups/postgres/myapp-latest.dump /tmp/
createdb restore_test
pg_restore --dbname=restore_test --jobs=4 /tmp/myapp-latest.dump
psql -d restore_test -c "SELECT count(*) FROM users;"
dropdb restore_test

Vacuum, and why tables grow when rows are deleted

PostgreSQL uses multiversion concurrency control: an UPDATE writes a new row version and marks the old one dead rather than overwriting in place. Dead rows are reclaimed by autovacuum. When autovacuum cannot keep up, tables and indexes bloat, and query plans degrade even though the live row count has not changed.

The default autovacuum threshold scales with table size — 20 percent of rows changed — which behaves badly for large tables. A table with 50 million rows waits for 10 million dead rows before vacuuming. Tune the busiest tables individually:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 2000
);

Find the tables that need it by querying for dead tuple ratios:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC
LIMIT 20;

A short monitoring checklist

• Connection count against max_connections — alert at 80 percent
• Replication lag in bytes, if you have a replica
• Longest running transaction — a forgotten open transaction blocks vacuum indefinitely
• Cache hit ratio from pg_stat_database, which should sit above 0.99 for most workloads
• Disk free on the data volume, alerting well before 90 percent
• Backup job exit status, alerting on failure and on silence

That third item is worth expanding. A transaction left open — by a crashed client, or a psql session someone forgot — prevents vacuum from reclaiming any row version newer than it, across the entire database. Tables bloat, disk fills, and nothing in the application looks wrong. Set idle_in_transaction_session_timeout to a few minutes and the problem disappears.