Nikolay: Hello, hello, this is
Postgres.FM.

I'm Nik, Postgres.AI, and as usual,
my co-host is Michael, pgMustard.

Hi, Michael, how are you doing?

Michael: I'm good, how are you?

Nikolay: Great, everything's all
right.

A lot of bugs to fix and incidents
to troubleshoot, to perform

root cause analysis as we say,
RCA.

Michael: It sounds related to our
topic today maybe.

Nikolay: Oh yeah, maybe yes.

So The topic I chose is, I'm still
not 100% sure how to name

it properly, so let's decide together.

But the situation is simple for
me, relatively.

So we help a lot of startups, and
at some point I decided to

focus only on startups.

Being like raised a few startups
and helped many startups.

I know how it feels to choose technology
and grow, grow, grow

until some problems start hitting
you.

This is exactly like usually people
choose RDS or CloudSQL or

Supabase, anything.

And they don't need to hire DBAs,
DBREs, and they grow quite

well until a few terabytes of data
or 10,000 TPS, that kind

of scale.

And then problems pop up here and
there, and sometimes they come

in batches, you know, like not
just 1 problem, but several.

And here usually, for us it's good,
they come to us.

I mean, Postgres.AI, we have still
consulting wing, quite strong

and growing.

And we helped more than 20 startups
over the last year, which

I'm very proud of.

And I collected a lot of case studies,
so to speak.

And I decided to have some classification
of problems that feel

not good at very high level, for
example, CTO level or even CEO,

when you think they might start
thinking, is Postgres the right

choice or it's giving us too much
headache?

And it's not about like, oh, out
of disk space suddenly, or major

upgrades requiring some maintenance
window.

Although this also can cause some
headache.

But it's more about problems like
where you don't know what to

do, or you see it requires a lot
of effort to solve it properly.

Michael: Yeah, I've had a sneak
peek of your list, so I like

how you've described it.

I also like the thought process
of whether it hits the CTO or

the CEO, and I was thinking, let's
say you have a non-technical

CEO, if they start hearing the
word Postgres too often it's probably

a bad sign.

Ideally you might mention it once
every few years when you do

a major version upgrade, but then
nothing bad happens and they

don't hear it again for a few years.

But if they're hearing, you know,
if it's getting to the CEO

that Postgres is causing problems
over and over again, the natural

question is going to be, is there
an alternative?

What could we do instead?

Or, you know, is this a big problem?

So I guess it's these kind of dangers,
not just to the startup,

but also to Postgres' continued
use at that startup.

Nikolay: Yeah, I like the word
dangerous here because when you

deal with some of these problems,
it might feel dangerous to

have Postgres for them.

It's bad.

Like I would like if things were
better.

So I have a list of 10 items And
we can discuss and the list

is unordered and I'm going to post
it to my social networks so

folks can discuss.

And I'm like sincerely think that
this list is useful.

If you're a startup, it's great
to just to use this checklist

to see how your cluster is doing
or clusters are doing and are

you ready.

So Postgres growth readiness checklist.

And interesting that I didn't include
vertical and horizontal

scaling there.

I did it indirectly, we will touch
it.

But obviously, like this is the
most discussed topic, the biggest

danger, how Postgres scales, like
cluster, single primary, and

multiple standbys, how far we can
go.

We know we can go very far, very,
very far on a single cluster.

At some point, microservices, or
maybe sharding, it's great.

But we had a great episode with
Lev Kokotov, a PgDog and it

resonates 1 of the items I have
today it resonates with what

he said during our episode.

So anyway, let's exclude vertical
and horizontal scaling and

talk about stuff which kind of
sounds boring.

My first item is heavy lock contention.

This is very popular.

Maybe 50% of companies that come
to us have this issue.

Somehow.

So at some point I decided to start
saying everyone, if you have

queue-like workloads, or additionally,
and or, If you don't know

how dangerous it is to change schema
in Postgres, just adding

column can be a problem, right?

We discussed it, I think, many
times.

You are not ready to grow, and
at some point, sooner or later,

it will hit you.

And it will hit you as a spike
of active sessions.

And we know some managed Postgres
platforms provoke you to have

huge number of huge max connections.

Michael: Max connections,

Nikolay: yeah.

RDS like 5, 000, 2, 500.

Why do they do this?

Easier for them.

But it's dangerous because it creates kind of performance cliff

