Author | Lightnews

Nik Samokhvalov @samokhvalov.bsky.social · 13d

I'm on a trip until 2025-10-22, and have only limited time with laptop – #PostgresMarathon will be continued when I'm back

Nik Samokhvalov @samokhvalov.bsky.social · 13d

#PostgresMarathon 2-008: LWLock:LockManager and prepared statements postgres.ai/blog/2025101...

#PostgresMarathon 2-008: LWLock:LockManager and prepared statements | PostgresAI

As was discussed in #PostgresMarathon 2-002, for a simple SELECT from a table, at planning time, Postgres locks the table and all of its indexes with AccessShareLock. A simple demo to remind it (let m...

postgres.ai

Nik Samokhvalov @samokhvalov.bsky.social · 15d

#PostgresMarathon 2-007: Should we worry about pg_blocking_pids()'s observer effect? postgres.ai/blog/2025101...

#PostgresMarathon 2-007: Should we worry about pg_blocking_pids()'s observer effect? | PostgresAI

Many years ago, when developing complex automated procedures for a large company, I realized that my automation needs monitoring components. Including understanding heavyweight lock contention – for e...

postgres.ai

Nik Samokhvalov @samokhvalov.bsky.social · 16d

Got very much attracted by title "Boosting Planning Performance | Scaling Postgres 387",

was very curious (because I do want to learn more ways to boost planning performance), checked it out: www.youtube.com/watch?v=d7__...

...and ERROR: infinite recursion detected

Boosting Planning Performance | Scaling Postgres 387

YouTube video by Scaling Postgres

www.youtube.com

Nik Samokhvalov @samokhvalov.bsky.social · 20d

Postgres hacking session LIVE www.youtube.com/watch?v=5lN7...

[Vibe] Hacking Postgres with Andrey, Kirk, Nik

YouTube video by PostgresTV

www.youtube.com

Nik Samokhvalov @samokhvalov.bsky.social · 24d

Key points to remember:
1. Heavyweight locks = same as just "locks"
- contention → fix your SQL/schema
- held until COMMIT/ROLLBACK
2. Lightweight locks = LWLocks:
- contention on them → lack of resources, misconfiguration, suboptimal workload patterns, or Postgres internal limitations

Nik Samokhvalov @samokhvalov.bsky.social · 24d

- we can observe pending and successful lock acquisition attempts using pg_locks (and joining it with pg_stat_activity on pid)

Lock Manager is a core component of Postgres responsible for managing heavyweight locks.

10/

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

- users can explicitly initiate a lock acquisition attempt
- attempts to acquire locks form a queue
- there are many types of locks and sophisticated rules of conflicts between them (see www.postgresql.org/docs/current...)

9/

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

- once acquired, they are held until the very end of transaction; IMPORTANT: acquired heavyweight locks can be released only when COMMIT or ROLLBACK happens, never before
- there is a deadlock detection mechanism – this is one of the jobs of Lock Manager

8/

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

Heavyweight locks:
- these locks need to coordinate concurrent access to various high-level resources including but not limited to: databases, tables, indexes, rows

7/

13.3. Explicit Locking

13.3. Explicit Locking # 13.3.1. Table-Level Locks 13.3.2. Row-Level Locks 13.3.3. Page-Level Locks 13.3.4. Deadlocks 13.3.5. Advisory Locks PostgreSQL provides various …

www.postgresql.org

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

when we see a high number of active sessions in pg_stat_activity with wait_event_type = 'lwlock'. If we take into account particular wait event (column wait_event), we can see cases like 'LWLock:SubtransSLRU', 'LWLock:BufferMapping', 'LWLock:LockManager'

6/

13.3. Explicit Locking

13.3. Explicit Locking # 13.3.1. Table-Level Locks 13.3.2. Row-Level Locks 13.3.3. Page-Level Locks 13.3.4. Deadlocks 13.3.5. Advisory Locks PostgreSQL provides various …

www.postgresql.org

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

- normally, we don't have a direct way to observe LWLocks (there is a setting "trace_lwlocks", which requires LOCK_DEBUG at compilation time, so it's only for hacking/debugging purposes), but we do talk of them a lot when Postgres suffers from an LWLock contention –

5/

13.3. Explicit Locking

