Originally from:[tweet](https://twitter.com/samokhvalov/status/1732486201830240283),[LinkedIn post]().

In[day 60](0060_how_to_add_a_column.md), we discussed how to add a column and that under load you need

low`lock_timeout` and retries (also

see:[Zero-downtime Postgres schema migrations need this: lock_timeout and retries](https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries)).

There we used`max_attempts` set to`1000` and this is probably too many, but interesting question is: how to understand

what's blocking our DDL, if it doesn't succeed after many retries?

The first thing to do is to enable`log_lock_waits`. In this case, after`deadlock_timeout` (`1s` by default; and this

setting defines when deadlock detection happens), you'll see some information about our blocked session.

But we don't want to start with`lock_timeout` more than`1s` – it would be too invasive. Solution is to set a lower

`deadlock_timeout` right in session. Example (assuming that there is another session just read from table`t` and keeps

transaction open):

postgres=# set lock_timeout to '100ms';

postgres=# set deadlock_timeout to '50ms';

postgres=# set log_lock_waits to 'on';

postgres=# alter table t1 add column new_c int8;

ERROR: canceling statement due to lock timeout

In the logs we have two entries – one after~`50ms` (`deadlock_timeout`) with some useful details about the waiting on

lock acquisition, and another when our statement fails after`100ms`:

Unfortunately, the log message that was written when we achieved`deadlock_timeout` (`50ms` in this case) doesn't report

details about the blocker – just`PID` (`Process holding the lock: 211`). If we're lucky, we might have details about

this session around the timestamp we look at in the Postgres logs – we should just look around the timestamp when

failure occurred. We might find that it's`autovacuum` running in the transaction ID wraparound prevention mode, some

DDL (if we log DDL with`log_statement='ddl'`), or some long-running query that has reached`log_min_duration_statement`

(worth setting it to`1s` or a lower value controlling how much we actually log to avoid the "observer effect"), and got

logged. But in many cases, there is nothing else in the logs, and we need another solution.

In this case, we can do this:

1) Use a custom`application_name` value for sessions that run DDL, to more easily distinguish them

in`pg_stat_activity` – simply setting it in`PGAPPNAME` or just via`SET`:

set application_name='ddl_runner';