additionally.

Michael: Yeah, it's another version of these cliffs isn't it?

Nikolay: We

Michael: had another good episode recently.

Nikolay: Yeah, I plan to research this a little bit, probably

we will publish something in this area to prove that it's not

good.

It's still not good even if you have Postgres 14 plus which has

great optimizations for a large number of idle connections it's

still not good.

Michael: And there have been some improvements like I know a

very good engineer who took Postgres down by adding a column

with a default, I think it was.

But it was many years, there's some improvements in recent years

of some DDL changes that are less dangerous than they were.

Nikolay: Yeah, there are several levels.

Michael: Yes, yeah, of course.

And if they get stuck in if they don't have a lock_timeout

for example in fact yeah we're probably going to be pointing

to episodes on every single 1 of these bullet points but we have

we had 1 on 0 downtime migrations I think it's probably the best

for that and we had a separate 1 on queues actually, didn't we?

Nikolay: So yeah.

Yeah.

So definitely there are solutions here and you just need to proactively

deploy.

It's interesting that I see some companies grow quite far not

noticing this problem, for example, with DDL.

It becomes, it's like going to casino, like you can win, you

can win.

Sometimes, boom, you lose.

Because if you deploy some DDL and you get blocked, you can block

others and it can be a disaster.

We discussed it several times.

And if you had a hundred deployments successfully, it doesn't

mean you will keep winning, right?

So it's better to have.

And it concerns me, I have a feeling we should implement this

in Postgres.

Like alter table concurrently or something like this.

It should itself perform these retries with low lock_timeout.

Michael: Yeah, it's tricky, isn't it?

But I agree, but then people still need to know that it existed

to actually use it because I think the main issue here is people

not realizing that it can be a problem.

And the fact it probably hits users.

Let's say you've got a statement_timeout.

When are you actually going to notice that users have been waiting

for it?

Are you going to notice that spike
on your on your monitoring?

I'm not sure like it depends how
many users actually got stuck

waiting behind it and had slow
queries.

So and it's going to be hard to
reproduce that you might not

know why it was that.

So

Nikolay: log_lock_waits is off
so you don't see who blocked

you.

And you might be auto vacuum running
in this aggressive mode

or it can be another session long
running transaction which holds

access share lock to a table and
you cannot alter it.

And boom, you block others.

So this is like a reaction chain.

And yeah, it's not good.

And queue-like workloads same, like
at some smaller scale, you don't

see problems at all.

Then you occasionally experience
them.

But if you grow very fast, you
will start hitting these problems

very badly.

And they look like spikes of heavy
lock contention or just heavy

lock and lock in Postgres terminology
is the same so just lock contention

and yeah it doesn't look good so
and suggestion is so simple

like I It's funny that we talk
a lot and people that come to

us actually they mention they watch
podcast and I say like, okay,

do you like workload?

Just take care of indexes, take
care of bloat, like maybe partitioning,

but most importantly, skip locked.

That's it.

This is a solution, but we spent
hours to discuss details.

Because when you go to reality,
it's not easy to learn this,

like there are objections sometimes,
but this is what we do,

like we work with those objections
and help to implement, right?

So yeah, but we, yeah, for everything
we had episode.

There are episodes for everything.

So this was number 1, heavy load
contention.

And I chose the most popular reasons.

Of course there are other reasons.

But in my view, DDL and queue-like
workloads, not to number biggest,

the biggest ones.

Okay, next it's boring, super boring.

Bloat control and index management.

We had episodes about it, maybe
several actually.

But Since again, managed Postgres
platforms don't give you tools.

For example, RDS, they did great
job in auto-vacuum tuning, but

only half of it.

They made it very aggressive in
terms of how much resources,

like throttling, they gave a lot
of resources.

But they don't adjust scale factors.

So it visits, autovacuum visits
your tables Not often, not often

enough for LTP.

So bloat can be accumulated and
so on, and they don't give you

resources to understand the reasons
of bloat.

I'm thinking about it and I think
it's tricky and it's also a

problem of Postgres documentation
because it lacks clarity how

we troubleshoot reasons of the
bloat because we always say long-running

transaction But not every transaction
is harmful.

For example, in default Transaction
Isolation level Read Committed,

transaction is not that harmful
if it consists of many small

queries.

If it's a single query, it holds
a snapshot, it's harmful.

