Michael: Hello and welcome to Postgres
FM, a weekly show about

all things PostgreSQL.

I am Michael, founder of pgMustard.

This is my co-host, Nikolay, founder
of Postgres.AI.

Hello, Nikolay.

What are we talking about this
week?

Nikolay: Hi, Michael.

We are out of disk space.

What to do?

Michael: Panic.

Nikolay: This is what Postgres does, right?

Michael: Yeah, in some cases, right?

Nikolay: Yeah, well, I saw it a
lot.

I recently saw it again, actually.

And yeah, it's not fun.

Why panic?

Don't panic, right?

Michael: Keep calm and carry on,
right?

Nikolay: Yeah, it's Postgres, you
should, yeah.

Just use Postgres, you know.

Yeah, so as we discussed before
this call, before recording,

Let's just talk about possible
reasons, mitigation and avoidance,

right?

Yeah.

Prevention.

Prevention is better.

Avoidance.

Michael: Avoidance.

Denial.

It's like the stages of grief.

Nikolay: It's not my problem, actually.

Yeah, I'm a DBA.

It's an SRE problem, right?

They should just give us more space.

To start about problems, I mentioned
it before the call, but

let's repeat.

The only reason, The only true
reason of this is an insufficient

disk space allocation.

It's always so.

And it's a joke, but we will be
returning to this joke, I'm quite

sure.

Because sometimes you do need more
space, that's it.

Of course, I would deprioritize
this path and consider everything

else.

But sometimes we end up with this
reason and mitigation action,

like just add this space.

But let's consider this as a last
resort, right?

Michael: Well, yeah, I like your
structure.

So, causes, then recovery, and
then mitigation or prevention.

Mitigation is recovery.

Yeah, okay, great.

Prevention being the last 1.

So, causes.

What are the most common reasons
you see this happening to people

in the wild?

Nikolay: Good question.

There are many reasons.

I don't have statistics.

Michael: I can tell you the 1 I
see most blog posts about, because

I don't see this that often, but
the 1 that comes up time and

time again in blog posts is an
open replication slot.

Nikolay: Oh, it's too narrow.

Let's just say a lot of WAL written
and it remains, a lot of

WAL files remain in pg_wal directory.

Or pg_xlog if we talk about ancient
versions of Postgres.

Michael: Yes, but that's the root
cause of why it's the most

common 1 I've seen.

Nikolay: I think it's a super popular
reason, but I would structure

this like if we need to provide
a comprehensive list of reasons,

I would put it to the second place
and for the first place I

would put...

And not only because of slots,
slots is just 1 subsection here.

Whole section is just...

PG_WAL directory takes a lot of
disk space.

This is the big class of reasons,
right?

But the very first class of reasons,
I would say, anything related

to the data directory itself.

Like a lot of disk space consumed
by tables and indexes, right?

It's also like a whole class, so
we have specific reasons inside

it.

Michael: Yeah, so we actually have
more data than the size of

Nikolay: the dictionary.

Than we expected.

We tried to delete, but the space
is not free, and so on.

Michael: Lots of bloat, for example,
that we talked about.

Nikolay: Data-related, all related,
and the third class, probably,

or category is probably anything
else.

Anything else is tricky and interesting,
we will talk about it

soon, right?

So how about this

Michael: classification?

Yeah, just to give people an idea
of anything else, like you

including logs in that, like things
that can grow quickly that

aren't data, like main data directory
stuff or WAL.

Nikolay: Base sub-directory is
where data is stored.

So if we think about PGDATA directory
structure, we can think

about base, so tables and indexes,
right?

Materialized views as well.

And then PG_WAL sub-directory,
and it is huge, for example.

And everything else.

Everything else is interesting
as well.

Maybe also outside of PGDATA, but
let's not reveal secrets before

it's time.

So what about tables and indexes?

What do you think?

Like, possible specific reasons?

I deleted 90% of my table, but
it didn't help.

This consumption is still 99% what
to do.

Time to panic, right?

So like obviously delete doesn't
delete data, right?

It just marks it deleted, but it's
a two-stage process.

