PostgreSQL – How to Efficiently Delete Records When Counter Reaches Zero

cursorsdeletepostgresqlpostgresql-performanceupdate

Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this in PostgreSQL?

The naive way involves two SQL statements and two searches in the table: a SELECT to fetch the counter value, followed by a DELETE or an UPDATE.

One alternative involves an UPDATE … RETURNING, followed by a DELETE only if the returned value of the counter is zero. However, this performs an unneeded record change in the case of a counter having a value of one, optimizing the uncommon case (counter has a value higher than one) at the expense of the expected common case.

Another alternative, which indeed optimizes the expected common case, involves a DELETE … WHERE … AND counter = 1, followed by an UPDATE when no deletion takes place.

Both alternatives may require a wasteful second search for the record in the table.

Can perhaps the two table searches be always avoided and the operation's efficiency increased by using a cursor? I haven't seen an example for this use case in the PostgreSQL documentation.

Best Answer

Targeting a single row, this avoids an "unneeded record change", i.e. writing a new row version without need:

DO
$$
BEGIN
   DELETE FROM tbl WHERE … AND counter = 1;  -- common case first!

   IF NOT FOUND THEN
      UPDATE tbl SET counter = counter - 1 WHERE …;
   END IF;
END
$$;

Should also be cheaper than a trigger solution, where a trigger function is executed for every affected row (and may or may not interfere).

However, this will not fly with possible concurrent write operations. When two or more transactions try the same at virtually the same time, the logic can break. Or additional rows could become visible in between the two commands in default READ COMMITTED transaction isolation.

This is inherent to the problem itself, rather than to my solution. (Applies to other solutions all the same.) Under concurrent write load, you'll have to at least write-lock the row to avoid race conditions, so you are back to finding the row twice. You can avoid writing rows without need (like UPDATE + DELETE) in any case, though.

Related Question