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

This is Nik from Postgres.AI.

And as usual, co-host Michael.

Hi, Michael.

Michael: Hi, Nik.

Nikolay: Yeah.

And we invited a couple of guests
today, Nate and Andrew.

And we invited because the company
Metronome experienced outages

in I think in May, right, or April,
in May, right?

In May of 2025, which were extremely
hard to diagnose, but these

folks did a very good job and discussed
all the details in excellent

blog post.

And this blog post looked so great,
so we couldn't miss it.

So thank you for coming.

Let's dive into some very rare
Postgres problems.

Nate: Yeah, thanks for having us.

And hopefully sharing this out
will keep making it rare.

Andrew: Yeah, yes.

Thank you for having us.

It's a pleasure to be here.

Nikolay: Great.

So, Michael, where should we start?

Michael: How about we start with,
could you guys give us a little

bit of an overview of what Metronome
does, why you're using Postgres,

and what you were up to that was
hitting the limits this hard?

Yeah, so I can start with

Nate: the overview, and then Andrew,
I can pair on the exact

way that we kind of blew our foot
off here.

Andrew: Absolutely.

Nate: Yeah, so Metronome is a usage-based
billing platform.

And so, you know, when it was founded,
I think it's coming up

on 6 years ago now, it was really,
you know, At that point in

time, this is just before COVID
and lockdowns began, the target

was really, there's a lot of infrastructure
and service companies,

most of them are building on some
kind of heartbeat, you know,

per minute or sometimes per second
model.

That gets really tricky to do at
scale.

In the simplest cases, yeah, you're
just incrementing a counter,

But as things ramp up and your
account executives go out and

cook up all different ways to finesse
how they want to charge

that client, it gets really complicated.

You add in things like committed
spend, credits, you want to

automate it, you want to cut people
off and they're not paying.

The complexity really compounds
immediately.

And the 1 of the founders got worked
on the billing at Dropbox

and had also encountered issues
where when you build this internally,

which is where everyone kind of
starts, it gets very easy to

build the brittle system by accident.

Builds your local Maxima, fits
your current pricing and packaging.

delayed.

Like they can't launch their product
or they can't charge for

it for weeks or something, months.

So you get led into the spot where
you're delaying launches or

just like burning compute because
you can't actually charge for

it, which feels really silly.

And then there's also a frequent,
you just accept a lot of slippage

because building these systems
to be perfectly accurate gets

really tricky.

And thankfully there's a lot of
foundational pieces such as Kafka

exactly once and strong transactional
databases to make this

possible to build and have confidence
in.

But it's very difficult to do this
internally and prioritize

that degree of rigor.

And also if you're building infrastructure
company, you know,

let's say you're a database startup,
your best engineers are

there because they want to work
on building a database, they're

not actually there because they
want to work on building.

And so we also think that, you
know, kind of consolidating talent

to work in this problem yields
a better result.

And I think the marriage of the
business logic domain, the exact

correctness needs, and the overall
data scale volumes, we're

right place, right time for this
AI boom, introduces a lot of

very interesting challenges.

And well, we're about to talk about
one of them.

Nikolay: And you chose Postgres,
obviously, right?

Nate: Yeah, the Postgres decision
preceded both of us by a ways,

but I think we're broadly quite
happy with it.

Nikolay: Okay, good, good.

So do you know why Postgres was
chosen in the past?

Nate: Not precisely.

I mean, like, probably, yeah, we
immediately went to using RDS.

I don't know if it was immediately
Aurora.

Interesting.

Yeah.

I think it would be an interesting
decision as a small startup

who anticipates having a lot of
data scale to want to deal with

the schema change pain associated
with MySQL.

Nikolay: Yeah, I wanted to add
a joke that maybe somebody googled

some open source relational
database with good money

data type.

But yeah, you know, money data
type is what people shouldn't

use in Postgres.

This is...

Nate: Yeah, no, I think we're mostly
in numeric, but at this

point we're kind of cursed.

We didn't lock down the precision
more tightly because Postgres

will give you far too much precision.

And kind of having to interoperate
with any other system becomes

difficult.

And so we're starting to figure
out how to unwind some of those

decisions.

Michael: Yeah, yeah.

That is an interesting tangent.

Especially, I guess, with people
billing by fractions of a cent

and things.

So yeah, anyway, probably too much
of a tangent, or maybe we'll

come back to it later.

Nate: Yeah, I mean, in short, yeah,
it gets very painful translating

between the different systems that
do or don't have that precision.

Sometimes even your billing provider,
understandably can't bill

in fractions of a cent, which means
that how all the different

sub-products you paid for and have
those round together, might

not end up with the same total.

Michael: Yeah.

Yeah.

Nikolay: Can you tell us a little
bit about the scale?

I remember 30 terabytes on Aurora
from the blog post.

Anything like in this area you
could share to get us the feeling

of the scale?

Nate: So we have 2 primary workloads
that are running on Postgres.

The 1 we'll mostly be talking about
is our main DB, which is

a common start-up pattern where
we've got our kitchen sink database

that has nearly every dataset within
it.

And yeah, that's ballooned out
to around 30 terabytes now and

growing terabyte plus per month
at this point.

And yeah, this is, wasn't an issue
for a while.

And then depending on how our clients
leverage our platform,

you know, part of billing is generating
invoices and invoices

are usually characterized, you
know, with like, you know, a top

level total and whatnot, but then
you have a line item per product

usage.

And, you know, some of these companies
have a lot of different

products you can pay for.

You know, you look at the list
of different LLM model engines

that are available.

Sometimes you can use batch, async
versus real time, or like

infrastructure vendors who are
deployed to dozens of different

regions.

And it's easy for the cardinality
here to explode, especially

if you want to group it by a logical
application on their platform.

And we may, but we now regret,
decision to basically persist