Michael: Well, and this is a tricky
1 because a lot of the times

we've discussed how to deal with
issues like this, we actually

require quite a lot of disk space
in order to repack or to actually

mitigate that problem.

The easiest way, the way to do
that in an online fashion, requires

double the size of the relation
or double the table size normally.

So it is a tricky 1 in this case.

Nikolay: Yeah.

Exactly.

Just a few days ago, I was discussing
this exactly problem with

1 of our customers.

And they mentioned that they have
a huge table which consists

of 90% of their database.

And to fight with bloat, they know
there is bloat.

And to fight with bloat, especially
in heap, it's obviously like

you need to use pgRepack, and to
use it, you need the same size.

Like, temporarily, you need to
have two large tables of the same

size, because it rebuilds the whole
table in the background and

then switches to it.

So yeah, this is a problem, and
if you don't have enough disk

space, oops.

But yeah, so I would say this is
edge case.

Normally we, there's no single
large table, which is like 90%

of whole database, right?

So normally it's not a big problem.

And if we keep free disk space
30, 40%, which should be so like

at least 25%, maybe, we have this
space and we know that it's

just temporary.

We like just when we're repacking
the heap, the table, right?

Not a problem usually.

But if you're in this situation,
well, not good.

Maybe you should do something else.

There are alternatives to PgRepack.

I think PgSqueeze alternative from
Cybertech also requires, also

has this issue.

It requires additional disk space
because it rebuilds the table.

But older solutions, like in this
particular edge case, I would

think I would return to solutions
which exist for quite some

time, several implementations of
not let's rebuild table, but

another idea.

It's called PG compact table.

Again, there are 3 probably attempts
from different persons to

implement this idea and all of
them I think are in Perl.

But the idea is let's issue some
updates which don't change data.

When you issue an update even saying
update blah blah set id

equals id where id equals some
number, you know a new tuple is

created.

Always, right?

So this tool is issuing these updates
in a smart way.

It checks which tuples are in the
end of table and it knows there

is bloat, so there is empty space
in first pages, for example.

So if we update tuples which sit
at the end of table, they will

move to the beginning of the table.

And once there are no tuples left
in the final page, final block,

it will be truncated.

By default, this is what vacuum
does, unless it's turned off.

And then we do the same with next,
from the end, the next page,

and so on and so on.

Like basically, this is similar
to like, I remember in Windows,

like defragmentation, right?

So it's moving data to different
places.

Michael: Remember that defrag screen
on Windows?

Nikolay: Yeah, yeah.

People love that.

People visualize it.

Yeah, yeah, yeah.

Maybe like red and blue, right?

So, yeah.

And this is much slower process
than using pgrepack for table.

But...

Michael: Also, in our case, if
we notice we've got 99% disk full,

maybe this kind of thing helps.

But if we're actually out of disk
space, even this isn't

Nikolay: an option.

Yeah, yeah, yeah.

I'm talking about mitigation here,
maybe too early, but of course,

if we already achieved 100%, we
cannot move, it's not what I

would do.

But you just raised a very good
point about extra disk space

needed, and I just remember there
are alternative solutions and

I think they don't require any
extensions so they could be used

anywhere.

True, true.

Like RDS, Cloud SQL, anything.

It's just simple updates.

Super slow, but not requiring extra
resources, which is great.

Also probably you will need to
make sure the tool is working

well with current version of Postgres
because they are quite

old, and again, there are 3 of
them.

I think Depesz also tried to implement
one of them.

Michael: That makes sense that
it's in Perl then.

Nikolay: This is a small zoo of
ancient tools.

And I like the idea, actually.

But it's not an emergency tool.

It's right before an emergency tool.

Trying to avoid it.

So yeah, this, right?

But as for indexes, probably yes,
when we rebuild an index concurrently,

like Postgres builds another index.

But I think this should not be
super noticeable because no index

can...

Yes, it requires extra disk space,
but we should have it if we

do it, right?

So anyway, if we delete, we should
think about, okay, those tuples

and bloat.

Keep it in mind.

Try to avoid massive deletes or
design them properly with MVCC

and vacuum behavior in mind.

Michael: We did a whole episode
on that, I think.

Nikolay: Right, exactly.

So yeah, I saw crazy cases when
some tables and indexes were

bloated like 99%.

Michael: Yeah.

And you saw these cause out of
disk issues.

Nikolay: Well, they contributed
a lot.

And if we keep it as is, we will
soon be out of disk space.

So it's definitely worth keeping
an eye on the situation in this

area, bloat and vacuum behavior.

And yeah, if you don't do it and
do it very infrequently, then

you once in 5 years you take care
of it and then you realize

out of 10 terabytes of disk space
you had, now it's below 1 terabyte.

And what do you do with the other 9
terabytes?

You keep paying the cloud provider,
right?

Because it's easy to add disk space,
but it's not easy to reduce

disk space because they don't have
such a function and it's possible

only if you create a new standby
cluster with a smaller disk and

then just switch to it, switch
over, right?

Michael: Yeah, But yeah, it's quite
funny seeing that message,

you know, this is a one way ticket.

Nikolay: One way ticket, yeah.

Michael: So I think those make
sense, right?

Normal data exceeding the size
of the disk, whether that's actual

data or whether that's bloat, makes
perfect sense.

Do you want to talk any more about-

Nikolay: Of course.

Actually, I still believe since
VLDB in Los Angeles, I think

in 2018, I still believe in the
keynote there.

I like this keynote about data
deletion is a huge, big problem

we engineers need to solve and
find better solutions.

So sometimes it's not about bloat,
it's about a lot of garbage,

duplicated data also.

And I saw many times when we start
paying attention to bloat

and pushing customers to fight
with it, suddenly they say, you

know what?

I had it like maybe a month ago.

You know what?

We don't need this table.

Let's just not spend time and just
drop it.

Drop it.

Drop this table.

Or truncate it.

We don't need the data.

And it's like, wow, some terabytes
are free.

So...

Michael: Yeah.

I see this.

I see this with like analytics
data, for example, people tracking

everything.

Like they don't know, they never
look at their analytics and

never look at who's using which
feature, but they track everything

because we might need it someday.

Or, you know, like that, there's
all these data use cases and,

And even the solution to this,
like, in terms of...

I've heard the phrase retention
policy.

I know it's not quite the same
topic, but talking about how long

we should retain data for, even
that we don't even mention deletion.

It's not deletion policy, It's
retention policy, which is quite

funny.

Nikolay: Also, maybe 1 of the last
things in this section I would

mention is it's becoming more popular
instead of deleting old

data to have some better approach
tiering, tiers of data storage.

And I know there is from Tembo,
there is extension called PG

tier.

First of all, timescale has this,
right?

But in cloud only, I think it's
not open source.

If I'm not right, if I'm not wrong.

And so idea is, let's consider
some data archived and it still

looks like it's present in Postgres,
but it's not stored on an

expensive disk which also has capacity
limits.

We offload it to object storage,
like S3 or AWS.

And this is a great idea, actually,
but I think some latency

issues will appear, and also maybe
some errors sometimes, inconsistencies

or so.

Like, it's interesting.

But it's a great idea.

And again, like I said, Tembo has
a PG tier extension, which

I think I hope to test someday
when I, or my team has an opportunity

and time for it.

It's super interesting to check
how it works.

And I think it's a great idea.

Like, if you have some huge tables,
maybe already partitioned,

you can go either with sharding,
or if some historical data is

not needed all the time, you can
just offload it to object storage,

which is like virtually infinite.

And then, okay, users' experience
some worse latency when reading

this old data.

Imagine e-commerce,

Michael: for example.

Just to quickly, I feel like this
is an old, kind of an old-fashioned

solution to this was table spaces.

Nikolay: Yeah, but table spaces
in cloud reality became not popular

at all.

Last time I used table spaces in
a serious project was more than

10 years ago, I think.

Michael: Yeah, but that's what
I mean.

We had this before, it's just it
had a different name.

Nikolay: Maybe, yeah.

Table spaces, yeah, you can have
cheaper disk attached to your

