How can I efficiently delete records when a counter reaches zero in PostgreSQL?
Targeting a single row, this avoids an &quot;unneeded record change&quot;, 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.