Long-running transactions

Nik and Michael discuss long-running transactions, including when they're harmless, when they cause issues, and how to mitigate those issues.

Here are some links to things they mentioned:

• transaction_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TRANSACTION-TIMEOUT
• Our episode on transaction_timeout https://postgres.fm/episodes/transaction_timeout
• Our episode on slow queries (which was also our first ever episode!) https://postgres.fm/episodes/slow-queries-and-slow-transactions
• Our episode on locks https://postgres.fm/episodes/locks
• lock_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT
• Transaction Isolation levels https://www.postgresql.org/docs/current/transaction-iso.html
• pg_current_xact_id_if_assigned() https://pgpedia.info/p/pg_current_xact_id_if_assigned.html
• Monitor xmin horizon to prevent XID/MultiXID wraparound and high bloat (how-to guide by Nik) https://postgres.ai/docs/postgres-howtos/performance-optimization/monitoring/how-to-monitor-xmin-horizon
• idle_replication_slot_timeout https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-IDLE-REPLICATION-SLOT-TIMEOUT
• PREPARE TRANSACTION https://www.postgresql.org/docs/current/sql-prepare-transaction.html
• log_autovacuum_min_duration https://www.postgresql.org/docs/devel/runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION
• PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
• statement_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT
• idle_in_transaction_session_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
• lock_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT

~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~
Postgres FM is produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With credit to:

• Jessie Draws for the elephant artwork