PostgreSQL interview question · Most asked
Why are long-running and 'idle in transaction' sessions harmful in PostgreSQL?
Summary
An open transaction holds back the oldest snapshot horizon, so VACUUM cannot remove dead tuples created after it started anywhere in the database: tables and indexes bloat, and queries slow down. It may also hold locks that block DDL and other writers.
Longer Answer
Common causes: an application that opens a transaction and then waits on a slow HTTP call or user input, a crashed client leaving a session idle in transaction, or analysts running hours-long queries on the primary.
Defences: idle_in_transaction_session_timeout, statement_timeout for application roles, transaction_timeout (PostgreSQL 17+), keeping external calls outside transactions, running reports on replicas (with hot_standby_feedback trade-offs), and monitoring pg_stat_activity for xact_start age.
Key Points
- Old snapshots block VACUUM cleanup.
- Bloat and lock contention.
- Timeouts for idle transactions and statements.
- No external calls inside transactions.
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?