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.
//
Search across all documentation pages
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.
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.
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:
idle in transaction is visible and killable in pg_stat_activity.| Concept | App impact |
|---|---|
| Snapshot isolation | Repeatable reads within transaction |
| Row versions | Updates leave dead tuples until vacuum |
| Long transactions | Block vacuum, increase bloat and wraparound risk |
FOR UPDATE | Locks rows; serialize concurrent checkouts |
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;"pg_stat_activity demo.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.
UPDATE/DELETE row lock scope.| Alternative | Use When | Don't Use When |
|---|---|---|
| Embedded DBA in squad | High-risk payment domain | DBA bandwidth limited |
| Lint rules in CI | Repeat N+1 patterns | Complex query plans |
| Read-only role for reports | Ad hoc SQL culture | Writes needed |
| Workshop recording | Distributed team | Interactive Q&A required |
Enough to respect transaction boundaries; defer WAL internals to optional reading.
Session lifecycle, lazy loading N+1, migration ownership, and raw SQL escape hatches.
Own scripts with DBA review; shared responsibility model.
Explain read-your-writes, lag, and why analytics hits replica not primary.
Fewer long-lived connections; emphasize pooler and short transactions harder.
Annually plus after each relevant Sev-2; onboarding within first sprint.
"Find the idle transaction" staging drills work well in workshops.
DBA or data platform engineer with tech lead sponsorship.
Yes, filtered per service role where possible for ownership.
See Query Review Standards for PR bar.
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+.
Reviewed by Chris St. John·Last updated Jul 18, 2026