So I guess with observability we
should shift from long-running

transactional language to xmin horizon
language fully and discuss

that.

Anyway, like I can easily imagine
and I observe how people think,

oh, like MongoDB doesn't have this
stuff.

Or some other Database system,
they don't have the problem with

bloat.

Or indexes, indexes, oh.

Actually with indexes, my true
belief is that degradation of

index health is happening in other
systems as well.

We also discussed it.

So they need to be rebuilt.

Michael: I was listening to a SQL
Server podcast just for fun

the other day and they had the
exact same problem.

But in the episode where we talked
about index maintenance, I

think it came up that even if you're
really on top of autovacuum,

even if you have it configured
really nicely, there can still

be occasions where you get some
bloat.

If you have like a spike or if
you have a large deletion or you

have like a there's a few cases
where you can end up with sparsely

populated indexes that can't self-heal
like if for example you've

got like an or like a even UUIDv7
index and then you have a section

that maybe deletes some old data
and it's not partitioned, then

you've got a gap in your index.

So there's a bunch of reasons why
they can get bloated anyway,

even if you're on top of autovacuum.

So I think this is 1 of those ones
that, yes, autovacuum fixes

most of the problems, but you probably
still want to have some

plan for index maintenance anyway.

Nikolay: Yeah, so there are certain
things that are not automated

by Postgres itself or by Kubernetes
operators or by, Well, some

of them automated some things,
but not everyone, not everything.

Or managed service providers, even
upgrades.

Also like lack of automation there.

We can mention this lack of automation
of analyze, but fortunately

future Postgres versions will be
definitely fine because dump,

restore of statistics is implemented
finally and goes to Postgres

18, which is super great news.

Anyway, lack of automation might
feel like, oh, this is a constant

headache, but it's solvable.

It's solvable.

Fortunately, it requires some effort,
but it's solvable.

Okay.

Next thing is, next thing is, let's
talk about lightweight lock

contention.

So we talked about heavy lock contention
or just lock contention.

Lightweight lock contention is
also, this feels like, like pain

and of various kinds.

So lightweight locks can be called
latches, it's in memory.

So when some operations with buffer
pool happen, for example,

there are the lightweight locks,
Postgres needs to establish or

working with WAL or various data
structures.

Also can mention LockManager.

So things like LWLock:LockManager
or buffer mapping

or sub-trans SLRU, multi-exec SLRU.

When you hear this, for me, like
these terms, imagine like this

font, like bloody, you know, like
red blood, blood drops, drops

of blood because, because III know
so like many projects like

suffered big pain, like big incidents.

So for me, these, these terms are
like bloody terms, you know,

because, because yeah, because
it's, it's, it was a lot of pain

sometimes.

For example, you know I'm a big
fan of sub-transactions, right?

Just my natural advice is just
to eliminate them all.

Well, over time, I'm softer.

I say, okay, you just need to understand
them and use very carefully.

But LockManager, couple of years,
remember Jeremy Schneider

posted like-

Michael: Yeah, great post.

Nikolay: Horror stories, and we
discussed it as well.

So this kind of contention might
hit you and it feels like performance

cliff usually so all good all good
boom

Michael: right it's it what is
or was is it changing in 18 but

it what it was a hard-coded limit,
right?

Nikolay: 2016, you mean for fast
path?

Also SLRU sizes are now configurable,
I think in 2017 already.

Well, nice, good, but not always
enough.

Because okay, you can buy some
time, but still there is a cliff

and if you're not far from it,
again, boom.

Or this, I recently saw it, like,
remember we discussed 4 million

transactions per second.

And we discussed that we first
we found pg_stat_kcache was an

issue, it was fixed, and then pg_stat_statements.

Okay, pg_stat_statements, if the
transactions are super fast,

it's bringing an observer effect.

And we see it in newer Postgres
versions as LWLock:pg_stat_statements

because finally code is covered
by proper props, right?

Not props, like is wrapped and
it's visible in the wait event

analysis observing just that activity.

So I saw it recently at 1 customer,
I know like some layer of

lightweight lock pages and statements,
So we need to discuss what's

happening.

It happens only when you have a
lot of very fast queries, but

it can be a problem as well.

But yeah, and performance cliffs,
it requires some practice to

understand where they are.

It's hard because you need to understand
what kind of, like how

to measure usage, how to understand
like situation risks.

