The Slotted Counter Pattern • Hacker News Board

Re: The Slotted Counter Pattern

#31

Post by deepsun » Tue, Aug 02, 2022, 8:19 PM UTC

robocat wrote: post #20

ukd1 wrote: post #16

Cool, but this method will still get locks in whatever percent of cases, regardless of if there are slots not in transactions. In Postgres you can probably do this with slots using SKIP LOCKED; though in practice I belive you have to deal with the case where everything is locked, by falling back to waiting for a lock. UPDATE counters SET count = count + 1 WHERE name = ? AND slot = (SELECT slot FROM counters FOR UPDAT…

How does that avoid race conditions? SKIP LOCKED doesn’t seem to be designed for that purpose: https://www.enterprisedb.com/blog/what-skip-locked-postgresq...

In SQL `count = count + 1` does not have race conditions. The discussion about contention (bad performance), not about race conditions (incorrect results).

Re: The Slotted Counter Pattern

#32

Post by dima_vm » Tue, Aug 02, 2022, 8:34 PM UTC

If we need slotted counters, we're pretty close to the boundary of "right tool for the job", and we need to consider using a time-series database.

For one use-case it might be ok, although if we expect more counters in the future, it's easier to make it now than refactor later.

Re: The Slotted Counter Pattern

#33

Post by ukd1 » Fri, Aug 05, 2022, 12:14 AM UTC

hiptobecubic wrote: post #29

ukd1 wrote: post #16

Cool, but this method will still get locks in whatever percent of cases, regardless of if there are slots not in transactions. In Postgres you can probably do this with slots using SKIP LOCKED; though in practice I belive you have to deal with the case where everything is locked, by falling back to waiting for a lock. UPDATE counters SET count = count + 1 WHERE name = ? AND slot = (SELECT slot FROM counters FOR UPDAT…

I would definitely complain about this in code review without some performance testing showing that 1) it's actually faster than waiting for a lock and 2) it's better than just increasing the number of slots.

I'd be doing the same in the case of the post; it's guarneteed to hit locks some percentage of time. Should be better than a single slot though.

The whole point of the article was that idle-in-transaction due to locks on a counter in another transaction cause bottlenecks; skipping them using SKIPLOCKED with enough slots eliminates this. Randomly selecting them also randomly picks the locked one, causing a wait.

Re: The Slotted Counter Pattern

#34

Post by ukd1 » Fri, Aug 05, 2022, 12:16 AM UTC

robocat wrote: post #20

ukd1 wrote: post #16

Cool, but this method will still get locks in whatever percent of cases, regardless of if there are slots not in transactions. In Postgres you can probably do this with slots using SKIP LOCKED; though in practice I belive you have to deal with the case where everything is locked, by falling back to waiting for a lock. UPDATE counters SET count = count + 1 WHERE name = ? AND slot = (SELECT slot FROM counters FOR UPDAT…

How does that avoid race conditions? SKIP LOCKED doesn’t seem to be designed for that purpose: https://www.enterprisedb.com/blog/what-skip-locked-postgresq...

Yep, it's meant for queues - a kinda popular one I maintain for ruby (QueueClassic) uses it. However, you can use it outside of that - this is basically a queue to update a column; note the caveat in my original post. SKIP LOCKED will I believe not find anything and just be fine with that if all are locked; you can deal with this, but I didn't in my example SQL.