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

all things PostgreSQL.

I am Michael, founder of pgMustard,
and today I am joined by

Chelsea Dole, staff software engineer
and tech lead of the data

storage team at Brex, and speaker
at several prestigious Postgres

conferences over the past couple
of years.

Thanks so much for joining me,
Chelsea.

Chelsea: Thanks so much for having
me, Michael.

Michael: Wonderful.

Well, I have seen several of your
talks and loved how well you've

explained a few concepts now.

One of those was bloat.

And looking back at our previous
episodes, I realized we hadn't

actually done an episode on bloat
itself.

We've done plenty that mention
it, that are around it, but I

loved your explanations and I'm
looking forward to having a chat

with you about what it is, how
people can think about it, and

some strategies around it.

Chelsea: Thank you.

Well, as a PostgresFM loyal listener,
I've definitely listened

to a couple of those bloat sphere
conversations, let's say.

So it's nice to be addressing it
more directly.

Michael: Yeah, awesome.

So in terms of where to start,
I guess we should cover what is

bloat.

How do you think about it?

Chelsea: So Postgres bloat basically
occurs as a function of

MVCC and it's kind of an extension
of the autovacuum process.

So in MVCC, all the actions you're
doing, inserts, updates, deletes,

those are all actually updates
or editing metadata on a tuple,

not hard-deleting it in place.

This basically allows MVCC to be
both compliant with ACID principles

and still fast, so it doesn't slow
things down too much.

When those tuples are generally
hard-deleted is through vacuum,

which runs every now and again,
depending on your configurations

and autovacuum.

But if autovacuum can't keep up,
and you have lots of those

inserts and updates and deletes,
then you can get into a state

where your Postgres pages are basically
bin-packed with a bunch

of dead or soft-deleted tuples.

And that leads to bloat.

So bloat is the state where you
have these table pages that are

full of basically useless data
that vacuum is hurrying to kind

of run around and catch up.

And so Postgres has to keep on
adding new empty pages to the

end of your heap, which leads to
all sorts of, I'd say, non-optimal

outcomes.

So too long, didn't read.

It's unoptimal tuple density in
your pages.

Michael: Yeah, I really like that.

So if we end up in a situation
where a large proportion of our

table, maybe including the indexes,
is sparsely stored on disk,

that has knock-on effects and that's
referred to as a table with

a lot of bloat.

I've thought about it in the past
as almost the diff between

the table's current state and if
we completely rebuilt that table

with its indexes.

Like the diff I think of as bloat.

I don't think it's the only definition
that's acceptable because

of like fill factor and things
like there are other technical

details but I like it because I
think it's quite practical and

in terms of what it means.

So what but why is this a problem?

Like when have you seen this
cause issues and how bad are

these issues?

Chelsea: There's a huge spectrum
there.

And I would first preface by saying,
I wouldn't call this a hot

take, but maybe a lukewarm take
is that bloat is really not always

a problem.

I think that a lot of people think
of this as this, oh God, our

tables are bloated, what are we
ever going to do?

But there's plenty of situations
where you have a bloated table

that's serviceable and you're able
to get it to a slightly better

state without any drastic measures.

But the main issue that bloat can
cause, which can lead to downstream

issues, is really increased IO.

And IO is sort of the grandfather
of all these downstream issues

that nobody likes, like high latency
on your reads.

Michael: You

Chelsea: know, it leads to IO because
essentially if you think

of it logically, like if I have
10 rows across 2 pages, if I

do a sequential scan, I've now
scanned 2 pages.

That's a certain amount of IO.

But if I have really bad tuple
density, I've got table bloat,

then maybe I'm scanning 10 rows
or 10 tuples across 8 pages.

And so I'm scanning the same amount
of data, but I just had,

you know, 4x the IO.

So that can lead to downstream
negative effects with Reads, of

course.

And you guys have talked a couple
of times about, you know, explain

buffers and things like that.

So that's a really good way to
sort of see off the cuff, you

know, whether you're using an appropriate
amount of IO.

But as I said, there are places
where you can have, you know,

some amount of table bloat and
it's not really causing an issue.

I would say that where I look for
it as the biggest long-term

issue to solve and really address,
is those workloads that are

going to be very update and delete
heavy.

Having some bloat on a normal workload,
if your users aren't

seeing effects on latency through
the IO, I would sometimes just

say shrug, you know, the important
thing is the end-to-end experience