those line items in a separate
row and a ginormous table in our

Postgres database.

How big is

Nikolay: that table?

Nate: It's about half the cluster
and growing.

Nikolay: Like 15 terabytes unpartitioned,
right?

Yep.

Yeah.

Yeah, well, I know this.

It's a pain,

Nate: Yeah.

There's some scaling problems you
get really ahead of and others

that you defer because I mean,
a lot of the technology in the

Postgres space is really fantastic
now.

Like Aurora does probably just
handle it, copy out, making sure

that you're dealing with vacuuming
and whatnot and all the other

kind of implicit thresholds.

Nikolay: Actually, I forgot to
mention that those who watch Postgres

FM regularly should watch already
by this point previous episode

with Sugu, where we discussed Multigres
and sharding.

And in that very episode, he mentioned
your case, right?

So yeah, I wanted to answer this
is what we go next to do next

week, but it was a secret at that
time.

So, and before that we also had
PgDog, Lev Kokotov from PgDog

And obviously sharding currently
is hot topic again and with

Citus in the past, there is SPQR.

Before that we had a PL/Proxy from
Skype very long ago, like

previous era.

So is it right to say that the
problem we are going to discuss

today, you wouldn't meet it if
you're on sharding schema?

If your database was sharded, this
wouldn't happen probably.

Nate: I think we've seen the app
suggest that it would be easier

to avoid it.

As part of the migration that we're
about to mention, we're moving

another table that's generated
data to a partition scheme.

And we've already seen a lot of
positive effects there, and that

you can run a lot of autovacuum
workers in parallel.

And so it allows you to much more
easily keep up with the rate

of change.

Nikolay: This is partitioning,
not sharding.

And with unpartitioned table, the
problem is vacuum is single

threaded, right?

Yeah.

So with partitioned schema, you
can raise a number of workers

for max workers you can raise it
and benefit from it if you have

a lot of CPUs and disk IO capacity.

But what I'm trying to say problems
like this they like can be

avoided completely if we have sharding
and every shard is smaller.

Every single shard

Nate: is smaller.

Yeah, and that's what I was trying
to get at.

By partitioning it, we can kind
of see the effect of, you know,

if these were all in logical chunks,
that were maybe at max 100

gigabytes, it's much easier for
the garbage collection system

to keep up.

Nikolay: Yeah, yeah, yeah, yeah.

That's interesting.

Nate: It would be even easier if
we were across many machines.

But at the very least, I think we can infer most of the effect,

even if it's all on 1.

Nikolay: Yeah.

Okay.

Maybe it's already time to dive into the problem itself, right?

Nate: Yeah.
Or maybe briefly, like, why we create the problem.

Nikolay: Yeah.

Before you go into this space, I was curious, do you have any

issues with multixacts before that at all?

Like maybe other kinds of issues like observing in performance

insights, lightweight locks, contention, lightweight multixacts,

or like a couple of them.

You never had them before, right?

Nate: Not in particular.

No, I mean, we had run into emergency out of the vacuums for

sure.

Nikolay: Yeah, yeah, that's very interesting because your path

is like definitely is different from what we observed with other

projects.

Because usually, different problems with multixacts came, and

the contention happened, and so on.

Your path is quite unique in this case, I think.

Yeah.

So let's dive in.

Let's dive in.

Nate: Yeah, I really want to describe how we create the problem

for ourselves.

And I really want Andrew to explain how we diagnose it.

Cause like, that's the most interesting part here by far.

Yeah.

As we keep seeing these tables grow, you know, we kind of realize

also do some access patterns that were introduced.

It wasn't really tenable to keep just appending data month after

month for every customer in terms of the line item table.

And yeah, this was also painful for the invoice table, which,

you know, there's a lot of right amplification between the 2.

The average invoice has, you know, dozens and sometimes hundreds

of line items, depending on how a client's using our platform.

And so invoices have to be queried a lot of different ways.

We didn't really see a way of moving that outside of a relational

store and like being able to use foreign keys and constraints

with it in the rest of our data.

It was like providing too much value to really doing anything

different there.

We did wind up partitioning it just to like enable the autovacuum

process to keep up much more easily.

But we realized we could constrain the access pattern for the

line items sufficiently that it didn't actually need to be in

Postgres.

Because I guess within Metronome,
you know, over the course of

a month, you have the notion of
an invoice that's in a draft state

where we'll calculate on demand
for you, or we're not actually

persisting data associated with
it.

Then only after you go through
a finalization, which is typically

the first of the month, because
most billing periods are going

to be monthly, that we do all the
math, do the calculations,

persist that data.

Which, you can then infer our workload
sounds pretty funky.

So we've got a steady trickle of
writes for configuration changes.

And then once a month, we have
to write out several billion rows

which you know amounts to like
1 to 2 terabytes of data.

Yeah

Michael: all on the same day.

Nate: As close to it as we can
get.

Nikolay: Yeah very different from
social media.

Very different.

Nate: Otherwise it's you know a
more typical you know we have

99 plus percent reads versus writes.

It's a lot of compute on demand.

So it's a little funky, I think
that because the high spikes

in write volume are so periodic
is why we haven't seen multixact

problems in the past.

I think we'd likely narrowly been
avoiding them because it's

so hard to observe whether or not
you're approaching them.

I feel like this is 1 of the meta-stable
states of Postgres,

if you're familiar with that, hey,
from the definition of stable,

where often things are working,
working, working, and they just

fall off a cliff or entirely lock
up.

But the basic trigger here being
we identified we wanted to make

this architecture change, and then
we were running the very long

migration process of pushing these
line items into S3, which

is what our back end store was
going to be, so their access pattern

was so simple.

But also rewriting and moving the
invoices from 1 table to another.

And fortunately, we're doing that
broadly 1 by 1, which we'll

get to why that's relevant.

Nikolay: So you mentioned performance
cliff.

This is a very good term when we
talk about multixact set,

when we had different cases in
the past where, as I mentioned,

contention happened.

And interesting enough, like we
always, like 20, 15, 10 years

ago, we often saw opinions that
foreign keys are dangerous, like

they have huge overhead, don't
use them because like they slow

down everything and so on.

But interesting that now we can
agree with it, but in very different

way, in an unexpected way, because
they, yes, they slow down

writes, but we can afford it.

And your table, which grown to
15 terabytes, is a good example,

right?

You survived with foreign keys
and you paid that price to have

good data consistency, right?

But then performance cliffs, right?

Nate: Yeah, and for that table
in particular, the reason why

we were able to move that was it
only had 1 foreign key, which

is back to the invoice row.

It was much simpler.

That table is effectively append-only.

It was a workload that just really
didn't have to be on Postgres.

I don't think we were taking advantage
of the qualities to justify

the cost.

Nikolay: So let's discuss multixacts
in general, like why we

should think about them.

Like, you know, my own story is
usually, like with multixacts,

I take some monitoring, this is
my hobby.

Take some Postgres monitoring and
we check which metrics they

have.

And usually they have transaction
wraparound risk analysis,

right?

Like to control freezing process
and to understand how far we

are from transaction ID wraparound.

My question number 1, where is
multixact ID wraparound analysis?

Most monitoring systems lack it.

But they lack it because it never
happens.

Usually, like, transaction ID wraparound
happens.

We know several blog posts and
horror stories from the past.

But multixact ID wraparound,
nobody experienced it.

So OK, Why should we think about
multixacts?

Tell us more technical details
what happened with your case.

Nate: Well, briefly I'll respond
to the point about foreign keys.

The community is right that you
don't always need them.

But I think what can be tricky
is that when you're a very young

company making lots of changes
to your product, trying to find

that product market fit and get
to the point where scaling becomes

the biggest concern.

Foreign keys are really useful to
like prevent teams from stepping

on each other and breaking your
data.

And only when those product areas
and like the platform you're

building on top of become mature
enough, can you maybe afford

to put in the, you know, like pull
request level, you know, integrity

checks that make sure you're not
changing some access pattern

or maybe build the asynchronous,
you know, like offline data

consistency check to make sure
you're not violating it.

And this is a case where we didn't
think critically about have

we made that leap from 1 side to
the other.

And in retrospect, we have.

And it's 1 of the mitigations we
took.

Yeah.

Where I guess I would defend having
a really strict schema early

on, and then as you scale the platform,
just becomes necessary

to progress some of those guardrails.

Nikolay: Yeah, to maintain good
quality of data, that's absolutely

great advice.

But let's discuss how multixacts
are created, and so on.

Andrew: Sure.

Yes.

So I suppose we should start with
what a multixact even is

and when it's used.

So basically, in Postgres, when
you have 2 or more transactions

trying to lock the same row at
the same time, for instance, when

you're using SELECT FOR SHARE or
SELECT FOR UPDATE or in fact

when you're inserting into a table
that has a foreign key to

another table, Postgres uses a
system called multixacts or multi

transactions.

And a multixact is basically a
logical object that represents

a set of transactions locking the
same row.

Each participating transaction
is called a multixact member.

And those are a little distinct,
but they are related.

And the way that a multixact
is stored is that it's represented

by a 32-bit integer ID and that's
where your wraparound issue

can happen with multixact IDs.

But in this scenario, that was
actually not the problem.

For every multixact, Postgres
keeps an offset into a globally

shared, append-only, multixact
membership space.

These are stored in the pg_multixact_members
file on disk.

You can think of it as logically
like a simple last recently

used cache that is contiguous,
so you have to append to it every

single time.

And so when you have to store these
members, you will have to

look up an offset into this theoretically
long array, and each

1 of your transaction IDs is stored
in 1 slot in this array,

and this array is indexed by a
32-bit integer.

This membership array is what it
was exhausted in our case.

So due to the way that multixacts
are created and subsequently

reaped, it is easy to exhaust this
membership space when you

have a lot of parallel transactions
attempting to lock the same

row, whether it's through foreign
keys or whether you're doing

SELECT FOR SHARE or SELECT FOR
UPDATE.

And the reason for that is that
since these multixacts are immutable,

suppose that you have 3 transactions
trying to lock the same

row.

You would first create the first
multixact between the first

2 transactions, and then the third
transaction would come in

and you would have to create yet
another multixact with the

old transaction IDs and then the
new 1.

But the membership space is not
relinquished until a vacuum occurs,

which can happen later, or perhaps
never in some cases, if you

are constantly adding new transactions
and you're super busy

doing all these updates.

And what's interesting is this.

The multi-transaction membership space cannot be reclaimed in

chunks.

It has to be reclaimed contiguously.

So suppose you have a very long-running transaction that is part

of a multixact that is very old.

It can hold up the entire vacuuming of subsequent multixacts

that are finished but are short-lived.

And it is not until that the oldest multi-transaction is finished

that the vacuum can actually reclaim all the membership space

from the old multixact to the most recent one.

And this scenario creates the ability to exhaust the multixact

membership space.

And that's what we experienced ourselves.

Nikolay: Yeah, I wanted to add one thing.

It might sound strange that SELECT FOR UPDATE also might, may produce.

You also explored this, right?

It happens only when we have sub-transactions, I think, right?

So if it's a single transaction, select for update, there is

no place for multixacts.

But if we start doing savepoints there, basically we have different

transactions inside one transaction, and then multixact mechanism

starts working again.

And I just remember, like, how many years ago?

Four years ago, I explored this when I explored another big performance

cliff related to sub-transactions.

So there I noticed this as well.

