PostgreSQL Tuning Basics - Memory, Pooling, Indexes, and EXPLAIN

Status: Active
Last Updated: 2026-08-26
Category: Databases - Operations
Prerequisites: database-selection, docker-basics
Time: 3 hours
Tags: postgres, tuning, pgbouncer, indexes, explain, memory, docker

Summary

Practical PostgreSQL configuration for small self-hosted deployments: sizing shared_buffers and work_mem inside containers, managing connections with PgBouncer, creating the right indexes for Prisma-generated queries, and reading EXPLAIN output to find slow queries.

🎯 What You'll Learn

By the end of this article, you'll be able to:

✅ Set sane memory parameters for a containerized Postgres
✅ Deploy PgBouncer and connect Prisma through it
✅ Choose indexes that match actual query patterns
✅ Read an EXPLAIN ANALYZE plan and spot the usual suspects
✅ Configure Postgres in docker-compose with tuned settings

Context / Why This Matters

The fogserv.cloud production target is Postgres (database-selection), running in a container on a shared host. Default Postgres settings assume a dedicated server with gigabytes to spare — on a 4 GB host also running the website, Traefik (traefik-v3-reverse-proxy), and CI, untuned defaults cause OOM kills and random evictions. Tuning here is about not being the loudest process on the box, not squeezing out benchmark wins.

Implementation / Core Content

Memory Parameters for Small Containers

Start from these values for a host with total RAM R and a Postgres budget of B (typically B ≈ R/4 on a shared box):

Parameter	Rule of thumb	Example (B = 1 GB)
`shared_buffers`	25% of B	256MB
`effective_cache_size`	50–75% of B (planner hint)	768MB
`work_mem`	B / 64, per sort/hash node	16MB
`maintenance_work_mem`	B / 16	64MB
`max_connections`	20–50 small apps; pool instead of raising	50

Critical subtlety: work_mem is per sort/hash operation, not per connection. One query can use it several times. Setting it to 512 MB "because we have RAM" lets ten concurrent analytical queries allocate 5+ GB and get the container OOM-killed (exit 137 — same signature as any container kill, see docker-basics).

Connection Pooling with PgBouncer

Postgres spawns a process per connection (~2–10 MB each). Prisma's default pool is num_physical_cpus * 2 + 1 per app instance; two app replicas plus migrations plus Studio easily exceed 30 direct connections. PgBouncer in transaction mode collapses that to a handful of real backend connections.

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type   = scram-sha-256
auth_file   = /etc/pgbouncer/userlist.txt
pool_mode   = transaction
max_client_conn = 200
default_pool_size = 20
reserve_pool_size = 5

# compose snippet
services:
  pgbouncer:
    image: edoburu/pgbouncer
    volumes:
      - ./pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro
      - ./userlist.txt:/etc/pgbouncer/userlist.txt:ro
    depends_on: [postgres]

Point Prisma at PgBouncer:

DATABASE_URL=postgresql://prisma:secret@pgbouncer:6432/app?connection_limit=10&pool_timeout=15
DIRECT_URL=postgresql://prisma:secret@postgres:5432/app

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

Migrations must bypass PgBouncer (prepared-statement and session-state issues in transaction mode) — that's what directUrl is for; Prisma Migrate uses it automatically. This mirrors the pooling strategy already described in prisma-connections.

Indexes That Match Query Patterns

Prisma creates indexes for @id, @unique, and explicit @@index. It does not index your foreign-key lookup patterns unless you declare them. From schema-overview, the hot paths are:

model CampaignLog {
  id          String   @id @default(cuid())
  campaignId  String
  subscriberId String
  sentAt      DateTime?
  @@index([campaignId, subscriberId])   // per-campaign engagement joins
  @@index([sentAt])                     // time-window analytics
}

model PageView {
  path      String
  timestamp DateTime
  @@index([path, timestamp(sort: Desc)]) // path + recent-first ranges
}