This requires some practice.

Michael: I think this is 1 of the
hardest ones.

I think this is 1 of the hardest
ones to see coming.

Nikolay: After all our stories
with LW LockManager, every time

I see some query exceeds 1000 QPS,
queries per second, I'm already

thinking, okay, this patient is
developing some chronic disease

you know.

Michael: Okay that's another that's
1 I haven't heard we've done

several rules of thumb before but
that's that's another good

1 so a thousand queries per second
for a single query check.

Nikolay: It's very relative also
how many vCPUs we have.

If we have less, it can hit faster.

Although we couldn't reproduce
exactly the same nature as we

see on huge machines like 196 cores,
we couldn't reproduce that

nature on 8 core machines at all.

So yeah, it's for big boys only,
you know.

This is, yeah, this is, like, or
maybe for adults.

So young projects don't experience
these problems.

Michael: That's a good point actually
the startups that have

hit this that you've written about
and things have tended to

be further along in their journeys
huge huge but yeah but still

growing quickly and it's even a
bigger problem at that point

but yeah good point should we move
on?

Nikolay: Yeah so the next 1 is
our usual suspect right it's a

wraparound of 8 byte transaction
ID and multi-exact ID.

So many words already said about
this.

It just bothers me that monitoring
doesn't cover, for example,

usually doesn't cover multi-exact
IDs.

And people still don't have alerts
and so on.

So it's sad.

Yeah, it's easy to create these
days.

Michael: I get the impression though,
I mean, there were a few

high profile incidents that got
blocked about.

I think, yes, yeah, exactly.

And I feel like I haven't seen
1 in a long while and I know there

are a lot of projects that are
having to you know, I think Agen

have spoken about If they weren't
on top of this it would be

it would only be a matter of hours
before they'd hit wrap around,

you know it's that kind of volume.

So they're really really having
to monitor and stay on top of

it all the time.

But I haven't heard of anybody
actually hitting this for quite

a while.

Do you think, I wondered if for
example there were some changes

to I think it was autovacuum being
like I think it kicks in

to do an anti-wraparound vacuum
differently or it might be lighter

a lighter type of vacuum that it
runs now I think I remember

Peter Geoghegan posting about it, something
like that.

Do you remember a change in

Nikolay: that area?

I don't remember, honestly.

I just know this is still a problem.

Again, at CTO level, it feels like,
how come Postgres still has

4 byte transaction IDs and what
kind of risks I need to take

into account.

But you are right, managed Postgres
providers do quite a good

job here.

They take care.

I had a guest at Postgres TV, Hannu
Krosing, who talked about

how to escape from it in a non-traditional
and in his opinion,

and actually my opinion as well,
in a better way, without single

user mode.

And since he is a part of CloudSQL
team, so it also shows how

much of effort managed Postgres
providers do in this area, realizing

this is a huge risk.

Michael: Yeah, and it's not even,
even if it's a small risk,

the impact when it happens is not
small.

So it's 1 of those ones where-
Absolutely

Nikolay: good correction.

It's low risk, high impact, exactly.

Michael: Yes, yes.

So I think the cases that were
blogged about were hours and hours

possibly even getting, was it even
a day or 2 of downtime for

those organizations?

And that was, that is then, I mean,
you're talking about dangers,

right?

Nikolay: That's- Global downtime,
whole Database is down.

Michael: Exactly.

People, you're gonna lose some
customers over that, right?

Nikolay: Yeah, unlike the next
item, a 4-byte integer primary

key is still a thing, you know.

I was surprised to have recently
this case, which was overlooked

by our tooling.

Michael: Oh, really?

Nikolay: I couldn't like, how come?

Yeah, because it was a non-traditional
way to have this.

Michael: Go on.

Nikolay: Well, it was first of
all, the sequence, which was used

by multiple tables.

Yeah, 1 for all of them.

And somehow it was defined, so
our report in postgres-checkup didn't

see it so when it came I was like
how come this like this is

old friend or old enemy, not friend,
enemy.

Michael: Old enemy.

Nikolay: I haven't seen you for
like so many years.

And you look differently, you know,
because multiple tables,

but still, like, it's not fun.

And this causes partial downtime
because some part of workload

cannot work anymore.

You cannot INSERT.

Michael: Yeah.

Nikolay: Yeah.

So, by the way, I also learned
that if you just do in place ALTER

TABLE for huge table, it not so
dumb as I thought.

I checked source code, I was impressed.

And this code is from 9 point something,
maybe even before.

So if you ALTER TABLE, ALTER COLUMN
to change from int4 to int8,

it actually performs a job like
similar to VACUUM FULL.

Recreating indexes.

And you don't have bloat.

I expected like 50% bloat, you
know.

Michael: Oh, why?

Nikolay: Because I thought it will
be, it will rewrite the whole

table.

I was mistaken.

It's quite smart.

Yeah, it's of course it's a blocking
operation, it causes downtime

to perform it, but you end up having
quite clean state of table

and indexes.

Not quite, clean state, it's fresh.

Michael: Yeah, so that is a table
rewrite, no?

Nikolay: Yes, well, table, yes,
well, you are right.

I was thinking about table rewrite
as a very dumb thing, like

create more tuples and DELETE other
tuples.

Michael: Got it, got it, got it.

Nikolay: But there is a mechanism
of table rewrite in the code.

Now I saw it finally, I'm still
learning, you know, sorry.

Michael: You might end up with
some padding issues if you had

it optimized well before, but yeah.

Nikolay: Yeah, it also feels like
Postgres could implement some

reshape like eventually because
there are building blocks in

the code already, I see them like
to first like offline style

to change COLUMN order and then
if you want it and then fully

online style if pg_squeeze goes
to core to core right yeah yeah

it would be great yeah I'm just
like connecting paths here and

can be very powerful in like 3
to 5 years maybe But it's a lot

of work additionally.

So all those who are involved in
moving huge building blocks,

I have huge respect.

So okay.

And I

Michael: think this is, if you
know what you're doing, this one's

easier to recover from.

I assume like with the sequence,
for example, you can handle

it multiple ways, but you can set
the sequence to like negative

2 billion and normally you've got
a good start.

Nikolay: Everyone thinks they're
smart and and this is first

thing I this is the first thing
I hear always when we discuss

this.

This was in the past, like let's
use negative values.

Of course, if you can use negative
values, do it.

Because we know Postgres integers
are signed integers.

So we use only half of capacity
of, 4 byte capacity, half of

it is 2.1 billion, roughly.

So you have 2.1 billion more, but
not always it's possible to

use.

But this is old, old, old story
still making some people nervous

and I think it's good to check
in advance.

Michael: So much better, so much
better to have alerts when you're

getting...

Nikolay: I sell several companies,
big ones, from this, just

raising this.

And I know in some companies, it
was like 1 year or a few years

work to fix it.

Michael: So what was the problem
before?

Was it looking at columns instead
of looking at sequences?

Or what was the-

Nikolay: No, sequences are always
8 bytes.

It was always so, like, if they
are 8 bytes.

Problem with report, I don't remember
honestly.

There was some problem with report.

It was not standard way, not just
create table and you have default

with sequence and you see it.

Something else, some function,
I don't remember exactly.

Okay.

But usually our report catches
such things.

Or you can just check yourself
if you have primary keys for byte,

it's time to move to 8 bytes or
to UUID version 7, right?

Maybe.

Okay, that's it about this.

Then let's heat up the situation,
replication limits.

So in the beginning I mentioned
vertical and horizontal scaling

and usually people say there's
not enough CPU or something and

disk I.O.

And we need to scale and you can
scale read only workloads having

more read only standbys.

But it's hard to scale writes,
and this is true.

But also true that at some point,
and Lev mentioned it, In our

PgDog episode, he mentioned that
in Instacart they had it, right?

So like at 200 megabytes per second
WAL generation, it's already

over 100.

I don't remember exactly what he
mentioned, but somewhere in

that area.

Michael: It was a lot.

Nikolay: Well, just 10 WALs per
second, for example, it gives

you 160.

Well, RDS sets less because they
have 64 megabyte WALs.

They raised it 4 times.

Anyway, 100 or 200, 300 megabytes
per second, you start hitting

the problems with single-threaded
processes.

Actually, it wonders me why we
don't monitor, like any Postgres

monitoring should have, with low
level access.

RDS doesn't have it, but low-level
access.

We should see how much CPU, we
should see CPU usage for every

important single-threaded Postgres
process.

Right?

WAL sender, WAL receiver, Logical
Replication Worker, Checkpointer

maybe as well.

That would

Michael: be helpful.

