Nik Samokhvalov

#PostgresMarathon 2-011: Prepared statements and partitioned tables — the paradox, part 3 | PostgresAI In #PostgresMarathon 2-009 and #PostgresMarathon 2-010, we explored why execution 6 causes a lock explosion when building a generic plan for partitioned tables — the planner must lock all 52 relations...

Deep dive in the situation with prepared statements and partitioned tables in Postgres:

part 1 postgres.ai/blog/2025102...

part 2 postgres.ai/blog/2025102...

part 3 postgres.ai/blog/2025103...

this repo deserves more stars gitlab.com/postgres-ai/...

we aim to have most detailed and advanced monitoring for Postgres -- and it's FOSS because it's just a foundation of something bigger

(after 20 years with Postgres, next month will be the most exciting announcement of my career)

Releases · postgres-ai/postgres_ai Expert-level Postgres monitoring tool designed for humans and AI systems - postgres-ai/postgres_ai

postgres_ai monitoring 0.11: VictoriaMetrics, lots of optimizations, new dashboards for table and index analysis, and more github.com/postgres-ai/...

Releases · postgres-ai/postgres_ai Expert-level Postgres monitoring tool designed for humans and AI systems - postgres-ai/postgres_ai

postgres_ai monitoring 0.11: VictoriaMetrics, lots of optimizations, new dashboards for table and index analysis, and more github.com/postgres-ai/...

TIL during this hacking session: REFRESH MATERIALIZED VIEW CONCURRENTLY creates temp tables github.com/postgres/pos... – so, if you do it super frequently, it might contribute to system catalog bloat

created telegram group for everyone who is hacking postgres source code or considers to start it: t.me/postgres_hac...

YouTube video by PostgresTV [Vibe] Hacking Postgres with Andrey, Kirk, and Nik

live session of Postgres [vibe] hacking happening now -- join! www.youtube.com/watch?v=6cZv...

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

#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...

#PostgresMarathon 2-008: LWLock:LockManager and prepared statements 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...

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

YouTube video by Scaling Postgres Boosting Planning Performance | Scaling Postgres 387

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

YouTube video by PostgresTV [Vibe] Hacking Postgres with Andrey, Kirk, Nik

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

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

- 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/

- 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/

- 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/

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 …

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 …

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 …

- 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 …

- 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 …

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/

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...

- 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/

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 …

#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/

- 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/

- 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/

- 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/

#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/

;-)

Good stuff as well!

I don't understand this