Yeah.

Andrew: That is true.

Nikolay: Good introduction.

Thank you so much.

So yeah, it's not it, right?

There's also some problems like quadratic behavior, right?

Andrew: Yes, I hinted at that before.

So essentially, if you have, let's say, five transactions that are

attempting to lock the same row, you have to create the multixacts

one at a time by essentially cloning it with the previous transaction

IDs and adding that additional transaction ID one at a time.

So now you have a multixact with two, then three, then four, then five.

And you add that all up, and that becomes a quadratically growing

use case of multixact membership space, at least until the

vacuum can actually clean up those old multixacts.

And so that's where you will find quadratic consumption of the

multixact space.

And this can happen very quickly if you have a lot of concurrent

transactions attempting to, say, insert into a table with a foreign

key that links to another table that is a low cardinality table.

So they're all trying to reference that exact same row.

Nikolay: But it's also applicable when we have also additional

long-running transaction, right?

Because otherwise, cleanup will happen, right?

Andrew: Correct, so a long running
transaction will cause the

vacuum to essentially stop reclaiming
multixact membership

space until it is finished, because
it can only do it sequentially.

Nikolay: Is it this transaction,
long-running transaction, should

it also participate in multixact
mechanism or it can be any,

even SELECT, simple SELECT?

Andrew: A simple SELECT probably
won't do it.

It would have to be in a multi-transaction
context.

So if you, let's say, have a multixact
and you have 2 transactions

in that and the first 1 finishes,
that multixact is not cleaned

up until that second transaction.

Part of that multixact is also
finished.

So all transactions in the multixact
have to finish before the

vacuum can reclaim it.

Nikolay: Yeah, so this highlights
pieces of advice like partition

your large tables, avoid long-running
transactions if imposed

with OLTP, right?

Like just yet another reason to
avoid long-running transactions.

And if it's, yeah, if, so long-running
transactions on replica

reported by, hosted by feedback,
they are harmless here.

So, right, so like again, yeah,
yeah.

I'm trying, we are debating right
now how to properly monitor

long running transactions because
there are different effects,

negative effects, and we need to
distinguish them.

In this case, yeah, we should monitor
probably long-run transactions

which participate.

And by long, how much long was
it in your case?

Like 1 minute is long or 30 minutes
is long in this case?

Andrew: I'm actually not sure.

I don't actually have the metrics
on that.

Nate, do you know?

Nate: I think 99.9%, probably 9%
of our queries are sub-second.

And I guess I previously worked
on a Database and for a company,

or for a team at Stripe, and had
some of these lessons, just

like really deeply, or have all
the scar tissue in short.

Running a platform at their scale
is difficult.

And so, I've been pushing us in
that direction and kind of moving

anything that we expect to be longer
off of the online workload

into our lake house.

And so it's very, you know, occasionally
we have queries that

go 5, 10 seconds, but I believe
we have a like, you know, global

time of about 30 seconds.

So I guess like part of my misunderstanding
here is I thought

Part of these multixacts is that
the multi-transaction can

be daisy-chained as you have many
concurrent overlapping transactions

occur.

I think that's more what you're
running into than a long running

1.

Was that we just had this steady
stream of overlapping transactions.

And until you break that chain,
you're in this like, host position

where you keep, you know, very
quickly at an increasing rate,

consuming space.

Andrew: Yes, I was only merely
stating the cleanup scenario and

the challenges there, but you're
absolutely correct.

When you have this daisy-chaining,
you know, constantly creating

new multixacts by adding new transaction
IDs to it, you will

very quickly exhaust your membership
space.

Michael: You say very quickly and
very easily, but I mean, we

are still talking about 2000000000,
right?

Like, I think that's a, there's
been a debate in the Postgres

world about 32-bit versus 64-bit
for a long time, but we are

still talking about 2000000000.

So You say very quickly and very
easily at your scale, right?

Like this is also due to how many
things you're doing and maybe

the pattern as well, like invoices
that have lots of line items

or, you know, like it feels like
there are specifics here that

make this more likely.

I'm not saying it shouldn't be
a problem, I'm looking forward

to hearing how you diagnose this
and what we can do better.

But I feel like we should say it's
not necessarily very easy

to hit this limit.

I'm not actually, I think I found
1 other Stack Overflow post

or somebody else who hit it I don't
know if you came across that

while you were while you were in
the middle of this But I couldn't

find any other examples on the
mailing list, but it's super interesting

I think we're seeing more and more
Companies using Postgres at

this scale Like Nikolay mentioned
a few of them that we talked

to, but we also had an episode
with folks from Notion and Figma

and Adyen as well on that same
episode.

So there are a lot of high volume
companies now on Postgres.

So I think this stuff is super
interesting, but 2 billion still

seems like, even with the daisy
chaining, it still seems like

a lot to me.

Nikolay: Let me disagree with you.

There is no, like debate is not
like 64 or 32.

Postgres desperately needs 64 transactions.

And for example, OrioleDB already
implemented it.

But OrioleDB is like alternative
storage engine and it's right

now in hands of Supabase in terms
of development.

And I think right now there is
launch week and they published

some benchmarks again, like it's
great, great project.

So I think many projects dream
about the day when we will have

64, not 32.

Right now only several like forks
implemented only.

There are commercial forks which
implement 64 transaction IDs

and multixact IDs.

Right?

Michael: I didn't know that.

Sadly not Aurora it sounds like.

Nikolay: Yeah.

So 2 billion is not a lot already.

It's not a lot.

Andrew: Yeah.

It's pretty easy to hit it if you're
going at scale and you're

hitting it with quadratic velocity.

You get to 2 billion quickly.

It does sound like a big number,

Nikolay: but it's not that big.

Michael, sorry for interruption.

Michael, you can take chess board
and start putting 1 grain on