machine.

But I still don't like this particular
approach because I like

offloading to object storage more
than that.

Because imagine you have primary
and multiple standby nodes.

If you use table spaces and cheaper
disks, you are forced to

use the same structure of disks
on each node and it becomes still

expensive and slower, for example,
right?

While object storage, it's worse
in terms of uptime compared

to EBS volumes on AWS, for example.

But it's much better in terms of
reliability or vice versa.

Michael: Durability or other things.

Nikolay: If you check, yeah, availability
and durability, if

you check SLAs, S3s, I forget.

So if you check characteristics,
you think, okay, this is what

I actually would like to have for
archived data.

Maybe latency will be slower, worse,
right?

But if you think about e-commerce
and order history, for example,

if you're off a large e-commerce
website, if you have the ability

to offload all the activities and
users touch them very rarely,

but it's still good to have the
ability to see the old order

history.

But it's not needed often, so we
can keep it outside of Postgres

and evict from caches and so on.

And don't pay for our main cluster,
we just pay for S3.

There we also can have tiering,
right?

Usually.

And even automated.

Usually providers have these features,
like if it's older than

like 1 month, it goes to some colder
and cheaper space.

So I think this direction will
receive some more popularity and

will be better developed and so
on.

Turing for storage.

Michael: Once again, we've been
sucked into kind of like prevention.

Nikolay: I think yes, it's hard
to talk about reasons and then

yeah, like this is a big flaw of
the structure I proposed.

Michael: Well, no, I like it still,
but it feels like we haven't

really touched on...

Nikolay: The most popular, let's
talk about.

Yeah.

WAL, right?

We have some...

So, PGWAL is huge.

We identified it, which is actually
already some good skill.

Not everyone can do that, but if
you manage to identify that

your PGWAL data is huge, there
are a few certain reasons.

And there is a good article from
CyberTech about this.

Why Postgres doesn't delete all
files?

It's already time to exclude them.

Michael: Ah, yeah, the various
reasons.

Nikolay: This is exactly when we
need to apply this article as

the list of possible reasons and
just exclude 1 of them until

we find our case, our real reason.

So 1 of them you mentioned, some
replication slot, logical or

physical, not progressing and accumulating
a lot of changes.

It's not like accumulating, it's
not like slot.

Postgres doesn't write to slots.

It writes to PGWAL, a lot of WALs,
right?

But slot has position, and if it's
inactive or position is frozen,

consumers don't consume and don't
shift this position.

It means Postgres must keep those
WALs in the PGWAL directory

until it finishes.

And since recently, a few years
ago, 1 of, I think, Postgres, maybe

13 or 14, received a setting to
limit this, to have threshold

when we give up and say, let's
better to kill our slot, to destroy

our slot, but let's stop this situation
and WALs should be deleted

already.

And I remember this big fear in
a good engineer I worked with,

he was not Postgres, he was more
SRE, but with a lot of Postgres

experience.

And when, it was long ago, when
initially replication slots for

physical replication, we were introduced,
we had basically a

small fight.

Like I said, let's use it, a great
feature.

He said, no, no, no, I'm not going
to use it.

He said, this is super dangerous.

And he understood that the danger
is if some replica is somehow

stuck, the slot is not progressing,
the primary is out of disk

space, and this is the last thing
he wanted.

After lost backups, of course.

But over time, we started using
slots, but then this setting,

I think it's a great setting, you
can understand your disk layout

and how much free disk space you
can afford.

If you approach 90%, it's time
to kill slots.

So you can do some math and understand
that the maximum number

of gigabytes you can allow for
a lag is this.

Of course, over time, the database
grows and still this...

It still can be a problem, right?

Because if data grows, Tables and
indexes grow, and probably

your setting will not save you
from emergency, right?

Maybe.

But this is definitely 1 of the
reasons.

What else?

What reasons?

Because of failing archive command,
right?

Because for Postgres it's important
if the archive command is

configured, for Postgres it's important
to archive.

And if it's failing, any reason
can be.

Postgres cannot archive, so if
it cannot archive, it cannot remove

these walls.

And that's a problem.

So you should monitor archiving
command lag separately.

And if it's growing, it's very
bad for backups already.

But it's also bad because it can
hit you in terms of disk space.

So yeah, these processes, replication
and archiving of walls

are 2 processes that if something
is wrong with them, Postgres

cannot remove walls.

Another reason actually...

Michael: And by the way, this can
grow quickly.

I think people don't always realize
how quickly this can grow.

Yeah.

Some database.

Nikolay: Yeah, it can be small,
but very active database and

you can have terabytes of wall
generated per days.

It's actually good to know how
much you generate per day or per

hour per second.

Michael: So I think even with like
a low activity, as long as

something's happening every time
there's a checkpoint, I've seen

people with toy databases like,
you know, like free tier RDS,

be surprised that it's generating
like a couple of gigabytes

a day, like 64 megabytes per 5
minutes or so.

Adds up quite quickly with small...

Nikolay: Funny reason, I also can
mention, you have not a huge

database, but also quite active,
and you think, oh, it's time

for checkpoint tuning.

We had an episode about it, checkpoint
tuning.

Let's raise max wall size and checkpoint
timeout.

You raise it, but you didn't do
proper math in terms of how much

disk space you have, and end up
having too big, normally too

big, pgwall.

Because distance between checkpoints
increases, and Postgres

needs to keep more walls.

And this can be just a mistake
of configuration.

So yeah, after this, I think we
can move on and let's talk about

other reasons.

Michael: Yeah, what else do you
see?

Nikolay: I would put on the first
place in this category, log

directory, especially if it's inside
PGDATA, inside this main

Postgres directory.

Not especially, like, no.

Especially if log directory where
Postgres writes logs, if it's

in the same drive as a data directory.

For example, inside PGDATA.

Or just next to it but on the same
drive.

If logs are suddenly, like we have
some performance degradation

and log_min_duration_statement
is configured or auto_explain.

Or, for example, we decided, oh,
we need more audit and PG Audit

is a great tool.

Right, let's bring it.

It starts writing a lot to logs.

And if you didn't separate your
data from logs, you should separate.

I'm not talking about PgWAL, I'm
not a big fan of having a separate

disk for PgWAL, actually.

It was a thing in the past, but
recently I don't see benefits

from doing this often.

Benchmarks don't prove it and so
on.

So, it depends, of course.

It depends if it's your own data
center, maybe it's worth doing

this.

But if you don't offload logs,
regular logs, and keep it on the

same disk, then you bring PG Audit,
they suddenly start writing gigabytes

per hour, or maybe some problem
with rotation of logs.

Michael: Yeah.

Nikolay: Like retention or retention.

So they are not deleted properly,
for example.

And then this can hit you badly
and you have panic actually for

your database, and your selects
are not working.

But if you're offloaded to a different
disk, and at least with

logging collector enabled, I know
that database actually will be

working fully.

That's great.

Actually, recently, maybe a few
months ago, I had this experience.

I got used to it, Postgres is fully
down if we are out of disk

space, and I expected the same
behavior when our log drive is

full.

If we are flooded, if it's full,
I expect big problems.

Postgres didn't notice almost.

So okay, we cannot log, but we
keep working.

That's great, actually.

So this is super big benefit of
having different drive for regular

Postgres logs.

And even if it's slow, it can affect
performance, right?

But if it's 100% full, not that
bad.

Michael: Yeah, we've talked several
times about excessive logging

having an impact on performance.

Nikolay: Observer effect.

Michael: Yeah, and I've definitely
done benchmarks, you know,

this is the age old log min duration
statement 0.

Like what, why we recommend not
set or I recommend never setting

it to 0.

Because this, like you can generate
gigabytes in like a 30 minute

benchmark, like with pgbench or
something.

So it's, it's surprised me how
much it can grow.

Nikolay: Yeah, it's actually a
good idea maybe to just, you know,

like maybe to ask our bot to benchmark
with regular PgBench and

then to have sampling for logging
of queries in 1 way or another

and just to see how observer effect
grows and kills your performance.