and the long-term maintainability
for you as a dev.

Michael: Yep, love that.

And very practical as well.

I've seen a couple of your talks
on this.

And you mentioned IO, but you mentioned
it quite late on.

I'm like, oh yeah, it is as simple
as that really.

Obviously there are other bad things.

Like it's taking up more disk space,
right?

Like it's taking up more space in
memory, but ultimately the user

experience wise, that's normally
what a lot of us are fighting

with, especially on busy systems.

So slow queries that users are
reporting is a natural sign.

How do you go from that to realizing,
oh, my problem is a bloated

table?

Chelsea: I think there's sort of
2 paths for that.

In my experience, the first one is
before you ever know what bloat

is, how do you discover bloat?

And I would say that usually the
path is you have a really bloated

system and there's really no other
explanation for it until you

go down those paths and try to
figure it out.

For myself, just sort of going
through how I discovered table

bloat, thinking back, you know,
I've worked, I would say, in

Postgres at scale, at the last two companies
I've worked at.

Before that, with it more as like
a passing, just a tool I happened

to use.

And I saw it in great scale when
I first started at Brex.

And the way that I actually saw
it is that we had a developer

reach out.

And they said, hey, you know, I
have this, let's say, 50 gigabytes

of tables total on this server,
there's only one database, and

for some reason, we're almost running
out of disk.

Why do I have so?

Where's all this space going?

Because I can see my table size
and then remaining table space

on disk.

We use RDS on Cloud, so you're
able to see that pretty easily.

I went and I said, I actually don't
totally know.

Let me check this out.

When I went and checked it out,
I could see that there was just

a ton of bloat.

Here, interestingly, the bloat
was actually coming from a TOAST

table bloat.

Michael: Oh, nice.

Chelsea: I know, which is an interesting
little side quest here,

conversationally.

But TOAST is just other tables.

The oversized attribute storage technique
or whatever fun acronym they

made up with it.

Those are just tables under the
hood too, so they can also get

Bloat.

So I'd say that was my first time
needing to go through the entire

song and dance of bloat that needed
to be fixed through, I would

say, like strong measures versus
maybe just tinkering some little

stats here and there.

But I think that once you know
what bloat is, it's pretty easy

to look out for it.

Operationally, there's observability
tools and dashboards.

You can instrument your own.

I think a whole lot of DBAs have
their own private stash or ideally

GitHub open source stash of fun
queries they like to use or extensions

they have in their back pocket.

And we can dive a little more into
those if you want.

But I think that there's sort of
those 2 paths.

There's the path of, oh, God, what's
going on?

And the path of once you've seen
it, you can kind of pattern

match.

Michael: Yeah, nice.

I like the system-level like path
in and I've all, because of

my background and the tool I work
on, I see it more from the other

direction, so somebody looking at
an incredibly slow read query

that has no right being that slow
and using explain analyze buffers.

And as you mentioned, when we say
incredibly bloated, you could

easily have at least before Postgres
14, which had some great

optimizations in it, you could
have an index that was 10 times

bigger than it needs to be.

If you, let's say, you rebuilt the
index concurrently, the result

afterwards might be 10 times smaller.

So that's a significant amount
of extra reads you might

be doing, especially if you're
returning a bunch of rows.

So you mentioned like 8 reads instead
of 1, but if you're looking

at the last thousand rows, if you're
doing 8,000 instead of 1,000,

you start to notice that in latency.

So it's quite cool.

We, in fact, we used to call the
tab bloat likelihood,

and I renamed it a couple of years
ago to read efficiency, partly

because of your, like it goes back
to your density question again.

It's not necessarily bloat, but
it's not necessarily

about the likelihood of it.

It's more about the efficiency
of those reads, and it might be

a locality issue which is somewhat
bloat-related as well.

So yeah, love this.

Great.

So you mentioned, and you've spoken
before about queries to estimate

and other techniques for looking
into like getting more, maybe

accurate or, depending on your definition
of accurate, measures

of this. What's your advice on
minimizing this, dealing with

it?

Chelsea: Yeah, I guess the first
thing to the measure of how

to identify whether you have bloat
or how much you have and decide

What to do with it.

The first step there is really,
again, kind of a trade-off of

what matters to you.

Does the speed and low system impact
matter to you in this sort

of DBA process of figuring out
how much bloat you have?

Or does accuracy of the exact percentage
of bloat or the exact

tuple count and being 100% sure
that your stats are right matter

