7 Postgres mistakes that cost me 3 weekends

7 Postgres mistakes I made on side projects, each one costing me a weekend of debugging. Indexes, JSON columns, migrations, connection pooling, backups — learn from my pain.

Mistake 1 — No indexes on foreign keys

-- Query SELECT FROM comments WHERE postid = 42; -- Seq Scan on comments (cost=0.00..345.21 rows=1) -- 14ms on 100k rows, 1.2s on 10M rows

Postgres doesn't automatically index foreign keys. Every JOIN or WHERE postid = X does a sequential scan until you add the index.

Fix: sql CREATE INDEX idxcommentspostid ON comments(postid); -- Index Scan using idxcommentspostid (cost=0.42..8.44 rows=1) -- 0.3ms on 10M rows

Rule: every foreign key gets an index. Every WHERE clause column gets an index. No exceptions.

Mistake 2 — JSON columns instead of normalized tables

-- Query: get all events where user = 42 SELECT FROM events WHERE data-'userid' = '42'; -- Seq Scan on events (cost=0.00..10234.50) -- 8 seconds on 1M rows

JSONB is great for flexible data you'll rarely query. It's terrible for data you'll filter or join on.

Fix: normalize what you'll query:

CREATE INDEX idxeventsuserid ON events(userid); CREATE INDEX idxeventstypedate ON events(eventtype, occurredat);

SELECT FROM events WHERE userid = 42; -- Index Scan, 4ms on 1M rows

Rule: if you'll write WHERE against it, it's a column. JSONB is for "stuff we might want to inspect later" — not for query targets.

Mistake 3 — No connection pooling

Each connect is 50-100ms of overhead. On a busy API, this kills latency.

Fix — use a pooler (PgBouncer or built-in):

def getuser(id): conn = pool.getconn() try: cur = conn.cursor() cur.execute("SELECT FROM users WHERE id = %s", (id,)) return cur.fetchone() finally: pool.putconn(conn)

Or use asyncpg with built-in pooling:

async def getuser(id): async with pool.acquire() as conn: return await conn.fetchrow("SELECT FROM users WHERE id = $1", id)

Rule: never psycopg2.connect() inside a request handler. Always pool.

Mistake 4 — No transactional migrations

Fix — use a migration tool with transactions:

# sqlx-cli (Rust) sqlx migrate run

# golang-migrate migrate -path ./migrations -database $DATABASEURL up

Every migration runs in a transaction. If any statement fails, the whole migration rolls back. State stays consistent.

Mistake 5 — No automated backups

I lost 6 months of data on a side project because the VPS died and I had no backups. The "I'll set it up later" trap.

Fix — automated daily backups to S3-compatible storage:

# Upload to S3 aws s3 cp /tmp/backup-$DATE.sql.gz s3://my-backups/postgres/backup-$DATE.sql.gz

# Keep last 30 days locally, 90 days on S3 find /tmp -name "backup-.sql.gz" -mtime +30 -delete

Cron entry: cron 0 3 /path/to/backup.sh /var/log/pg-backup.log 2&1