```

2)In a separate session, run an observer query. For example, this anonymous`DO` block with PL/pgSQL code:

This observer code will report somethinglike this, if DDL session waits more than 50ms:

>2023-12-06T19:37:35.746363053Z2023-12-0619:37:35.746 UTC [237] LOG: DDL session blocked. Session info: pid=197, query_left50=alter table t1 add column new_c int8;, wait=Lock

> :relation. Blockers: {"{\"pid\" : 211, \"state\" : \"idlein transaction\", \"wait\" : \"Client:ClientRead\", \"query_l50\" : \"selectfrom t1limit0;\"}"}

This should be enough for troubleshooting of failing DDL attempts.

A couple of more notes:

- Instead of anonymous DO block, it's probably better to wrap PL/pgSQL code into a function, so the `CONTEXT` and

`STATEMENT` parts of the log messages don't consume too much space.

- Such a function then can be invoked by`pg_cron`, so you have a permanent observability tool. But it should be taken

into account that it's a whole backend that runs this, so it might be not wise to have it constantly running –

Originally from:[tweet](https://twitter.com/samokhvalov/status/1732855546141933694),[LinkedIn post]().

Removing a foreign key (FK) is straightforward:

However, under heavy load, this is not a safe operation, due to the reasons discussed in

[zero-downtime Postgres schema migrations need this: lock_timeout and retries](https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries):

- this operation needs a brief`AccessExclusiveLock` on*both* tables

- if at least one of the locks cannot be quickly acquired, it waits, potentially blocking other sessions (including

`SELECT`s), to both tables.

To solve this problem, we need to use a low lock_timeout and retries:

set lock_timeout to'100ms';

alter ...-- be ready to fail and try again.

The same technique needs to be used in the first step of the operation of new FK creation, when we define a new FK with

the`NOT VALID` option, as was discussed in[day 70, How to add a foreign key](0070_how_to_add_a_foreign_key.md).

See also:[day 71, How to understand what's blocking DDL](0071_how_to_understand_what_is_blocking_ddl.md).

Originally from:[tweet](https://twitter.com/samokhvalov/status/1733252574479040950),[LinkedIn post]().

If a spike of active sessions (`pg_stat_activity`,`state='active'`) occurs along with a spike of exclusive locks

(`pg_locks`,`mode ~* 'exclusive'`), we need to analyze the blocked and blocking sessions, to understand the root cause.

Ad-hoc approaches were previously discussed:

-[Day 22, How to analyze heavyweight locks, part 1](0022_how_to_analyze_heavyweight_locks_part_1.md)

-[Day 42, How to analyze heavyweight locks, part 2: Lock trees (a.k.a. "lock queues", "wait queues", "blocking chains")](0042_how_to_analyze_heavyweight_locks_part_2.md)

- Specifically for the cases, when DDL is

blocked:[Day 71, How to understand what's blocking DDL](0071_how_to_understand_what_is_blocking_ddl.md)

However, an ad-hoc approach is not helpful if the incident is already over. A big part of this is because even with

`log_lock_waits = 'on'`, Postgres logs report only the "victim" (blocked session) query text (in the`STATEMENT` part of

log entries), with only`PID` information available about the blocker session.

To troubleshoot past incidents and understand trends, we need lock analysis to be implemented in monitoring, providing

details about the blocked and blocking sessions.

Here is a query that[@VKukharik](https://twitter.com/VKukharik) developed for

the[pgwatch2 - Postgres.ai Edition](https://hub.docker.com/r/postgresai/pgwatch2). It doesn't involve

the function`pg_blocking_pids()` because it would be unsafe due to possible observer effect.

with sa_snapshotas (

from pg_stat_activity

datname= current_database()

and pid<> pg_backend_pid()

and state<>'idle'

)

(extract(epochfrom now())* 1e9)::bigintas epoch_ns,

waiting.pidas waiting_pid,

waiting_stm.usename::textas tag_waiting_user,

waiting_stm.application_name::textas tag_waiting_appname,

waiting.modeas waiting_mode,

waiting.locktypeas waiting_locktype,

waiting.relation::regclass::textas tag_waiting_table,

waiting_stm.queryas waiting_query,

(extract(epochfrom (now()-waiting_stm.state_change))*1000)::bigintas waiting_ms,

blocker.pidas blocker_pid,

blocker_stm.usename::textas tag_blocker_user,

blocker_stm.application_name::textas tag_blocker_appname,

blocker.modeas blocker_mode,

blocker.locktypeas blocker_locktype,

blocker.relation::regclass::textas tag_blocker_table,

blocker_stm.queryas blocker_query,

(extract(epochfrom (now()-blocker_stm.xact_start))*1000)::bigintas blocker_tx_ms

frompg_catalog.pg_locksas waiting

join sa_snapshotas waiting_stmonwaiting_stm.pid=waiting.pid

joinpg_catalog.pg_locksas blockeron

waiting.pid<>blocker.pid

andblocker.granted

andwaiting.database=blocker.database

and (

waiting.relation=blocker.relation

orwaiting.transactionid=blocker.transactionid

)

join sa_snapshotas blocker_stmonblocker_stm.pid=blocker.pid

where notwaiting.granted;

With this added to monitoring, here is what can be observed there during incidents involving lock contention spikes:

And it has all the details available for both blocked and blocking sessions, supporting the troubleshooting and

root-cause analysis activities:

- 0067[Autovacuum "queue" and progress](./0067_autovacuum_queue_and_progress.md)

- 0068[psql shortcuts](./0068_psql_shortcuts.md)

- 0069[How to add a CHECK constraint without downtime](./0069_howd_tod_addd_ad_checkd_constraintd_withoutd_downtime.md)

- 0070[How to add a foreign key](./0070_how_to_add_a_foreign_key.md)

- 0071[How to understand what's blocking DDL](./0071_how_to_understand_what_is_blocking_ddl.md)

- 0072[How to remove a foreign key](./0072_how_to_remove_a_foreign_key.md)

- 0073[How to analyze heavyweight locks, part 3. Persistent monitoring](./0073_how_to_analyze_heavyweight_locks_part_3_persistent_monitoring.md)

- ...