to you.

And so again, kind of like I said
in the discussion about how

bloat can kind of be okay or not
something you really need to

deal with in certain situations,
this is one where you kind of

get to decide your priorities.

If your priority is 100% accuracy,
or I would say also, if you

have downtime hours on your database
where you can easily do

this without any user effect, or
if you have a small system and

for whatever reason, you happen
to be bloated through your own

write patterns, but it's not actually
that important, then I

would suggest pgstattuple.

pgstattuple is a Postgres contrib
module.

Basically, it gives you some functions
that you're able to run,

and they will basically do a sequential
scan through a target

table, or I think through a target
database if you want to run

all tables, and it will return
to you the count of live dead

tuples, free space map stuff, as
well as a few other statistics.

That one I would say is on the side
of you have resources to spare,

you're not gonna impact users because
CPU does spike during this.

And there's no way to get around
the sequential scan because

you can't exactly index it.

The point is to actually look at
the pages.

So it's always going to be a sequential
scan.

The other option is through using,
I would say, estimation queries.

And these tend to leverage internal
tables already used by Postgres

or kept up to date during the Analyze
process.

So these would be like pg_class
reltuples and things like that

will estimate the number of live
dead tuples you have based on

a sampling during Analyze.

So before you run anything that's
an estimate, you do want to

run Analyze, you know, right before,
but then you're able to

guess a proportion based on a sampling
of your table data.

This is a better option, I would
say, for really high-criticality

systems or extremely large tables.

If you have a 500-gigabyte, 800-gigabyte
table, or even just

one that's 80 gigs, but is very,
very critical for users, and maybe

you're already kind of medium on
CPU, you can't stand those resources,

then there's really no downside
to just using a table sample.

There's no reason that this number
needs to be precise.

Michael: Yeah, so what is alarming?

Well, I guess the answer is it
depends.

But you've in fact, I've rewatched
your talk recently.

So I know I'm stealing from you
there.

But you've got some great rules
of thumb on what you consider

to be bad on the table bloat front.

Chelsea: Yeah.

Well, I don't think I could possibly
stall, it depends.

Cause that's just like everybody
in engineering should be

saying all the time, but you know,
my personal rules of thumb

speaking just for myself is that,
on very small tables, bloat

is not a problem.

A gigabyte, 2 gigabytes, that as
I'm saying is very, very small.

Even if you would see up to 75
percent bloat, out of vacuum we'll

be able to take care of this, the
total impact on your system, there's

just no way that it can be high
or significant, you know, knock

on wood.

I'm sure somebody will comment
with some counterexample, but

at least in my mind.

Beyond that, I would say if you
get to a 1 to 30 gigabyte range,

I would say 25% bloat.

So 25% of your total table space
being taken by dead tuples is

acceptable.

And then as you go higher, from
about 30 gigs, I would say you

want to inch that acceptability
downwards.

So I would say once you get up
to 100 gigabytes, I would aim

for like 18% and then I would flatline
at about 18%.

1 important thing is you should
never expect a 0% dead tuples.

If you completely rebuild a table
or if you tweak every autovacuum

setting or you've used an extension
and repacked it, you still

will have some bloat and that's
okay.

Michael: Yeah, right.

Like unless you're gonna show off
because you've got like an

append-only table with no bloat,
but if you've got updates and

deletes, it's a trade-off of the
system, right?

It's the trade-off for the design
that Postgres has given us.

You've recommended this talk a
few times.

I'm most of the way through watching
it, but Peter Geoghegan's given

another great talk that is criminally
underwatched at 200 views,

so I'm going to share that.

But he mentioned some of the trade-offs
that Postgres is

making in this area.

So it's guaranteed, I think as
soon as we start doing updates

and deletes, it's guaranteed that
at least past a certain frequency

of those updates and deletes, we're
going to have some bloat.

So yeah, it's a really good point.

Chelsea: Yeah, there's no whole way
to avoid it.

It's more about mitigating the downstream
effects and making sure you

don't accidentally look up and
realize you're in a crazy situation

in a couple of months or years.

Michael: Where did, I'm curious
though, I understand totally

as you get larger data
volumes, trying to aim for a smaller

percentage of bloat, because like
in real terms, that's more gigabytes

of bloat or more pages that could
be being scanned through for

reads and things like that.

But did 18% come from trying to
get it lower than that, like

fighting some update-heavy tables?

