1

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.

4
  • 1
    You could use a trigger that turns an update into a DELETE if the counter reaches zero
    – user1822
    Dec 8, 2020 at 7:52
  • Thank you! This will still require the database to search twice for the record, no? Dec 8, 2020 at 8:18
  • 1
    Only if the row is actually deleted (implicitly through the DELETE command). But a lookup by PK is extremely fast and its performance is pretty much independent of the table size
    – user1822
    Dec 8, 2020 at 8:27
  • Thank you again. Your proposal should have a cost similar to the second alternative I mentioned. I was looking for a way to eliminate the unneeded update and lookup cost, as is, for example, the case for an upsert using an INSERT … ON CONFLICT … UPDATE statement. Dec 8, 2020 at 9:47

2 Answers 2

3

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.

2
  • Thank you! Indeed, the WHERE clause will identify a single row and concurrency regarding this row is not an issue. IIUC your proposal is similar to the second alternative I wrote, and may still require two searches for the row. However, it has the advantage of avoiding two separate trips to the database server. I was hoping that the two searches could be eliminated through a cursor. Dec 9, 2020 at 7:07
  • 1
    Yes, single trip to the server. And since DELETE is the common case, it's also just a single action in most cases. A trigger solution achieves that, too, but I expect it to have more overhead. Dec 9, 2020 at 11:49
1

The reference documentation made it quite difficult to understand how to put all elements together, especially because most examples mainly use cursors for scrolling through multiple results. In the end I was able to create a query with a single table lookup based on this tutorial.

DO
$$
DECLARE
  value integer;
  curs CURSOR FOR SELECT counter FROM elements_table
    WHERE … FOR UPDATE;
BEGIN
  OPEN curs;
  FETCH FROM curs into value;
  IF value > 1 THEN
    UPDATE elements_table
      SET counter = counter - 1
      WHERE CURRENT OF curs;
  ELSE
    DELETE FROM elements_table
      WHERE CURRENT OF curs;
  END IF;
END
$$;

The above code:

  • places curs on the record of interest,
  • retrieves the counter column into value, and,
  • based on value, either
    • it updates the current record decrementing the counter, or
    • it deletes the current record.
5
  • 1
    Nice proof of concept. But as long as the WHERE condition is backed by a btree index, I expect this to be more expensive than any of the other discussed solutions (and mine to be fastest). Creating a cursor has a cost. Run a performance test to see for yourself. Aside: DECLARE is only needed once. Dec 9, 2020 at 21:16
  • 1
    I benchmarked the two alternatives, and your proposal has indeed superior performance, even in scenarios where the common case isn't common. spinellis.gr/blog/20201210. (BTW what do you mean by DECLARE is only needed once?) Dec 10, 2020 at 10:25
  • 1
    The keyword DECLARE is only needed once in a plpgsql code block. Random examples: dba.stackexchange.com/a/231017/3684, stackoverflow.com/a/54917900/939860 Dec 10, 2020 at 13:31
  • Thank you; I adjusted the example. Dec 10, 2020 at 13:57
  • 1
    Very well written blog post, btw! Would be interesting to add a trigger solution for comparison. Dec 10, 2020 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.