Interview Q&A / PostgreSQL

1. What does SELECT ... FOR UPDATE do in PostgreSQL?

   It locks the selected rows until the transaction ends, so other transactions cannot update, delete or lock them meanwhile. Use it to read a value and change it safely, like a balance or a seat count.

   BeginnerQuick answer1 min read
2. What is a deadlock in PostgreSQL and how do you avoid it?

   A deadlock occurs when two transactions each hold a lock the other needs, so neither can proceed. PostgreSQL detects it after deadlock_timeout (1 second by default) and aborts one transaction. Prevent it by locking rows in a consistent order.

   BeginnerQuick answer1 min read
3. What is the difference between lock_timeout and statement_timeout?

   lock_timeout cancels a statement that waits too long to acquire a lock. statement_timeout cancels any statement whose total running time exceeds the limit, including time spent working.

   BeginnerQuick answer1 min read
4. What is the difference between VACUUM and VACUUM FULL?

   VACUUM marks dead space reusable and runs alongside normal reads and writes, but usually does not shrink the file. VACUUM FULL rewrites the table into a new compact file, returning space to the OS, but takes an ACCESS EXCLUSIVE lock for the whole run.

   BeginnerQuick answer1 min read