first cell.

And so.
Michael: Oh, yeah.

So wait, maybe I misunderstood.

If there are 5 row changes we're
trying to do, do we create 2

to the 5 rows in this table?

Or is it like 1, then 2, then 3?

Is that only 5 rows?

So in the member space?

Andrew: Yeah, good question.

So basically, it's more of a sequencing
thing.

So essentially, you have 2 transactions.

You start with 2.

You get 1 multixact taking up
2 members.

The third transaction comes in,
you create a new multixact

with 3 members, but that old 1
with 2 members has not been cleaned

up yet, so you're only taking up
5.

The third 1 comes in, I'm sorry,
excuse me, the fourth transaction

comes in, you create a multixact
with 4 and 3 and 2.

So now you've added an additional
4 on top of that.

And now the fifth 1 comes in, and
now you have a multixact

with 5432.

And that all add together, it takes
up membership space until

the vacuum can come up and reap
those old multixacts.

Nikolay: Yeah, chessboard example
is good here, I guess.

We can try to understand volume
when we approach 64 cell, number

64, how big it will be.

Michael: Andrew, you've done such
a good job describing how this

could happen, but when you're in
the middle of this, I'm guessing

this is relatively new knowledge,
or like when you're in the

middle of this, how did you work
out what on earth was going

on?

Andrew: Yes, that's quite the journey.

So I'm actually really new to Metronome.

I just joined in April.

So when this incident was occurring,
it was only my fifth week

or so.

And this was occurring on a Saturday
right before a vacation

to Miami.

So, wow.

Nate: Well, let's rewind a little
bit, because I guess the most

embarrassing part of this is like
the first, what we now know

was the first occurrence, was the
full week prior.

Andrew: Yes.

Nate: Where our migration workload
had basically implicitly triggered

this.

We hadn't really known what to
do.

And unfortunately we're not able
to get to a full RCA.

And so, yeah, we got to our best
explanation, which is like,

oh, there's something in transaction
ID wraparounds that we're

not grokking correctly, or Amazon's
hiding something from us.

And then we proceeded to turn it
back on with a slightly lower

concurrency, you know, 5 days later.

And so like, yeah, so we didn't
get there immediately.

But then, you know, that was a
P1 incident, you know, internally,

tried to be cautious, but didn't
follow through sufficiently.

And so this is like kind of in
the second chapter where Andrew

comes in.

Andrew: Yes, that's right.

I was not a participant in the
first 3 incidences.

It was only the fourth 1, which
I think was on a Saturday in

which the call went out from Cosmo
to see if additional engineering

resources could come and jump on,
so I decided to.

But having been so new, I actually
didn't have any tactical knowledge

I could use to address all the
specific fires that were being

started by this.

So I decided to actually figure
out what exactly was happening.

And as Nate had said, we weren't
really sure, but we knew it

was related to multixact in some
way.

We just weren't really sure exactly
why.

And we were struggling to really
fully understand because in

our initial evaluation, we saw
that the number of multixact

IDs that was being created was
well below the threshold of like

400 million.

And we weren't really sure why.

So what I did is I took that knowledge
and I just went ahead

and looked into the Postgres source
code and looked at the multixact.c

file, which is this enormous
file that contains pretty much

all the logic that you'll need
to know about multixacts.

And in there, we were working with
the hypothesis that somehow

this threshold of 400 million,
but we were only seeing 200 million

before vacuums started occurring,
was related in some way.

So I found the function called
multixact member freeze threshold

which calculates the threshold
that you should start basically

an autovacuum based on the number
of multixacts that are in

use and newly the number of multixact
members that are in use,

and that's what caught my eye.

So from there, I mathematically
proved that the amount of members

should be extremely high given
the code path that we are obviously

hitting, because we're seeing vacuums
occur at a specific number

of multixacts being used.

And from there I was able to find
log messages related to multixact

membership exhaustion and correlate
that with Datadog logs that

we had found ourselves in the recent
week.

And there we could conclude that
multixact members were being,

the membership space was being
exhausted.

And we were able to put together
what I would consider kind of

a hacky estimate of the number
of multixact members that are

currently in use by looking at
the files that are written for

each member and estimating by multiplying
by a constant how many

members are currently in use at
the moment.

And that's how we came across the
solution, or the cause, I should

say.

Michael: I think this is worth
diving into a little bit because

I definitely didn't realize that
there were at least 2 exhaustion

possibilities with multixact
so we've got the the total well

yeah so what why are there these
2 numbers like why do we have

2 there's 2,000,000,000 that you were
monitoring at 400 million,

which should be super safe, right?

Because we have the possibility
to go up to 2,000,000,000, but you

want it to be cleaning up much
sooner than that.

So you monitored at 400 million
or so.

Why is there another limit?

Andrew: Yeah, so we have 2 limits
here.

We have an ID that we give to multixacts.

That's a 32-bit integer.

So, of course, you cannot assign
more than 2 billion to these

multixacts because we don't assign
negative IDs.

And then you have the multixact
membership space.

Now that is represented as a logically
a simple LRU that is append

only.

And so we can only address up to
32 bits of membership space.

Each membership slot is addressed
by a 32-bit integer.

And thus now you have 2 different
values that you have to worry

about.

You have to worry about how many
multixacts you're creating and

how much membership space you're
consuming with those multixacts.

And that's where those 2 numbers
come from.

Michael: And 1 of the great things
in your blog post you mention

is how do you even monitor that?

Have you worked out a way since?

Is there anything you can do there?

Andrew: Yes, I think I might have
just offhand mentioned it,

but we use an estimate where we
read the membership files because

they are written to disk, And then
we know that the files have

to be a certain size and we multiply
by that constant, whatever

the size of these members are,
and we get an estimate for the

number of members.

