Skip to Content
SQL Advanced: Queries That Hold Up Under Load
Advanced
0 likes
Courses
SQL Advanced: Queries That Hold Up Under Load

Check Yourself: Concurrency

Five scenario questions on bloat, vacuum, lost updates, deadlocks and lock timeouts. If you miss more than one, reread the lessons in this section.

5 min SQLDatabasesTesting and DebuggingPerformance Optimization Advanced
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. Every row of a 20,000-row table is updated once, and the table grows from 89 to 177 pages. After VACUUM, the file is still 177 pages. Why?
PostgreSQL shrinks the file only after a restart. VACUUM failed, so nothing was cleaned. The table needs ANALYZE to shrink. VACUUM marks the space of dead rows as reusable inside the table, and does not return it to the operating system.
2. VACUUM finishes, but pgstattuple still reports 20,000 dead rows. Which cause is most likely?
The table has too many indexes. VACUUM only removes dead rows when ANALYZE ran first. A transaction that started earlier is still open, and its snapshot can still see those row versions. Dead rows are removed only by autovacuum, never by a manual VACUUM.
3. Two checkout sessions each read a stock of 10, subtract 1 in the application, and write 9. Two pens were sold, and the stock says 9. What is the best fix?
Raise lock_timeout so the second session waits longer. Let the database do the arithmetic in one statement, SET qty = qty - 1, or read the row with SELECT FOR UPDATE. Run VACUUM between the read and the write. Raise deadlock_timeout.
4. The log shows deadlock detected. One transaction updates the pen row and then the mug row, and another updates the mug row and then the pen row. What is the fix?
Raise deadlock_timeout so PostgreSQL waits longer. Add an index on both columns. Retry only the statement that failed. Make every transaction take its rows in the same order, for example sorted by key.
5. A migration needs ACCESS EXCLUSIVE on a busy table, and a long report holds a lock on it. What keeps the whole application from stalling behind the migration?
Run VACUUM on the table first. Set a short lock_timeout for the migration, and retry it when it fires. Raise deadlock_timeout. Run the migration in the same transaction as the report.

About

Five scenario questions on bloat, vacuum, lost updates, deadlocks and lock timeouts. If you miss more than one, reread the lessons in this section.

Duration
5 min
Last update
09/22/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.