Michael: Hello and welcome to PostgresFM, a weekly show about
all things PostgreSQL.
My name is Michael, founder of pgMustard, and I'm joined
as usual by Nik, founder of Postgres.AI.
Hey Nik.
Nikolay: Hi, Michael.
Michael: And today we have a special guest.
We have Harry Brundage from Gadget, co-founder and CTO there.
Hey, Harry, thanks for joining us.
Harry: Thanks so much for having me on.
I'm happy to be here.
Michael: It is our pleasure.
So you've been doing some cool things with Postgres for a while.
Recently blogged about a no downtime, a 0 downtime upgrade, but
there's a ton of other things we wanted to talk with you about
as well.
Where would you like to start?
Harry: Yeah, I'd love to tell you about this 0 Downtime upgrade,
just because, I don't know, it's like a point of pride of mine,
I guess.
It's always so frustrating to me when I get an email from 1 of
my providers.
It's like, hey, Sunday at 2 PM, we're going to be down for 30
minutes.
Like, hope that's OK.
So yeah, Gadget's an infrastructure provider.
Like, the developers who use us use us for mission critical apps,
So we just try to take it more seriously than that and like not,
you know, disappear.
So yeah, we did a 0 downtime upgrade with the help of Postgres.AI
and Nik, thank you so much for your help, Nik, what a treat.
Nikolay: Thank you for trusting us because you know, like, we
did it with other companies and always like in the beginning,
I like, I might hear pushback because understanding how much
work it's needed there, you know, like, because it's far from
being well automated.
It should be in Postgres and it's improving, but
Harry: yeah.
Not quite yet.
Nikolay: Yeah, yeah, yeah.
So you did this journey and I thank you for not stopping.
Harry: Absolutely, absolutely.
So it was a lot of work,
Nikolay: it was, yeah.
Harry: I think too, it's like, to me it just kind of feels like
something that should be built into the managed providers.
Like I'm kind of surprised that so few of them have it.
I think the only 1 I could find that has like true 0 downtime
is PlanetScale.
Nikolay: But for Postgres,
Harry: the GCP options, I don't know about the new Postgres one.
I guess I know they have it for MySQL but not for Postgres yet.
Nikolay: For MySQL, yeah, I think so.
I don't know details but for Postgres I don't see yet anywhere.
Harry: But again, like what the heck?
Like as an industry shouldn't this have been the thing we kind
of like solved 20 years ago and never looked back?
You know, I don't understand.
Nikolay: Yeah.
And there's no AI needed there.
It's just pure automation.
Harry: Yeah.
Terrifying button to press.
Nikolay: I think.
Harry: But
Nikolay: yeah, I think what happens is that it should be automated
more in Postgres itself.
And most managed platforms like RDS and others, they just sit
on the fence.
Well, I know they they work and do it, but they don't want to
automate things outside Postgres too much.
For example, ANALYZE, right?
ANALYZE is not automated.
You always forget about this.
And they don't automate it, and they just put it in documentation
on the shoulders of users.
Now, it's time to analyze.
And I started like asking around like maybe a couple of years
ago, why, why, why 1 year ago and then okay, Postgres 18 should
be out next week, right?
Finally, statistics will be dump restore of statistics will be
fully automated.
So they will just exclude this from their how to manual, right?
So this is an example.
Things should be inside Postgres.
But I think still, they should automate much better.
Indeed.
Not waiting for Postgres itself.
Anyway, I'm with you here, 100%.
Harry: I'm glad.
What I was going to say is I've always attributed it, or the
absence of something like a true 0 downtime major version upgrade
to the fact that it's like more than just Postgres that you need
to pull it off.
Right?
Like you need a connection pooler or a proxy or something in
front of it sitting outside the system and you need a bunch of
tight coordination between that proxy and the rest of the system
to kind of orchestrate it all and so like I know at least on
our previous Postgres provider like they ran the PgBouncer on
the same VM as Postgres itself.
And so they're kind of hamstrung by the architecture where they
can't have this piece that lives outside that can talk to 2 at
once, whereas we self-hosted our bouncers for exactly this reason
and can kind of do it all and orchestrate it all and it's like
sucks to self-host it but gets you that architectural flexibility
you know we needed that.
Nikolay: Yeah and PgBouncer supports pause/resume which helps you
achieve 0 downtime.
Harry: We're maybe nuts too and we run our PgBouncers in K8s
and do the whole rolling restart and whatever and it's turned
out to be, I think, an okay choice.
I think a lot of people may be shy away from that because it's
sort of super stateful, super critical.
Maybe this is a pet, not a cattle.
But we did it in case just because that's where the rest of our
compute runs and it's like easy to sort of bin pack and share
it all.
It actually really saved our butts recently because we're paying
a lot for intrazonal network transit to our databases.
Specifically, like once from our application pod to the bouncer,
and then again from the bouncer to the database.
And because the bouncer was in K8s, we could turn on the handy-dandy
like prefer zone local network routing configurations and kind
of have the application pod go to a zone local bouncer and then
to the database And I think were we outside of K8s, that would
have been a lot more annoying to kind of like, teach all the
routing to be able to do the right thing.
Nik, you look scared.
You look skeptical.
Nikolay: No, no, I'm just thinking this is also a job for database
platform, I think, to take care of such things.
So yeah, and I know like RDS has RDS proxy but it doesn't have
a it has cool features PgBouncer doesn't but it also lacks cool
features PgBouncer has so it's a zoo and yeah And I think pause
resume is a wonderful feature.
Still, I have to emphasize it.
Michael: I was going to ask about your PgBouncer setup, actually.
Because your blog post on the upgrade is really cool, and goes
into good detail on the actual upgrade process.
But you didn't talk much about your PgBouncer setup, and how
many you're running.
And do all of your application stuff go through that?
Or does any of it skip it going to the database?
Is there anything interesting about that that you wanted to share
with people?
Harry: Good question.
I have 2 interesting tidbits.
The first is, I think our, so our PgBouncer deployment's a bit
weird, and then we have 9 instances.
Michael: Interesting.
Harry: Yeah, and we take our kind of total connection count,
where we're targeting 400 kind of server-side connections, and
split that up over 9, And the reason for that is that same K8s
network-aware routing thing, where the kind of up until very
recent versions of Kubernetes, you needed 3 endpoints per availability
zone for the network-aware routing to kick in.
So we had to use a minimum of 9 and initially we were really
skeptical about that because it seems kind of bad right instead
of having like say 1 or 2 buckets of a lot of connections, we
were kind of brokering them fairly.
You have many small buckets and you could get kind of weird inefficiencies,
but it didn't come to pass where that bit us.
It seemed actually okay in the end.
And what I like about it is we have a lot of CPU headroom, right?
Like PgBouncer, I think is still largely single-threaded.
I don't know the innermost details of it, but we're using the
fancy new prepared statement stuff within it, and so I'm kind
of happy that I can give it 9 cores instead of 2 or 4 or something
like that, and kind of be like, you know.
Take as much as you need.
Michael: Yeah, nice.
Always good to have headroom.
Cool.
Anything else about the upgrade, or like which bits were most
scary when Nik came to you with the proposed plan, you said
the pushing the button, is it because it is single direction
or did you have a rollback plan?
Harry: Good question.
We had a rollback plan, but it was written rather than tested.
Because we sort of like, So the way that we pulled it out, or
the way that we kind of built the whole thing is we built a temporal
workflow.
So we're like huge fans of temporal, very easy to kind of script
something like this.
And the reason why we did this is because it lets us get all
the nice kind of retries and item potency stuff for free, where
we get to write in a somewhat failure oblivious way.
And we wrote our workflow to have like a dry run capability,
where we do all but the very last step.
So we would go do everything where we like prepared the new host,
kind of replicated the data, made sure we were caught up, did
the PgBouncer pause, and then we just wouldn't reload the config
or change over and we'd just do a resume and everything would
keep on going as it was.
And so The terrifying part is we just never actually done the
middle thing.
And we weren't really sure, like, here's how we can test this,
other than like standing up a whole parallel set of infrastructure,
which we were like, I'm not even sure that will prove this 1
will work, right?
Like, just in my admittedly still limited lifespan, like, the
things that go wrong are always the things you didn't foresee,
right?
So it's like we dry-runned it a million times, found all the
things we could foresee, and then it was like the spooky, you
know.
Nikolay: Yeah, so it's really like It's impossible to test switchover
in production, but I wanted to emphasize it's possible to test
everything before that.
And it's great.
Because this is the beauty of this procedure.
You create the whole cluster, which is running already a new
version.
You see it's working.
You can touch it and it's great, and so on.
But yeah, I agree, the scariest part is switchover how to test
it properly.
Of course, it was tested in non-production, but it's different.
Here we just need to jump at some point.
Harry: Exactly.
It was the best kind of jump, where it was like a big meeting
that we were all like scared of, we all like sat down, Nik was
on the phone, and it was over in like 7 minutes, where it was
like, oh, well, I guess it worked.
Like all the numbers look good.
Like yeah, that's it.
Onwards you know.
So I feel fortunate to work with a team where, you know, that
was how it went as opposed to like us screaming around like,
check out their heads.
Nikolay: I wanted to thank you for this, like emphasizing that
you use this temporal framework because we, like Postgres.AI, we're
always big fans of Go and we like developed using Go a lot of
stuff.
But recently, our team, we have a few Python guys right now.
And I just envision that probably we'll learn from you in this
experience as well and do some stuff with temporal as well.
So thank you for emphasizing.
Harry: Yeah, we're heavy, heavy users of it.
We use it for these kinds of systems administration tasks or
behind-the-scenes maintenance workflows.
But we use it for a lot of other stuff too.
Like Gadget is mostly used today by people building Shopify apps.
And when you build Shopify apps, you have to receive a metric
shit ton of webhooks.
And so the Gadget has like a background kind of queuing system
that knows how to take a webhook and process it later or process
it with retries or whatever.
So each 1 of those is a temporal workflow for us and so we're
doing I don't know something like 3k, 4k temporal state transitions
per second also backed by Postgres in this big ass temporal cluster.
And that's something I never ever want to have to try to build
myself.
Like it's so complicated and nasty and fun for some.
Michael: You mentioned in the post also that Sharding was on
your to-do list.
Yes.
You mentioned though you were using a different database for
that now, like AlloyDB specifically I think for the new setup,
but you said it was just for the user data.
What's the separation there with like the Shopify?
Is it the data of the Shopify apps, for example?
Harry: Yeah, so Gadget is like a general purpose app development
platform.
So If you want to make a to-do list tracker or a ChatGPT clone
or a Shopify app, you can sign up for Gadget and you get kind
of like a full stack dev environment.
So you get a place to run kind of backend code.
It's all JavaScript, TypeScript.
You get a database or a sort of virtual slice of a database to
store back-end data.
You get a front-end and React and whatever and you're off to
the races.
You write back-end code, you write front-end code, you define
your data models, you transact, so on and so forth.
And then the Shopify part comes in where we kind of have a managed
API connection to Shopify, where we'll do the OAuth and the webhooks
and sync the historical data, et cetera, et cetera.
So like many infrastructure providers, we have a control plane
and a data plane.
The control plane stores our list of users, our who has what
access to what app, so on and so forth.
Then the data plane is the actual apps data, like this app has
a to-do model that stores, you know, this many to-dos.
This app has the Shopify product model and, you know, stores
this many Shopify products.
So when when we started we just stuck those in the same Postgres.
Yeah.
And that Postgres got too big and so this major version upgrade
kind of precipitated splitting the control plane in the data
plane which I think is like a pretty obvious best practice.
Michael: When you say too big, by which metric, like what's what
was the where was the bottleneck coming from?
Harry: Good question, so we had about 8 terabytes of data in
the 1 instance, or sorry, our disk was 8 terabytes big and we
were at 6 or something like that, 6.5, and we're like, okay,
this is scary, like we gotta figure out the next thing, and we
were on PG13, which was end of life long ago, and so we needed
to both do the upgrade and we just wanted kind of a better roadmap
for how we were gonna deal with growth.
Because Gadget has lots of these e-commerce customers, BFCM is
always a quote fun unquote time for us and like we'll see probably
this year like a 4x traffic increase and like a 4x database QPS
increase and so that means like we kind of need to be ready for
a 4xing of you know a database CPU and our provider at the time
it didn't sell a big enough 1.
So we were kind of like, we have to do the horizontal shard,
or scaling sort of journey.
So what we did is we sharded in order to move the bulk of that
6 terabytes out into newer instances that had much more room
to grow, leaving only the reasonably sized control plane left
over to do this major version upgrade.
And it simplified a few of the steps of this 0 downtime major
version upgrade, because we didn't have to replay 6 terabytes
worth of stuff on the new instance.
Michael: Yeah, yeah.
Makes sense.
But why not stick with the existing provider for those shards?
Harry: 2 reasons, or 3 reasons.
The first is we're a Google shop, and we get good pricing from
Google.
The second is Google claims up to a 4X TPS increase with AlloyDB.
Like, I like Google and I don't want to poo-poo them.
I'm not sure I've ever seen it actually hit 4X, but we did see
like at least a 1.5X kind of like TPS increase without really
much config change or tuning or whatever.
I can't even say if it's actually the same CPUs like under the
hood, you know what I mean?
I don't know what the initial provider was using, but the instance
was faster for the same price, so that was nice.
And then the other big thing with Alloy is you don't pay intrazonal
network transit fees.
It's not like a normal VM.
I present that without comment to the competitive forces that
may or may not be listening.
But yeah, it's free networking to the Alloy instance.
Whereas if you're Aiven or Crunchy or whatever and you run instances,
your customers have to pay from 2 out of 3 zones just to talk
to it, so that sucks.
And then the second thing is you don't pay for replica storage.
It's a split storage compute sort of setup, where The disk is
this sort of auto-scaled elastic fancy log storage service thing,
and then you pay for your nodes that kind of read and write from
that shared tier, but you don't pay n times, you pay once.
So it's like 40-ish cents a gigabyte, which is like more expensive
than like say a big fat EBS volume but not that much more and
so if you're running like a lot of replicas it ends up being
cheaper storage-wise and you don't have like all the nasty IO
spikes from backups or any of that stuff.
Cool.
Nikolay: And unlike Aurora, I don't see the hidden cost with
Aurora, usually I/O costs.
Harry: Yeah, no I/O costs.
Nikolay: Yeah, that's interesting.
Harry: So our database bill came out to be about the same as
a pretty classic like GCP VM with you know then they're not EBS
and GCP but like fat kind of SSD volumes backing it and we save
money on the networking we save money on not having to pay for
storage again for the replicas, and we deleted our I/O spike
for the backups problem.
Nikolay: That's an interesting comparison.
So we even don't talk about features like this column store or
row versus row store.
Even without that, it's a direct comparison.
Interesting.
Harry: Yeah.
We intend to make use of that column store.
Like, I'm skeptical.
I used to work on sort of like the big data warehousing team
at Shopify when I worked there and like I just don't really believe
that you can force Postgres to do the kind of super wide throughput
stuff you need for crazy scale analytics.
But you can definitely get pretty far with it.
You know what I mean?
So my guess is some of our operational reporting use cases where
you're traversing half a million a million rows to answer a query
on a read replica with this column store is going to be fine.
But in the like billion, 10 billion scale, I think purpose-built
analytics databases still should exist.
Michael: We've had a few of those conversations recently.
Harry: I'm sure, I'm sure.
Michael: I had 1 more boring question on the major version upgrade
before we move on to more interesting things.
It looks like you're on crunchy data, you were on crunchy data
before for the control plane stuff and are going to be again
for the upgrade.
But you only upgraded I think from 13, which by the way you're
a bit harsh on yourself.
I don't think it's quite yet out of support.
I think it's
Harry: like a
Michael: couple of months time that'll be out of support.
But you only moved to 15 and I think Crunchy have always been
very good at supporting the latest versions so probably 17 is
available.
So why not?
Like why 15 not 16 or 17?
Harry: Yes, Crunchy has been great at that kind of thing.
I think the main reason was...
Shoot, you know what?
I don't even remember.
Michael: Maybe Nik knows.
Nikolay: I also don't remember.
I remember that Crunchy had different end of life dates somehow.
Crunchy Bridge had different dates.
Harry: Yeah, they deprecate support, or they stop sort of long-term
support before the full-on end of the kind of core support.
But they, in fairness, they gave us extreme advanced warning
and like we knew it was coming for a long time and they supported
it for 4 years or whatever, which is, I think, pretty good.
I don't remember.
I think it might have been a desire to keep the versions homogenous
across all our instances.
Sure.
And for really silly reasons, the Alloy shards that we stood
up were stuck on 15 and couldn't be newer.
Michael: Yes, that would explain it.
So, Alloy, I think because it's a fork and it's probably quite
hard to maintain fork, I don't think they're as good at keeping
up with the major versions.
So, there's a good chance you wouldn't even have the option of
going to 16 or 17 on there.
I don't know
Harry: for sure.
That's correct.
At the time, 16 was GA and 17 wasn't even out yet.
Now 17 is out, but it's still in preview.
Like, you can't, shouldn't run production workloads on Alloy
17.
But the weirdest thing was we wanted to go to 16, but they just
don't have pg_squeeze support on their 16 version.
They have it on 15 and they have it on 17 and not on 16 and couldn't
tell you why, but we squeeze a lot, so We really cared about
that.
Michael: That makes so much sense.
In my head, the only reason I could think of is because now you're
so good at major version upgrades, you wanted to give yourself
more to do in the future.
Harry: Let's rinse and repeat.
Yeah.
Nice.
Michael: All right.
So you're running Postgres for a lot of different workloads.
Are there any challenges or fun things you wanted to share on
that front?
Harry: I think maybe a good 1 just for the audience and like
if you're listening out there and you think I'm full of it, like
please send me an email.
I would love to know what you think.
But we did the classic Postgres is a queue thing early in our
life.
And I think that that, Well, it's on my mind a lot.
So a lot of people who have like background queuing problems
use Redis.
When we were at Shopify or when I was at Shopify, we used this
library called Resque
There's another really popular 1 called Sidekiq and Node, it's
BullMQ.
There's a litany of them, right?
They all seem to use Redis.
We decided that for our user, who tends to be like a front-end
dev or sort of someone who isn't deeply familiar with like data
consistency or data durability concerns, that we didn't really
want to be like, hey, we'll happily process your background jobs,
but we'll drop 0.5% of them and not be able to tell you which
ones we dropped and not be able to tell you when it happened.
So for our background queue product, we said it's going to be
durable.
Not only is it going to be durable, it's going to be transactional,
where it's like when you do some business logic and you enqueue
a job within a transaction, if the transaction commits, then
the job will get enqueued.
And if the transaction doesn't commit, the job won't be enqueued,
and that's the transaction boundary.
So we were like, this is nice.
This is us kind of trying to prioritize our customers' sanity.
And I think it's the kind of thing that's like annoying, because
not that many of them are going to notice or care.
It's just going to be no data quality issues for this business
logic.
But that locked us into needing Postgres to be on the hot path
for those end queues, right?
We have to participate in that business logic transaction to
kind of save the thing.
We knew we didn't want to build a giant queue of our own.
So we knew we were going to get it into Temporal and let Temporal
take over, but we sort of needed like a Postgres outbox, if that
makes sense, where it's like, you write to the outbox and then,
you know, you're slowly forklifting out of the outbox into the
actual system eventually.
And then Temporal itself is durable.
And so, yeah.
So that's the architecture we went with.
But as we sort of built it out more and more, we learned about
more limitations of Temporal.
Like it came out of Uber from this system that kind of predates
it called Cadence.
And my guess is they learned all this stuff the hard way and
built in a bunch of limits that make it, you know, easier for
the operators to guarantee certain sort of performance characteristics,
but really annoying for people like me.
So 1 of those limits is when you start a job, there's a maximum
payload size that's pretty small.
I forget what it is exactly.
I think it's like 50 megs or 5 megs or something like that.
And then as that job executes, it can only generate X amount
of state transitions, they're called, but events in its processing,
where it can only have a certain number of retries, basically,
and you need to sort of re-architect your temporal workflows
if you want to overcome that.
And again, in our case, our users are kind of like, hey Harry,
I have this 400 megabyte video file that I'd like to process
in a background action.
Can you just make it work please?
Can you not make me like re-architect my entire thing to toss
it into cloud storage?
Whatever, it's ephemeral data.
Like I don't need it in the cloud.
I just want to push it to this thing and work it within the background.
So we found that the temporal limitations were annoying enough
that we actually needed to stage a bunch of each jobs data outside
of temporal.
And so we ended up kind of having this nasty hybrid where that
Postgres outbox I mentioned sort of became like a store for a
bunch of extra stuff and then the temporal workflow as it went
it would be you know transacting with its state storage and doing
its state transition things, but then also updating what is now
no longer the outbox to be our source of truth, it powered our
UI, it had all these extra params, yada, yada, yada.
So we weren't even really using Postgres as a queue.
We weren't having the classic skip locked problems and whatever,
but we were just having like this sort of like request scale
data transmit, like 1 web hook would be 1 write to this outbox
to enqueue, 1 write to start processing, 1 write to finish processing.
So just like massive, massive tuple of turnover.
And we also needed to build a UI on this stuff.
So we only could get a few of them to be like HOT updates, like
many of them were not.
And so we were just like destroying this poor Postgres instance
with what I would call like less important data than the actual
data plane data but still important enough that we felt that
merited being on a disk.
So all that to say we ditched Postgres for this and we switched
to Bigtable, which is like Google fancy super distributed key
value store and it friggin sucks.
It really sucks and the reason is no transactions, right?
It's like a way worse model for me as a programmer and a systems
designer, and a way more scalable, fundamentally supersized system
under the hood.
But boy, oh boy, do I miss the creature comforts, we'll call
them, of secondary indexes and transactions and a sane query
language, let alone the gazillion and 1 features that a full
relational system has over a key-value store like that.
So we use Postgres, I think, by default for all our workloads.
And then when we find something that we're like, okay, I think
there's maybe a better fit here.
We try to switch it out for the next best thing.
But it's like, it's really hard to beat.
And I know this is maybe unsurprising on a Postgres podcast,
but it's like, I just wish I didn't have to do that.
I could have stayed with my creature comforts and got my scale,
you know?
Nikolay: Yeah, so I'm curious what was the number 1 reason.
Is it like bloat maybe or?
Harry: Yeah, it was like bloat, tuple turnover and just like
the fact that we were like always vacuuming.
Nikolay: Yeah, yeah.
I can imagine.
Harry: Another big motivation for switching to something akin
to Bigtable is like compute elasticity, where we can kind of
like Bigtable auto scales by default.
You can't even turn it off, I don't think, where they just add
more nodes as demand kind of grows.
And for us, where we're kind of like the background subsystems
are the thing that absorb load, right?
When you have a flash sale on your Shopify shop and you need
to enqueue a bunch of stuff, like it's quite, We need this thing
to be able to handle unpredictable surges.
And it was a bummer at the time, we didn't have any way to do
a 0 downtime, like non-disruptive Postgres resize.
Now we do, but even then I don't really want to be doing it automatically,
like in response to load.
I kind of want something like Bigtable that's like, 5 seconds
later you can get your capacity expansion.
Nikolay: Yeah, well, I can see it easily.
Harry: I think, I think like My ideal would be a million tiny
Postgreses and have some fancy system for like disaggregating
a logical Postgres from a physical VM.
Or if I have like, let's say I shard my workload into 16,000
Postgreses, but they only run on 4 VMs.
And then as I need more compute, they kind of split out, get
bigger, and whatever.
But that's like a twinkle in old man Harry's eyes.
Nikolay: There is work in this direction by multiple teams right
now.
So we have good format.
Harry: Go on.
Go on.
Yeah.
Nikolay: Yeah.
There is PgDog.
There is Multigres and PlanetScale also.
They're all working in this direction.
Michael: And Multigres specifically is 1 of the things Sugu
mentioned as well.
From his experience at YouTube, he loved having lots of smaller
databases and he much preferred that in terms of reliability
and localized outages even.
So there were just so many benefits
Nikolay: highly automated operational source in the roadmap like
upgrades as well
Michael: and rebalancing yeah
Harry: yeah I am forgive me for another brief aside but are you
familiar with Azure Cosmos DB?
Like the kind of main 1, not the Citus 1, the main 1.
Michael: I thought Cosmos was Citus.
I get confused by their names though.
Harry: So I think there is Azure Cosmos DB for Postgres, which
is Citus.
But I think there's, and I'm not deeply familiar, so I may be
totally wrong here, but I think there is a different original
Cosmos DB that's kind of the Azure answer to Dynamo and Bigtable.
Like they're kind of like super scale, sort of less sophisticated
query patterns, but guaranteed scale for whenever.
The way that it works, or the way that it scales is you specify
like a partition key with every piece of data, and you're only
allowed partition local transactions.
So it's not like a Spanner or a Cockroach where the whole key
space can be transacted against together.
It's just like a million tiny partitions and then every partition
could be a max of 50 gigs.
And to me, that's like so desirable because it means like 1 little
part, you get like no local performance, but the same horizontal
scaling properties of the super scalers, but without necessarily
two-phase commit or Paxos or whatever.
And so I'm hoping someone manages to do that.
I think it's maybe, what's it called, Neki, like the Vitess
for Postgres.
Did I get that
Michael: right?
It's Neki.
I have no idea how to pronounce it.
Harry: Yeah, same.
I have no idea.
But I'm hoping that's what it is, which is like tiny little boundaries
that force me to design my data in a way that makes the transactions
efficient where you don't need a bunch of computers to agree
on a Transaction outcome and instead you can just funnel them
to the right place
Nikolay: unfortunately, they all Not all but many of them came
to me and asked, have you used 2PC?
I said, no, I always avoided it, but so they need it, unfortunately.
Of course, like some workload will avoid it, but some workload
will still need 2PC.
This is the sad truth.
Harry: Totally.
Totally.
I think like the thing like I, so I used to work at Shopify circa,
I don't know, 2012, 2013.
We were running like a MySQL fleet at the time, and we felt constrained
by the biggest Database servers we could buy.
Like we were sharding, but it was still like the biggest customers
could push the 1 instances, I don't know what it was, probably
64 threads, like to the max, I'm not really feeling that way
anymore.
Like I feel like there's few workloads that don't have a natural
partitioning where you need more than 1 Server's worth of compute
just to satisfy 1 partitions worth of work I mean, maybe you
talked about this assignment last time But it's like you can
always split it up and when you split it up Like you don't need
you need an Aggregate many computers, but 1 Transaction boundary
doesn't need more than 1.
And so I'm just, and like the hardware has just gotten so much
better in that 10 years that I'm not sure I could grow my company
fast enough to need that, Right?
It's like maybe if we had like a graph problem, maybe if we were
Facebook or whatever, but as it stands right now.
Nikolay: That's true.
And on GCP, if we don't look at AMD, we look at only Xeon scalable
fifth generation, the best available C4 nodes, I think, right?
You can get 192 cores or so, but if you go to AWS, you will have
even more, like almost, I think, 800 cores also the same type
of processor, and this is already huge.
So you can scale 1 node.
Harry: It's just bonkers.
Like, that's ridiculous.
Yeah.
It's a lot of doge coins, you know?
Sorry, so that was a long-winded rant.
But what more can I tell you about that temporal sort of Postgres
workload setup thing?
Michael: Before we started, you mentioned 1 of the most interesting
things you're working on is that you're serving other people's
workloads and that you don't control with design, like Schemas
and design decisions that you don't control.
How do you stay sane while doing that?
And also, what are the interesting parts of that?
Harry: Great question.
You hit me with the question that just invalidates my entire
prior rant, which is like, yeah, you can partition your workload
if you think hard about the schema design, but yeah.
So, okay, let me set the stage a little.
So, Gadget's, like, reason for being.
Like, the reason why we're working hard on this thing is because
we kind of believe software development is still hard, unnecessarily.
That, like, when you go to spin up an app on the weekend or your
next big business or just, like, solve a small problem in your
life with software.
You spend a lot of time on what we call like baloney problems
of provisioning infrastructure, setting up connection poolers,
like worrying about the autovacuum schedule, so on and so forth.
We just think that's all a distraction from actually building
something useful.
It matters, like you can't ignore it, but you also ideally don't
want to have to deal with it.
So similar to the people who came before us, like the Firebases
of the world, We kind of want to delete a bunch of those problems
from our customers' lives, take them on ourselves, and allow
them to focus on whatever unique, interesting custom problem
they want.
Like, recently, it might even be allow cloud code to focus on
whatever unique, interesting problem they want, right?
But, like, by kind of deleting infrastructure foot guns and systems
integration, we just let people kind of succeed faster.
And then I guess I should say, bearing in mind the audience listening
here, it's not for everybody, right?
Like, if you have the perfect knowledge of the Postgres schema
you want, Gadget's probably going to be less efficient than that,
right?
Because we've added lots of layers of abstraction.
We have our own operations and infrastructure team kind of managing
this stuff.
So I'm not trying to say, like, no 1 needs to care about these
problems.
I'm just trying to say that a lot of people, I hope, don't have
to care about these problems so that me and my team pull our
hair out stressing about them.
All this means is there's this tension between asking the developer
to be more specific about their data access patterns and adding
friction for them to like on the path to success right that like
every time you say do you want 4 8 or 32 bytes for this number
you're you're posing what I would call computer baloney question
rather than like it's just a number like you know screw off I
don't know I don't know how big it's ever gonna be just you figure
it out you know so in Gadget we have like a number field type
instead of an int and a float and a decimal we've taken this
to a pretty intense extreme where when you do data modeling in
Gadget you specify tables, we call them models because they include
behavior as well, but you specify tables and you specify fields,
you can specify relationships among them, but you don't run any
SQL to realize those schemas.
And then you get a kind of fully auto-generated GraphQL API for
interacting with those tables without doing anything else.
You just, you specify your schema, you get the API.
We generate a nice client for that API, we generate nice docs
for that API, but the idea is like you say, to do title string,
completed boolean, and now you immediately can write some React
code for a form, or you can ask the AI to write some React code
for a form to like transact against that stuff.
All this to say, we're responsible for the SQL that, or the schema
of those tables under the, like the real tables under the hood,
as well as the SQL that that API generates and executes.
So this is a win because we can avoid things like the
N+1 problem, like we know how to do that good.
We can implement things like 0 downtime cutovers or kind of maintenance
modes where we know how it's like this app is in the process
of being moved to a different shard, like, hold on a second,
we'll finish that and then serve your request, you know.
It's powerful that Gadget sort of owns the whole stack.
Oh, we can implement other stuff like cloud storage, where you
can store data blobs in the right way, instead of within Postgres,
you know.
But it means that all we know about a given application is what
we've collected from the developer, which is that list of tables,
the list of fields on the tables, and the relationships among
them, Not stuff like, ah, this is a shard key, or this is how
I intend to transact, or if I'm going to make an HTTP call in
the middle of my transaction, like 2 days from now when I change
my code.
So we picked this bed and we made it on purpose.
The idea is we want building high quality software that scales
and like meets the demands of Black Friday, Cyber Monday to be
more accessible and to not be locked behind you know whatever
between the 3 of us 60 years of Postgres experience or something
like that, right?
You should be able to build and get an app going way faster without
that.
So the gist of it is we need some way to structure our data under
the hood within our Postgres such that people can't take down
the platform.
They can't use more than their fair share.
We just need to be safe by default where we're committing to
serving this API, and regardless of what strange queries you
throw at it, strange transaction patterns, poorly written code,
either out of ignorance or malice or whatever, just needs to
still work.
Same as like a Firebase or yeah, those other companies I mentioned
before.
And the way that we do that is like kind of 2 main ways.
We don't give everyone their own instance and say it's your job,
like you manage the performance.
I think that's what, say, a Neon or Supabase does, which is
still helpful, but it's different, right?
They're sort of saying, you get this much resources and within
that, it's up to you to optimize.
We don't say that.
We charge by operation, more usage-based, like say an Upstash
Redis or something like that.
And then we consider ourselves Gadget on the hook for the performance
of those operations.
And that means that, again, we have to find some way to not process
queries that could take us down.
So we do 2 things.
We refuse to execute a query unless there's an index on the predicates.
And that's just a safety must, basically.
We can't run unindexed queries because we can't know that they'll
process fast enough for it to be safe to run at scale.
And then we rate limit.
And that allows us to say like, you know, no 1 can have more
than their fair share.
But the crazy thing is almost no 1 hits their rate limits.
We consider that like a regrettable private platform protection
mechanism as opposed to like a pricing mechanism or like an incentive
and the reason that no 1 hits their rate limits is because
if you just force every query to be indexed like you get pretty
far.
There's there's the planner is so good and like the bitmap index
combining stuff gets you so far that almost nobody, or any time
we ever have a database CPU spike, it's usually because something's
gone wrong with our creation of the indexes, like Gadget's automations,
their Temporal workflow, surprise, like have failed to set the
index up properly, or we like let the code that makes the API
calls ship before the index was actually in place, or something
like that, you know.
But it's like Postgres just works if you do this.
So it's really naive, but it works.
Nikolay: Are you okay to share how many indexes you've already
created?
Harry: Yeah, we have, I think, probably over 2 million indexes
in our system right now.
Actually, sorry, I take that back.
At the time of the sharding we did, but we've reduced that drastically
since then, but for kind of unrelated reasons.
But yeah, basically if a user wants to be able to, in their API,
sort or filter on a column, we just say there has to be an index.
And so we have, I don't know, most of the columns indexed.
And like, oh god, I can feel the hate mail now, talking to a
giant legion of database engineers who are all screaming in their
cars right now.
Why are you indexing recall?
No, no, no.
The modern hardware that respected Nik just mentioned means
that the cost of this, while substantial, is not actually that
crazy.
It's higher, no doubt.
But we're not in the days where you need to be so incredibly
stingy about every index you make, especially when the cardinality
of the tables are like a million, 2 million records, as opposed
to like 10 gazillion, right?
When our first 10 gazillion customer comes along, we're going
to work with them closely to be like, you don't need that index.
Like, let's turn that off.
Your stuff will go faster.
But the vast majority of people, the overhead is it adds up,
but it's not so meaningful that our business is busted.
Nikolay: Yeah, so I wanted to say that this is a pet versus cattle
philosophy.
If we like think about indexes as a pet, we need to get rid of
unused indexes, take care of bloat and all.
But at your scale, before schema became sharded, millions of
indexes, you think about this as cattle as well.
Just inside 1 Postgres instance, right?
Before it became sharded.
This is an interesting approach.
And I agree with, since we have small tables, we know there is
right amplification, all indexes, if it's not HOT updated, it
needs to update all those indexes, some of them are not used,
but since approach by design it's cattle, okay, it's not that
bad.
Yes.
This is interesting case, I must say I never saw it before, it's
interesting.
Michael: I've heard of 1 other case which was Heap Analytics
blogged about, something about, yeah you know the 1, great.
I sure do.
So that was the only other time I heard about this.
Nikolay: Was it also where they discussed that they create indexes
without work concurrently, right?
Michael: Yeah, because you can't do more than 1 at the same time.
Nikolay: Yeah, to parallelize work.
Michael: Yeah.
Harry: What's neat or strange or weird about Gadget is we kind
of participate in the whole SDLC, like the whole life cycle of
like authoring a feature, debugging it, deciding it's good, merging
it and deploying to production.
Gadget has a development environment as well as the production
environment.
And so we kind of get to witness the moment that you want to
go live.
And we just do a normal style, call it Rails or whatever, migration
where on deploy, we create the indexes concurrently behind the
scenes.
Then we ship the code that makes use of them.
And then it's like, we can do that because we're not sort of
exposing the raw SQL and asking people to manage their own schema,
so on and so forth.
But what do I want to share about this that I think is the most
interesting?
Oh, yeah.
So the first thing is, like, giant catalogs where you have, you
know, we had, I think, at peak in 1 instance, like 250,000 tables,
1.4 million indexes.
Giant catalogs suck, but were more manageable than I was guessing.
I was assuming there was going to be some O(n²) system
table inside Postgres, or some doinky little thing that just
breaks when things are that big.
But no, that instance operated well.
The thing that did break was a bunch of the analytics tools out
there.
Like we were using.
Like pg_stat_statements?
pg_stat_statements was OK.
It was big.
It takes a lot of data, but it wasn't, like, an ungodly amount
of performance overhead.
But, like, pganalyze and many of the kind of collectors, they're
like, let's get the size of every table every minute, you know?
It's like that's a hard no.
Nikolay: Our monitoring also didn't work properly because of
the catalogs.
Harry: Yeah, but you guys only had to make a couple tweaks.
I think we're talking about pgwatch2, like the Postgres.AI
remix, and it works well for us.
It's the kind of fastest 1.
I haven't tried pgMustard, but I must.
I
Michael: owe them a minimum.
It's not
Nikolay: a money-ching code.
Harry: It's all right.
So that was surprising.
But the other, actually, This is just a fun fact.
So we did our sharding, we moved this gazillion number of tables
over to AlloyDB.
We turned on their analytics thing.
It's called Advanced Query Insights.
And it's not bad.
It's a nice UI for pg_stat_statements, souped up a bit.
They store all the data for it in a private database in your
instance.
So like you have an almost superuser but not a full superuser
and there's this one database you can't touch that has it in it.
And currently, this is my fault, I set it all up and I was
like, oh, this is probably going to be not that expensive.
Currently our like AlloyDB Insights database is almost 1.5x the
size of the actual database.
And I think it's because the query diversity is so high, and
all the data that they're keeping about each one adds up so much,
that yeah, we're paying more than double in storage just for
the privilege of this thing.
And half the queries time out.
But again, I'm going to blame me for that, not Alloy for that.
We just need to turn that off.
Nikolay: I'm curious if the main, like if we have just one AlloyDB
database and we store metrics inside the same thing, if it's
down, how will we troubleshoot?
Harry: I think it is only like query insights like the equivalent
of pg_stat_statements.
I guess you're right, I don't know.
Michael: Isn't the same true for Postgres like with pg_stat_statements?
Nikolay: Well yeah, but that's why we need external monitoring
tool which will pull data and store it independently for troubleshooting.
I thought about this like self-observed or externally observed
system.
I think we actually need both.
But if it's just self-observed, it might be a problem.
Harry: Yeah, it goes full circle to that same thing we were talking
about with the infrastructure provider earlier, which is like,
they don't have anywhere else to put it unless they're going
to pay for it.
Right?
So by putting it in my instance, I pay for it, which I am paying
for dearly, and I'm now going to turn off, you know, but I agree.
We, you do get the like system metrics that they store in their,
you know, fancy time series database, but yeah.
Nikolay: Yeah, I didn't know about this.
Interesting.
Harry: Yeah, I think, if I may, the most interesting thing we've
done to manage this ungodly number of indexes and tables is classic
computer science, a hybrid strategy.
What we noticed is many, many, many Gadget apps are like tutorials.
It's like someone wants to kick the tires, or they're building
a to-do app and they only ever add three to-dos.
It's kind of excessive to create them one to-do table, one user table,
indexes on many of those columns to store zero to five rows.
So we use a thing we call a storage strategy pattern where different
Gadget models are backed by different data.
We still store them in the same Postgres, and that's so that
we can still transact against them together.
Like we support real, actual Postgres transactions, but we use
doinky little JSONB tables when the cardinality is small.
So we have 1 shared JSON table, it has an ID, a model identifier,
and then just field values, and we don't index that at all.
No GIN, no nothing.
We just do a table.
You could call it a table scan even though it's not, it's like
a range scan.
And then we just bound the cardinality of that.
And then when we witnessed the, I think it's like 22,500th record,
we swap out kind of a real table that has the real indexes and
whatever.
And so that cut our table count and our index count down by a
lot, a lot, a lot.
Again, that swap is 0 downtime, and it has to be.
That's again our commitment to our customers.
And that was actually quite difficult in order for someone's
doing a bulk load of data or whatever.
We witnessed that 2500 record, we have to kind of like pause
traffic, do the switcheroo, resume traffic without, like it'll
be a latency spike, but not an error spike.
And I think that's kind of, it's Gadget is a bundle, right?
Where we have this backend runtime, this frontend runtime, this
database, all these things that work nicely together.
And we see that as validation that the bundle's showing through.
Or it's like, we can only do that because we have control over
the app tier, control over the load balancers to be able to be
like, ah, hold on a second.
We've got to do some maintenance on your app real quick.
We won't drop any requests.
We'll do a little switcheroo without anybody knowing, and you're
off to the races with the full performance after that.
Michael: That's a really neat trick.
I like that.
You must have provisions in that code that doesn't allow
queries without indexes to like account for tiny tape like Yeah,
oh my goodness.
This sounds painful
Harry: We've been working on it for like 4 years, so yeah, yes,
but I think we're in a good position.
Like, I'm excited for BFCM this year, which for operations staff
is, I think, a rare thing to hear.
Like, I think I'm excited for the challenge, you know?
Michael: I saw the color drain from Nikolay's face.
Nikolay: Yeah.
Yeah.
Do you think there might be some point that true Postgres will
be fully replaced in your company by something?
Or there will always be a place for true Postgres, not AlloyDB
Postgres-like?
Harry: Great question.
I think yes.
I do think it will be fully replaced.
And I think it's only because we haven't seen any alloy downsides.
Like while it's a fork, it's still 99% true Postgres.
So we have like pgvector support, for example, and pg_squeeze
support.
It's not like a wire compatible thing.
It's like mostly actually Postgres with the WAL and the block
storage kind of ripped out.
Nikolay: It also has lightweight locks and so on, all those problems.
Harry: Yes.
Yeah.
Yes, true.
I think we're going to stick on Alloy, but not because it's the
very best, but because now we have familiarity, Now we know we
can maintain it, now we can scale to Valhalla.
So we've sort of unlocked something and we've got other stuff
to do.
I think the thing that would make me revisit that is OrioleDB,
or whatever the...
Whatever InnoDB for Postgres is.
Like, when that comes around and I don't have to vacuum anymore,
that seems like a pretty epic win that I'd be interested in realizing.
Nikolay: Makes sense.
Great answer.
Great, thank you so much.
Enjoyed.
Thank you
Harry: guys, I really appreciate getting to nerd out.
These are the things that keep me up at night.
They're like 1.4 million indexes.
What do I do?
It's nice to let it out a little bit.
Nikolay: Cool.
Thank you.
Michael: It's our pleasure.