Database users access and modify shared objects and data during normal activity.
Relation databases handle these concurrent accesses and modifications using locks.
PostgreSQL has several types of lock, some of them are compatible and some others not.
If users want to access the same objects or data with not compatible locks some of them may need to wait for acquiring it.
For this reason, some maintenance activities or new deployments can involve downtime.
PostgreSQL has a catalog view called pg_locks which is holding the information about cluster-wide locks.
There are several types of locks reported in that table for different objects like relation, tuple, page etcetera.
All these locks can access the database objects in different modes, some of them are not compatible.
This view is the main source for monitoring locks and waiting time in your cluster.
You may find useful queries to monitor this view and the locks in your PostgreSQL cluster in the Postgres wiki pages reported in the external resources section.
There is also a very useful function which tells you what session is blocking the statements you are monitoring.
The function is pg_blocking_pids(pid) and can be used, for example, in the following way
(Select datname from pg_database where oid=pg.database::regclass::text),
relation::regclass::text as relation,
pg_blocking_pids(pid) AS blocking_pid
FROM pg_locks pg;
The above query collects the main locks information including the blocking session
PostgreSQL 14 pg_locks version
The following picture describes the pg_locks view structure
PostgreSQL has eight different modes to acquire a lock.
Below you find the matrix of lock compatibility which you can refer in case or lock conflicts.
|Access Share||Row Share||Row Exclusive||Share Update Exclusive||Share||Share Row Exclusive||Exclusive||Access Exclusive|
|Share Update Exclusive|
|Share Row Exclusive|
= Not Compatible
PostgreSQL 14 statements and locks
The following is a not exhaustive list of statements and their required locks in PostgreSQL 14.
To have the complete list of locks required by each statement please refers to PostgreSQL documentation.
SELECT FOR UPDATE
SELECT FOR SHARE
(Also access share on any referenced table)
Share Update Exclusive:
ALTER TABLE SET STATISTICS
ALTER TABLE SET
ALTER TABLE RESET
ALTER TABLE VALIDATE CONSTRAINT
ALTER TABLE SET CLUSTER ON
ALTER TABLE SET WITHOUT CLUSTER
ALTER INDEX RENAME
VACUUM (without FULL)
CREATE INDEX CONCURRENTLY
DROP INDEX CONCURRENTLY (Many limitations)
CREATE INDEX (without CONCURRENTLY)
Share Row Exclusive:
ALTER TABLE ADD FOREIGN KEY (Also on the referenced table)
ALTER TABLE DISABLE/ENABLE TRIGGER
REFRESH MATERIALIZED VIEW CONCURRENTLY(Letting other users select the view)
ALTER TABLE (unless explicitly indicated)
ALTER INDEX (unless explicitly indicated)
REFRESH MATERIALIZED VIEW (without concurrently)
REINDEX (without concurrently)
DROP INDEX (without concurrently)
Note that the full list of locks to be acquired may change depending on the PostgreSQL version you are using.
Also note that some statements like attaching and detaching partitions may acquire different type of locks on different tables (parent table, child table, etcetera...)
Also note that some strategy may be required to minimize the downtime of some maintenance activities like adding constraints with NOT VALID attribute and later on validate the constraint using VALIDATE CONSTRAINT.
Refers always to the PostgreSQL documentation to have the exact picture
Before planning any maintenance activity or new deployments in your production environments, check the lock matrix, read carefully the PostgreSQL documentation and test your activities in a production clone environment simulating a usual load
The following wiki pages might help you in monitoring locks