Now, this is not the best solution,
which is why I submitted

a Postgres patch to surface this
telemetry directly.

Michael: Nice, Thank you.

Nikolay: Yeah, that's what I wanted to mention.

We have several things going on in mailing lists after this blog

post.
By the way, before this, why did you decide to post in so great

detail?

Like, what was your intention?

To help community or like?

Nate: I guess a couple of factors.

1, we do want to help the greater Postgres community.

I think there are lots of other situations where we've benefited

greatly by blog posts put out, in particular by companies like

Percona, as well as others who have maybe led us away from paths

that would have wound up being painful.

But another part is that internally at Metronome, we view ourselves

as a infrastructure provider for our clients.

And while I think it's okay to say that we're not fully recognized

as that yet, and we're still on our journey to solidifying that

narrative, it is what we want to, or how we want to act to be

perceived that way.

And we are in a critical path for a lot of our clients and how

they operate.

And so for us to have multiple multi-hour downtime in the space

of less than 10 days is pretty bad and honestly not what you

want for your infrastructure provider if you choose to be in

that way.

And so having a really crisp explanation of, hey, we are a little

embarrassed by this, but here's what we learned so you can learn

from it, as well as a little bit of vetting of, we didn't run

into the easiest thing.

It is novel.

There is a reason why it took a while for us to sort it out.

And so I think a lot of our partners felt a lot better.

Better could be a strong term.

But when you have an honest RCA, and Cloudflare might be leading

the industry in how they operate in this vein, you feel a lot

better that your provider is actually learning from mistakes

and is going to do a better job of avoiding them than you might

internally if you're evaluating, should I just build this in-house

instead?

Nikolay: So great transparency and so on.

It pays off over time.

Yeah, that's great.

So I saw several mailing list discussions, but before that I

also like, I feel like with multixacts, we can have different

kinds of problems.

1 is wraparound, right?

Okay, as I said, I never saw it.

And that's why many monitoring systems even don't monitor it.

And we have several cases of SLRU limits reached, right?

And 1 of them was explored in the past.

We can provide maybe blog posts, links to description.

And this is your case.

And your case obviously bumped into lack of observability bits,

like how much of capacity we reach, like how far from the performance

cliff we are, right?

But also besides that, I saw different topics were discussed

in mailing list.

For example, there is a setting vacuum_multixact_failsafe_age,

which is about wraparound, actually.

Peter Geoghegan brought to attention that this setting doesn't take

into account multixact membership exhaustion.

So it's also an interesting side effect from your blog post.

Maybe fully unexpected, right?

Then I saw, Andrew, I think your proposal to have multixact

monitoring, like system view, so any monitoring could have some

graphs and also alerts probably, right?

And this proposal currently at what stage?

I saw it's in commitfest, but it requires review, right?

Andrew: Correct.

Yes.

The patch is in the commitfest and, it definitely still requires

review.

We've, well, I've engaged with another contributor named Naga,

who's, we've actually submitted competing proposals.

His, actually directly reads from the, the memory space while

mine uses the pg_stats infrastructure to surface this telemetry.

But yes, it is still just in review.

It has not been accepted yet, as far as I can tell.

Nikolay: Yeah, so but it's great that it's registered in commitfest

and I think if some people who listen to us and maybe some people

who watch also our sessions of hacking we do with Andrey and

Kirk sometimes almost every week.

We missed today because of some issues, but we are going to be

back next week.

So we show how easy it is actually to test.

You take Cursor, you like tell it, just compile Postgres, apply

patch, and let's start using it.

And just you, instead of diving too much into C code, you think

about use cases and how to, for example, emulate problems, right?

And so on.

This is actually fun.

And I encourage our audience to maybe to start with their first

review and test this patch and this can increase chances that

it will be included to Postgres 19, right?

Andrew: Yes, what's also kind of interesting is I know you earlier

mentioned that there was a debate about moving to 64-bit integers

and in fact part of this commitfest does include a proposal

to change those offsets to 64-bits.

So there's a lot of changes that are coming in 19, it seems,

if they're accepted, of course,
which would help alleviate this

multixact membership and multixact
issue writ large.

Nikolay: That's great, but it's
only next year.

Andrew: I suppose we can't go too
fast.

Michael: Well, and even longer
if it's Aurora, right?

They have a generally a bit of
an additional lag to getting major

versions because it's a fork that
they have to do a lot of work

on.

On that note though, you mentioned
trust in an infrastructure

provider and how issues, big issues
can rock that trust and you

need to see some maybe root cause
analysis or maybe some action.

Has this at any point rocked your
confidence in Postgres or less

so Aurora, I guess, in terms of
is it the right platform as you

scale?

Maybe initially and then since
you've worked out exactly what

the issue was, what are your thoughts
there?

Andrew: Yeah, I don't think it
actually has really rocked our

confidence in Postgres.

I haven't seen anyone run for the
hills yet.

But you know, it definitely surfaces
the fact that there is quite

a bit to learn and that this is
a very complex system But to

that end something I've learned
at least Participating with the

Postgres dev community and being
on the mailing list is that

there's a lot of very smart individuals
who are part of this

community and who are actively
contributing and are passionately

debating what should and shouldn't
be part of it and how things

should be designed.

So, in many ways, it actually may
have built my confidence to

see so many people paying attention
and caring about it from

all across the world, from Fujitsu,
Amazon, Google, ourselves,

even just regular folks doing it
in their hobby spare time.

So in many ways perhaps it's a
testament to how far open source

can go and how much confidence
we can have in these systems because

like you said this is a pretty
rare instance and many of the

core cases that most people need
to use are covered or at least

will be.

Michael: That's really good to
hear and of course when people

hit issues with proprietary systems
like SQL Server and oracle

I'm sure when they blog about it
those conversations do happen