Or where, that seems quite a specific
number to me for like a

rule of thumb.

Chelsea: Yeah, great question.

And actually, this is the first
time I've been asked this question,

which I agree.

The first answer to why 18% or
why these numbers is, as someone

who watches Postgres talks and
as somebody who takes it in, I

have to admit, there's part of
me that gets annoyed when nobody

gives real numbers.

I realized that it comes from the
reality of it depends as we

already kind of espoused on, but
I really wanted to provide something

concrete.

When I gave those numbers, I thought
about my previous projects

and I thought about, okay, well,
what happens if I completely

rebuild this table or I repack
it and don't insert anything?

Usually, you would see a table bloat around 8 to 10% then still,

at least in my experience, because
if you're rebuilding a table,

at least if you're using an extension
like pg_repack, for example,

or pg_squeeze, you know, it's still
gonna need to kind of build

up and write all the live data
into it as it's rebuilding the

duplicate table.

So you're not gonna end up at like
0% unless you, I believe,

I assume, unless you VACUUM FULL.

And so 18% came out of, I'd say,
like opportunism of what I wanted

to provide, and also just on experience
of when I started to

see a tipping point into performance,
and when you start to get

far enough that you can't really
recover it without rewrites.

So the reason I would say 18% is
not because 20% or 25% even

on a large table is the end of
the world, but because I think

once you start slipping into that
more bloated table space at

large tables, it's harder to recover
from.

And it needs more drastic measures
than a little tuning here

and there.

And also, if you imagine you have
a, you know, 1 terabyte table,

which I know that I've had before
at companies, then if you get

to 20% of dead tuples, you now
have, you know, 200 gigabytes

of just bloat.

So at scale, you know, that becomes
money, whether you're in

the cloud or not, you're paying
for that disk.

That's kind of another downstream
negative effect other than

IO.

So it's kind of, it's money, it's
personal experience, and it's

also just wanting to put a sticker
and a number on something.

So you have a target to aim at.

Michael: Yeah, I really like that.

I agree, it's difficult without
any real numbers, but it also

like you could have said 15% I probably wouldn't

have questioned it.

It's just more it's more practical.
It's more likely to be based

on your real experience, which
I liked a lot. Cool.

So I wanted to come back to the,
you mentioned TOAST and the

acronym or probably backronym.

I don't know if you've got that
phrase here.

That's a really fascinating case
that I don't think I've not

seen, I've not spoken to anybody
about, but it makes intuitive

sense because of the nature
of TOAST, we could have potentially

large values across multiple pages,
but I would have guessed

they would be more easily reused,
but I'm not, but that's a total

guess.

I've yeah.

Do you, Do you have any more details
on that?

Chelsea: Well, I'm sure I'll get
some of this possibly wrong,

but I think, actually, it's surprising
that we don't see TOAST

in more situations of bloat.

Because when you're updating a
value in TOAST, To my knowledge,

it's not able to be as intelligent
as other update methods can

be.

You're actually replacing or updating
more often than not.

TOAST tables themselves, as I guess,
a background in case anybody

listening is not familiar, What
happens is if you have an extended

field, you know, you have extended
and non-extended data types

in Postgres.

If you've an extended field, so
things such as JSONB or, you

know, VAR cars with I think longer
limits or byte A, then all

these are examples when they can
go over the maximum single tuple

size.

If they reach that threshold, then
Postgres will compress it

through some method depending on
your Postgres version, and they'll

just store it in a separate table
called TOAST table.

In the main table, let's say you
have a users table, then that's

basically just a pointer to this
TOAST table.

In the example I mentioned before
when I ran into bloat on the

TOAST table, the reason you would
see this is maybe you have

a small table even, it could be
a 5 gigabyte table, but every

row in this is extremely large
data types.

I'm sure we've all seen those tables
where there's 5 columns

and each of them is like a massive
JSONB value.

They could be updating those all
the time.

You could be doing field updates,
you could be doing anything.

And if you get up to a certain
TPS on that, then every single

time you do an update, it's gonna
be rewriting that value in

the toast table.

And Postgres does look at TOAST
as far as I know, like just any

other table.

So autovacuum is running on it the
same exact way.

You know, I could talk on partitioning
and I kind of say the

same.

Partitioning to me is like UX.

It's DBA UX.

We see it as 1 table.

Postgres just sees tables.

And same thing with, same thing with TOAST.

And so, in that case, we had a small table that was just super

