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.
DELETE
command). But a lookup by PK is extremely fast and its performance is pretty much independent of the table sizeINSERT … ON CONFLICT … UPDATE
statement.