Postgres advisory locks as a poor man's job scheduler

We had a cleanup job that ran every ten minutes. Then the app went from one server to three, and so did the job. It was idempotent, mostly, but it was also slow and it held row locks, so three copies at once were noticeably worse than one.

The usual answers are "use a proper scheduler" or "pick a leader". Both are fine. But if you already have Postgres, there is a cheaper one: advisory locks.

The idea

SELECT pg_try_advisory_lock(42);

returns true for exactly one session at a time; everyone else gets false immediately. The lock is released when the session ends or when you call pg_advisory_unlock(42). The number is yours to choose — I hash the job name into a bigint.

In code

func runExclusive(ctx context.Context, db *sql.DB, name string, fn func(context.Context) error) error {
	conn, err := db.Conn(ctx) // the lock belongs to a session, so pin one connection
	if err != nil {
		return err
	}
	defer conn.Close()

	key := int64(fnv64(name))
	var ok bool
	if err := conn.QueryRowContext(ctx, "SELECT pg_try_advisory_lock($1)", key).Scan(&ok); err != nil {
		return err
	}
	if !ok {
		return nil // someone else is doing it
	}
	defer conn.ExecContext(context.Background(), "SELECT pg_advisory_unlock($1)", key)
	return fn(ctx)
}

The one thing that bites people: the lock is per session, so you must run lock, work and unlock on the same connection. With a connection pool, take a dedicated *sql.Conn as above.

Caveats

• If you use PgBouncer in transaction mode, session-level advisory locks don't work. Use pg_try_advisory_xact_lock inside a transaction instead.
• This gives you mutual exclusion, not scheduling. Every server still wakes up every ten minutes; only one of them does anything.
• If the process dies, the connection drops and the lock is released. That's a feature.

Two years later this is still running. Boring, again.