high update for those very large fields.

Correct me if I'm wrong, Michael, maybe you know more about this,

but I think that the nature of how TOAST does those updates is

they're not able to do, like, I think, key-value updates in TOAST

fields.
They have to always rewrite.

Does that sound right to you?

Michael: Yeah, well, it sounds like any other value in Postgres,

right?
Like, it's a single entity.

So like, even in a large text field, without it being toasted,

if you had it pasted in the entirety of War and Peace and then

only changed 1 word, but it was all a single value, I believe

we'd need a new tuple with, well, bad example, because that one

definitely wouldn't fit in a single page, as it would be toasted.

But yeah, if you, let's say you took a 1-kilobyte chunk of text

and changed only like a few characters in it, I think it would

work the exact same way.

So, yeah.

Chelsea: The only caveat to that, I believe, is that when you

TOAST something, toast an object, you toast it in chunks of a

discrete size.

So, if you did toast War and Peace, then, you know, it would

be, you know, you would run into the tuple length issue in TOAST

as well.

TOAST doesn't have a longer, you know, maximum tuple length.

It's just that it will chunk it via some algorithm, compress

it.
And then so War and Peace will actually be a pointer to let's

say 10 tuples all of them compressed.

So I believe that when you edit 1 word, you know, you fix your

spelling mistake, you know, Leo Tolstoy really has to go back

and fix that.

Then when he re-TOASTs it, then he has to recompress and re-chunk

all those 10 values again, not just the one.

So I think it might be, you know, we're figuring this out, really

talking about it, I think it might be something that scales the

issue with depending, with increased size of the object itself.

Michael: Yeah, I'd love to hear from anybody who has dealt with

this or looked into it or written about it.

Sometimes we get some quite in-depth responses, which we love.

The question I guess is, let's say we've got something that's

spanning tens of kilobytes, so multiple pages, your 8-page example

might be good.

And we rewrite it so we get 8 more
pages.

The question is what happens to
the 8 previous ones?

Once they get marked as dead and
if they could get reused easily,

we shouldn't get too much bloat.

But if they're getting, I guess
if autovacuum can't keep up,

that's when it accelerates.

So it would make sense in a system,
for example, where autovacuum

isn't able to keep up with that
toast table for some reason,

or maybe it's been disabled on
that table.

Chelsea: Yeah, totally.

That can also be a place where
you need to tune autovacuum max

workers higher, because autovacuum
max workers defaults to 3.

And this doesn't really have an
impact depending on your table

size, it has impact depending on
your table count.

If you have, I would say, more
than hundreds, so getting into

thousands of tables, that's or
and many of them are large, maybe

the Autovacuum worker takes a long
time on 1 table.

That's where you're going to want
to start tuning that up, giving

extra resources on your server
over to vacuum compared to servicing

queries.

I would say for the greater good.

Michael: Yeah, and just to be clear
when you say like hundreds

of tables We're counting each partition
as a table in that case.

Chelsea: Yeah

Michael: Yeah makes a lot of sense.

Cool So anything I haven't asked
you about that I should have

done or any other tips for this?

Chelsea: Let's see.

I think the only thing we didn't
get around to that I think I

would want to make sure I shill
is some more details about how

to fix a bloated table once you
see it.

So if you have discovered you have
a bloated table, let's say

that you're above that metric,
that rule of thumb, maybe well

above it.

Like I said, when I found that
toast table, it was like 95 percent

bloat.

That explained a lot.

I would say that at that point,
there's a couple routes you need

to go down.

The first is to recognize that
bloat is caused by a combination

of autovacuum configuration and
update and delete heavy workloads.

Just 1 of them won't get you there.

It's both together.

And so if you're only a little
bit bloated, maybe you just kind

of keyed onto it via some, you
know, observability metric or,

you know, warning level ping you
got in some place or other.

At that point, I would recommend
going the route of tuning your

autovacuum configurations, as
well as really talking to the

dev team or working as a dev yourself
to figure out whether there's

anything you can do to reduce the
volume or difficulty of those

updates and deletes.

Some common anti-patterns I see
are things like cron jobs that

run every day or week and delete
a huge amount of data.

And they often think they're being
helpful when in reality they

can be kind of degrading the quality
and I.O.

Performance of that database.

In terms of tuning configurations,
usually you want to go one of

two broad ways.

You either give the server more
resources for Autovacuum through