Nikolay: Of course, because it's
dangerous to grow at scale when

you hit 100% of a single vCPU.

And then you need to either vertically
or horizontally scale

or start saving on WAL generation.

Fortunately, in pg_stat_statements,
we have WAL metrics, 3

columns since Postgres 13.

But unfortunately, this is query
level.

What we need, we need also table
level to understand which tables

are responsible for a lot of WAL.

And pg_stat_activity lacks it.

I think it's a good idea to implement.

If someone wants hacking, this
is a great idea.

Add 3 more columns, WAL-based,
WAL-related metrics to pg_stat_all_tables,

pg_stat_sys_tables, and
user tables.

It would be great.

Also, maybe pg_stat_databases, like
a global view of things, how

much WAL.

Michael: Yeah, I've got a vague
memory there were a couple of

WAL-related new views, new system
views

Nikolay: introduced.

Oh yes, but it's about, Yeah, it's
about...

Are you talking about the pg_stat_io?

No.

WAL-related in Postgres 13, it
went to EXPLAIN and it went to

pg_stat_statements.

This is what happened.

Anyway, this is not an easy problem
to solve.

It's easy to check if you have
access.

Unfortunately, if you're on managed
Postgres, you don't have

access.

They need to check it.

What's happening, especially on
standbys.

And also, it makes sense to tune
compression properly because

compression can eat your CPU.

Remember we discussed WAL compression.

I always said, let's turn it on.

Now I think let's turn it on unless
you have this problem.

In this case, WAL sender.

Yeah, you need to check how much
of that is WAL compression.

And also we have new compression
algorithms implemented in fresh

Postgres versions.

So, yeah.

And that is CD.

So, so, so we can choose better
1.

For example, as it 4 should be
providing similar as remember

some I saw some benchmarks.

I didn't do it myself yet.

So it should be similar to to be
gels at default compression

in terms of compression ratio,
but it takes much less CPU, like

2 to 3 times less CPU.

So it's worth choosing that.

Michael: I looked at it briefly
just for our own use for planned

storage.

And it was, we even got better
performance as well.

So It won on both for us.

Nikolay: Well, you probably saw
what I saw, Small Datum blog,

right?

No?

OK, maybe not.

Let's move on, because there are
more things to discuss.

Michael: Nice.

Nikolay: Design limits.

So some people already think what
they will do when their table

reaches 32 terabytes.

Michael: Yeah, I guess this and
the last 1 both feel like adult

problems again, right?

Like, There aren't too many small
startups hitting the Instacart

level of WAL generation or 32
terabytes of data.

Nikolay: So yeah, it's a really
big clusters.

But we can start thinking about
them earlier and be better prepared.

Maybe sometimes not spending too
much time, because you know

like if you spend too much time
thinking about how you will bring

statistics to new cluster after
measure of red, but it's already

implemented in 18, so just a couple
of years more and everyone

forgets about this problem.

Michael: It's the kind of thing
that's useful to have like a,

if you've got like a to-do list
you're going to be using in a

couple of years time or a calendar
that you know you're going

to get an alert for, Just put a
reminder in for like a year or

two's time, just check.

Nikolay: We also mentioned a few
times during last episodes,

latest episodes, this bothers me
a lot.

First I learned CloudSQL has this
limit, then I learned RDS

also has this limit.

64 terabytes per whole database
what's happening here it's already

not huge database

Michael: but again in a couple
of years who knows they might

have increased that

Nikolay: yeah well I think it's
solvable of course I guess it's

this is limit of single EBS volume
or disk on Google Cloud, PD,

SSD, or how they call it.

So yeah, it's solvable, I think,
right?

With some tricks.

But these days, it doesn't feel
huge.

64, it feels big database, right?

But when we say

Michael: to most of us,

Nikolay: but we have stories, 100
plus databases, all of them

are are self managed.

I think everyone has

Michael: all of them were sharded.

All of those were sharded.

Nikolay: Yeah, 100%, yeah, we had
a great episode, 100 terabytes,

where it was Adyen, who else?

Michael: We had Notion and we had
Figma.

Nikolay: Figma, right.

And Notion and Figma, they are
on RDS, but it's a shard, a single

cluster, it's impossible on RDS,
right?

And I think Adyen has 100 plus terabytes.

No, they have 100 plus terabytes,
but it's self-managed.

Yes.

