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 autovacuum daemon. PostgreSQL might also reuse the on-disk space for an insert statement as soon as the tuple isn’t visible for any transaction anymore.

Let’s say we mistakenly added characters from another play, and we don’t want to have to deal with them. First, we’ll insert the characters: