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

all things PostgreSQL.

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

Nikolay, founder of Postgres.AI.

Hey Nikolay.

Nikolay: Hi Michael.

Michael: And this week we have
not 1, not 2, but 3 special guests

with us to celebrate episode number
100.

And we asked you, our listeners,
for ideas for this episode.

And we got some great suggestions,
including the topic of scaling

to 100 terabytes for episode 100.

So we thought that...

Nikolay: And beyond.

Michael: Oh, and beyond.

Yes, always beyond.

Yeah, no downtime allowed.

We're delighted to welcome 3 people
who have successfully scaled

Postgres exceptionally well.

And those are Arka Ganguli from
Notion.

Hey, Arka.

Arka: Hey, good to be here.

Really excited to talk about Postgres.

Michael: And hey, Sammy, Sammy
Steele from Figma.

Sammy: Thanks so much for having
me on.

Michael: And finally, Derk van
Veen from Adyen.

Derk: Yeah, thank you very much
for the invitation.

I'm honored.

Michael: We are honored to have
all 3 of you.

So yeah, thank you all for joining.

So we're gonna try a kind of panel
round table style discussion.

I'll play good cop trying to keep
us on track, but you are all

welcome to chime in with questions
for each other.

And I know Nikolay will as well.

So, let's start on the simpler
side of things.

And starting with you, Derk, would
you mind letting us know what

kind of things you're using Postgres
for at Adyen there?

Derk: Well, that's an easy question.

We keep our data within Postgres
databases.

The thing is we are processing
financial transactions.

And the thing about financial transactions
is you'd better not

lose them.

Because if you have a social media
kind of platform, right, and

you refresh your page, and you're
missing a picture and refresh

again, and the picture's there,
nobody really cares.

But if you refresh your bank app
and your money is gone, then

people, for some reason, they kind
of freak out.

So yeah, we try to not lose a single
bit of data within our Postgres

databases.

Nikolay: So you don't use data
type money as well, right?

Derk: No.

Okay.

Michael: Nice.

Thanks so much.

Sammy, how about at Figma?

Sammy: Hi, everyone.

I'm Sammy and at Figma, our platform
is an online web-based SaaS

software that allows designers
to collaborate together.

And you know, kind of the magic
secret sauce is this real-time

platform where you can comment
on a design and it immediately

shows up for other people.

And so all of that core data is
powered by Postgres and it has

to be really reliable, really fast
and low latency, so it can

feel real-time.

And we've grown 100X in the last
4 years.

So that's where we're getting our
100 for this episode from.

Michael: Nice, even more hundreds,
we like it.

And Arka, how about Notion?

Arka: Yeah, so unsurprisingly,
we also use Postgres to store

our data and make sure that it
continues being there when our

customers expect it to be.

For those of you that don't know,
Notion is kind of like a collaborative

workspace.

You can use it for documents, wikis,
product management, and

it has a lot of AI built in recently.

So really it's like the place where
a lot of customers store

their entire knowledge base and
run their company or their personal

use cases as well.

Nikolay: I just wanted to mention
that I'm user of all 3 companies

products, because for example,
Notion, I'm a big fan of lists

and so on, so it's a great thing
for that, for such kind of people

I am.

And Figma, I actually remember
I created a lot of interfaces

in Figma myself.

It's super cool, too, and especially
this real-time thing as

well.

And somehow I noticed Figma replaced
all things I used in the

past.

And Adyen, I just told Derk before
we started recording that every

time I go with my daughter to Legoland,
I see Adyen on payment

machine when I pay for something.

And I think, oh, Postgres processing
is there.

So it's cool.

Thank you for coming once again.

Michael: I love how humble you
all are, assuming people might

not know who your companies are
as well.

It's nice.

Let's flip the order and let's
look at how you set things up

Postgres-wise.

So how things were in the past,
how things are set up now.

I've realized this might be quite
an in-depth answer and any

future plans.

Perhaps as well.

So maybe at the highest level,
you can describe that would be

awesome.

Okay.

We restart on your side.

Arka: Sure.

So I joined Notion about a little
bit over 3 years ago.

And when I got there, we had actually
just finished like our

first sharding effort, but essentially
at Notion, we run a pretty

simple deployment.

We use RDS, which is Amazon's managed
database offering.

We run Postgres on that.

And we run pgBouncer in front
of our RDS cluster for connection

pooling.

And that's pretty much it.

We do have sharding, like I mentioned,
which basically is just

like splitting up the data across
more databases.

And the logic for how to talk to
the database is actually controlled

by the application.

So depending on some ID, we know
what database to send your query

to.

And yeah, that's kind of how it
is even today.

We have a few more databases than
when I started, but the overall

high level architecture is pretty
much the same.

Michael: Nice.

I've read both of your sharding
and resharding blog posts.

I'll link both up in the show notes.

But I'm guessing that will continue
to work.

Any reason it won't continue to
work for the foreseeable?

Arka: Yeah, I think our plans are
not really to move away from

Postgres.

It's worked really well for us
so far, and we've got a lot of

people at the company now that
have built up a lot of knowledge

around how to operate this thing
at scale.

So definitely something we want
to stick with moving forward.

Probably we do have to re-shard
in the future at some point,

but in the longer term we're actually
looking at moving to more

of like a cellular architecture
where we can kind of deploy Maybe

like different versions of Notion
to scale our capacity with

like their own databases and caches
and things like that.

So we don't have to continuously
spend our time re-sharding over

and over again, because that's
a very time intensive and operationally

intensive kind of thing for engineers
to take on.

So we probably do have to re-shard
in the near future, but in

the longer term, we hope our more
like cellular architecture

will be able to scale much further.

Michael: Super interesting.

Sammy, let's go to you.

I know you're sharding as well,
but slightly differently, I believe.

Sammy: Yeah, well, we definitely
have a lot in common with what

Arka just described.

And so your blog posts were a great
inspiration for us as we

were in the early days of horizontal
sharding.

So similar high-level stacks, and
that we're also on RDS Postgres,

Amazon's managed system, and we
also use pgBouncer for connection

pooling, at least for now.

And where we start diverging from
Notion is that I think our

data model is somewhat more complex
and less naturally a good

fit for sharding.

My understanding is Notion had
shard IDs based on the orgs or

the workspaces and that that worked
pretty well.

But at Figma, we have a lot of
data not associated with an org

and data moves quite frequently
between orgs, which makes that

kind of sharding model quite hard.

And so we actually ended up going
with a more tactical approach

of, for each table, picking a set
of a few shard keys that would

be more optimal.

And we ended up building a proxy
layer so that instead of the

application having to be aware
of shards, we actually have this

DB proxy service we built, which
is a Golang service.

And it's able to do all the routing
and handle all of the horizontal

sharding, scatter gathers where
a query hits many shards, and

then you aggregate results back.

And so that's kind of a superpower
for us to have a lot more

control in that layer.

And long-term, we have sharded
our simplest, highest write rate

tables, but we still have a ways
to go until everything is sharded.

And we have to build out a lot
more infrastructure to support

that.

So unlike Notion, we actually expect
restart operations to be

quite common.

And so our goal is like 1 click
failover where you can just hit

a button and then have a restart
operation happen and transparently

in the background.

Nikolay: Sounds like logical replication
involved, but maybe

we'll talk about that later.

Derk: I'm so interested in this
proxy layer.

Did you build it yourself in-house?

Sammy: We did.

Yeah.

So it's a Golang service.

So it has a couple of components.

It has a query engine, which has
a parsing layer that takes in

raw SQL and transforms it to an
AST.

And that part we did borrow CockroachDB's
Postgres parser, which

has mostly worked well for us with
the few modifications, but then

all of the logic for, we have like
a topology layer that we've

built that knows where all the
databases are and what shard keys

map to what tables.

And so the query engine really
does all that evaluation and query

rewriting.

Derk: Yeah, that sounds pretty
mind blowing to me.

It's really cool.

Nikolay: Yeah, there are a couple
of projects like PGCat and

SPQR which

Sammy: do that,

Nikolay: but maybe when you started
they are very early stage,

right?

Sammy: Yes, actually to clarify,
so we wish we had named this

something besides dbproxy, because
PGCAT would not be a replacement

for what we have, because we actually
might use PGCAT and replace

a pgBouncer, because we are hitting
some pgBouncer scaling problems.

I'd be curious if you guys are
also running into challenges.

But dbproxy is really, it's a stateless
service that talks to

every single database.

And so a request gets routed to
it, and it has a connection pooling

layer which talks to a pgBouncer
or PGCAT-like layer that is

specific to a single host.

So it's really playing more of
a high-level orchestrator role.

Michael: So cool.

And I love that we've already got
2 different approaches to sharding,

1 that's transparent to the application
and the developers, and

1 that's not.

And I think we're about to get
a third answer from Derk's side.

How are things set up at Adyen
and past, present, future?

Derk: Let's start with the past.

I think we had a pretty, it was
our by far, it's our most well

read blog article.

And that's how to upgrade a 50
terabyte database.

And when looking back on it, it's
quite a while ago.

So I think 2 years ago, we had
an internal event and it was like,

no, it's not how you upgrade a
50 terabyte database.

It's how you upgrade a terabyte
database with multiple zeros

in the end.

Nikolay: At least 2, I guess, right?

Derk: Yeah, we have 2 zeros in
the end.

Nikolay: And not just upgrade,
but 0 downtime upgrade, or like,

I just want to clarify, because
regular upgrade is not super

big problem if you can afford a
couple of hours downtime to.

Derk: Yeah, we tried to think about
ordinary upgrades, but the

rough calculation, it will take
us like 4 or 5 weeks downtime,

which didn't cut the bill.

Nikolay: We've had links.

Derk: No, that 1 is much faster.

Fortunately, I think we limited
to 15 minutes of downtime.

And we already had a big fight
for this 15 minutes, but it has

to happen.

And it's it's not even a database
downtime, but it's more like

all the infrastructure around it
to get it fixed, which required

downtime.

If you look purely at Postgres,
I think it was minutes, but it's

not just a database.

Nikolay: I know this problem very
well.

So

Derk: Yeah, that's

Nikolay: everything around.

It's more and more minutes and
then sometimes hours, right?

Sammy: Yeah.

I'm curious, what's the biggest
individual table or do you have

partitioning under the hood there
to have so many terabytes on

1 box?

Derk: What is the biggest table
we have?

I don't know, but the biggest partition
is over 30 terabytes,

which is a single partition.

Sammy: Wow.

And you don't run into vacuuming
issues with that?

Derk: I think we are the most skilled
company when it comes to

vacuum and how to tweak vacuum.

Nikolay: And rebuild your indexes,
which blocks XminHorizon,

right?

Derk: Yeah, we have seen many vacuum
issues.

When I started to work with Postgres
and I was installing this

database and transaction wraparound
was some kind of weird theoretical

thing.

And then I joined Kotien and then
something you do every week,

at least.

Nikolay: So I yesterday asked My
Twitter audience, what would

you ask to people who manage many,
many, many terabytes?

And 1 of the questions was, how
are you?

I guess this is the case when you
have problems like 30 terabytes

on 1 table.

How long is the index creation
time or rebuild time?

It's like days or?

Yeah, days.

Days.

During which XminHorizon is blocked,
right?

So we accumulate bloat in whole
database for all tables, right?

It's a super annoying problem.

Derk: But most of these tables
are partitions.

So first we create an index per
partition, and then finally in

the end, we create the index on
the parent.

Otherwise we don't make it before
we hit wraparound.

Nikolay: Oh, wraparound.

It's also a problem, but I'm talking
about also vacuum and bloat

problems, so multiple problems
here.

Well, that's super interesting
experience.

Derk: It keeps us busy.

But it's also, it's an honor to
have these problems, but I think

all 3 of us got this big databases
because the company is very successful.

successful.

Nikolay: Right.

Derk: And I think at the end of
the day, having a big database

is actually a design problem.

You forgot to design in time for
the size you need to accommodate.

Sammy: See, I actually disagree.

I think it's a good, It's an expected
problem.

If you design a company too early
to scale, then you're not gonna

have a company probably if you're
trying to solve those problems

when you're 5 or 10 people.

I think 1 of the cool things about
Postgres is all of our companies

did get very far on 1 host.

Like Figma existed for 7 or 8 years
and was pretty successful

before we had to actually start
scaling out.

Derk: That's definitely true.

Nikolay: I agree with that.

And my team helped a couple of
companies who went to IPO being

like on a single Postgres cluster
and they got evaluation, thousands

of billions of dollars having 1
cluster and this was impressive.

Of course, later it should be changed,
but yeah, so I agree with

1 cluster you can scale a lot,
But I would not like to be in

your shoes dealing with multi-day
index rebuild and also transaction

ID wraparound problems.

So how do you escape from these
problems today?

Like 30 terabytes 1 partition,
It's quite difficult, right?

So is it to be partitioned into
smaller partitions or

Derk: Yeah, I'm thinking about
how to answer this question.

I think before I joined Adyen,
I tried to manage my database

in a proper way to do it by the
book and by the rules and everything

is fine.

And Adyen is pushing me to find
which boundaries can be bended

or violated without too much risk.

I think that's the way to put it.

We still are on the safe side,
but with more knowledge, you're

better able to decide when do I
cross a boundary and when is

it really dangerous and when is
it safe to do, but not strictly

advised.

Sammy: I think that's definitely
one of the benefits of staying

on a system like Postgres that
you end up understanding really

well is all these limits are extremely
specific to the workloads

and the tables and even what works
for one table at Figma doesn't

work for another.

And so there is a lot of just accumulated
organizational knowledge

that makes it easier to run these
systems at scale and understand

actually at what point do things
get scary.

Nikolay: Yeah, I agree.

If, for example, index creation
takes one day, but our real transaction

ID doesn't grow a lot, it's not
a big problem.

But if it grows very fast, then
we have two kinds of problems.

So transaction ID wraparound and
bloat accumulated.

I agree.

So it's very specific to particular
workloads.

Michael: I want to go back to Derk
quickly because you've done

such a great series of blog posts
on partitioning.

I'll share those.

I've reread them today and part
3 promises a part 4.

So I'm, I'm hopeful of getting
another installment at some point,

but yeah, is it, would you say
partitioning is the big, like

the way you've largely scaled there
or are there other strategies?

Like, are you moving data out?

Like how are you dealing with it?

And how are you planning to in
the future?

Derk: Yeah, I think we started
with horizontal sharding and then

within every shard, tables are
partitioned, but financial transactions,

you need to be able to refund them
for a very, very long time.

So it takes quite a long time before
you can actually archive

data.

So we have a lot of old data, which
is still around, but yeah,

these partitions kind of shift
more back to the back in the row

and vacuum is much easier if you
don't change a lot on a partition.

But at the moment we are actually
reaching the limits of our

sharding solution again.

So we basically start redesigning
the entire system again.

Arka: You know, that's really interesting.

Cause that notion like it's interesting
that you chose partitioning

Postgres as like your thing that
like got you really far because

that notion we actually ran into
I don't know like really weird

issues with dropping indexes on
partitioned tables, But that's

because I think we were running
on like, like this database was

running on like Postgres 12.

And I think a lot of these things
may have been fixed in the

future releases.

But we actually went the opposite
way where we're like, we don't

want Postgres partitions, let us
manage our own.

Because I don't know, there's some
weird behavior we ran into

when creating and dropping indexes,
especially because some things

you can't do concurrently on partitioned
tables.

Derk: Yeah, dropping indexes is
a nightmare.

I always tell my developers, you
can create indexes on a partitioned

table, but if you want to drop
them, I come after you.

Arka: Yeah.

Fun problem.

Michael: Actually, Derk, you said
something earlier about knowing

which rules you can kind of bend
or break.

One of the ones you mentioned in
those posts was around adding

check constraints.

And so adding them in a not valid
state, but then not running

validate afterwards.

I think you mentioned just updating
the system catalog.

And that's such a cool trick.

But like, how?

Yeah, I guess you have to make

Nikolay: up comfortable.

We use this trick like 15 years
ago when validate was not an

option.

So like something like that we
use it, but it wasn't like undocumented

thing.

Don't do it.

Like if you're, if you're not owning
database, For example, if

you're a consultant, this is not
something you should recommend,

because who knows what will happen
next.

If you own a database, if you work
inside the company, it's probably

okay.

It's not official.

By the way, I wanted to emphasize
problems we just started touching.

Most people who listen to us don't
have, right?

Because it's like extreme problems.

Derk: I'm not completely sure.

I think partitioning becomes useful
for a lot of people way before

you hit the limit where you have
to.

So if you're listening to this
podcast, and you think like, yeah,

partitioning is too far away, just
think again, because if you

do it now, when you have all the
time in the world to figure

it out, find a good strategy, Then
you don't end up with a huge

partition, a small partition, weirdly
partitioning things.

So I would say just starting time.

Nikolay: I'm on the same page with
you here because you mentioned

this rule, like 100 gigabytes as
threshold when you need to start

partitioning.

Same rule we also apply everywhere.

If you exceed 100 gigabytes, it's
time to partition, I agree.

Sammy: Interesting.

We have a lot of tables larger
than that that aren't partitioned,

but probably about a terabyte is
what we aim for max for our

largest shards, although we have
a 116 terabyte table today.

But another interesting topic that
I think is more relevant to

people are upgrades.

We have built this no downtime
tooling that we've mostly used

for re-shard and horizontal sharding
operations, but more recently

we've been using it for no downtime
major version upgrades with

the ability to roll back if you
run into problems.

And so I think that's something
that could be interesting for

anyone who's running Postgres.

Upgrades are always a hard thing
to do.

Nikolay: Yeah, By the way, we forgot
to mention that ADN is on

self-managed situation.

It's not on RDS, right?

Because it's very different.

Yeah.

I'm very curious how you did this
for RDS case, not using Bluegreen

deployments, right?

Just fully...

Sammy: At least today...

Well, Bluegreen only very recently
became available for Postgres.

Last year it was only really MySQL.

And so we'll probably explore it
going forward.

1 blocker that actually we're talking
to the RDS team today is

our rollback.

Exactly.

There is no way to rollback.

Nikolay: And that

Sammy: is the main thing.

Nikolay: That's wrong.

Bluegreen deployment's idea.

It should be symmetric.

And when you switch over, reverse
replication should be installed

immediately.

Without this, you don't have rollback
plan, right?

Sammy: Exactly.

It means data loss.

Yeah, so that's what our failover
operation does.

I think it's also nice to have
more control when you do it locally

because we're able to, for instance,
do a no downtime replicas

failover.

And because most of our P0 critical
workflows read from replicas,

that means that most failovers
don't actually incur any downtime.

Whereas, you know, if you're using
a managed thing like Blue-Green,

you lose that kind of control.

Nikolay: Yeah, well, I'm super
curious about details here, but

maybe it should be a separate discussion.

And first of all, I wanted to thank
all of you.

We invited you because you shared
very great posts.

So this knowledge sharing is super
important.

So I would be happy to see more
and more coming.

And maybe if we talk more and exchange
ideas, we could collect

better materials, for example,
how to do 0 downtime upgrades,

because I know very good recipe
for self-managed Postgres, but

I don't know for RDS, because they
don't allow you to control

recovery target LSN.

So I'm very curious to talk about
details, but maybe in a separate

discussion.

Because I know Michael has a different
plan, not to talk half

an hour about upgrades.

Michael: Well, maybe we could actually
do a quick lightning round

of which major version each of
you are currently running, if

you know and are happy to share.

Nikolay: Is it a single version
only?

Michael: Also a good question.

Sami, are you happy to share that
1?

Sammy: Sure, I can definitely talk
about that.

Well, we are newly, as of 2 months
ago, fully on PG-13.

So we had a couple of PG-11, very
legacy boxes.

There were some of our earliest,
largest databases, and that

actually was the main motivation
for building out this upgrade

workflow, was to get those into
PG-13.

And we would love to use some of
the features that are available

in PG-15, PG-16.

And so we're probably going to
look into fleet-wide upgrades

sometime next year to get to a
newer version.

But this is where this one-click
upgrades or failovers really

matters because today it's quite
toilsome and it would be pretty

painful to upgrade the whole fleet.

Michael: Yeah, absolutely.

How about you, Arka?

Arka: Yeah, we're actually we're
like 90% on Postgres 15.

And it is my life's mission

Nikolay: to

Arka: get us that final 10% over.

Yeah, like most of our databases
are on actually Postgres 15.

We finished that upgrade last year.

And yeah, we really wanted some
of the features that I think

got introduced maybe after Postgres
14, especially around being

able to filter the replication
stream because that's something

we want to use in the future, probably
if we want to rechart,

that'll be a very useful thing
for us.

And just wanted to touch on Sammy's
point a little bit.

We actually use the exact same
kind of, I think, failover that

you guys use as well at Figma based
on pgBouncer and with the

reversibility as well.

And that's something that has been
really, really surprisingly

amazing.

Nikolay: Yeah, you mean pgBouncer
post-resume.

I guess this is why you don't use
RDS proxy being on RDS, right?

Because it doesn't have post-resume.

Yeah, that's great.

I also was surprised last year
that it can handle really heavy

workloads.

Yeah.

Just a few seconds spike of latency,
you can upgrade and switch

over to a different primary.

Arka: Yeah, It was one of those things
where when we were writing

out the tech spec for it, I was
like, is this thing going to

actually work?

Nikolay: The problem with this,
by the way, there are no good

materials about this.

Proving like, nobody says, like
it's like public secret.

Nobody talks about it somehow,
but it works really well.

So we need more materials proving
that Pause Resume works well

in pgBouncer.

Sammy: Yeah, it's been great for
us.

We have some PgBouncer scaling
problems, but everything on the

failover's PgPause, PgResume side
has been flawless.

Michael: That's so good to hear.

And finally, Dec, on the major
version side of things.

Derk: We are fully on 13, and we're
having discussions about

the next version.

We want to upgrade too because
every version has nice things.

I want certain partitioning functionality,
A colleague wants

more logical replication functionality.

So, of course, 17 would be the
ideal, but then do we really want

to run 17 already with the biggest
clusters we have?

So, yeah, to be continued.

Michael: And I think also the fact
that, I mean, Notion being

on 15 was somewhat of a surprise
to me.

I think because of these major
version upgrade challenges, I

see so many companies, even at
the cutting edge of what Postgres

can do, lagging several years behind
because upgrades are so

difficult.

So I know we won't get a chance
to discuss all the reasons why,

but I think it's proof because
we're all lagging.

Sammy: Yeah, well, I actually think
it's not for us so much fear

of newer versions.

You know, I actually worked in
MySQL before Postgres, and MySQL

major version upgrades were truly
terrifying.

These were like 2 or 3-year-long
efforts where you found a lot

of major incompatibilities.

But one thing I love about Postgres
is it is mostly backward compatible

across major versions.

And so it's not so much that we're
scared of the newer ones as

versus we can't take downtime
to do these upgrades.

And so there it's much more manual toil.

Michael: Yeah, exactly.

I think that is, I think that's
the case for a lot of people.

So last one I was going to ask each
of you is if you have that

funny or scary, and I don't know
which one I want most story that

you can share with us related to
your scaling.

Any of you got one prepared already,
feel free to jump in.

Or Nikolay?

Nikolay: Wide-width locks, lock
manager, multi-xact transactions,

multi-xact IDs and so on, like
something like that, maybe.

Derk: Yeah, I think we definitely
got bitten by the LockManager,

like most people who start doing
partitioning.

It's like,

Nikolay: yeah, well,

Derk: we were, we started partitioning
and we, we did a good

job.

And I built this framework around
partitioning.

By the way, Michael, the fourth
blog post is, we are writing

it at the moment.

Yes.

So it will be there.

It's a nice idea.

Now I lost the rest of my story.

Oh yeah, partitioning.

Nikolay: So it sounds like we have
prepared statements.

Derk: No, we just have to prepare
statements.

But You know everything about prepared
statements, right?

You try it 5 times, then the optimizer
decides, do we go with

the generic plan or the custom
plan?

But we also created a lot and a
lot of partitions and a lot of

partitioned tables.

So we definitely ran out of logs.

And our CPU, basically every time
we created a new partition

for table, the CPU uses jumped
10%.

But jumping 10% was fine because
we had a lot of CPU to spare.

So by the time it alerted, It was
pretty high.

And then we were looking back and
we see these jumps of 10% and

10% and 10%.

And then we were looking like the
next jump of 10% that's above

100.

And then we are not in a good position.

So, then we had this small window
with all kinds of things prepared,

like don't create any partitions
anymore on this cluster.

What can we break open partition
boundaries so we can basically

extend the final partition to hold
more data?

And then we were researching this
problem, like why is the CPU

usually jumping like this?

And in the end, we started to force
the optimizer to pick generic

plans because custom plans were
faster in execution, but definitely

not at prepare time.

So we won like a few milliseconds
in execution time, but we were

paying like 100 milliseconds in
prepare time.

And that was really heating up
the CPUs.

Nikolay: Because during planning
Postgres locks all indexes and

tables, all partitions, all its
indexes if partition pruning

is not working.

So it's like, it's terrible.

Derk: Yeah, So you're running out
of your fastpath locks because

you need to lock like 20 partitions
and 20 indexes on these partitions

for a single table.

And then you join with a partitioned
table, and with partitioned table,

all these locks go to the log manager,
which is burning CPU cycles.

Nikolay: You're in a good club
of a bunch of companies who experienced

this over a couple of last years.

Derk: Yeah, I was

Sammy: going to say, we hit this
for vacuuming on our bigger

tables.

When you get above like 4 terabytes,
you start seeing that at

the end of the cycle, when you
run analyze and the cache gets

cleared out, and then you suddenly
have this custom plans that

are 5x more expensive.

And that's when CPU spikes to 100%
for a few seconds.

Nikolay: I have a question to all
of you.

When you have these problems, do
you discuss this with people

who can influence this?

I mean, hackers somehow.

Do you raise these problems in
mailing lists or somewhere else

where hackers live?

Arka: We work with AWS support
quite a bit.

Nikolay: Well, some hackers there
are for sure, but not all of

them, right?

Sammy: Yeah, we're good friends
with the AWS RDS team and they

have actually a team that contributes
to Postgres, so we try

to leverage them to get some of
these patches in.

But it's honestly a lot of us reading
the source code ourselves

as well and trying to figure out
workarounds.

Nikolay: By the way, I just last
week I think I realized that

SLRU patches created by originally
by Andrey Borodin, which will

be in Postgres 17, they are already
adopted by AWS RDS Aurora

for a couple of years, almost.

I was super surprised.

Actually, Andrey was also surprised.

But you're not going to use Aurora,
right?

Instead of regular RDS.

Arka: Maybe.

I think, I mean, for Notion, I
think we are actually starting

to look into if Aurora makes sense.

Mostly because the AWS team promises
a lot better replication

for global replicas compared to
the RDS built-in replicas.

So it's something we're considering,
but we haven't actually

used before.

So was curious if any of you have
any experience with that or

like scaling read replicas in general?

Because that's also something we
haven't leveraged too much at

Notion yet.

Sammy: Well, less scaling read
replicas, but actually my scary

story was going to be around moving
things more to replicas because

At the same time that we were growing
100X, we were dealing with

a lot more pressure on the reliability
of our system and trying

to move towards 4 nines globally
as a company.

And so our application, because
it's a web browser and we have

a kind of single threaded Ruby
monolith that serves a lot of

traffic, is very latency sensitive,
that if your database slows

down for a minute, then the whole
application can back up and

your whole website can be down.

And so that was 1 of our biggest
reliability risks and most persistent

pain points is our RDS does have
some big latency spikes on EBS

that most customers don't notice,
but we were really hitting.

And so our 2 big solutions there
were to move, basically remove

writes from our most business critical
routes wherever possible

and put all reads, or 99% of reads
onto replicas for these P0

user workflows.

And then we build out replica hedging.

So every request hits 2 replicas
and returns the first results

that are successful there, which
allows us to tolerate any 1

replica being down.

And that's been hugely useful.

We also got onto IO2 for EBS, which
has been a game changer for

EBS reliability.

Arka: Oh, nice.

How many replicas do you guys normally
have off 1 database?

Sammy: Today it's quite, there
are only 2 large replicas.

In the future we want to move towards
more smaller replicas.

Derk: We have multiple, Let's put
it that way.

Reading from a replica is much
harder when it is about financial

data because you want to have the
correct data and it might just

been updated.

So we try to move a lot of stuff
to the replicas, but it's kind

of hard.

Arka: Yeah, that would definitely
be a problem for financial

data.

Nikolay: Are we talking about asynchronous
replicas only, or

maybe some semi-synchronous or
quorum commit approach?

Derk: We have both.

Arka: Also, cache invalidation,
I think That's 1 of the bigger

concerns for us at Notion because
we're super, super heavy users

of memcache.

And reasoning about how that works
for replicas is another thing

that we don't really want to do,
but we have to do.

I don't really have a super scary
story, but actually it's something

I've been working on recently,
which we ran into some surprising

behavior.

So maybe it'd be just fun to talk
about.

So recently at Notion, we've been
trying to upgrade that last

10%.

And of course those databases are
just the hardest because they're

just so big.

So we're trying to upgrade this
partition table that is around

20 terabytes with like 16 partitions
on each database.

So, you know, each partition is
about like a terabyte ish.

And we were having trouble getting
this to work with just regular

logical replication.

So we actually started exploring
DMS, which is like an AWS service

that lets you migrate data between
two separate databases.

Side note, it's actually super
cool.

Like you can go from like a completely
random system to like

another new system.

It doesn't have to be like Postgres
to Postgres only.

I don't know how good it is at
huge scale.

Nikolay: It just requires a department
in your company to manage.

Arka: Yeah, so originally I was
like, oh yeah, I can do this.

And then it's been like 4 months
now and I'm still like, oh my

God, this is a lot.

But anyway, 1 of the problems we
ran into was actually related

to Postgres bloat.

Cause it turns out with DMS, you
get a lot of kind of knobs that

you can tune for the concurrency
of like the copy.

And that goes pretty fast.

Like we're able to copy, you know,
a terabyte of data in maybe

12, 14 hours with the way we have
our DMS set up.

But the thing we noticed after
we set up the target databases

is somehow the data size, like,
so we use PG Analyze as like

our Postgres monitoring tool.

And like, we noticed that the data
size literally on disk was

like 3 times larger on the new
tables for some reason than the

old 1.

And you know, we were going from
like Postgres 12 to Postgres

15, so at first we thought maybe
it was like a TOAST compression

thing.

Cause we know that the TOAST compression
algorithm changed between,

I think, 12 to 14 or something.

It went from like PGLZ to LZ4 or
something.

I don't remember exactly but I
know that that changed.

So I was like, oh like is it is
it the new post compression that's

causing like the data size to be
literally much larger on disk?

Which obviously leads to much worse
latency because then every

1 of your queries are doing much
more I/O.

Nikolay: Buffers.

Buffers.

Arka: Yeah, so you know we tried
a few tests with like changing

the compression back to the old
1.

We weren't fully convinced still,
But it turns out actually we

were just hitting like bloat and
this clicked when I was actually

listening to 1 of your earlier
podcasts I think on Postgres bloat

and I realized that our vacuums
weren't actually fully completing

on the new table because there
was just so many new transactions

being written the vacuum could
not keep up which meant that the

bloat just kept on accumulating
and accumulating.

And on top of that, a lot of the
data in this table was being

TOASTed because they're just JSON
B columns.

So anyway, it was just like 2 weeks
of investigation to be like,

oh, we should just run a full vacuum.

And turns out that brought the
data size right back to what it

should be.

So it was a fun little kind of
investigation.

Nikolay: It sounds like PgAnalyze
doesn't tell you about XminHorizon

and proper like things like why
a vacuum can be lagging and skipping

some dead tuples that cannot be
deleted.

PgAnalyze has a very good vacuum
dashboard.

So I'm very curious, does it speak
about

Arka: this new horizon?

It actually does point those things
out.

We just didn't have the new databases
in PGAnalyze yet, because

we're like, oh, we're not using
production traffic on these,

so we don't need it there.

When we put it there, we realized
that, oh yeah, like the vacuums

are falling behind.

Nikolay: I'm just personally myself
very annoyed that Postgres

monitoring systems lack better
xmin horizon and long transactions,

which is 2 separate things basically.

Alerts.

Long transaction can happen and
nobody tells you.

It's not the right thing if you
have sometimes 2 Postgres monitorings

and nobody tells you about this.

So I'm curious if PGAnalyze has
some alerts.

Arka: It does.

Nikolay: Okay, okay.

Arka: It sends you an email if,
for example, you have had any

transaction running for over an
hour.

Nikolay: Right, But if you use
logical, it's not the only reason

of xmin horizon being installed.

So, yeah.

Arka: Right.

Nikolay: Okay.

For

Arka: us, like, PGAnalyze has
been really great, actually.

Like, I love working with that
team.

Shout out to Lucas.

Like, he's been so amazing.

Nikolay: And also content, again,
back to content sharing.

Lucas does a great job and his
team does a great job.

Sammy: Yeah, Lucas is awesome.

I talked to him as well a few weeks
ago and everything at PGAnalyze

their blogs are really
great.

Okay, I think it's interesting
you guys had the data copy be

such a big bottleneck.

We also had a big pain point there
when we were first doing vertical

sharding.

And what we ended up finding out
is we kind of went into the

source code for Postgres and logical
replication and realized

that it's really, really expensive
to have indexes on your database

table while you're doing logical
replication, and much faster

to drop the indexes and then recreate
them afterwards.

So it went from weeks to a day
for a multi-terabyte data copy

when you drop all of your indexes
and foreign keys.

Arka: Yeah, that's a great insight.

Nikolay: Yeah, I'm also curious
if you talk about regular logical

replica provisioning when data
is copied logically or maybe,

For example, there is a binary
copy option and also there is,

if you need it for upgrades, for
example, or you need majority

of data, sometimes it's better
to convert physical replica to

logical, which I hope soon will
become standard recipe inside

Postgres itself, but now it's only
possible if you orchestrate

it properly.

I'm very curious if you do this
trick on RDS, both of you, or

no?

Sammy: We rely a lot on logical
replication for all of our core

failover tooling, so we haven't
really explored physical replication.

I'm not sure if we really can because
of RDS.

Nikolay: On RDS it's possible if
you apply so-called Instacart

recipe, but

Sammy: there

Nikolay: are debates about is it
safe to do it.

Actually, Lucas's blog posted about
it as well recently.

Arka: You're talking about the
thing where you create a replication

slot, take a backup, and then advance
the replication slot.

Nikolay: Yeah, either this or...

Yeah, on RDS only this, yes.

Arka: Yeah,

Nikolay: yeah.

On self-managed, you can play with
recovery target LSN, right?

Arka: Yeah.

Nikolay: So I'm curious, is it
only in Stackgres or what...

Arka: We've actually used that
before too.

So actually, before we knew the
trick that Sammy just told us

about with the indexes, we could
never get logical replication

to catch up ever to our tables.

So we actually ended up using the
Instacart trick to do our first

round of upgrades that I ever did
at Notion.

It worked well.

I think 1 of the problems you run
into with that though is similarly,

when you provision the new database
off the backup, you have

to do another extra step of actually
dropping the indexes again.

Because even just catching up with
logical replication from the

point that you took the backup
for a sufficiently large database

Still took us like a day or so
just to catch up the changes between

the replication time and that thing
So I think yeah that trick

could work really well probably
combined with dropping the indexes

would speed it up significantly.

But actually for us, after that,
we've always just used regular

logical replication, and it's been
fast enough if we can provision

the schemas without any indexes.

Nikolay: Actually, dropping indexes
lead us to 1 topic I wanted

not to miss.

Huge WAL volumes generated and
index-write amplification.

Anyone can tell anything about
these problems, like experience

something about it or...

Because this is 1 of the key criticism
points from Uber when

they posted this infamous article,
right?

Postgres has index write amplification.

We have hot updates, but still,
it's a problem, right?

Actually, Derk, I remember an article
about maybe a couple of

years ago about fill factor, right?

Is it still the tool you use actively?

Derk: Well, fill factor is not
not a tool, right?

It's just a parameter you can set
for your tables and your indexes.

Nikolay: But the tool is to use
it to provoke more hot updates.

Derk: Yeah.

If we lose hot updates, like we
did last week, it's like we doubled

our WAL usage.

So that's already, and that was
only for single table for some

reason, fifth, next or the new
release.

We had a longer running query,
which means pretty long because

it was holding back hot updates.

And that means like you just double
the WAL volume on your system,

but we have many tables, right?

And then this, only this single
table is responsible or becomes

responsible for half the WAL of
the entire cluster.

For me, that's an amazing number
that you can generate or skip

creating that amount of WAL when
using hot updates.

So yeah, if we are having heavily
updated tables, we always try

to find the right fill factor.

I've been trying to get this formula,
like what tables do you

need to fill factor and how high
do you set it?

And it's still a mystery to me
because we have some tables which

do fine at 95% fill factor and
some do really great at 70%.

Nikolay: And also you need to protect
this table from new indexes

being created and losing all the
hot updates somehow, right?

If developers decide to add one more
index.

Derk: Yeah, I can't.

I have no way to force my developers.

Sammy: I'm actually curious.

A big pain point we have right
now is for really expensive index

creations, there's no good way
of throttling it.

So if it eats a lot of your IOPS,
there's not really a postgres

way We've found to tune this so
that it, you know, maybe you

want the index creation to happen
much more slowly in the background

over a few days for some really
large tables.

Nikolay: But it will hold xmin
again, this infamous problem

from...

Sammy: Sure, but I mean, maybe
3 days of holding that is better

than an hour of the website being
down.

Michael: This might be a stupid
question, but would reducing

maintenance work mem in a, can
you do that in a session?

Would that help?

Nikolay: More disk I/O maybe.

Sammy: Yeah, I think we have the
minimum.

Yeah, we're only using 1 thread
for the maintenance settings

there, but it's, it has been a
coulnle of times enough to spike

our IOPS to basically the RDS limits.

Derk: I always want indexes to
be created faster.

Nikolay: Yeah.

But it requires a faster disk IO
capabilities, of course.

Michael: This is what I love about
TrueScale is you just hit

trade-offs.

You hit times where 1 thing's best
and you hit other times where

the complete opposite is best.

So yeah, I'm really conscious of
time.

You've all been amazing.

Thanks so much for joining.

I wanted to give you each an opportunity
to say anything else

you wanted to add or forgot to
say, or feel free to shout out

at you if you're hiring, that kind
of thing.

Nikolay: Let me ask 1 more technical
question.

Everyone uses form keys everywhere,
or decided to drop some of

them or everywhere, right?

Arka: No, I will actually say something
about this.

I think like, honestly, we use
probably Postgres in like the

least interesting way possible.

For our sharded cloud databases,
we don't have foreign keys.

We don't do joins.

We don't have any triggers.

We don't have really anything that
can make, we don't have any

generated columns.

Like we limit so many things that
you can do that we're basically

using Postgres as like a blob store
at this point, which is why

we end up with really giant databases
with a lot of indexes,

but the queries are quite simple
actually to retrieve the data.

And I think that's been like probably
1 of the primary reasons

that I've actually, and like my
team has actually been able to

kind of keep up with the growth
of our Postgres cluster because

we're able to do things like re-sharding
without having to, you

know, talk to every team at the
company and be like, can you

please change this query?

Can you please not do this thing?

You know, I think a lot of people
get really excited about using

all the fun features of relational
databases, but actually you

can use it in a very boring way,
but still get some of the benefits

of relational databases, such as
consistency and things like

that.

So I think that's like my 1 trick
to high scale with any database,

not just Postgres, MySQL and really
any other thing that you

can think of.

It's good to be aware of some of
the pitfalls of the more interesting

features.

Nikolay: So no foreign keys?

Arka: Absolutely not.

Okay.

Not allowed.

Okay.

Nikolay: Any more input?

Sammy: Yeah, We do allow foreign
keys on the sharding key.

And similarly, we allow joins on
the sharding key.

So for us, it's a matter of picking
a sharding key so that most

operations are happening within
that.

And then that allows us to avoid
rewriting a lot of application

logic.

Smart.

Derk: We also have foreign keys
at Postgres, which is in 2 weeks

time, a little bit less.

I tell you how to find a proper
partitioning strategy because

up to now I've been focusing on
partitioning a single table in

the best way possible, But I kind
of forgot to look up the bigger

picture.

So all my tables are partitioned
slightly different with different

partition boundaries.

And then the entire system becomes
a bit messy.

So now we are working on basically
straighten out all the partition

boundaries because then the joining
and the foreign keys work

much more efficient.

So it's partition pruning.

Michael: Clever.

Derk: I kid you not, some queries
became 20 times faster by straightening

out partition boundaries.

Michael: Wow.

Sammy: I'll make my last plug on
the hiring side.

My team is hiring really aggressively
right now.

We're basically trying to double
this year.

And in particular, we just had
an awesome senior staff engineer

become a manager.

And so we have a big gap in senior
staff, principal type engineers,

folks who have worked at scale
before.

So if you're interested in those
problems, please reach out to

me or come apply.

Arka: Notion is also hiring.

But my pitch is definitely not
as refined as Sammy's.

But if you want to come work on
fun problems, check out our careers

page.

Derk: I've actually think this
is might be the first time in

20 years.

We are not expanding my team this
year.

Nikolay: Oh wow.

Michael: But we should plug Derk's
talk at Postgres, which you can

watch.

So I'll include links to all of
these things.

Final 1 final thank you from me.

This has been amazing.

Thank you so much for joining us
for episode a hundred.

Nikolay: Yeah.

Thank you so much.

I want to just to once again, like
say, thank you for knowledge

sharing.

Super important.

Looking forward to more, definitely
to more blog posts and maybe

some run books published.

I don't know, like things like
you use in your daily work maintaining

these large databases, large Postgres
databases.

So super curious to see more from
you and maybe to collaborate

on some things like some knowledge
and exchanging and so on.

Thank you.

Derk: Thank you very much, both
of you.

It's really cool to be here, especially
in such good company.

Sammy: Yeah, this is a really fun
conversation.

Thanks for getting us all together.

Arka: Yeah.

Thanks again.

I really enjoyed this.

I hope to be back in the future
as well.

Nikolay: This was the best episode
number a hundred we could

imagine.