Skip to content
PodSubs
ShowsPeopleGuestsJobsNewsEvents
More
BlogPodCred
Menu
ShowsPeopleGuestsJobsNewsEventsBlogPodCredNetworksSign in
Join nowSign in
  • Shows
  • People
  • Jobs
  • News
  • More
    • Guests
    • Events
    • Blog
    • PodCred
    • Networks
    Join nowSign in

Postgres FM

Postgres FM artwork

LWLocks

Oct 17, 202538 minAudio

Nik and Michael discuss lightweight locks in Postgres - how they differ to (heavier) locks, some occasions they can be troublesome, and some resources for working out what to do if you hit issues. Here are some links to things they mentioned: Wait Events of Type LWLock https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LWLOCK-TABLE Our episode on (heavier) locks https://postgres.fm/episodes/locks Nik’s new marathon posts https://postgres.ai/blog/tags/postgres-marathon Postgres LISTEN/NOTIFY does not scale (blog post by Recall ai) https://www.recall.ai/blog/postgres-listen-notify-does-not-scale Explicit Locking https://www.postgresql.org/docs/current/explicit-locking.html pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW Tuning with wait events for RDS for PostgreSQL https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.html MultiXact member exhaustion incidents (blog post by Cosmo Wolfe / Metronome) https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025 pg_index_pilot https://gitlab.com/postgres-ai/pg_index_pilot Myths and Truths about Synchronous Replication in PostgreSQL (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=PFn9qRGzTMc Postgres Indexes, Partitioning and LWLock:LockManager Scalability (blog post by Jeremy Schneider) https://ardentperf.com /2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork

Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.

Transcript

From the show.

Michael: Hello and welcome to PostgresFM, a weekly show about

all things PostgreSQL.

I am Michael, founder of pgMustard, and as usual I'm joined by

Nick, founder of Postgres.AI.

Hey Nick, how's it going?

Nikolay: Hello Michael, going great, how are you?

Michael: I am good also.

What are we talking about this week?

Nikolay: We're talking about...

What's written on the cover?

Michael: Are we gonna write LWLocks or lightweight locks as a full

word?

What do you think?

Nikolay: I like the shorter version of course.

There's also a question to write 3 uppercase letters or everything

lowercase.

Yeah, lightweight locks, it's like, it's even hard to pronounce.

So bottleneck locks also not good, right?

LWLocks.

I like some systems call it latches, you know?

Michael: Latches, yeah.

Yeah.

Why Postgres doesn't do it?

Nikolay: Maybe because of Linux or I don't know.

Michael: Naming things is hard.

Nikolay: Yeah, yeah, yeah.

Because we have confusion sometimes when we say just locks.

You know, like when we say backups or logical backups dumps,

here also locks, there are 2 types of locks.

There are more types

Michael: of locks, right?

Nikolay: But there are big 2 major types.

Yeah, categories.

Yeah.

Is it category or type in pg_stats, in pg_locks?

Michael: Good point.

Yeah, maybe types.

I don't know.

Another loaded term, types is another loaded term.

Nikolay: Or mode or maybe more let me let me just check right

now maybe it's called mode in pg_locks I'm constantly confused

about yeah there are you know there are some terms like class

type mode like they can be like they are quite abstract right

it's called mode in pg_locks it's called mode

Michael: cool

Nikolay: but pg_locks is about heavyweight locks we are talking

today about lightweight locks right

Michael: yeah we did we did a whole episode that we just called

locks because that's generally what they referred to the heavyweight

locks yeah

Nikolay: if there is no additional word it means heavyweight

locks so yeah and and why why so because heavyweight locks you

can name them just locks because they are closer to User, right?

You can see them in pg_locks, for example, you can sometimes, not,

not always, but sometimes you can acquire them directly using

just lock SQL command right just lock table name or SELECT for

UPDATE and you sit in transaction being acquired some locks right.

Michael: Yeah I also think in general more people come across

them because they affect you at earlier stages in project life

cycle.

Like you don't have to be at such extreme scale to start being

affected by them or having to be aware of them.

Nikolay: Yeah, I agree.

In general case.

Because in some cases, for example, if you have read-only workloads,

that's it.

In read-only, you don't like, you have heavyweight locks, but

you won't notice them because they're like ACCESS SHARE lock,

that's it.

