Data Manipulation and Concurrency Control: Delete

The SQL delete statement

The delete statement allows marking tuples for removal. Given PostgreSQL’s implementation of Multiversion Concurrency Control (MVCC), it would not be wise to remove the tuple from the disk at the time of the delete operation:

• The transaction might roll back, and we don’t know that yet
• Other concurrent transactions only get to see the delete statement after commit, not as soon as the statement is made

As with the update statement, the most important part of the delete statement has to do with concurrency. Again, the main reason why we use an RDBMS is so that we don’t have to solve the concurrency problems in our application’s code; instead, we can focus on delivering an improved user experience.

The autovacuum daemon

The actual removal of on-disk tuples happens with vacuum, which the system runs in the background for you automatically, thanks to its ...