Autovacuum Max Workers, or you
tune it to run more frequently.

So you tune Autovacuum to actually
run more aggressively, which

I generally recommend based on
system defaults.

Autovacuum did get more aggressive
in more recent versions of

Postgres.

However, it's still generally good
to tune up like Autovacuum

Vacuum Scale Factor, especially
if you have those large tables,

you know, defaults to only triggering
Autovacuum when 20% of

your table is dead rows.

So, you know, that's already beyond,
I would say, my recommended

18% goal.

So if you really wanted to trigger
more proactively, you would

need to tune that down from 0.2
to let's say like 0.1 or far

less.

You know, I see a lot of recommendations
online that I've used

as a user that suggest, you know,
every 1% or 3% of dead tuples.

Michael: Yeah, because for a huge
table, that's still a lot of

tuples.

We could still be talking about
tens of thousands, hundreds of

thousands, even millions of tuples.

Chelsea: And by the time that it's
actually done, you could be

up higher than that because that's
what triggers.

What if it takes hours and hours?

Michael: Yeah, absolutely.

Chelsea: If you do get really,
really far behind, you check the

clock and you're up to that 90
percent bloat table, that is a

good time to consider rebuilding
the table.

If you can afford it, vacuum full.

Most people can't these days if
it's a user-facing application,

so that's the reality.

But I always say that first because
there are situations in which

you can, again, looking from the
user perspective, and it's a

lot easier.

The other thing you could do is
use an extension like pg_repack

or pg_squeeze to rewrite it.

And this basically creates a
shadow table duplicate schema.

It will copy over all the data,
use triggers to update all the

incoming data coming in from one to
another.

And then once it's all caught up,
it will within an access exclusive

lock on the which lasts you know
less than a second.

Definitely.

It will basically switch the table
names to the prod traffic

points towards the new table.

This is something that I have seen
be flaky.

I wouldn't personally recommend
automating pg_repack.

I've seen and heard of cases in
various talks and just through

friends in the industry, people
that try to automate pg_repack.

And, you know, I've seen it lead
to a lot of incidents and issues.

You know, I've personally never
run into an issue where there's

any data loss, because in the case
that you just kill the PID

of pg_repack, for example, you
just have some dead tables you

have to manually drop.

The duplicate ones aren't complete,
so you use the old one.

But I've heard of it causing other
issues, so I would say be careful,

but I wouldn't stray away from
it totally.

Michael: Nice, I've always thought
of it as like, reindexing concurrently,

but for the whole table.

Is that a reasonable comparison?

Or I guess it's missing some subtlety
around potential flakiness,

but that can fail as well, right?

Like if reindexing concurrently
fails, you can end up with some

invalid indexes.

Chelsea: I think that's a really
good analogy, actually, mainly

because they're both nontransactional.

Because concurrently or anything
concurrent, the thing that it's

an unusual move by Postgres, and
I'm sure there was a lot of

discussion on the core team about
this when they first started

releasing concurrent features,
because it's a decision by the

core team to value the user Postgres
experience by DBAs and applications

over the strict rules of MVCC.

Because when you create something
index concurrently, if it fails,

you have the invalid index.

So it's not atomic, it's not able
to roll back.

The same thing with pg_repack.

If it fails, then you have these
invalid tables and valid indexes

that you need to drop.

If you try to rerun it, it's not
a no-op.

You'll have to clean it up first.

Michael: Yep, cool.

Okay.

Oh, wow.

That's good.

So that's a really good point in
terms of autovacuum and in

terms of repacking or vacuum full
if you can afford the heavy

locks, or if your system just doesn't
have any users at a certain

time of day or something like that
right but it is, it's rare

but it is common enough that I
have been caught out by not recommending

it a couple of times which is super
interesting.

CLUSTER is in the same category
right, like same as VACUUM FULL

but you get to order it by an index,
which could be helpful for

like reads.

Definitely.

Cool.

Last, in fact, you mentioned the
right at the beginning and I

had to bite my tongue not to jump
straight on the pun because

that's how my brain works.

You mentioned having a hot take.

So you talked quite a lot in the
past about access patterns.

And one point I loved was the idea
of considering if you've got

an update or delete heavy workload
which could be the reason

you're in this situation.

Do you even need to be doing those
updates and deletes?

That's a question that doesn't
get asked very often, and you

made a really good point.

So there's that angle that I'd
love you to talk on if you want

to.