Right.

But if you have really a lot of TPS, you might start observing

some lightweight locks.

Yeah,

Michael: I was thinking even like Schema changes though, like

even in read-only.

Nikolay: This was edge case.

Michael: Yeah, okay, fine.

Nikolay: In general, I agree with you, heavy locks, you bump

into them sooner.

Schema change is a great example.

Michael: Cool.

So where did you, I mean, starting with the difference between

locks and lightweight locks is probably great.

Nikolay: Yeah, let's talk more about differences, because there

are important differences to understand and feel and take into

account when you develop things or tune, optimize, scale, migrate.

So heavyweight locks are acquired during like SQL operations and

they are acquired for like Database objects, like Relational

level locks.

By the way, this is super confusing.

You know, yeah, I'll be talking about heavyweight locks a little

bit, trying to make it shorter, but as you know, I write again

almost every day.

I skip some weekends, but I write Postgres Marathon posts again,

And many days I already sit in between heavyweight locks and lightweight

locks and research 1 of LWLock:LockManager, right?

So relation level locks, it's quite confusing name because they

are called in documentation, they are called Table level locks.

Yeah.

Which is misleading because they are also this type of like the

same thing and inside documentation it's already, it becomes

clear that indexes are also involved and materialized views and

views.

And so all the relations, sequences are also relations, right?

Michael: Yes.

I never really thought of it like that.

Nikolay: Well, if you check class and reltype, I think S

is sequence.

Maybe I should check again.

Should we develop a habit to check things right online?

Michael: Why not?

Nikolay: Yeah, so I will be checking, but meanwhile, you can

acquire locks, heavyweight locks on tables, on indexes, on database,

right?

Like even higher level, you can lock the whole database.

And we know the recent problem when Recall.ai blog post, right?

Our clients, they posted about database level lock acquired when

NOTIFY happens to establish sequential NOTIFY events at commit

time.

And also row level locks.

Yeah.

Tuple or row level.

Let's leave it for another time.

So you can acquire locks on database objects.

These are heavyweight locks.

Documentation is also confusing because it says explicit locking.

Although most of the cases where you have it, it's implicit locking.

You say alter table and you don't say lock table, you say alter

table.

So it's actually implicit.

Well, this, I have also always like some shift in my brain when

I need to Google documentation for lock or heavyweight locks.

I just remember, I need to search.

I need to ignore the fact that I'm going to look at explicit

locking documentation, although I need implicit locking documentation,

right?

Michael: By the way, before we move on, I checked pg_class and

you're right, sequences are in there, and weirdly the rel kind

is capital S all the others are lowercase I think well the ones

I can see anyway.

Nikolay: Does it mean something?

Michael: Don't know.

Nikolay: Yeah.

Oh, by the way, explicit locking documentation, it mentions that

you can lock indexes with ACCESS SHARE lock, for example.

But You cannot do it explicitly.

You cannot say lock an index name.

So I'm pretty sure you cannot do it with sequences as well.

Yeah, anyway, so these are heavyweight locks, right?

So you basically, your actions, I mean your SQL, this is what

directly creates heavyweight locks.

And why is it needed?

Because we need to, we are not working in single user mode.

We need to protect resources from concurrent operations, reading,

writing, changing.

And usually we don't need to protect from reading, but while

somebody is reading, another Backend shouldn't modify it usually,

right?

Or for example, if you read from table, dumping it for example,

other Session cannot modify, like add a Column, for example,

cannot run DDL, for example, right?

Michael: Or drop it, for example.

Nikolay: And the important thing about heavyweight locks compared

to lightweight locks is to understand that once a lock is acquired,

it can be released only in the very end of Transaction, Commit

or Rollback.

That's it.

Only 2 options to release this lock.

You cannot release it midway.

Right, and this is super important for understanding always.

It means that Transactions should be shorter.

Right, so your actions won't affect others.

Like, or chances to affect others would be lower.

Right?

This is...

Michael: Or time that it affects others is lower, right?

Nikolay: Yeah, yeah, yeah.

Michael: Because you will affect people just for less time and

there's a point at which that becomes unnoticeable or acceptable.

Nikolay: Yeah, yeah.

Or won't affect at all if they come a little bit later.

But if you change something or even if you read something and

keep Transaction open for hours, it means nobody can modify this

table, no DDL is possible, autovacuum cannot do some things

and so on, like it's bad.

Michael: Yeah, it's worse than that, isn't it?

I know we've talked about this before, but if DDL comes along

and doesn't have a lock_timeout, then naturally you can suddenly

be down.

Yeah.

Nikolay: Yeah.

Yeah.

So because, yeah, this is also a good point.

So heavy locks, they have this ability, like this property to

be acquired.

Release happens only in the very end.

And what you say also good point.

They also, there is a LockManager.

There's, By the way, I couldn't find definition of LockManager.

Nowhere, nowhere.

Like it's like, it's obvious, right?

Even in the source code, it's not defined, which is interesting.

So LockManager is responsible for managing locks, heavyweight

locks, right?

And backends can form a queue of waiting for a lock acquisition.

So if I'm waiting to acquire lock, some other backends can be

waiting and they like ask where is the end of the line and go

there, right?

So it's just natural, like in the order of first, like in natural

order, right?

So unlike lightweight locks, lightweight locks acquired and released

very quickly.

I think documentation, source code, I mentioned it's like dozens

of operations.

Unlike There is underlying concept of spin locks, which like

few operations only, like few instructions only.

Lightweight locks are bigger, but it's very fast as well.

Acquired and released, and they don't wait until the end of transaction

because they work in lower level of abstraction.

It's not closer to users, closer to resources like memory.

So their main purpose is to protect some physical resources like

parts of the memory, shared buffers, and so on.

Right?

OK.

Yeah.

Like these things.

So they can be acquired and list quickly.

There are only 2 types, exclusive lock and share lock, unlike heavyweight

locks.

Heavyweight locks have a list.

And interesting relationships between different ones, right?

Here it's only share and exclusive, shared and exclusive.

Shared locks don't conflict, shared lightweight locks don't conflict.

But exclusive lock cannot be acquired while share lock is still

running, lasting.

Right?

Share lock should be released first, then only you can acquire

exclusive lock, because exclusive lock is needed to modify the

resource, right?

Share lock is needed to protect for reading.

It's saying I'm reading, don't change it because I'm still reading.

And when I'm done you can modify it, right?

So this is lightweight locks.

And that's why they are lightweight, because they are much, much

shorter living, right?

So these are main differences between them.

What else?

Michael: Maybe types of lightweight locks?

Well forgive the word types but you know what what should people

be aware of Because I've only really come across 1 type because

that's the type that seems to cause the problems.

But what should people be aware of at least?

Nikolay: Yeah so types, modes, I struggle, I'm mixing these terms

and it's really hard to distinguish between them.

So if we talk about types, exclusive and shared, we just covered

it.

If we talk about different kinds or, let's say wait events

we observe in pg_stat_activity.

pg_stat_activity is the main cumulative statistics system view.

Everyone should think, like, learn about it, right?

It's super important because it shows what's currently happening

in database.

And there are 2 columns called wait event type and wait event.

Also, by the way, slightly confusing because the word type is

there and so on.

I would prefer like, it would be good to name that thing like

classes maybe or category.

I don't know because type word is so overused or like overloaded.

Right.

Anyway, wait event type can be, I think there are less than

10 class, 10 types.

And 2 of them which are most interesting today is lock, meaning

heavyweight lock.

And LWLock.

And wait event type LWLock, you can check documentation.

There are many, many, many, many dozens of wait events for LWLock,

meaning that we have a lot of kinds of LWLock.

These kinds, like again, types are only like exclusive and shared,

but these kinds, it's classification with respect to the resource

we are locking.

For example, LockManager itself, although the main purpose of

LockManager is to handle, to manage heavyweight locks.

When it does it, it does it using a piece of memory, shared memory.

Special piece of shared memory called, well it's called like

main lock table, right?

It's a big piece of memory which is segmented partition to 16

partitions starting Postgres I think 9.2 or 8.2 actually it was

very long.

num lock partitions 16.

And when a new information about heavyweight lock is needed to

be written there, The partition of this main lock table where

it needs to be written, it needs to be locked by lightweight

lock, right?

To ensure nobody else is writing to it.

So, LockManager can have up to 16 lightweight locks, which are

seen as LWLock:lock_manager.

16 because we have 16 partitions of this main lock table in memory.

And how it works based on, for example, for relational level