Only people with small databases
can recommend log min duration

statement 0.

Michael: Or quiet databases, yeah.

Nikolay: Quiet, yeah.

Not active.

Small databases and small workloads.

Michael: That's it.

So yeah.

Or I guess super low, like turn
it to that only very, very briefly,

like a minute or 2 or something.

Nikolay: Yeah.

Yeah.

So for, for serious workloads,
you cannot do that.

I already shared my experience
putting big data, critical databases,

productions down and yeah, don't
repeat my mistakes.

Michael: As well as logging, what
else did you have in this category

of things?

Nikolay: Oh, that's a good question.

Well, let's not maybe spend too
much time discussing some unknowns

or external things like we have,
I don't know, something else

installed on the same machine and
using the same disks and suddenly

we are out of disk space.

Also it can happen sometimes.

Or maybe, for example, you do some
troubleshooting, you have

self-managed Postgres, you can
SSH to the box, you started sampling

every second some good logs, but
do it for example to home directory

and then it's full or something
like this.

So you always need to be careful
with that.

But interesting case when, for
example, you try to upgrade, you

have Patroni, or you some, I don't
remember exact details, but

in some cases Patroni decides to
retry provisioning of a standby

node, for example, and it fetches
PgData in the way you configured

with PgBaseBackup or from archives,
but it renames the old directory

to make it like backup, not like
local backup, right?

It just renames it and you end
up having 2 directories suddenly,

right?

Data directories, full-fledged,
like, wow, it can quickly be

a problem in terms of disk space.

What

Michael: else?

Does that mean like if you had,
let's say your data storage was

about 40% of your disk size, So
you thought you had loads of

headroom.

Suddenly you're at 80% plus whatever
else is on there.

Yeah.

Nikolay: Yeah, okay.

Wow.

Maybe large temporary files, but
it's quite exotic.

I like, it can be, they can be
huge, right?

But it's like, it's exotic.

Michael: I saw some Stack Overflow
posts mention that, you know,

people that were out of disk got
the error, but when they checked,

they did have free disk space.

And one of the suggested answers
mentioned check for temp file,

like your queries could be doing
a lot of temp file stuff, but

yeah, I've never seen that myself.

What about, well, backups is an
interesting one.

Like backups, if you, cause some
people recommend keeping some,

obviously keeping all your backups
on your, on the same disk

as your database is suboptimal
for recovery purposes, but keeping

some on it makes sense, especially
for huge databases, right?

Nikolay: I don't think so.

I would not recommend doing this.

Michael: But what about for recovery,
like no network?

Nikolay: Regular backups should
be separate, of course.

But if you do some operations manually,
for example, you decided

to back up some tables and just
dump them to some compressed

form and keep on the same drive,
well, it might happen.

But there are many mistakes you
can do manually.

Or you just create a table select,
for example.

You basically just clone the table
and then forgot it.

Well, many such mistakes can be
done.

I think there are many exotic situations
we don't discuss here,

and I'm curious if our listeners
had some interesting situation

we didn't discuss yet.

It will be interesting.

Like I think many exotic things
can happen.

Let's talk about what to do in
emergency.

Right?

Yeah.

First thing, understand the reasons.

Or maybe no.

This is extremely interesting.

Michael: No, I don't think so.

Yeah.

I mean, bear in mind, if we're
actually in an emergency, our

database is no longer not only
not accepting updates and writes

and things, but it's also in a
lot of cases not accepting SELECTs,

which...

Nikolay: Which is ridiculous, right?

You shared with me before our call,
you shared Aiven doc, which

says if a managed Postgres service
has automation, if it understands

that we are approaching a full
disk space, it converts our database

to read-only state.

Just setting a parameter, which
actually any client can override.

So it's weak protection.

Default transaction read-only turned
on.

This parameter, default transaction
read-only.

I like the idea, actually, because
it's weak protection, but

it's quite reliable if you don't
have this set to off all the

time in your code.

Usually, there are low chances
you have it in your code.

So it means that it will protect
you.

And then you have alerts, you need
to have proper monitoring

and so on.

