Mentoring App Teams

Application developers cause most production Postgres incidents: long transactions, connection storms, and ORM patterns that fight MVCC. Mentoring shifts left on data literacy without turning every developer into a DBA.

Recipe

Quick-reference recipe card - copy-paste ready.

MVCC one-liner for app devs:
"A transaction left open holds row versions and blocks vacuum,
 even if the HTTP request already returned."

-- Show damage: idle in transaction
SELECT pid, application_name, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

When to reach for this: Onboarding new services, post-incident training, ORM adoption, or pool misconfiguration repeats.

Working Example

Checkout API holds transactions open while calling Stripe, causing autovacuum lag and bloat.

# Anti-pattern (pseudo)
with db.begin():  # transaction opens
    order = create_order()
    charge = stripe.charge(...)  # network I/O inside transaction
    db.commit()

# Mentored pattern
order = create_order_no_tx()
charge = stripe.charge(...)
with db.begin():
    mark_order_paid(order.id, charge.id)

SET idle_in_transaction_session_timeout = '60s';

What this demonstrates:

• External I/O belongs outside database transactions.
• idle in transaction is visible and killable in pg_stat_activity.
• Timeouts back teaching with production guardrails.
• Pair language-specific examples (Java @Transactional, Rails, etc.).

Deep Dive

MVCC Concepts for App Devs

Connection Pool Rules

1. Pool size << Postgres max_connections
2. One pool per service, not per pod unbounded
3. PgBouncer transaction mode: no prepared statements unless configured
4. Release connection before calling external APIs

# PgBouncer show pools
psql -p 6432 pgbouncer -c "SHOW POOLS;"

Teaching Formats

• Lunch MVCC: 45 minutes with live pg_stat_activity demo.
• Incident replay: Walk timeline from last Sev-2 lock storm.
• ORM cheat sheet: How your stack opens transactions implicitly.
• Office hours: Weekly DBA slot for EXPLAIN help.

Metrics Devs Should Watch

SELECT datname, numbackends, xact_commit, blks_hit::float / nullif(blks_hit + blks_read, 0) AS cache_hit
FROM pg_stat_database
WHERE datname = current_database();

Share dashboard links in each service README.

Gotchas

• Blaming developers in post-mortems - Stops questions next incident. Fix: Blameless systems focus; fix pool and timeouts.
• Abstract lectures without ORM examples - Devs forget. Fix: Use their repo patterns in slides.
• Skipping PgBouncer education - Mystifies connection errors. Fix: Diagram app → pool → Postgres.
• No staging load tests - Long transactions appear only in prod. Fix: k6 with realistic think time inside transactions flagged in CI.
• Teaching only SELECT - Writes cause locks. Fix: Cover UPDATE/DELETE row lock scope.

Alternatives

FAQs

Related

• Tech Lead Basics on Postgres - leadership triangle
• Lock Storm Triage - incident outcome
• Transactions & MVCC Basics - deeper mechanics
• Connection Math - pool sizing

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.