locks, based on the relation name, there's a hash function which

understands which partition to use, determines which partition

to use, right?

Michael: Yeah.

So

Nikolay: The same table or index will always go to the same partition

of all of those 16 partitions.

Right?

Michael: So this was a long time ago.

So back in the day, I'm guessing this was 1 thing, and there

was probably too much contention.

Nikolay: Before 8.2.

Yeah.

Michael: Yeah.

Okay.

That's what you're talking about.

Great.

Nikolay: Yeah.

And this just to like, there's a confusion because there was

another 16.

Yes.

Michael: That's what I was thinking.

Nikolay: It's a different constant, which, which changed that

behavior changed in, in Postgres 18.

This behavior hasn't changed.

Yeah, fastpath changed.

This hasn't.

This hasn't.

This still is 16 partitions and if you have a lot of heavy lock

acquisition attempts for the same relation, a lot I mean like

thousands or maybe dozens of thousands per second, a lot, really

a lot, then exclusive lightweight locks on the same partition

will be competing.

And while you are waiting, like while we try to establish heavyweight

lock to some index or table, but that partition is already locked

by exclusive lightweight locks from different backends attempt

write heavyweight lock information about it.

We need to wait a little bit.

And this little wait will be seen as wait event type LWLock and

wait event lock manager.

Right?

Is it clear?

Because we are like, we have weird combination of heavyweight

locks and lightweight locks in the same topic here.

Michael: Especially because the lightweight lock manager is actually

looking at heavyweight locks.

That's the confusing part for sure.

But I was just looking up in the docs, in the table of all of

the...

They've called them types, wait events of type LWLock, and

you're right, it's such a long list.

I think there might be 50 or more.

Nikolay: You will see checkpoint, autovacuum there, but isn't

it great that we don't observe them?

It means it's quite well optimized, right?

Like for example, yeah, yeah, you'll find a lot of stuff.

I see many of them, I do observe, not just a lock manager.

We saw many of them in production and yeah.

Usually the rule is if you see lock wait event type, it means

you need to go and think how to redesign your application.

Because classic example is, for example, we are doing some billing

system and we have a single account which needs to be updated

for each transaction people do.

I mean, financial transaction.

And this is a classic example when you shoot yourself into the

foot because updating the same row will be like hotspot.

And you will see a lot of, you see heavyweight lock contention

because many, many backends try to update the same row.

Right.

Yeah.

So, or you mentioned a very good example.

If you do DDL without lock timeout and retries, you also can

have a chain of waiting backends, which just wait until your

DDL finishes, but it itself is waiting for some other SELECT.

And people, I see examples in blog posts, people, like I see

examples, people try to explain this problem, but many of them

involve updates, deletes.

No, just SELECT, DDL, and many other SELECTs.

You don't need even to update anything or INSERT or DELETE.

That's it.

Just SELECTs and DDL.

And we see a lock wait event in pg_stat_activity.

It's bad.

But when we talk about...

Yeah, and for lock you saw like it's a relation, object, page,

also page interesting, tuple, virtualxid, that's interesting.

Advisory locks is kind of a different thing.

But for LWLock, we have a lot, and among them, there is lock manager.

I like the approach, which I think RDS started, maybe not RDS,

but they use it a lot and I also started using it.

We usually take WaitEventType and WaitEvent to columns from pg_stat_activity

and write them with a colon in between.

So it becomes LWLock:LockManager.

Just in, you know, like in texts where we discuss problems and

do RCA or something.

Yeah, it's just convenient.

Michael: Like a naming convention.

Nikolay: And I wanted to highlight that this problem, which related

to both heavyweight locks and lightweight locks, in the name of

it, we have the word lock twice.

LWLock:lock_manager.

First time it's about lightweight lock but in lock manager it's

about heavyweight lock.

That's why the lock is encountered twice, 2 times.

What else?

We have other, we observed other types of lightweight locks problems.

For example,

Michael: yeah.

Well, I've spotted 1 in the list, SubtransSLRU.

Nikolay: Yeah, this is my favorite 1.

Although I must admit, I haven't touched this topic for a few

years.

Yeah, yeah.

I touched it heavily in 2021 when GitLab had the problem.

Yeah.

And studied it.

And yeah, and since then, SLRU, it's simple, at least recently

used, it's small caches, Postgres have multiple of them.

