𝗣𝗼𝘀𝘁𝗴𝗿𝗲𝗦𝗤𝗟 𝗔𝗱𝘃𝗶𝘀𝗼𝗿𝘆 𝗟𝗼𝗰𝗸𝘀 𝗳𝗼𝗿 𝗗𝗶𝘀𝘁𝗿𝗶𝗯𝘂𝘁𝗲𝗱 𝗝𝗼𝗯 𝗦𝗰𝗵𝗲𝗱𝘂𝗹𝗶𝗻𝗴
Stop adding Redis or SQS to your stack just for job scheduling.
You can use PostgreSQL advisory locks instead. This approach removes the need for new infrastructure.
In my tests, this setup handles 10,000 jobs per minute on a single database instance. It often beats Redis in latency because your workers already hold database connections. You avoid an extra network hop.
How to implement it:
• Use pg_try_advisory_xact_lock for job claiming. • Use FOR UPDATE SKIP LOCKED to handle row contention. • Use the transactional variant to avoid lock leaks.
Why transactional locks matter:
Transactional locks release automatically when a transaction commits or rolls back. This prevents orphaned locks if your application crashes. It also works safely with PgBouncer in transaction mode.
Avoid session locks if you use PgBouncer. PgBouncer reassigns connections between transactions. This breaks session-level locks and causes silent failures.
If you need session locks, create a separate connection pool for your workers. Do not mix web traffic and job worker traffic in the same pool.
Scaling with keys:
Advisory locks use a bigint or two integers. Using two integers provides natural namespacing. If you hash text keys to a bigint, watch for collisions. Collisions stay low until you hit 100,000 distinct lock IDs. Beyond that, use the two-integer form to stay safe.
The Tradeoff:
• Advisory locks win on simplicity and low operational cost. • Redis wins on raw throughput and horizontal scaling.
Most teams do not need more than 10,000 jobs per minute. If you are below that limit, stick to PostgreSQL. It keeps your architecture clean and your costs low.
Source: https://dev.to/software_mvp-factory/postgresql-advisory-locks-for-distributed-job-scheduling-15kp