Advisory locks in PostgreSQL - Time & Space Complexity

Time Complexity: Advisory locks

O(n)

Understanding Time Complexity

When using advisory locks in PostgreSQL, it's important to understand how the time to acquire or release a lock changes as more locks are requested.

We want to know how the system behaves when many sessions try to get advisory locks at the same time.

Scenario Under Consideration

Analyze the time complexity of acquiring and releasing advisory locks.

-- Acquire an advisory lock with a key
SELECT pg_advisory_lock(12345);

-- Do some work while holding the lock

-- Release the advisory lock
SELECT pg_advisory_unlock(12345);
    

This code acquires a lock identified by a key, does some work, then releases the lock.

Identify Repeating Operations

Look for repeated actions that affect performance.

• Primary operation: Checking and managing lock state in the lock manager.
• How many times: Each lock request involves searching the lock table, which grows with the number of active locks.

How Execution Grows With Input

As more sessions request advisory locks, the system must check existing locks to avoid conflicts.

Input Size (n)	Approx. Operations
10	About 10 checks
100	About 100 checks
1000	About 1000 checks

Pattern observation: The number of operations grows roughly in direct proportion to the number of active locks.

Final Time Complexity

Time Complexity: O(n)

This means the time to acquire or release a lock grows linearly with the number of locks currently held.

Common Mistake

[X] Wrong: "Acquiring an advisory lock always takes the same time no matter how many locks exist."

[OK] Correct: The system must check existing locks to avoid conflicts, so more locks mean more work and longer wait times.

Interview Connect

Understanding how advisory locks scale helps you design systems that handle concurrency well and avoid bottlenecks.

Self-Check

"What if advisory locks were replaced with session-level locks that do not check other locks? How would the time complexity change?"