Database Foundation Stack: PostgreSQL and Redis Production Tuning

Why a Database Foundation Stack

Most applications start with a single PostgreSQL instance and default Redis config. This works until it doesn't — connection pool exhaustion, silent data loss on Redis restart, corrupted database files, or a DROP TABLE with no backup to restore from.

A production database foundation stack addresses these failure modes before they happen. It's the same Postgres and Redis you already use, but configured for resilience rather than convenience.

Architecture

PostgreSQL Configuration

Memory Tuning

PostgreSQL's performance is dominated by memory configuration. These settings assume 4 GB RAM allocated to the container:

shared_buffers = '1GB'
effective_cache_size = '3GB'
maintenance_work_mem = '256MB'
work_mem = '32MB'
wal_buffers = '16MB'

Rule of thumb:

• shared_buffers = 25% of available RAM
• effective_cache_size = 75% of available RAM
• maintenance_work_mem = 5% of available RAM (for VACUUM, CREATE INDEX)
• work_mem = total RAM / (max_connections × 16) — keep under 64MB

Connection Pooling

The Docker Compose stack exposes PostgreSQL on the default port (5432). For production workloads with more than 20 concurrent connections, add PgBouncer as a sidecar:

pgbouncer:
  image: edoburu/pgbouncer:latest
  environment:
    DB_USER: app
    DB_PASSWORD: "${POSTGRES_PASSWORD}"
    DB_HOST: postgres
    DB_PORT: "5432"
    POOL_MODE: transaction
    DEFAULT_POOL_SIZE: 25
    MAX_CLIENT_CONN: 100

PgBouncer in transaction mode reuses connections between queries, allowing 100 application connections to share 25 database connections.

Automated Backup

The backup system uses three strategies in parallel:

1. Daily pg_dump (logical backup)

PGPASSWORD="${POSTGRES_PASSWORD}" pg_dump \
  -h postgres -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
  --format=custom \
  --compress=9 \
  --file="/backups/daily/$(date +%Y%m%d).dump"

Custom format (.dump) enables parallel restore with pg_restore -j 4.

2. Continuous WAL archiving (point-in-time recovery)

# In postgres.conf
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'

WAL archiving enables restore to any point in time, not just backup snapshots.

3. S3 sync for offsite storage

aws s3 sync /backups s3://your-backup-bucket/postgres/ \
  --storage-class STANDARD_IA

All three are included in the stack's backup-agent container.

Redis Configuration

Persistence Mode

Redis offers two persistence mechanisms. Production deployments use both:

Feature	RDB (Snapshot)	AOF (Append-Only)
What	Point-in-time dump of dataset	Log of every write operation
Recovery speed	Fast	Slower (replays log)
Data loss window	Last snapshot interval	Last second (with always)
File size	Smaller	Larger

The stack configures:

# RDB every 5 minutes if at least 100 keys changed
save 300 100
save 60 1000

# AOF — fsync every second (balance of safety and performance)
appendonly yes
appendfsync everysec
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb

This combination ensures at most 1 second of data loss on crash (AOF) and fast recovery for large datasets (RDB).

Memory Management

Redis is an in-memory store. Set a maxmemory policy to prevent unbounded growth:

maxmemory 512mb
maxmemory-policy allkeys-lru

allkeys-lru evicts the least recently used keys when memory limit is hit. For caching workloads this is ideal. For queue data (Bull/BullMQ), use noeviction and monitor explicitly.

Recovery Procedures

Restore PostgreSQL from pg_dump

pg_restore -h new-host -U postgres -d appdb \
  --clean --if-exists \
  --jobs=4 \
  /backups/daily/20260525.dump

Point-in-Time Recovery (WAL)

# 1. Restore base backup
pg_restore ... /backups/daily/20260524.dump

# 2. Apply WAL up to target time
# Set in postgres.conf:
restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-05-25 14:30:00 UTC'

Restore Redis from RDB + AOF

# Stop Redis, replace dump.rdb and appendonly.aof, restart
docker compose stop redis
cp /backups/redis/dump.rdb /data/dump.rdb
cp /backups/redis/appendonly.aof /data/appendonly.aof
docker compose start redis

Production Checklist

• Tune shared_buffers and work_mem to your VM's RAM
• Set max_connections to prevent app connection leaks (default: 100)
• Configure archive_mode and archive_command for WAL archiving
• Set Redis maxmemory and maxmemory-policy
• Test restore procedure — untested backups are not backups
• Monitor replication lag if adding read replicas
• Set up pg_stat_statements for query performance analysis

Key Takeaways

• Default Postgres config is tuned for a laptop, not production — always adjust shared_buffers, work_mem, and effective_cache_size
• Redis without AOF loses data — always enable appendonly yes in production
• WAL archiving enables PITR — without it, you can only restore to backup snapshot time
• Test your backups — a backup that's never been restored is a backup that doesn't work