13.3. Explicit Locking # 13.3.1. Table-Level Locks 13.3.2. Row-Level Locks 13.3.3. Page-Level Locks 13.3.4. Deadlocks 13.3.5. Advisory Locks PostgreSQL provides various …

www.postgresql.org

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

- interesting fact from lmgr/README: "if a process has to wait for an LWLock, it blocks on a SysV semaphore so as to not consume CPU time" – we'll return to this later
- in general, very well optimized in modern Postgres versions; LWLock contention is observed only in heavily loaded systems

4/

13.3. Explicit Locking

13.3. Explicit Locking # 13.3.1. Table-Level Locks 13.3.2. Row-Level Locks 13.3.3. Page-Level Locks 13.3.4. Deadlocks 13.3.5. Advisory Locks PostgreSQL provides various …

www.postgresql.org

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

Lightweight locks ("LWLocks"):
- internal mechanism to coordinate access to shared memory structures
- usually very short, so they are typically released really fast (although may take longer – for example, to protect I/O operations)
- can be exclusive (for writes) or shared (for read-only ops)

3/

13.3. Explicit Locking

13.3. Explicit Locking # 13.3.1. Table-Level Locks 13.3.2. Row-Level Locks 13.3.3. Page-Level Locks 13.3.4. Deadlocks 13.3.5. Advisory Locks PostgreSQL provides various …

www.postgresql.org

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

- LockManager source code – src/backend/storage/lmgr (and first of all, README github.com/postgres/pos...)
- Egor Rogov's "PostgreSQL Internals", Part III "Locks"

2/

postgres/src/backend/storage/lmgr/README at master · postgres/postgres

Mirror of the official PostgreSQL GIT repository. Note that this is just a *mirror* - we don't work with pull requests on github. To contribute, please see https://wiki.postgresql.org/wiki/Subm...

github.com

1

Nik Samokhvalov @samokhvalov.bsky.social · 24d

#PostgresMarathon 2-001

To warm up, let's talk about lightweight and heavyweight locks (or "regular locks" or just "locks").

I'm using these materials:
- PG docs (first of all, www.postgresql.org/docs/current...)

1/

13.3. Explicit Locking

13.3. Explicit Locking # 13.3.1. Table-Level Locks 13.3.2. Row-Level Locks 13.3.3. Page-Level Locks 13.3.4. Deadlocks 13.3.5. Advisory Locks PostgreSQL provides various …

www.postgresql.org

1 1

Nik Samokhvalov @samokhvalov.bsky.social · 25d

- Ideally, in the end, all the info will be compiled into a big blog post to cover one big topic

Make sure you follow and share it with your followers.

The first topic will be: ** Lock Manager **

Let's go!

4/

1

Nik Samokhvalov @samokhvalov.bsky.social · 25d

- I'll be digging deeper (source code, case studies, complex experiments), and also educating myself, so we all understand all the details, and can return to these records later if needed
- You'll have a chance to participate and help me make sure there are no unknowns left

3/

1

Nik Samokhvalov @samokhvalov.bsky.social · 25d

- Every day, I'll be posting bits of info: from basics, to internals, to experiments and benchmarks and finally, practical pieces of advice (aka "howtos", which made up my first #PostgresMarathon in 2023 that lasted 3 months)

2/

1

Nik Samokhvalov @samokhvalov.bsky.social · 25d

#PostgresMarathon is back!

Make sure you follow me so we could learn some good stuff about Postgres together.

This time, I'll be doing it a bit differently:
- I'll choose one big Postgres topic that I'll be covering over a week or two

1/

1 1

Nik Samokhvalov @samokhvalov.bsky.social · 25d

;-)

Nik Samokhvalov @samokhvalov.bsky.social · 25d

Good stuff as well!

Nik Samokhvalov @samokhvalov.bsky.social · 25d

I don't understand this

1

Nik Samokhvalov @samokhvalov.bsky.social · 25d

anti-tip:

SELECT INTO is ...DDL. It creates a table.

Not what you expect after spending hours inside pl/pgsql

www.postgresql.org/docs/current...

I wish Postgres didn't have this feature 😂

SELECT INTO

SELECT INTO SELECT INTO — define a new table from the results of a query Synopsis [ WITH [ RECURSIVE …

www.postgresql.org

Nik Samokhvalov @samokhvalov.bsky.social · 25d

If you think that you understand the whole meaning of max_locks_per_transaction, think again