And there's also the hot update
optimization we have in Postgres

that can be a huge help for some
avoidings.

If you're aware of it, not indexing
a certain column if you don't

have to.

There are some trade-offs there that
might be interesting.

I don't know if you've got experience
with those.

Chelsea: Yeah, I guess so first
address the first part of it.

Glad you brought it up because
this is definitely

a strong opinion of mine.

And I think that's something that
comes from coming to being

what I would describe as somewhere
between the liminal space

of a software engineer and a DBA
through, you know, backend engineer

to data engineer to DBA, just,
you know, sinking my way down

into infrastructure.

And, you know, I think that I still
tend to think from a perspective

of a backend or data engineer a
lot of the time.

And from that, I think that it's
good for us to all remember

that so many of these access patterns
and rights, the biggest

hammer you can use is to just not
do it or to rewrite it.

If you're a DBA managing a couple
or hundreds of databases, speaking

for myself, I'm managing the hardware
and the high-level metrics.

I don't really have access or knowledge
about talking to somebody

into the why.

Why do we have this query?

Why do we have this database itself?

What the heck's in it?

I think that if you really want
to address bloat, often the best

thing you can do is to start a
conversation and say, hey, what's

the value of this?

Can we simplify it?

Do we need to be deleting this?

Do we need to have this table?

You know, it's crazy how many times
that I've dealt with a problem

that way, and I've never needed
to delve into the more, I would

say, extensive measures.

And also, if you can, keep those
relationships with people

at your organization or whoever
you're working on a project with

to try to be able to let them keep
that up by themselves.

You know, at my company, we've
built some automation around let's

say like auto education as far
as we can.

We're still working on it, but
a way to kind of allow developers

to be proactively educated about
how they're building their systems.

And so I think that as much that
you can do that and just, I

would say, change the patterns
from the code side is the quickest

way.
That's a PR.

Michael: Awesome.

I was not expecting it to go that
way.

What's this automatic education
thing?

Is there anything you can share
there?

Chelsea: Yeah.

I will mea culpa here and say
that I wish I could say it were

a more, a more, you know, like
amazing system than it probably

is, but we've used, for example,
git hooks and GitHub webhooks

to automatically comment documentation
that we've written on

PRs.

For example, if we see running
a migration, we pin the migration

guide to your PR rather than requesting
us as reviewers because

I work in an organization of larger
than, you know, a thousand

people.

So I don't wanna be a blocker on
your migration, I want to educate

you.

Same thing with partitioning.

I wrote, after we dealt with partitioning
stuff, you may have

noticed that a lot of the talks
I write are based off whatever

the heck I'm struggling with at
work.

And so I wrote a very in-depth
partitioning migration

guide, both for future people on
my team, as well as people who

might want to partition and need
to understand why, how, whether

it's a good idea.

So I think that creating documentation
is good, but we all know

it falls out so quickly.

You change 1 thing, it's deprecated,
you forget about it, the

person leaves.

So I think that the underappreciated
side of it is figuring out

easy systems where you're auto-commenting
it or you're pushing

it to people in a way that
actually keeps it actively read.

Michael: Awesome.

Sounds great.

Is there anything else you'd like
to plug or give a shout out

to?

Chelsea: Not particularly.

I think that I'm sort of a relative
newbie to the Postgres community

being involved in the open source
side.

I went to my first Postgres
conference last year and then

sort of ran out it at 100 miles
an hour ever since then, which

has been really fun to get involved.

So I guess I would just say thank
you to all you guys for inviting

me in.

It's been a great past year being
more involved in Postgres.

Michael: It's awesome to have you
in the community.

I still feel new, and I feel like
I've been here for like 5 or

6 years.

So it's awesome having you here.

You're a wonderful contributor
to the community.

Your talks are great.

Please keep giving talks on issues
you're hitting with Postgres

that are some of the most fascinating
that the community can have

and also not common enough at conferences,
I think, personally.

So, yeah, appreciate it.

And appreciate the real numbers
as well.

So, thanks so much Chelsea.

Chelsea: Yeah, thank you for hosting this.

I listen to you guys, as many
people do, in the car. So, you know,

keep me going with good things
to read while yelling at

various drivers on the road.

Michael: Well, apologies, you're
probably going to have to skip

a week.

Chelsea: Okay.

Yeah.
Yeah.

Listening to my own voice is too
hard.

Michael: Tell me about it.

Take care, Chelsea.