internally at Microsoft or oracle
but you can't see them you

can't see the the actual conversations
and part of the way you

diagnosed this issue was diving
into the source code so there

are some really cool factors that
wouldn't have been possible

if this was an open source.

Yeah, that's not just open source,
but open discussions as well,

open mailing lists.

So yeah, that's really good to
hear, but it would have been very

understandable if...

People have definitely left Postgres
for less good reasons.

So not naming names, Uber.

Andrew: Yeah, I will say, as we
said in our blog post, 30 terabytes

is not a small amount of data to
move.

And so if we decided to make a
large decision like that, I believe

we would have to reckon with the
transition itself.

And so in many ways, you know,
we probably want to try to work

with what we got.

And thankfully, I think it's served
us well for the most part.

Nate: Yeah, there's something to
be said for the devil you know.

And, you know, there's lots of
other systems Postgres compatible

that, you know, promise you the
world and something gives typically

whether it's the price, the actual
performance on either the

read or the writes or how maintainable
it is to actually run

it.

And yeah, I think it's clear that
we're going to have to undergo

the investment to figure out what's
next, because we can only

go single writer for so long, especially
with the demands that

we put on the database on the first
of the month every single

month.

A great extreme.

Nikolay: Yeah.

And behind the scenes, we also
chatted with Nate about the formula.

So Michael, for the first time,
we should include some formula

in the show notes.

There's a formula that is saying
that roughly 63,000 transactions

overlapping is enough to reach
a 2 billion limit.

Nate: Yeah, and it's a really dangerous
thing of some math on

the fly that can be checked by
anyone after the fact.

But yeah, as I worked it out, yeah,
only 63,000 overlapping

transactions, which is not that
many.

Relatively easy to do depending
on how you, you know, with a

highly concurrent workload operating
1 row at a time with a potential

overlap between them.

You can consume that really quickly.

Andrew: It might not also be transactions.

If you take a checkpoint, that's
almost seen as like a sub-transaction

and will also create a new multixact
as well.

So if you're doing a lot of checkpointing,
you can easily hit

that as well.

Nikolay: Are you using sub-transactions?

Andrew: I'm not actually sure myself,
but it is a possibility.

Nate: I don't believe we are, at
least not intentionally.

Nikolay: I think right now in the
latest versions of Postgres

there's ability to change SRU sizes
to avoid SRU overflow cases,

But my opinion about sub-transactions
in projects like this,

at this scale, remains just avoid
them.

Yeah, Because they can bring other
problems as well, like sub-transfer

can be overflown and so on.

And they consume transaction ID.

So yeah.

Good.

That's I think quite good deep
dive, unless we forgot anything.

Michael: Any last things you wanted
to add?

Nate: I think we can talk about
the mitigations, just so that

if someone's unfortunate enough
to run into this, that they have

a few breadcrumbs for how to work
around it.

Because it was rather expensive
for us to figure out what those

were, including Andrew and others
digging through the source

to really understand it.

But a lot of what we inferred after
the fact was, so we had this

migration which was doing a bunch
of row by row writes, not ideal

if you're doing a highly concurrent
process here.

And so we did make those batches.

We also did a lot of deep thinking
on which of the foreign keys

we actually needed on this new
table.

Nikolay: Batches, How did you find
the ideal for you batch size?

Nate: I don't know that we're at
an ideal, but if we climbed

the hill far enough, that it's
not a problem.

Nikolay: I see.

Nate: And so it was kind of a trade-off
between what throughput

we could obtain and making sure
that we weren't having the kind

of like overtime growth in our
heuristic for the member space

utilization, which is mainly based
on how many of the offset

files existed.

Yeah,

Nikolay: I usually say choosing
batches, choose the biggest possible,

but avoid locking rows for too
long to affect users, because

users, we know perception is 200
milliseconds, so if you lock

like 1 second should be maximum,
2 seconds should be absolute

maximum for batch size choice.

Nate: Yeah, it's less often.

So we're even batches around like
200,

Michael: which

Nate: is enough to mitigate this
concern and kind of keep all

of the operations up, you know,
short, since there's a very online

workload hitting this as well.

Nikolay: This is not users in terms
of batch transactions, but

users read results, right?

Or no?

Nobody's using

Nate: it now.

So sorry, this is in the context
of our greater migration to

move line items, line item construct
out of our database, as

well as partitioning the invoice
table, which has also grown

to be quite large.

And so, you know, kind of we would
read the old version, all

the line items in the invoice row,
write those to a new format

in S3, which is ultimately keyed
by the invoice ID.

And then a lot of logic, like we
can't just do a deterministic

mapping because then you get hot
partitions in S3, so you have

to do a little bit of extra work
and leave yourself a link in

the actual database row.

And so then we were writing those
out to the new table, which

is partitioned 1 by 1.

And our diagnosis, and it's a little
hard to confirm this, because

again, there is no observability
bit here, is that that was the

big mistake, as well as the single
inserts, like very quick.

It was the issue that they all
reference a handful of other tables

for foreign keys, some of which
were very low cardinality.

So, you know, we have like some
enum tables and we, you know,

for better or worse, in this case
worse, had chosen to use a

foreign key reference to them.

And so if every invoice is corresponding
to the USD credit type,

for example, you're going to have
a lot of overlapping transactions

all accessing that row to verify
the integrity of the data.

And so we kind of walk through
it, and we realize a lot of these

low cardinality cases, we don't
really change those code paths.

We're quite confident they're going
to be stable.

It's only the higher cardinality
cases.

An invoice also references its
customer that owns that invoice.

But that's a much higher cardinality
connection.

We're much less likely to have
overlapping transactions referring

to the same customer in a migration
like this.

Nikolay: Without foreign keys,
did you think about periodical

checks of referential integrity,
like asynchronously?