But this is like a prevention measure,
maybe.

Michael: Yeah.

Again.

Nikolay: Yeah, it's again prevention.

Yeah.

But if you're in an emergency, this
is like, I think we should write

some good how-to in general, how
to for any case.

Michael: I did check your how-tos
and I did.

I was surprised not to find one for
this actually.

But I think also one thing worth
mentioning, nobody's going to

be listening to a podcast and be
like half an hour in or whatever

and be like, you know, because
they're panicking.

Yeah.

Yeah.

But one thing to remember that does
seem to catch people out is,

I mean, hopefully everybody listening
here won't, this won't

happen to you, but they know that
WAL stands for Write-Ahead

Logging.

Assume that it's logs and therefore
can delete it.

And then because it's like a large
amount of what they think

are logs, they delete those to
try and recover.

And I think that the main advice
from me in terms of recovery,

like from every guide you'll read
on this, is don't do that.

Nikolay: Yeah, I will say the main,
the like classic approach

is understand reasons and fix them,
mitigate, right?

But you might have pressure of
time.

In this case, it's good if you
can quickly identify something

you can delete safely without big
consequences.

For example, regular Postgres logs.

If you have a lot of logs, gigabytes,
it's good to delete just

1 file or so, start database if
it's down, and then you do need

to do full analysis and mitigate
the real reason and so on.

Or maybe you can delete something
outside Postgres data directory.

Something.

You can check quickly if there
are different directories around

which have some gigabytes of data,
or at least megabytes, you

can quickly delete and let Postgres
behave normally for quite

some time, it buys you some room,
right?

For analysis and proper mitigation.

But mitigation is inevitable, You
need to understand what happened.

Is it a WAL or data or something
else?

And then you need to mitigate.

Michael: Yeah, I was reading some
guides on this.

There's a good 1 on the Crunchy
Data blog, and they made a really

good point that we don't all think
of doing when you're in that

kind of panic mode.

Get, you know, get things back
online is, is my primary objective

in cases like that.

But they recommend taking a file
system level copy of everything

in its current state.

Like take a snapshot of the current
directory as it is before

you start deleting things.

Nikolay: Yeah, it's additional
work in progress.

Michael: Yeah, because it takes
time and we're down.

Nikolay: In cloud actually, if
you don't see what you can delete

quickly, like in Cloud, it's managed,
for example, you cannot

go and run the UDF and so on.

But in this case, probably it's
easier just to add a few percent

of disk space quickly to buy some
room again, and then to investigate.

Obvious approach, right?

Michael: I also think because of
some reasons we're going to

discuss in a bit, this is just
so much less likely to happen

in cloud environments.

Like it just feels like this is
1 of those features.

There aren't that many.

Nikolay: This is their job, right?

Michael: Yeah, this is 1 of those
2 or 3 features that they really

do manage and can manage for you
automatically.

Nikolay: Notify properly and so
on, right?

Michael: Or just auto-scale.

Auto-scale, yeah.

You just don't hit this button.

Nikolay: I don't like auto-scaling
in this area because it's

auto-scaling of budgets.

Michael: Well, but it's also not
solving the root cause of the

problem.

If you're, if several of those
problems we talked about, it's

not your actual data growing.

It's not your like, It's not your
true tables and indexes.

It's write-ahead logging going
out of control because of an open

replication.

Some reason that it shouldn't be
that much data.

So auto scaling those is just not
solving the problem.

Exactly.

Yeah.

Nikolay: Like hiding dirt under
the rug, is it?

Michael: As long as you've been
told when it has scaled up, then

at least you can go and investigate.

Nikolay: Yeah, so I think we covered
quite well mitigation in

general.

Michael: Well, what do you think
about...

Yeah, I think increasing makes
sense.

I saw some warnings not to increase
in place necessarily.

Maybe you should look at setting
up a replica and failing over,

but I thought that would be slower.

Like I didn't, I wasn't sure myself.

Nikolay: I don't know.

Like I think if you can add space
or delete something safe, it's

good.

First step, then you have big step
of analysis, proper analysis

