6 ms·
USE. ADVISORY. LOCKS. Do not use SKIP LOCKED unless it is a toy/low throughout. Row locks require transactions and disk writes. Advisory locks require neithe
by paulddraper 3y ago
USE. ADVISORY. LOCKS.
Do not use SKIP LOCKED unless it is a toy/low throughout.
Row locks require transactions and disk writes.
Advisory locks require neither. (However, you do have to stay inside the configurable memory budget.)
- ukd1 3y agoMaybe it's changed in the last year or so, but from benchmarking and writing / running queue software for Postgres - SKIP LOCKED was/is significantly faster. Is that different for MySQL?
- mikeocool 3y agoPretty common advice for scaling Postgres is to deploy pgbouncer in transaction mode in front of it to handle connection pooling. Advisory locks don’t work in this setup (and will start behaving in strange ways if you do try to use them.) Something to consider if you go this route.
- paulddraper 3y agoDepends. That has more to do with how your're scaling application servers.
- ris 3y agoTransaction-scoped advisory locks are very much a thing too.
- sorentwo 3y agoTo do anything safe and interesting you’ll need transactions. Using SKIP LOCKED won’t be your bottleneck, your application will. Job queues are about side effects and the rest of your application needs to keep up. Oban is able to run over 1m jobs a minute, and the ultimate bottleneck is throttling in application code to prevent thrashing the database: https://getoban.pro/articles/one-million-jobs-a-minute-with-oban https://getoban.pro/articles/one-million-jobs-a-minute-with-...
- paulddraper 3y agoThat's true. However in the empty poll case, you can avoid a transaction.
- klysm 3y agoNot all use cases are high throughput. That’s not what makes it a toy
- paulddraper 3y ago"Toy/low throughput" = "Toy or low throughout"
- djur 3y agoCan you define "low throughput"? I think people have significantly different ideas of what that means.
- klysm 3y agoYeah I think people generally reach for “big” tools when this “toy” would work fine for the vast majority of projects.
- paulddraper 3y ago> Can you define "low throughput"? IDK maybe <1000 messages per minute Not saying SKIP LOCKED can't work with that many. But you'll probably want to do something with lower overhead. FWIW, Que uses advisory locks [1] [1] https://github.com/que-rb/que https://github.com/que-rb/que