Postgres advisory locks are surprisingly effective at coordinating work across multiple independent Postgres instances, even when they aren’t directly aware of each other.
Let’s see how this plays out. Imagine you have a fleet of workers, each running its own Postgres database, and you need to ensure that a specific, expensive operation (like generating a large report) only runs once across all of them.
-- Worker 1 on DB A:
-- Check if lock is held
SELECT pg_try_advisory_lock(123456789); -- Returns true if acquired, false if already held
-- If true, proceed with report generation.
-- ... report generation code ...
-- Release the lock when done
SELECT pg_advisory_unlock(123456789);
-- Worker 2 on DB B:
-- Same logic, same lock key
SELECT pg_try_advisory_lock(123456789); -- Will return false if Worker 1 holds it
The magic is that pg_try_advisory_lock is a transaction-level lock, and the key (the integer 123456789 in this case) is global within that Postgres instance. When you use the same integer key across different Postgres instances, you’re essentially creating a distributed lock. The first worker to successfully acquire the lock on its respective database prevents any other worker, on any other database, from acquiring a lock with that same key.
This mechanism is incredibly useful for several distributed use cases:
- Preventing Duplicate Work: As seen above, ensuring a specific task runs only once across a pool of workers. This could be anything from sending a critical email to triggering a complex data import.
- Distributed Rate Limiting: You can use advisory locks to limit the number of concurrent operations initiated by different services. For example, if you have a public API that should only handle 10 requests per second in total across all your API servers, each API server can attempt to acquire a lock with a unique key for each request. If the lock acquisition fails, the request is throttled.
- Leader Election (Simple): For very basic scenarios, a set of workers can all try to acquire a specific advisory lock. The one that succeeds is the "leader" and performs a designated task. If the leader fails, other workers will eventually pick up the lock.
- Resource Scoping: Imagine you have a shared, but not natively distributed, resource that only one process should access at a time. An advisory lock can act as a gatekeeper. For example, if you have a single external API client that all your workers need to use, you can wrap its usage in an advisory lock to ensure only one worker is making calls at any given moment.
The pg_try_advisory_lock(bigint) function attempts to acquire a lock. It returns true if successful and false if the lock is already held by another session. The lock is held until the end of the transaction. If you need to hold the lock for longer, you can use pg_advisory_lock(bigint) which will block until the lock is available. Releasing the lock is done with pg_advisory_unlock(bigint).
The bigint key is a 64-bit integer, allowing for a vast number of unique lock identifiers. You can also use pg_advisory_xact_lock() for exclusive locks and pg_advisory_shared_lock() for shared locks, but for distributed coordination, the basic pg_try_advisory_lock is often the most straightforward.
The crucial insight here is that advisory locks are not tied to specific database objects like tables or rows. They are associated with a unique bigint identifier that is global within a single PostgreSQL instance. When you coordinate using the same identifier across multiple independent instances, you achieve distributed coordination. Each instance independently manages its own set of advisory locks, but by agreeing on a common identifier, they can communicate their lock state implicitly.
A common pitfall is forgetting to release the lock. Since advisory locks are tied to the transaction, if a worker crashes mid-transaction, the lock will be released automatically when the connection times out or is explicitly closed. However, relying on this is poor practice. Always ensure explicit pg_advisory_unlock calls within your transaction’s success path.
The next challenge you’ll likely face when using advisory locks in a distributed system is how to manage the lock keys themselves, especially if you have a large number of potential tasks or resources to protect.