Nate: Yeah, so we're going to be
adding those to run on our Lakehouse.

So yeah, we have a syncing process
that brings all the files

over into Parquet.

Eventually, we're going to finish
this way on getting it to be

CDC, so it's more incremental,
probably using Iceberg.

And so on those, we'll run every,
I mean, it's much cheaper to

run the checks there, for a lot
of reasons.

Nikolay: More and more reasons
to move data out of Postgres.

I understand it for analytical
reasons, but yesterday S3, yesterday

S3 released vector index.

If you haven't heard it, it's interesting.

So I'm just curious where the place
for Postgres will be in our

systems.

But I totally understand, For analytical
workloads, definitely.

Nate: Yeah.

I mean, everything that's OLTP-shaped
is going to be staying

in Postgres for some time.

Can't see a reason to move it anywhere
else.

Nikolay: Well, yeah.

Michael: Great.

In terms of mitigations, did you
tune any of the settings around

multixact parameters?

Yeah.

What did you do?

Which ones did you increase or
decrease?

Andrew: I don't know if there was
actually any specific like

Postgres like flags that we changed actually.

I don't think we changed any constant specifically.

But what we did do is we did introspect into the code base and

determine the danger levels for membership spaces.

And so we used that as a threshold for alerting ourselves with

Grafana.

And so if our membership space consumption breaches a certain

level, what we will do is we will take actions to reduce the

number of concurrency, the amount of concurrent Transactions

that are occurring until Postgres can recover that membership

space.

So it's a little bit of a manual intervention at the moment,

but I don't think there's any specific knobs that we can tune

because it is fundamentally limited by a 32-bit integer.

And the telemetry with respect to multixact membership numbers

is not used anywhere else except in the detection of when autovacuum

should occur.

And that is sort of automatic, so, and kind of hard-coded.

Nikolay: Yeah, and the current utilization, you use function

pg_get_multixact_members, right?

Or how do you?

Andrew: No, I don't believe that is it.

I think it's some specialized function where we actually have

to get all the files and read the file sizes themselves.

So it's not something from Postgres, I don't believe.

Nikolay: But it's Aurora.

You don't have access to files.

Andrew: There is some function.

I can't remember, Mnid.

Nikolay: It's JLS or something, right?

So it lists files.

You can still SQL.

You can list for, oh, OK, interesting.

Nate: The Query originated from Amazon blog posts.

I'll try to pull it up.

You can see if you want to include it in the show notes.

Nikolay: Let's make sure.

I don't remember this recipe was shared in your blog post, how

to monitor this.

Andrew: It was not, we did not post it there.

We actually, intend to have a followup with a more engineering

based audience where we would detail these steps kind of in the

same way that we detailed it here, but in a more concrete format

for engineers and not for executives and company leaders.

Nate: Yeah, and we were a bit more timeline constrained for initial

RCA.

And we are optimistic that we can link to Andrew's patch for

the second follow-up blog post.

Nikolay: Exactly.

That's awesome.

Because people like, probably we
should add it to monitoring

like practically everywhere in
large systems, right?

This would be main outcome of this
discussion probably, right?

For those who listen and have big
systems.

Andrew: Absolutely.

Nikolay: Before your patch Andrew
got accepted and everyone upgraded

to Postgres 19, which will take
some time.

Right, so...

Andrew: Yes, until then we definitely
have some guidance for

monitoring systems.

We admit though that it is not
the most ideal, but it is something

that people can use to guide themselves
away from disaster.

Michael: Well and I think work
well there's the thread you mentioned

from Peter Geoghegan he's somebody
that has worked in the past on

mitigation factors.

So not only to be able to monitor
a transaction ID wraparound,

but he did some things to vacuum
so that it's just less likely

to get into that state in the first
place.

And I think making that parameter
he mentioned aware of the member

space or have some different parameter
that kicks in and does

a, like an anti-wraparound vacuum
or whatever the equivalent

would need to be earlier would
make this less likely to happen.

And of course, monitoring is a
good idea anyway, but prevention

seems even better.

So yeah.

Nikolay: Optimization inside Postgres
could be implemented here

Michael: because it's

Nikolay: quadratic behavior, at
least it could, as like in other

cases in Postgres, at least it
could be split to some pieces,

like 128 pieces, you know, something
like this.

I don't know.

I haven't seen discussion of actual
optimization to get more

capacity.

Have you seen it?

Michael: No, but I was looking
through what parameters we even

have that mention multixact,
and 1 of them is in version 17,

we got multixact member buffers,
which is quite low by default,

only 256 kilobytes or 32 pages,
and we got multixact offset

buffers, also low, 128 kilobytes
by default.

So I'm wondering whether increasing
those would help with the

member space maybe that's the offset
buffers 1 and then yeah

we also have multixact failsafe
age which you mentioned version

14 that's the 1 for Peter Geoghegan
and 2 much older ones I did wonder

about Freeze Min Age, that's 5
million by default, I wondered

if lowering that would just get
you at, make sure that the vacuum

ran quicker when it did need to
happen, you know that kind of

thing, and then there's the freeze
table age is quite is 150

million, so I again I'm not sure
if lowering that would mean

you just get vacuums more often
but I guess when we're talking

about such a huge table it's still
going to be a problem with

the vacuum does so until you get
partitioning in place anyway

thank you so much both of you it's
been amazing learning about

this scary but great and thank
you for paying the pain and the

cost so that so many fewer people
don't have to in future.

Nikolay: And special thanks for
transparency and admitting like

your own mistakes in the past.

Like I especially appreciate this
like how you talk freely about

your own mistakes.

This is very valuable and a good
example for others as well.

Andrew: Absolutely.

Thanks for having us.

It was a pleasure to be here.

Nikolay: Thank you.

Nate: Yeah.
It's really fun chat.