# How to check what locks PostgreSQL will create for a statement

Wed Jun 17 2026

Today I learned how to check what locks will be created by PostgreSQL for a SQL statement. After many years of programming, the art and science of database locks are still a mystery to me, but I know that adding an index or a foreign key can mess up your day if you're not careful. My tech lead asked me to check what locks would be created by a migration I had up for review.

Perplexity suggested the following useful query:

BEGIN;

-- Run your query
ALTER TABLE foo ADD COLUMN bar_id UUID REFERENCES bar (id) ON DELETE SET NULL;

-- Check what locks were created
SELECT 
    relation::regclass AS table_name,
    locktype,
    mode,
    granted
FROM pg_locks 
WHERE pid = pg_backend_pid();

-- Don't commit
ROLLBACK;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

You will get an output that looks something like this:

   table_name    |   locktype    |         mode          | granted
-----------------+---------------+-----------------------+---------
 pg_locks        | relation      | AccessShareLock       | t
                 | virtualxid    | ExclusiveLock         | t
 foo             | relation      | AccessShareLock       | t
 foo             | relation      | ShareRowExclusiveLock | t
 foo             | relation      | AccessExclusiveLock   | t
 bar             | relation      | AccessShareLock       | t
 bar             | relation      | ShareRowExclusiveLock | t
                 | bar_id        | ExclusiveLock         | t
(8 rows)

1
2
3
4
5
6
7
8
9
10
11

You can get your favourite LLM to explain what each of those locks mean. I found it useful to compare the output to that of similar, but modified SQL commands to see what the difference in impact would be.

Thank you for reading my blog! If you enjoyed this post, you're welcome to subscribe via RSS here (opens new window) (I can recommend NetNewsWire on iOS).