I think since Postgres 12 or 13, we have pg_stat_slru system

view, where you can see like counters of work of those SLRUs,

But also SLRU mechanism got some handles, I mean, settings, GUC,

GUCs, right?

People say GUCs.

You can change them and increase it and yeah, to postpone this

performance cliff.

So I haven't seen them often since then.

Like there are customers who usually read and come, we help like,

we help easily, like just try to get rid of sub-transactions,

although, like I still think by default you should avoid sub-transactions,

but in some cases I already see they can be used in safe way.

You know, you need to just understand the limits and then you

can use them.

For example, again, DDL, sometimes complex changes of schema.

You don't want to lose part of schema.

And this approach with attempts to acquire lock, how can you do

it inside transaction, you need sub-transaction, right?

Yeah.

Because if attempt fails, you don't want to lose everything.

You want to lose only the last step, right?

And this is exactly where I think it's worth thinking about to

use some transaction, but you need to understand like details.

For example, you don't want to have a long transaction running

on the primary in parallel.

To other table, by the way, not to any table.

And replicas which receive a lot of like transactions per second

because they might be down because of the use of sub-transaction

and you can see subtrans SLRU because SLRU is overflown.

And again when it's overflown the lightweight locks acquisition

like we see contention and we see it as in pg_stat_activity and

wait event analysis as LWLock:SubtransSLRU

Right.

There are other SLRUs, right, mentioned like notify SLRU, I'm

pretty sure MultiXact upset, MultiXact member SLRU.

Speaking of them, we had an episode about the case from Metronome,

right?

Michael: Yeah, true.

Nikolay: Yeah, it was a great blog post.

Like this is a great example how company can share with community

what happened and others benefit.

Since then we had another client, new client we had, which came

to us with very same problem related to MultiXact member.

Michael: Wow.

Member exhaustion.

Nikolay: Yeah, exactly.

Wow.

So it's also observed like a lightweight lock, MultiXact blah,

blah.

There are, there's a bunch of MultiXact lightweight locks you

can see in the table.

Another 1 is a very popular 1 is LWLock:buffer_mapping.

So usually it's called a buffer thrashing, right?

When we let like the buffer pool is not big enough and a lot

of eviction happens and new pages are loaded all the time and

and we see when of course when it's happening to protect memory

Postgres needs to use exclusive lightweight lock when writing

happening, shared lightweight lock when reading happening.

And this is exactly when we can see some backends are a little

bit waiting for other backends, right?

And this is how it's seen.

Michael: So like if somebody's limited by the amount of shared

memory, or like shared buffers.

Nikolay: Yeah, solution is simple.

We need to increase the buffer pool.

We need to fight bloat because this is what like increases this

problem.

You need to get rid of unused indexes and other things because

they also contribute to it.

Right.

Unused ones.

Of course.

Michael: Like, I was just thinking they would be evicted and

not.

Nikolay: Think better when you change something with insert or

non-HOT update.

Michael: All indexes need to

Nikolay: be loaded to be changed.

Yeah. Yeah.

And they contribute to this spam coming to the buffer pool and

also to WAL, but it's a different story.

So we, that's why I think people underestimate how bad bloat

is.

I feel it like we have a new wave of companies coming to for

consulting to us, which I call AI companies.

They probably are quite old companies, but they have the transition

to AI.

And they have increasing data volumes, increasing workloads,

and they underestimate the problem of bloat and unused indexes

and index write amplification, all this spam coming to memory

and WAL and it means backups, replication.

It's like, this thing is like multi-sided.

Yeah.

Michael: Like cascades, doesn't it?

Nikolay: Yeah.

Yeah, yeah.

It doesn't, and also it's not performance cliff which you like

suddenly see and oh we have a problem it's slowly slowly slowly

like growing

Michael: or like you sink into it slowly like like sand or like

a swamp

Nikolay: yeah yeah And then you need to increase instance size

or think about sharding and so on.

By the way, I like sharding a lot, but I think in many cases

it's just hiding the problem.

It's just distributing the problem and you just you pay to not

to solve problems.

For business it's sometimes a valid approach, right?

You just you don't have time to solve this.

But we also can just implement automated procedures to reduce

the amount of trash you have, right?

Michael: Yeah, I hear about it all of the time as well, even

