PostgreSQL in Production

Migrating from PGlite sandbox to a real PostgreSQL instance, running Drizzle migrations against the production DB, connection string format, pg-bouncer for connection pooling, and backup config.

PostgreSQL in Production

What you'll learn

Configure PostgreSQL for production reliability, set up connection pooling, and implement essential performance tuning.

Don't Use PGlite in Production

PGlite is a WebAssembly SQLite-compatible embedded DB used for local development. It stores data in a single file and is not suitable for production:

Switch to PostgreSQL by setting USE_PGLITE=false and DATABASE_URL in production.

Essential postgresql.conf Tuning

For a 4-core / 8 GB RAM server:

# postgresql.conf — append or override these values

# Memory
shared_buffers         = 2GB         # ~25% of RAM
effective_cache_size   = 6GB         # ~75% of RAM
work_mem               = 64MB        # per sort/hash operation
maintenance_work_mem   = 512MB       # for VACUUM, CREATE INDEX

# Connections
max_connections        = 100         # use PgBouncer for more
connection_timeout     = 10s

# WAL / Durability
synchronous_commit     = on          # safe default
wal_compression        = on
checkpoint_completion_target = 0.9

# Query planner
random_page_cost       = 1.1         # SSD (vs 4.0 for spinning disk)
effective_io_concurrency = 200        # SSD

Connection Pooling with PgBouncer

The Paperclip API opens one connection per request. At scale, use PgBouncer to pool:

# pgbouncer.ini
[databases]
paperclip = host=postgres port=5432 dbname=paperclip

[pgbouncer]
pool_mode          = transaction     # most efficient
max_client_conn    = 500
default_pool_size  = 20
server_idle_timeout = 600

Update DATABASE_URL to point at PgBouncer:

DATABASE_URL=postgres://paperclip:password@pgbouncer:5432/paperclip

Essential Production Indexes

Run these after initial setup:

-- Issues: heartbeat claim query
CREATE INDEX CONCURRENTLY idx_issues_open_by_agent
  ON issues (company_id, assigned_agent_id, priority, created_at)
  WHERE status = 'open';

-- Cost events: monthly budget check
CREATE INDEX CONCURRENTLY idx_cost_events_agent_month
  ON cost_events (agent_id, date_trunc('month', created_at));

-- Approvals: pending approval lookup
CREATE INDEX CONCURRENTLY idx_approvals_pending
  ON approvals (company_id, status, created_at)
  WHERE status = 'pending';

VACUUM and ANALYZE

Paperclip updates issues.status frequently. Enable autovacuum tuned for write-heavy tables:

ALTER TABLE issues SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- vacuum after 1% dead rows
  autovacuum_analyze_scale_factor = 0.005   -- analyze after 0.5% changes
);

Checkpoint ✓

• [ ] You understand why PGlite is not suitable for production
• [ ] You can apply the essential postgresql.conf tuning values
• [ ] You know how to add the three most important production indexes