and mitigation of true reason,
main reason why you approached

it and not noticing, right?

So a big part of mitigation, if
you perform proper root cause

analysis, I'm quite sure will be,
we didn't notice this and it's

a problem itself, so our observability
is bad.

Michael: The process is not- Either
monitoring or probably alerting

is, in fact, yeah, we have to go
to prevention.

Nikolay: Yeah, prevention, I think,
philosophically, is quite

simple.

You just need, like, the better
way to prevent it is understand,

like, keep a good level of understanding
of how you spend your

bytes of disks, of gigabytes.

So you understand the layout, you
understand wall data, real

database data, is it like actual
data or a lot of bloat, and

then you understand logs and everything
else and you understand,

okay, we are good and we understand
how we spend our disk bytes.

And if this understanding is good,
everything will be fine and

you will notice problems early.

But it's hard to do if you have
hundreds or thousands of clusters,

right?

In this case, you do need some
rules to be alerted if bloat is

high, if pgwall grows without good
control, if logs are not properly

– like also, logs consume too much.

You just need to have good observability,
right?

To have mitigation.

And then you just, with understanding
of possible reasons, you

navigate in these reasons and find
mitigation.

But the key of prevention is understanding
and understanding

is observability, right?

Michael: Yeah, 2 other things.

I think like whilst I'm kind of
against auto scaling in principle,

I think if you've got the option,
going up in budget and having

to migrate later back down to a
smaller size is so much better

than being down.

I personally would have that on
myself if I had that option.

And I think also, oversizing.

Like, disk is so cheap compared
to everything else we're doing.

Unless you really are in the like
extreme...

Huh?

Nikolay: IM.

Disk is not cheap if you have a
lot of terabytes and many standby

nodes and oh, it can be not cheap.

Michael: But most of us aren't
in that case.

Like most of us aren't in, so yeah,
I agree that for like people

with serious, serious days.

Nikolay: Some people pay like millions
for disk space.

It's like, it can be headache.

That's why I mentioned PgTier is
a quite very good, potentially

good thing and Teering is good.

And like some design, understanding
how the structure of your

disk space consumption, you design
some rules and to float data

properly and keep everything under
control.

And that's, that's, this is good.

This is good.

But yeah,

Michael: I think...

Imagine, imagine advising some
startup and they had like some

10 gigabyte disk or something,
and their database was only a

few hundred megabytes.

The cost of being on a hundred
gigabyte disk instead is just

so tiny, like it's just such a
small, but yeah, I take your point.

Nikolay: But again, I think observability
can be always improved,

I think.

So like if, especially if we know
that this is visual, this is

data, this is logs, everything
else, and we have some proper

analysis and alerts for it.

Michael: I think that's the bit
that trips people up.

I think a lot of people actually
have quite decent observability,

like they can see what's going
on, but alerting people aren't

getting alerted early enough, or
not at all is the 1 I see quite

often.

No alerts at all for, you know,
disk is at 50% sends an email,

70% raises a, you know, 90% sets
off a page, you know, like these

kinds of like serious alarms.

Nikolay: Yeah, you know, I know
we need to go already out of

time, but let's mention some small
practical advice in the end.

If you cannot SSH to the box, for
example, it's RDS or other

managed Postgres service, and Postgres
is still alive, but you

wonder why the disk usage goes
up.

At SQL level, you can check what's
happening in directories using

pg_ls_dir, pg_ls_valdir.

There is a set of administrative
functions which allow you to

inspect the content of directories.

And this is very helpful when you
troubleshoot and understand

what's happening.

And maybe also it's helpful, can
be helpful in mitigation activities,

in observability.

For example, if you have a report
triggered out of disk, almost

soon usage exceeded 90%.

Then you can include some analysis
automated to have a snapshot

of what's happening in key directories
and understand what are

consumers of disk space.

Just some advice I thought we could
miss.

Let's not miss it.

That's it, I think, right?

Michael: I think so.

I hope none of you ever actually
have to face this and you can

all mitigate or prevent it from
happening.

And yeah, thanks so much, Nikolay.

Catch you next week.