Skip to main content

4 posts tagged with "internals"

View All Tags

· 7 min read
Nikolay Samokhvalov

After 2-003, @ninjouz asked on X:

If fast-path locks are stored separately, how do other backends actually check for locks?

The answer reveals why fast-path locking is so effective - and why PG 18's improvements matter so much in practice. // See lmgr/README, the part called "Fast Path Locking".

Remember from 2-002: when you SELECT from a table, Postgres locks not just the table but ALL its indexes with AccessShareLock during planning. All of these locks go into shared memory, protected by LWLocks. On multi-core systems doing many simple queries (think PK lookups), backends constantly fight over the same LWLock partition. Classic bottleneck.

Instead of always going to shared memory, each backend gets its own private array to store a limited number of "weak" locks (AccessShareLock, RowShareLock, RowExclusiveLock).

· 3 min read
Nikolay Samokhvalov

As we discussed, Lock Manager manages heavyweight locks – various kinds of them (various modes, various levels of granularity). These locks are released only at the end of the transaction.

In the most trivial case, when you run a SELECT on a table, this table is locked with AccessShareLock. And not only the table, but all its indexes, which happens during planning time (always happens unless you use prepared statements). This is to protect against concurrent DROP. All of these are released only when the transaction ends.

Information about pending and successful heavyweight lock acquisition attempts is stored in shared memory and can be displayed in the "pg_locks" view. Operations on this table are also protected by locks – but this time, lightweight locks. They are short-lived, being quickly released.

· 7 min read
Nikolay Samokhvalov

Let's talk about relation-level locks and various confusions, surprises and what is worth to remember in practice.

The key page in Postgres docs describing relation-level locks is here: https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES

This page in the docs is called "13.3. Explicit Locking" and it might cause confusion because it talks about implicit locking (e.g., if you run a DML or DDL, locks are applied implicitly; while if you execute LOCK or SELECT .. FOR UPDATE, you explicitly request locks to be acquired). However, this might be just my own terminology bias.

This page has a useful "Table 13.2. Conflicting Lock Modes" that can help understand how a lock acquisition can be blocked by another, already acquired or pending (!) lock:

Table 13.2: Conflicting lock modes in Postgres

A possible confusion here is the word "row" used in some lock modes – we shouldn't think that those modes are row-level. They are still relation-level. There is a special concept of row-level locks, we'll dive into that separately. This confusion is covered in the docs though:

Remember that all of these lock modes are table-level locks, even if the name contains the word "row"; the names of the lock modes are historical.

Back to the table above, there is a very useful transformation of it in old blog post by Marco Slot, "PostgreSQL rocks, except when it blocks: Understanding locks" (2018, https://citusdata.com/blog/2018/02/15/when-postgresql-blocks/) -- it's incomplete, but speaks common operations and can be used as quick reference:

Lock conflicts for common Postgres operations

Next, another terminology confusion might come from reading the section, "13.3.1. Table-Level Locks". Let's not be confused, it talks not only about tables, but rather relations.

In Postgres, the word "relation" can be applied to many objects, we can see it looking at the docs for pg_class.relkind (https://postgresql.org/docs/current/catalog-pg-class.html):

r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index

· 2 min read
Nikolay Samokhvalov

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

I'm using these materials: