Bloat and long transactions
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
find bloat and the long transactions that cause it.
Planned topics
- Bloat is dead space in tables or indexes that vacuum cannot reuse fast enough; it slows scans and wastes disk.
- Vacuum can only remove versions no transaction might still need; an old open snapshot holds the cleanup horizon back.
- Idle-in-transaction sessions are the usual cause, often from application code that opens a transaction and then calls a slow service.
- Protect with
idle_in_transaction_session_timeoutand short transactions.
Draft outline details
Demonstration
SELECT pid, state, xact_start, now() - xact_start AS age, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start;Check your understanding
Why can one idle transaction cause table bloat?
Show answer
Vacuum cannot remove versions the old snapshot may still see.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.