Because on RDS it's impossible,
not supported.

Michael: Well, and they shard it
also.

Nikolay: Yeah, yeah, yeah.

But yeah, large companies like
that, they're always like some

parts are sharded, some are not.

So anyway, when you have 10 to
20 terabytes, it's time to think

if you are on RDS or CloudSQL,
is it like how you will grow

5X?

Because if you're 20 terabytes
to grow 5X, 100, it's already

not possible with a single cluster,
right?

Another reason to think about splitting
somehow.

So, okay.

Then a few more items, like, so
data loss.

Data loss is a big deal.

If you poorly designed backups
or HA solutions, Yeah, it can

be.

Let's join this with poor HA choice
leading to failures like

split brains.

So data loss, split brain.

Actually I thought we had a discussion.

There is ongoing discussion in
the project called CloudNativePG

where I raised the topic of
split-brain and demonstrated

how to do it a couple of weeks
ago.

And good news as I see, they decided
to implement something to

move in a direction similar to
Patroni because when network partition

happens and the primary is basically
alone, it's bad because

it remains active.

And as I demonstrated, some parts
of application might still

talk into it.

This is classical split-brain.

And I saw, based on discussion,
I saw it triggered, I never thought

deeply, actually.

But is split-brain just a variant
of data loss?

Michael: Well, I guess you technically
might not have lost the

data.

It's still there, you just have
2 versions.

Which 1

Nikolay: is correct?

Michael: It's worse

Nikolay: than data loss.

It's worse than data loss.

It's worse.

Because now you have 2 versions
of reality.

And it's bad.

With data loss, you can apologize
and ask to like, Bring your

data back again, please.

And in some cases we allowed some
data loss.

Of course, data loss is really
a sad thing to have.

But sometimes we have it like...

Officially, some data loss might
happen.

The risk is very low.

And maximum this number of bytes,
for example, but it might happen.

With split-brain it's worse.

You need a lot, spend a lot of
effort to merge realities into

1.

Michael: Most cases of data loss
I've seen have tended to be

at least 2 things gone wrong like
user error or some some way

that the nodes gone down but quite
often it's user error accidental

delete without a where clause or
dropping a table in 1 environment.

Nikolay: Well, this is like higher
level data loss.

Michael: Well, but that, but that
can cause the low level data

loss if you then also don't have
tested backups and it turns

out you didn't have a good, so
it's the combination of the 2

things often.

Nikolay: For me it's still, yeah,
well, yes, yeah, if backups

are missing, it's bad, yeah, you
cannot recover, but also like

data loss for me, classical, like
lower level, it's database

said, commit successful, and then
my data has gone.

Michael: So yeah.

So that's scary and dangerous as
like a CTO.

Nikolay: Undermines trust into
Postgres again, right?

Yeah, yeah.

If procedures are leading to data
loss and also split brains.

Michael: Is it actually happening
often or is it more the CTO

thinking, I don't want to choose
this technology because it could

happen?

Nikolay: Depends on the project
and the level of control.

I'm pretty confident in many, many,
many web and mobile app,

OLTP style projects, if they are,
unless they are like financial

or something like social network,
social media, like maybe even

e-commerce and so on, data loss
happens sometimes unnoticed.

Yeah.

If you have asynchronous replicas
and failover happened and the

process of failover, like Patroni
by default with asynchronous

replicas allows up to 1 megabyte,
it's written in config, Up

to 1 megabyte of data loss, officially.

Maybe byte.

Right, so 1 megabyte is possible
to lose.

And who will complain if it's a
social media comments or something,

we store like comments.

We lost some comments, nobody noticed
maybe.

But if it's a serious project,
it's better not to do it.

Or split brain.

Yeah, anyway, this is more not...

Postgres doesn't drop quite well
here.

It's a question mostly to everything
around Postgres infrastructure

and if it's managed Postgres to
their infrastructure, how they

guarantee there is no data loss.

And last time we discussed the
problem with synchronous_commit

and we discussed in detail how
right now Postgres doesn't do

a good job of not revealing proper
LSNs on standbys, right?

So even Patroni can have a data
loss in case of synchronous_commit

with remote write.

We discussed it.

Okay, anyway, this feels like something
for improvement definitely

here.

Good corruption.

My general feeling, people don't
realize how many types of corruption

might happen.

And it remains unnoticed in so
many cases.