at smaller companies that just upgrade their instance, especially

at the lower sizes.

They just don't wanna throw engineering time

Nikolay: at it. I'm very surprised.

I recently started asking directly on the very first call when

we have consulting like guys, do you care about bloat and index

health?

And I usually hear no.

And then It's our job to explain why, right?

Michael: So it's easy.

Yeah, bloat is not the problem.

Bloat is like, well, it might be the root cause, but it's not

the problem users see.

They don't see.

They're not.

Nikolay: Well, unused indexes also they don't see.

Michael: Yeah, yeah.

Nikolay: They created some indexes and forgot about it, right?

Or redundant indexes.

Same problem.

I mean, similar in this case, but then boom, like buffer mapping,

LWLock:buffer_mapping.

Why?

We don't have enough memory.

Michael: Yeah.

Also, this might be part of it.

I think Bloat used to be worse.

Like I remember in the

Nikolay: before a

Michael: few optimizations

Nikolay: 14-14.

Michael: Yeah before then we you could come across especially

indexes that were like 99.9% you know in extreme cases you could

come across very very very bloated indexes it's just much less

likely now so I think it's

Nikolay: less likely but not much

Michael: yeah well okay

Nikolay: well only deduplication doesn't solve the problem when...

Like Postgres B-tree doesn't have merge.

Michael: But it does have bottom up deletion.

It does like a lot less spitting.

Nikolay: Well, if you have in the middle of B-tree, half empty page,

This space won't be used if you write, for example, it's an incremental

timestamp.

You're writing to the end always.

In the middle, nobody will write, so you have this Bloat which

won't be eliminated.

Michael: True.

Until like, yeah, anyway, let's get back to

Nikolay: the topic.

Until reindex.

Michael: Well, I was going to say until like logical replication

upgrade or something like that.

Nikolay: Well, yeah, yeah, yeah, Anyway, when you rebuild index

anyway, right?

Michael: Yeah.

Nikolay: By the way, let me advertise something.

We have open source component, which we just recently developed.

It's now entering beta stage and it aims to like to automatically

rebuild indexes on any platform, just reach out to me in any

way.

I will share details because we are looking for more cases before

we move on and make it more publicly available.

I don't advertise it because I want to understand the use cases

people have in terms of...

Anyway, if you want to rebuild indexes in an automated fashion,

we have an open source component for you.

Fresh 1, very interesting, not only for B-tree, but for any index,

almost any.

I think BRIN is not supported.

All others are supported.

Michael: I've never seen a bloated BRIN index.

Nikolay: Have you?

Yeah, good question.

It can degrade a lot if you modify.

Michael: We've got another good episode on that actually.

MaxMulti I think is...

Nikolay: On every sentence we say we had an episode already,

right?

Anyway, yeah, we're looking for early adopters for this small

tool.

Michael: Cool, I'll put it in the show notes.

Nikolay: Yeah, yeah.

Which is open source.

Yeah.

Okay.

WAL write is another 1 we see often.

Michael: Oh really?

Nikolay: Yeah.

Again, like when somebody hasn't dropped unused and redundant

indexes And they write a lot of, oh, I forgot to mention, of

course you can write, you can find queries which contribute to

this buffer thrashing, right?

And maybe get rid of them or make it less, causing less smaller

storms, right?

So you can optimize queries sometimes and, and, and avoid that

LWLock:buffer_mapping contention.

Yeah.

So about WAL writes, same thing, like if you have a lot of indexes

which contribute to WAL writes, or you have like very frequent

checkpoints and you have a random access pattern of writes.

When you write to the same page often, but between those writes

to the same page you have checkpoint, It means this page will

go as a full page right to WAL multiple times over and over.

You have a lot of WAL and this can be also an issue.

And if disks are, maybe disk I/O is saturated as well.

Yeah, these things.

Michael: Yeah.

Or IOPS.

Nikolay: Yeah. There are, there are several things there.

Like there is I/O WALWrite, I think, and LWLock WALWrite.

I don't remember from top of my head, but there are interesting

nuances there.

I probably should cover it some day in Postgres Marathon, because

sometimes you are waiting on disks, but sometimes you are waiting

on locking internal structures, lightweight lock.

So if WAL buffers is like, there is a quite small amount of

WAL buffers in the memory, so if it's already fully written,

