Michael: Hello and welcome to Postgres FM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard, and this is my co-host Nikolay,
founder of Postgres.AI.
Hey Nikolay, what are we talking about today?
Nikolay: Hi Michael, let's talk about a lot of TPS we can get
from a single Postgres node.
I recently achieved something.
I'm curious, is it some interesting achievement or like no?
I'm sure it's possible to get more on bigger machines.
Because my machines were big enough, right?
It's like just 360 vCPUs and just 2 terabytes of RAM or something,
maybe 1.5 terabytes of RAM.
Yeah.
Fourth generation AMD Epic.
It's, there is already 5th generation AMD Epic and there are Intel,
Intel's also, Xeon Scalable, bigger instances and so on, it's
possible.
But I've almost achieved 4 million transactions per second, full-fledged
transactions, not just like what other NoSQL databases have,
it's transactions.
Read-only, we should admit, read-only, and meaningful transactions.
It was a table with, I think, 10 million rows.
It's a tiny table for this machine.
Everything is cached, of course.
And it was a primary key lookup and PgBench.
Regular PgBench accounts table, you know it, right?
So...
Michael: Is it single read per transaction?
Single query per transaction?
Is that right?
Nikolay: That's a good question actually, let's double check,
I will double check it right now, but I think yes, it's just
select from PgBench, random number, random ID, and that's
it, should be, should be.
Michael: I was just hoping you'd get to multiply your 4 million
by 2 or something.
Well, well, I get queries per second.
Nikolay: Let me tell you what you don't know.
Yesterday I was driving to LA and I used this like I think
I should check my eye and get even more TPS.
And thankfully, I had AI with autopilot in the car, right?
So I could, maybe it's still not a good idea to use phone, But
it's just a quick question, right?
So I asked my bot to repeat this TPS.
Oh, I forgot to mention, all these experiments are done with
our Postgres.AI bot, which simplifies experimentation a lot.
So If you need to provision a huge machine, run some PgBench
during a couple of hours according to some script, you just ask
for it, then you think, oh, what if we change something else?
It's just a few words.
You just ask, and then it reports back in a couple of hours.
So I asked, can you please check not regular select only for
PgBench, which is very standard Postgres for benchmarks, quite
simple but widely used, but rather let's just use SELECT;
Not meaningful, right?
Not dealing with data at all, but still it needs to be parsed,
right?
And so on.
Michael: I had it on my list of questions for you to ask what
would happen if we got...
I was going to suggest SELECT 1, which I guess is the beginner's
version.
Nikolay: I can tell you we exceeded 12000000,
Michael: wow nice.
Nikolay: Yeah but it's I don't consider it fair because it's
just heartbeat.
Well it
Michael: depends what you're measuring right like If you're trying
to measure what's the most we can achieve, then sure, it's meaningful.
But if you're actually trying to do something meaningful, read-only
is probably not realistic.
There's so many things you could say, and I like the idea of
what's the maximum we could get if we don't worry about constraints,
about how realistic it is.
Nikolay: Right.
So yeah, in this case, we only checked the parser and general
transaction, read-only transaction, not full-fledged transaction,
all these pieces.
But we didn't check how Postgres works with, for example, buffer
pool with reading data and so on.
Because I know there was some contention in the past, even for
read-only queries, which was improved, I think, in 9.6 on the
road to achieve 1 million TPS.
We will probably discuss this slightly later.
In general, what's interesting, I needed to explain, I hate SELECT
1 statements.
You know why, right?
No.
Because 1 is not needed.
Michael: Fair enough.
Nikolay: You can say SELECT and pause this.
It will return you a result set consisting of 0 rows and 0 columns.
Michael: Can you just run semicolon on its own or something?
Nikolay: Yes, you can.
I think we also discussed it.
Michael: Must have done.
Nikolay: Yeah, it has issues, negative side effects, because,
for example, you won't see it in logs, if somehow it's slow.
It's like empty query.
You won't see it in pg_stat_statements, for example, unlike regular
SELECT;
And I failed to convince bot to run SELECT;
Somehow it didn't work.
And I also had difficulties convincing bot to select.
It started telling me...
I think it was Gemini.
It was this passive-aggressive manner, it's Gemini for sure.
So it started telling me, you know, like it's incorrect, it's
invalid SQL statement.
So let's like, it offered me, let's put 1 at least there, or
where is the table name?
Like, okay, let's, I said, no tables, no tables, it was just
like idle queries, like no real action.
And then said, like, let's put at least 1 because otherwise it's
incorrect statement.
I forced, like, are you sure?
You can check.
So we had some battle.
But in general, yeah, it worked and we achieved 6 million TPS,
which was good.
But SELECT; didn't work for our bot.
I think it's possible.
I don't expect a huge win compared to SELECT.
Maybe it will be parser.
I don't know.
You can
Michael: run the experiment manually, right?
Could you just submit the JSON yourself?
Nikolay: It's better to convince the bot.
Michael: You prefer to fight the LLM, fair enough.
Nikolay: A little bit, yeah, because every time we do it, I find
opportunities to improve, of course.
But back to meaningful results, which is interesting.
We almost achieved 4 million, but originally it was just 1 million.
And when I saw just 1 million on modern Postgres, we checked
16 and 17 beta 1.
By the way, beta 2 is already out.
I think we should update and switch to it in our tests.
So I expected much more because I remember the post from Alexander
Korotkov from 2016, when I remember he worked with Percona people,
Sveta Smirnova particularly.
They had like a friendly battle, let's see what's better, Postgres
or MySQL, in terms of how many TPS we can get from a single node.
And I think both Postgres and MySQL learned something from it.
And Alexander created a few patches because some issues were
found, some contention was found.
And it was improved.
In Postgres 9.5 it was not so good and in 9.6, it was improved just
based on this work.
And here achieved 1 million TPS.
MySQL also achieved 1 million TPS.
Good.
But I was surprised like so many years, like 8 years, right?
We have so many Postgres versions, basically 8 Postgres versions
already since then, and maybe 7.
And much better hardware.
Not much, but significantly better hardware.
So I expected to get more from this.
Michael: Well, we looked into the machines, didn't we?
And even back then, they had managed to get their hands on 144
VCPU machine.
And I think you mentioned yours was a bit more than double that,
was it?
Nikolay: 360, and I think they had the Intel's, much older Intel's,
and I had AMD, which is very easily available on GCP, which I
use because I have credits there, right?
So yeah, before we started this call, I cannot miss this.
You showed me what AWS has.
Super impressive.
How many, almost 668, almost 800.
No, more, more.
Almost 900.
Michael: 896 vcpu a month.
Nikolay: And it's Intel scalable fourth generation.
Michael: Yeah, it'll cost you.
Nikolay: But also 32 terabytes of RAM maximum.
Yeah.
2224x large.
Wow.
And they don't have spots on it.
Of course, this is something new.
Yeah, and it's fourth generation, it's scalable.
What I found interesting, like during last year and this year,
various benchmarks, not this, like, toy benchmark, it's a side
project for me, but working with customers, various cases.
What I found, what my impression right now from Intel's AMD,
like recent versions of them available in cloud, is that AMD,
like for example, on GCP, you can get more vCPUs and reach more
TPS on very simple workloads.
And it will be cheaper in general in terms of how many, like
if you divide TPS per dollar, find TPS per dollar, it will be
cheaper probably, and you can achieve higher throughput for simple
workloads.
But when it comes to complex workloads and various problems like
lightweight lock manager contention we discussed a few times.
And in general, complex workloads and behavior of auto vacuum
and so on, AMD looks less preferable and Intel's are winning.
So that's why I'm additionally impressed that AWS offers Intel
scalable Xeons, 4th generation, almost 900 CPUs.
It's insane for me.
32 terabytes.
Wow.
And you think I can exceed 4 million TPS, right?
It's a joke, I guess.
Well, it'd be interesting
Michael: to hear more about what you think the current bottlenecks
are, like where you think.
But I guess, like my stupid brain is thinking, like, this is
probably CPU constrained at this point.
Throwing more at it makes sense that it could...
Like, throw double at it, maybe you can get close to doubling
it.
Nikolay: Well, we collect a lot of information.
We have a wait event analysis, we have flame graphs collected
automatically for each step.
And it still needs to be analyzed deeper.
But what I see right now, the bottlenecks are mostly communication.
Let's actually maybe take a step or a couple of steps back and
discuss what issues I had originally.
Why only 1000000?
Michael: Can we take a step back further than that?
Why were you doing this?
Yeah,
Nikolay: it's a good question.
I was asking myself why do we do this.
Well, for fun, first of all, actually.
I was very curious.
I remember that work.
I think for Alexander and Sveta it was also for fun 8 years ago.
But it led to some interesting findings and eventually to optimizations.
So this fun was converted to something useful.
And we started doing this for fun because we have big machines,
we have very good automation, we have newer Postgres.
I was just curious how easy it is for us to achieve 1000000 and
maybe go further.
And first thing we saw, since we have a complex system, a lot
of things, the observability tools, first thing we saw is that
1 of extensions is a huge bottleneck.
It was pg_stat_kcache.
And it even didn't let us achieve 1 million TPS, quickly showing
huge observer effect, meaning that just measuring pg_stat_kcache
extends pg_stat_statements to see metrics related to physical metrics
like CPU, real CPU, user time, system time, then IOD, real disk
IOD, not just talking to the page cache, and context switches,
and very good detailed physical query analysis.
And we like to have it, but if you have it until the latest versions,
it quickly became a huge overhead when you have a lot of queries
per second for a specific query.
Michael: I remember we discussed this back in, I just looked
up the episode back in February, about overhead.
I'll link that episode up as well.
Nikolay: Right, And yeah, just to recap, we found that it shows
up in the wait event analysis using pg_wait_sampling.
Great.
And we just quickly excluded it and also reported to the maintainers
and They fixed it in 4 hours.
It was amazing.
And we tested it with Bot, confirmed that now it's improved.
So this is how we achieved 1000000.
But why only 1000000?
We have newer Postgres and better hardware.
Did I answer the question why, by the way?
Michael: You said for fun.
Nikolay: For fun, right.
Yeah, this is the key, actually, here.
I enjoy it.
You know, bot, let's just run pgBench that way, or this way.
Let's collect more data points.
Let's visualize it.
Let's repeat this, but with an adjusted version.
Let's compare various versions for example.
Michael: So it sounds like for fun but also you've got credits
and also dogfooding, right?
Like trying out products, trying it out, here's an idea.
Nikolay: Yeah, we try to collect successful experiments, and
we do it on smaller machines as well, but on bigger machines
we collect just to understand how the system is working.
Of course, we're moving towards more useful benchmarks, for example,
when it will be already some specific database and specific kind
of workload and maybe not only PgBench and so on.
And all the tooling will be in place and automation is super
high, so you can just talk to chatbot and get results visualized
and collected with all details and you don't miss any point.
So we collect almost 80 artifacts for each datapoint.
It's huge.
It reflects many years of experience, I would say, not only mine.
For example, you can say, okay, let's compare versions 12 to
17 beta 1.
This is what I did as well.
And I confirmed that for regular PgBench workload, or for Select-only
PgBench workload, no difference.
These versions are stable, no regression, but also no wins.
But for regular...
Michael: 12 through 17.
Nikolay: Yeah.
But for select-only, this is exactly like workload which allowed
me to achieve almost 4 million TPS.
For this, 12 is losing significantly.
Michael: Wait, wait, wait.
You lost me.
So for read-only...
No,
Nikolay: no, for regular.
Michael: Oh, for read-write.
Nikolay: You know, regular transactions, PagerBench has, It's
like, I don't know, like a couple of updates, insert, delete,
a couple of selects, and they are packaged as a single transaction,
and then you just say, okay, let's just check it.
And the way we check it, it's called like actually not load testing,
but stress testing, specialized version of load testing.
And we check in the edge, we say, okay, with 1 client, how many?
50 clients, how many?
100 clients, how many?
And then you have a graph dependency of TPS, how TPS depends
on the number of clients.
Yeah, and it's interesting that we cannot say 0 and due to scale
we need to jump with big steps like 50.
So I say 1, 50, 100, then dot, dot, dot, 500.
And the bot understands that it needs to go with 50 increments,
but first dot is shifted to 1 and it's okay.
So it's interesting, it adjusts very quickly.
So, yeah, for fun and this, and back to versions comparison,
So we found that for select only, we don't see a difference,
but for regular workload and we don't get a million transactions
there, we have only like 30,000 maybe, 40,000, something like
quite lower because it's already writing transactions and so
on.
We saw that 12 is losing apparently, and 16, 17 are winning slightly
compared to like average for these old versions.
But yeah, I think we will continue exploring and maybe test some
specific workloads, not just standard ones.
Michael: And just to check, this is like completely untuned,
like just default config.
Okay.
Nikolay: It's tuned.
Good to check.
Yeah.
We use PostgreSQL cluster, it's Ansible playbooks maintained
by Vitaly, who works with me and Vitaly Kukharik.
And it's a great project if you don't like Kubernetes, for example,
and actually containers.
You just need bare Postgres installed on Debian, Ubuntu or something,
and you want automation for it, this is a good project.
It goes with everything, like everything most popular stuff,
like Patroni, pgBackRest or WAL-G, what else?
Like a lot of simple things.
Actually, even with TimescaleDB, it's packaged.
So you can choose to use it and quickly have it on your cloud
or...
So for self-managed Postgres, it's a good option.
It takes time to install, of course, because it will be running
like apt-get install.
So it's coming with some tuning.
It's similar to what cloud providers do, like 25% for shared
buffers.
It also adjusts operational system parameters, like kernel settings.
Michael: I guess the dataset is so small that 25% still so easily
fits within.
Nikolay: Yeah, of course.
And maybe we can tune additionally, but I didn't see the point
yet.
This default tuning this project provides was also like...
I know it quite well because we adjusted in the past together
a few times, so I know how it works.
So I relied on it for now, but maybe we will additionally adjust
some things.
But queries are super simple, right?
Data is cached.
So...
Michael: I understand.
I'm just trying to think, like, the major things, like, all cached,
I guess, basic, like, write-ahead log stuff, when you've got
the read-write stuff going on.
Doesn't matter.
Yeah.
Nikolay: It doesn't matter because we don't write.
Michael: So in 1 of them, in the 30, 000, I reckon the 30, 000
is low and you could get much higher there.
Nikolay: Well, yeah, if we talk about writing transactions, of
course, we should think about checkpoints and so on.
And it comes with some adjustments based on the size of machine
already.
So I just relied on this default behavior.
We can look further and good thing, we consider settings as 1
of artifacts we collect.
So for history, we record everything, all custom settings and
some system info as well.
So we can revisit it later and think how to improve and next
step.
But bottlenecks are not there yet.
Michael: Yeah.
And I was wondering, last question I had on the kind of the top
line number.
I get keeping pg_wait_sampling for being able to introspect,
for being able to kind of try and work out where is the bottleneck
now.
It's great for that.
But doesn't it also add a small amount of overhead?
I know it's only sampling, so it's probably minimal.
But Were you tempted to try without it as well and just see what
you got?
Nikolay: I think we did.
And I think it's very minimal.
It's worth revisiting as well.
But for now, our impression is that PageVision sampling among...
We have...
That's why, by the way, we encountered the problems with pg.kcache,
because we used basically the package we use for various kinds
of experiments in the case of self-managed approach, right?
Michael: Had it more automatically on by default.
Nikolay: Yeah, We already had these observability tools there,
and this is how we found the pg.stat_kcache problem.
As for pg.wait_sampling among all 3 extensions, pg.stat_kcache, pg.stat_statements,
and pg.wait_sampling, by the way, both pg.wait_sampling and pg.stat_kcache
depend on pg.stat_statements because they basically extend it for
query analysis.
They all provide query analysis.
Regular pg_stat_statements provides query analysis like regular
database metrics like calls, timing, IOMetrics at upper level,
buffer pool metrics, right?
Kcache goes down to physical level, CPU and disk.
And PgWaitSampling for each query, it also provides a profile
in terms of wait events, which is super useful.
It's like different angles of analysis.
So if you can have them all free, it's super good.
Also worth mentioning, it's off-topic but interesting.
Until very recently, PgWaitSampling didn't register events
where weight event is null, which most weight event analysis
we know, such as RDS Performance Insights, they picture it as
a green area called CPU.
By the way, Alexander Korotkov, who's originally the author of
PgWaitSampling, confirmed my idea that it's not fair to name
it CPU.
It should be like CPU or some unknown weight event which is not
yet implemented.
Because not everything is covered by a weight event analysis
in the code base.
Until recently, the pre-reward sampling had a huge problem.
It didn't register nulls, naming them somehow, at least like
CPU or something, at all.
But guys from CyberTech implemented it a couple of weeks ago.
It's huge news.
It's good.
Thank you.
Thank you, guys, if you listen to us.
I'm happy that it's implemented.
And I know GCP Cloud SQL also has PGWait sampling.
And I like the idea that unlike in Performance Insights, in RDS,
we have interface to this data.
I mean, good interface with SQL, right?
We can just query this data right from our database and build
our own observability extensions or automation.
For experiments, it's super important because I want to take
a snapshot and it's easier for me just to take a snapshot using
SQL.
Let's collect profile, global, and per query.
We dump it to CSV files basically.
That's it.
For long-term storage.
So we quickly found Kcache, solved it, and we stuck at 1 million
TPS, which made me sad because it just repeated the experiment
Alexander had 8 years ago.
So what's next?
Do you know what was next?
Michael: I've cheated.
I've read your post already.
Nikolay: You know.
Michael: Yeah.
And I think the last episode as well gave it away in February
with page set statements being next, right?
Yeah.
Nikolay: Huge bottleneck for such cases.
I consider this edge case because normally we don't have super
high frequent query, super fast, super high frequent, and a lot
of CPUs.
But theoretically it can happen.
For example, you have Postgres and a few replicas, and you need
to...
Like you have some huge table, maybe not so huge, single index,
and you need just to find records.
Basically, almost like a key value approach, right?
Maybe partitioned.
And then queries are super fast, much, like a lot below 1 millisecond.
So in this case, if you have a query, which after you remove
parameters is the same.
You have a single normalized query.
Very high frequency.
Michael: Yeah, is it how many?
There are probably only 2 or 3 normalized queries in that pgbench.
Nikolay: Well, by the way, I double-checked for select only,
it's just a single query.
Michael: It's 1.
Okay, wow.
Nikolay: It has also set this macro backslash set to get random,
but I think it's client-side.
So it's not going to Postgres.
It's only for PgBench itself to generate a random number.
And I don't think it's a bottleneck.
I hope not.
Oh, by the way, our PgBench clients were sitting on the same
host as Postgres, which is interesting.
Because they consume CPU as well.
And unfortunately, right now we don't collect host stats and
so on.
We don't know how much of CPU was used.
But usually it's noticeable.
It's not like, not 50% usually, when you do experiments like
that, co-hosting clients and servers.
But I think it's noticeable, I would say maybe 10-20%.
So if you offload them somehow, but if you offload them, you
bring a network.
Yeah.
And we will get back to that point.
So, single machine, clients are on the same machine, eating some
CPU.
And if you have this pattern, just a single query, normalized
query, pre-resource statements becomes a huge bottleneck.
Just removing pre-resource statements, we jumped from 1 million
to 2.5 million TPS.
Michael: Yeah, wow.
Nikolay: Yeah.
And then what's next?
We reached 3 million.
How?
Let's recall.
Then we reached 3 million just because I forgot I originally
I should use it.
I remember Alexander used it in 2016.
Prepare statements, right?
I forgot to use it and how did I understand that I forgot?
Looking at flame graphs.
Our bot collects flame graphs.
Ah, nice.
Yeah.
And I just, I was inspecting flame graphs and I just saw planning
time.
And I'm thinking, oh, we spend a lot on planning time.
Right?
Let's just get rid of it.
So the right way is just to say dash capital M hyphen, capital
M prepared for PgBench.
Michael: And it's a perfect workload for it because it's simple,
same query over and over again, no variants.
Perfect.
Nikolay: Right.
So also interesting that when you like, again, step back, when
you see that starting from 150, 100, 150, 200, you go to 500
clients, then you have 360 cores.
And when you see it goes up to like 100 or 150, and then it's
plateau.
It also gives you a strong feeling it's not normal, right?
Because it means other Postgres doesn't scale in terms of number
of parallel clients to number of cores.
I expected it to scale to number of cores.
Of course, we have clients running on the same host, so maybe
the situation will start earlier, but definitely not at 100 or
150 if you have 360 cores.
So this is how I saw this is not right.
And it happened with pg_stat_kcache and then pg_stat_statements.
Situation started earlier.
So yeah, some bottleneck, obviously.
But now the curve looks already much better.
It's already like we reach maximum point already close to number,
of course, 300 or 360, maybe 400, actually, slightly bigger.
So then it doesn't make sense to increase, but we just check
it, trying to find a plateau.
So prepare statements gave us more than an additional half a
million of TPS.
Michael: Oh, I expected more, actually.
I was...
Only because, you know, when you're looking at, like, super fast
primary key lookups, quite often planning time is more than execution
time in a simple EXPLAIN ANALYZE.
Nikolay: Interesting, yeah.
Michael: Yeah, so it's just interesting to me that it wasn't
like double, but yeah, half a million was nothing to look down
our noses at.
Nikolay: Yeah.
Well, interesting.
Maybe I should think about it and explore additionally, but this
is how it is.
Michael: Did you do force generic plan as well?
Nikolay: No, not yet.
But mind
Michael: you, that would only, that would only like be 5.
Yeah, no, that probably won't help.
Nikolay: Here's the thing, it's in my to-do, maybe for this week
or next, when I have time.
I don't have time.
This is, again, this is a side project just for fun and understanding
general behavior of Postgres.
What I remember about this generic plan, Forrest, is that Jeremy
Schneider posted a very good overview of problems others had
with log manager recently, right?
And I remember some benchmark, some specific case someone showed
on Twitter.
If you just tell pgbench, If you adjust user, for example, alter
user, and set force plan cache mode, this force generic plan,
it leads to huge degradation in terms of TPS.
So I need to check it.
And lightweight lock manager pops up in top of wait events.
So I need to double check it because I don't understand the nature
of it.
It's super interesting.
Michael: I realized soon after saying it that the reason I thought
it would help is not going to help here because you've got the
same query over and over, not lots of little different queries.
Nikolay: But that should be cached for sooner, right?
Michael: Yeah, but like after 5 executions, which is probably
like half a millisecond total.
Nikolay: Yeah, it should be no difference in my opinion, but
no degradation.
Why degradation?
It's something interesting to check, or maybe I just misunderstood.
This is my to-do to clarify.
So I guess it's interesting, exploring postgres behavior on the
edge.
So let's just recap.
PgStart.kcache, we removed it, also fixed, but did we get it
back?
We should get it back, actually, because now it's much better.
They just removed some log, and it's good again.
Then we found PgStart statements.
We achieved 1000000.
We removed PgStart statements.
This is the key.
By the way, when I say we keep only pg_wait_sampling, but it
also depends on PgStart statements, I also feel some inconsistency
in my...
Michael: I've looked it up.
When you said it, I got confused.
It doesn't depend on it.
It's just more stuff is possible.
Nikolay: It's optional, right?
Yeah.
It can run alone, as we do right now in these experiments.
But if pg_buffercache is present, we can join data using query
ID, right?
Michael: Exactly.
Nikolay: This is a good correction, because I said the wrong
thing.
But pg_stat_kcache does depend on pg_stat_kcache, you cannot install
it without it, this is for sure.
Okay.
And yeah, and these guys are not available in cloud environments
usually.
PgWord sampling is in CloudSQL.
I mean, not in cloud, in managed Postgres.
PgWord sampling is available on CloudSQL, but that's it.
But maybe after this podcast, people will consider adding it,
especially if pg_wait_sampling, which is really great, especially
now with this fix from Cybertech.
So we removed pg_stat_statements, reached 2.5 million.
We added prepared statements, exceeded 3 million.
And then final step, how I almost approached 4 million.
It's like 3.75 million TPS.
How?
Like Last optimization.
It's actually not super fair optimization.
My last optimization was, let's just switch from TCP connection
to Unix domain socket connection, which is possible in limited
cases, because it works only when you're on the same node.
You should exclude network completely.
And of course, this is obvious, TCP/IP connections are heavier,
definitely, like more overhead than just when processes talk
through Unix domain socket.
It's much more lightweight.
And this allowed me to jump from 3 something to 3.75 million
TPS, which is good.
And also I found that 17 works worse on Unix domain socket than
16.
Yeah, this is also in my to-do.
Why?
For TCP connections, no difference.
It's on this edge case, like select only.
But for Unix domain socket, I see degradation and it's worth
exploring why.
So it's maybe I found some degradation for 17.
I also need to double check.
Michael: Quite a big difference.
Yeah.
Oh, no, no, no.
So sorry.
Nikolay: 7 to 8% as I remember on higher number of clients.
Michael: Yeah, sure.
Sure, sure, sure.
I made the mistake of looking at 17 beta chart and looking at
TCP versus Unix, not 17 versus 16.
By
Nikolay: the way, when you have a long chat with bot and different
series of experiments, then you want to cross-compare something,
it's super easy.
Just visualize this and that on the same graph, that's it.
Michael: Much easier than getting it to give you the correct
JSON I saw from the chat transcript.
Nikolay: Well, yeah, it depends.
And Gemini is better in JSON than GPT.
Well, we have a lot of fun stories.
So Let's not go there.
But it happens.
So, yeah, almost 4 million TPS for quite meaningful workloads.
I think it's worth checking bigger tables, for example, to see
how it depends.
For example, we can just take like 300 clients and then check,
draw different picture.
It's in my mind, like for example, let's take very small table,
bigger, bigger, bigger and huge, right?
And how it will degrade, for example, right?
Michael: When you say bigger table, do you mean like 100 million
rows instead of 10 million rows?
Nikolay: 100 million rows, yeah.
200 million rows, half a billion rows, billion rows, partition,
not partition.
Like it's many, it's like a huge maze.
You can go in many directions and turn and then have like dead
end maybe, and you turn and inspect another.
Like it's cool.
The cool thing is that I do it like anywhere.
Like I don't know, like I'm eating breakfast and checking what's
up with our experiments.
Let's do something else.
It's just my current hobby, you know, to explore.
I think we should have more tools like Sysbench, for example,
to have different kinds of workloads to be brought up to this.
So yeah, that's it.
I'm very curious for select-only PgBench, anyone had more?
But now, I was thinking, oh, maybe it's the biggest number.
It's definitely the biggest I ever saw, but I didn't see everything.
Maybe someone has bigger numbers or saw bigger numbers or like
totally pg-bitch.
Michael: If they have, I couldn't find it.
I did do some looking before the episode just to try and see
if anyone pushed it further.
If it's been published, I've struggled to find it.
Nikolay: But it's definitely possible on these huge machines
on AWS, right?
Because you have more vCPUs and as we see Postgres scales quite
well in terms of number of clients and number of CPUs.
Michael: That's the direction.
If you're talking about that maze, the way I would be tempted
to go with this is with the currently available cloud-provided
VMs, what's the most TPS I can get?
I don't care about cheating.
I don't care if it's only select semicolon.
And I just would love to know, what is that number?
Is it, I'm guessing it's above 10 million, but is it 12?
Is it 15?
Like, how many could we, like, that would be super interesting.
How
many millions?
Nikolay: Actually, it's another good point.
By the way, I forgot to mention that the problem with pg_stat_statements
statements identified.
Again, if you have a select-only key-value approach and you need
to squeeze more, pg_stat_statements can drop performance 3 times
here, basically.
So it's worth removing it.
And of course, there are already discussions.
Let's mention there is a new discussion started recently, I think,
by Michael Pacquere.
Sorry, I pronounce all this wrong.
How to optimize, what to do with producer statements in general,
and he reacted on Twitter on my benchmark.
So definitely there is an opportunity here to improve producer
statements.
1 idea was start sampling at some point.
Michael: Sampling is the obvious 1.
Nikolay: Also, like, deal with high contention case differently,
and how then, like, update less.
There are ideas.
Michael: I would hate for the takeaway here to be that there's
a real overhead here of pg_stat_statements, mostly because this
is so much the extreme version, right?
This is the same query being worked
Nikolay: on.
I put it everywhere.
Disclaimer, this is edge case.
On production, you probably don't see it.
With your workload, you won't see it.
With your workload, probably it's like a few percent only overhead.
But in some cases, when your workload is just 1 or a few normalized
queries and the frequency is super high, you have a lot of VCPUs
and they start competing, this is a high contention situation.
In this case, just check it.
In terms of wait event analysis, PG stat statements didn't show
up in wait event analysis until I think Postgres 16, because
basically it was not covered by wait events, I think.
Interesting.
Maybe I remember incorrectly, but definitely it was not covered
until some version, and then it started to be covered.
So you can see it, if you have a very fresh version of Postgres,
you can see it in, for example, RDS performance insights.
In this case, it's worth just removing it, but I think some guys
have such kind of workload.
It's not for everyone.
Quite rare, let's say.
It's quite rare.
But it's valid, key value, right?
Just selects.
You need to serve a lot of, like, you need to respond very quickly
using just B-tree, index search, just single row, super fast query,
and then you have a lot.
So just removing pg_stat_statements, you can get more.
So this leads to the question of how to optimize pg_stat_statements.
There is an interesting discussion in Hacker News.
Let's just advertise it a little bit for those who are interested.
Michael: Yeah, for sure.
Nikolay: And another point I wanted to mention, you started this
discussion on various clouds.
Our bot knows already about price, about cost, so we could potentially
check different machines on GCP and bring also AWS and start
discovering, like, I want 1 million TPS, just select where it's
cheaper, which machine, like ARM, Intel, AMD, let's compare different
clouds, different machines, where?
It's interesting, right?
I mean, performance cost.
Michael: I think so.
I liked your transactions per second per dollar.
TPS per dollar, I reckon you should go
Nikolay: with.
Starting from very simple trivial workload like these selects
and then maybe to extend it to closer to what you have and then
you can decide which machine is better for you.
Michael: This is something I've actually been struggling with.
I think it could be really useful is how do you let someone know
that their workload could be done on the next instance size down,
you know, with maybe through a bit of optimization.
But if you give people if people have a rough idea of their workload,
maybe they know it's all OLTP.
And they know roughly like the average transactions per second,
what size instance should they roughly be on?
Like just as a rule of thumb.
Nikolay: This is called capacity planning.
Michael: I know, but also I think so many people are so over
provisioned because they're so under tuned.
Nikolay: Many people are under provisioned.
Michael: For example, I have
Nikolay: cases when people like when, yeah, the people already
hit the ceiling for specific kind of platform, for example, for
example, Graviton on AWS or Yandex, for example, and you have
already the biggest size.
Maybe it's time to, I don't know, like to scale somehow or to
take different kinds of machines because you see how big Intel
you have.
But it's not available in RDS yet.
So I see cases both under-provisioned and maybe you're right,
it's more common.
And over-provisioned is more common.
When you have CPU, like 5% maximum, and you pay for everything.
else, like why?
But I also see cases when it's already hitting ceilings and it's
time to do something about it, right?
Of course.
Maybe I see them because people go to us for consulting and help,
right?
So they don't go for, well, sometimes we have this, like, let's
optimize our spendings, right?
But it's like, we're mostly useful when you have problems with
performance and how to grow further.
Michael: Yeah, well, I see the opposite.
I think not because they come to me, or they come to us, it's
more that I know friends, like founder friends and things, quite
often the cheapest solution in the early days is double the instance
size.
You know, if you're going from $30 to $60 to $120, there's not
many engineering hours that can go into fixing that.
You might as well just upgrade and then worry about it later.
For example, you
Nikolay: became a big fan of Graviton, arm, right?
And you decide, oh, it's great.
And it's like checked performance costs, great.
But maybe it's not like it was good a year ago, but maybe not
now.
And things change very quickly.
And different nodes, instances, and types of instances are added
in the cloud very quickly.
So in an ideal world, we should have some rule how to find the
best optimal choice for instance, family, and size.
And also we should have great migration automation to switch.
0 downtime, right?
Michael: That's the other thing.
I think people are scared of going back down.
Nikolay: Test, test and then switch.
Everything is possible, but requires some effort, unfortunately.
So, good.
4000000 TPS, almost.
Michael: Almost, yeah.
Or 6 million.
I'm going with 6 million TPS.
Nikolay: And maybe someone already has this machine with almost
900 vCPUs, and they can show us 10 million or something.
Michael: I'd expect more.
Nikolay: Maybe.
10 million TPS next milestone for Postgres on a single node.
Great.
By the way, final note, I tried to find information about MongoDB.
I asked guys, I found only slightly outdated benchmarks, like
3 years old or so, and I didn't see anything above, like from
a single node.
I even didn't care about if they have transaction mode enabled
or not, because they can cheat here, right?
But I didn't see 4 million, I saw only 2 million, 2.5 million
or something TPS.
That's it.
So I think Postgres is very competitive.
Michael: It's a nice area.
Nikolay: Yeah.
Okay.
Michael: Good.
Awesome.
Cheers, Nikolay.