When you start talking, people's
reaction sometimes is, wait,

what?

So yeah, corruption at various
levels.

Michael: So actually, maybe this,
so in terms of the list where

we started, kind of the point of
the topic, there was kind of

dangers, right?

Is this 1 of those ones that if
it silently happened for a while,

it suddenly becomes a complete
loss of trust of the underlying

system or?

Nikolay: Yeah, as usual, I can
rattle about a little about how

Postgres defaults are outdated.

We know data checksums only recently
enabled by default.

Yeah, great change.

Like a month or 2 ago, right?

It will be only in 18.

It should be done 5 years ago.

We saw

Michael: evidence.

Some,

Nikolay: yeah.

Yeah, many managed Postgres providers
did it, like RDS.

That's great.

Michael: Which is great.

And that kind of is then the default
for a lot of people.

Nikolay: Yeah, but it also doesn't
guarantee that you don't have

corruption.

You need to read all the pages
from time to time, right?

And do they offer something, some
anti-corruption tooling?

They don't, nobody.

Okay, they enabled, so what?

This is just a small piece of the
whole puzzle.

Michael: Yeah, and amcheck improving
as well.

I think, is it in 18?

Nikolay: Yeah, that's great.

Michael: In indexes as well.

Nikolay: Yeah, big.

Michael: In fact, I think we, did
we have an episode on amcheck?

I feel like it came up at least
once or twice, maybe index maintenance.

Nikolay: I cannot remember all
our episodes, so many.

Michael: Yeah, me neither.

Nikolay: But it's great to see
the progress on the area of amcheck,

but again, it's a small piece of
the puzzle and it has settings,

like options to choose from.

So it's not trivial to choose among
options.

And also again, like a couple of
episodes ago, we discussed support

and how difficult it is to sometimes
understand what's happening.

Right.

Yeah.

And I, I right now have a case
where very big platform doesn't

help to investigate corruption.

And nobody has like ability to
investigate it but them, and but

they are not helping.

So it was 1 of reasons I was provoked
to talk about that in that

episode.

So it's bad.

It looks really bad.

Like, you guys are responsible
for that corruption case, and

you don't do a great job.

And I think it's a problem of industry,
and we discussed it already,

so let's not repeat.

But in general, in general, I think
if you are CTO or like leader

who decides priorities, my big advice is to take this list and

check this, like evaluate situation for yourself.

Better let us do it, of course, but you can do it yourself.

And then plan some proactive measures because corruption testing

can be done even on RDS proactively.

If it happens, you need support, of course, because sometimes

it's like it's low level, you don't have access, but at least

you will feel control over it, right?

So, over corruption.

So, anti-corruption tooling is needed.

This is what I feel.

That's it.

That's all my list.

I'm sure it's lacking something like security, more security

related stuff for example as usual I tend to like it what do

you think like was it was it good

Michael: yeah you see a lot more of these things than I do obviously

but yeah I think it's a really good list and check with checklists

right it's not of course if you could go on forever with with

things to be scared of but this this feels like if you ticked

all of these off you'd be in such a good position versus most

and obviously things can still go wrong but these are some of

the most at least even if they're not the most common some of

the things that could cause the biggest issues the things that

are most likely to get on the the CEO's desk or in the inbox

so yeah this this feels like if you're on top of all of these

things, you're going to go a long, long way before you hit issues.

Nikolay: I have a question for you.

Guess among these 10 items, which item I never had in my production

life?

Michael: Oh, maybe...

Wait, give me a second.

Nikolay: It's tricky, right?

Michael: Transaction ID wraparound?

Nikolay: Exactly, how, yeah, I never had it.

I only-

Michael: It's rare.

Nikolay: Well, it's rare, yeah.

Let's cross it off, no problem.

Yeah, I only found a way to emulate it, right, which we did multiple

times, but never had it in reality in production.

So yeah, everything else I had.

Yeah.

Not once.

Michael: I nearly guessed too quickly that it was going to be

Split Brain, but then I was like, no wait, read the whole list.

But I'm guessing you had Split Brains like a couple of times,

maybe maximum?

Nikolay: Yeah, Replication Manager, Split Brain as a service,

yes.

Michael: Okay, yeah, pre-Patroni days.

Yeah, makes sense.

All right, nice 1.

Thanks so much, Nikolay.

Nikolay: Thank you.