it needs to go to disk, probably you are waiting on disk.

But if you are writing to...

Yeah, so it should be checked in detail when we have it.

There are several wait events there.

Also interesting thing which pops up recently is SyncRep.

LWLock SyncRep.

Synchronous replication, when the primary cannot continue because

it waits confirmation from replicas, synchronous replicas.

Michael: So, okay, and you're seeing a lot of those.

Nikolay: Not a lot, but this started happening more and more

if you use synchronous replication, Quorum commit.

Michael: What?

Yeah.

I don't...

I come across a lot of people that think they're going to use

synchronous replication but then end up don't.

Do you see it quite commonly used?

Nikolay: Let's say so.

Big old clusters are on async.

All new clusters should be on synchronous replication, although

there is a bunch of issues with it.

And there was a great talk a few months ago presented by Alexander

Kukushkin about misunderstanding of synchronous replication and

various anomalies you can experience in current implementation

because it's actually not synchronous replication.

This is the thing.

Because when commit happens, main thing, when commit happens

on the primary, it actually happens.

It already happened.

Commit happened.

But we just are locked, by the way, on heavy lock, right?

Heavyweight lock.

Our transaction is locked.

And we are waiting for 1 of replicas to confirm.

Or this is actually a lightweight lock sync replica.

Yeah, this is it.

Yeah, this is it.

Michael: This is it.

We are Nikolay: locked and we are waiting.

And when a replica confirms, this lightweight lock released.

This is a special case when we need to wait for something outside,

which will help us unlock.

Michael: So I have watched that talk, and I remember a really

good slide in it with like all of the hops, like a really good

diagram of what actually happens.

So yeah, I'll include a link to

Nikolay: that.

Yeah, and this how to troubleshoot LWLock:SyncRep is like, it's

not fully understood.

There are interesting new cases which are not covered by in articles

and talks.

I think more materials are coming.

I know about some.

Michael: Okay.

Can you share them with me?

Nikolay: Well, it hasn't happened yet.

Check out the upcoming PGConf.EU.

Michael: Great.

So, yeah.

How would you feel about calling the episode there and actually

then talking about specifically the lock manager issues in a

different episode

Nikolay: Sounds good.

Good. Nice because there are interesting answers inside.

Yeah, great Let's call it a day for today.

I think we covered 1 and a half percent because it's huge.

The list is huge.

Some of them I haven't seen ever.

Michael: But I think that's useful.

I think it's useful to kind of, for people to get a grasp on

like which ones are they most like?

Nikolay: Oh, main thing, always I mention when we talk about

lightweight lock and actually wait event analysis, RDS documentation

has great list of like knowledge and how to style troubleshooting

documents for many wait events, including many lightweight locks,

not all of them, only subset, but it's great documentation.

I hope it will be improved over time, extended, right?

Michael: Yeah.

Yeah. It's

Nikolay: good.

I know a lot of effort was invested to building by many people

I recently reread the blog post by Jeremy Schneider how

it was done during a couple of years.

So it was huge effort That's why it's so good Short yeah, but

yeah short documents, but so so much wisdom inside

Michael: Yeah Done over a long period of time, but also by very

good people, like people that really know, you know, stuff.

Nikolay: So basically do this, this, this, like list of mitigation

action items.

But behind each step, many RCAs, right?

Yeah.

Cases, case studies, it's so much time paid to just write 1 line

what to do.

Or what to check, or how to change, how to improve.

That's a great example of documentation.

Michael: Thanks so much, Nikolay.

Thank you.

Look forward to talking again soon.

Open the transcript file

Hosts and Guests

Hosts

  • Michael Christofides Host Feed-stated
  • Nikolay Samokhvalov Host Feed-stated

Credit the people on this episode

Be the first to rate this episode

More episodes

All episodes
  1. Postgres 19

    Nov 19, 202653 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...

    Credit the people on this episode

  2. The hidden costs of CREATE TEMP TABLE

    Nov 13, 202639 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...

    Credit the people on this episode

  3. Extensions on demand

    Nov 6, 20261 hr 9 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...

    Credit the people on this episode

  4. UUIDv4 vs UUIDv7

    Oct 30, 202648 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...

    Credit the people on this episode

  5. Dump/restore done. Now what?

    Oct 23, 202651 minAudio

    Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...

    Credit the people on this episode