Earlier quoted context omitted.
This approach depends on the level of transaction isolation being used, and relying on the higher levels is a foot gun best avoided unless you really know what you are doing and the consequences you are... erm... locking yourself into.
Can you elaborate? The Postgres documentation leads me to believe this would work fine with the default (READ COMMITTED) isolation level. If multiple transactions attempt to modify the same row (incrementing the counter) they're forced to line up until previous updates to that row have been committed or rolled back, hence the risk of lock contention, but each transaction should consistent view of the counter.
(so performance wise, a sequence is still better due to less locking, but if you can't have holes it seems fine)