Rules:

Index columns that appear in WHERE + ORDER BY together (composite, matching order).
Every FK you filter or join on should be indexed — Postgres does not auto-index FKs.
Don't index low-selectivity columns alone (approved boolean); composite with something selective.
Each index slows every write; on write-heavy tables like CampaignLog, keep the count minimal.

Reading EXPLAIN

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.*, l."errorMessage"
FROM "Campaign" c
JOIN "CampaignLog" l ON l."campaignId" = c.id
WHERE c."scheduledFor" > now() - interval '7 days'
ORDER BY c."scheduledFor" DESC;

What healthy looks like:

Index Scan using ... or Index Only Scan on large tables.
Small tables (< few thousand rows) showing Seq Scan — that's correct and fast.
Total cost roughly proportional to returned rows.

Red flags:

Seq Scan + Rows Removed by Filter: 984213 → missing index.
Nested Loop with huge inner estimates → stale statistics; run ANALYZE.
Sort Method: external merge Disk: ... → work_mem too small for this query.
Estimated vs actual row mismatch > 10x → planner guessing wrong; check stats, consider extended statistics.

Practical Examples

Example 1: Tuned docker-compose service

services:
  postgres:
    image: postgres:16-alpine
    command: >
      postgres
        -c shared_buffers=256MB
        -c effective_cache_size=768MB
        -c work_mem=16MB
        -c maintenance_work_mem=64MB
        -c max_connections=50
        -c wal_compression=on
        -c log_min_duration_statement=500
    environment:
      POSTGRES_DB: fogserv
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
    volumes:
      - pg-data:/var/lib/postgresql/data
    deploy:
      resources:
        limits: { memory: 1536M }
volumes:
  pg-data:

log_min_duration_statement=500 gives you a slow-query log for free; pair it with monitoring from netdata-basics.

Example 2: Verifying an index pays off

-- Before
EXPLAIN ANALYZE SELECT * FROM "PageView" WHERE path='/kb/' ORDER BY "timestamp" DESC LIMIT 20;
-- Seq Scan on "PageView" (cost=0..24811 rows=...) actual time=45ms

CREATE INDEX CONCURRENTLY pageview_path_time ON "PageView"(path, "timestamp" DESC);

-- After
-- Index Scan using pageview_path_time ... actual time=0.3ms

Use CONCURRENTLY in prod so reads aren't blocked (note: run outside a migration transaction).

Example 3: Finding top offenders

SELECT calls, round(mean_exec_time::numeric,1) AS avg_ms, left(query,80)
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

Requires shared_preload_libraries='pg_stat_statements'.

Common Pitfalls & Troubleshooting

Problem	Cause	Fix
Container killed, exit 137	work_mem/shared_buffers exceed container limit	Lower params or raise memory limit consistently
`FATAL: remaining connection slots reserved`	No pooler; app instances multiplied connections	Deploy PgBouncer, cap Prisma `connection_limit`
Random `Prepared statement s0 not found` via pgbouncer	Transaction mode + prepared statements	Use `directUrl` for migrations; set `pgbouncer` `max_prepared_statements` or disable prepared statements
Slow query got slower after adding index	Planner picked worse plan from stale stats	`ANALYZE table;` then re-check plan
Seq scan despite existing index	Function wrapped around indexed column, or type mismatch	Rewrite predicate to use bare column and matching types
`CREATE INDEX` blocked writes for minutes	Non-concurrent index build in prod	Use `CREATE INDEX CONCURRENTLY`

Next Steps / Ops Actions

Apply the tuned compose snippet and confirm no OOM events over one week.
Put PgBouncer in front of Prisma per prisma-connections.
Enable pg_stat_statements and review the top-10 weekly.
Back up before experimenting: backup-recovery-drill.

Sources & Related

External references consulted:

Change Log

2026-08-26

Initial creation covering memory sizing, PgBouncer pooling, indexing strategy, and EXPLAIN basics.