Nikolay: Hello, hello, this is PostgresFM, podcast about Postgres,
as you can guess.
My name is Nikolay from Postgres.AI, and as usual, my co-host,
not guest, co-host is Michael from pgMustard.
Hi, Michael.
Michael: Hello, Nikolay.
Nikolay: So you chose a topic, and it is?
Michael: Slow counts, Or like why is count slow and what can
we do about it?
Nikolay: How to make counts slow?
You just count.
So yeah, let's discuss.
I don't know.
For me it's super old topic and I'm not sure I will use some
fresh knowledge here.
I have a lot of fresh knowledge about Postgres, and I share it
constantly.
But this is something which is super old.
There are improvements we can discuss, like index-only scan.
We talked about them recently a lot.
But I guess my role will be to use my knowledge from, you know,
like 15 years ago mostly.
And just before we started this recording, I shared with you
how I a little bit helped PostgREST to get rid of count, right?
Because sometimes you don't need it.
So I'm going to stay in that area 10 years ago.
And If we have something new, I will be just putting more to-do
items to my to-do list to explore and that's it.
Maybe you know some things better than me.
Let's see.
Michael: Yeah, well, the PostgREST link is great because The resource
I still like to point people to on this topic, the best thing
I've ever found on it, is a blog post by the PostgREST author,
Joe Nelson, on the Citus blog.
So I'll link that up for people that I think explains it in a
lot of depth, but in 2016.
So quite a long time ago, in 8 years at the point of recording.
And I was kind of surprised how many things have changed, but
also how little has changed in a way.
Like there are optimizations.
A lot of things that were kind of little gotchas back then aren't
true anymore.
There are a bunch of ways that it's faster now by default.
But the essence of it is still true, like because of the way
Postgres does MVCC, when we do an application.
Because it's Rowstore.
Yeah.
Basically, because all the things that make it great as like
a transactional database count against it.
And a pun not intended.
When it comes to aggregating across a lot of data and count is,
I think, at least in my experience, that's the most common aggregation
people want to do in like typical use cases.
Nikolay: Yeah, that's a good point.
Since the Postgres row store, it's better.
Imagine you have a table of users, for example, as many SaaS
systems or e-commerce systems do, like customers, users.
Since Postgres is mostly like OLTP, row store, OLTP-focused database
system, it stores a tuple, a row.
If it's not too wide, it stores it in 1 place, in 1 page, basically.
As we discussed many times, a page is 8 kilobytes by default
in most cases, and very roughly, if a row doesn't exceed 2 kilobytes,
it will be on the same page, and that's it.
The mechanism called TOAST won't be involved.
If it exceeds it, it will be chunked to pieces and stored in an
additional table called the TOAST table.
So imagine we have a users table.
And then users can log in, so we need to retrieve information
about this user.
And it's good because all information about a single user is
stored on the same page, right?
If it's narrow, right?
As I said, it means that it's quick to retrieve.
It's just 1 buffer hit or read.
Michael: Yes, extremely efficient for like if we wanted to show
the profile of that user if we wanted their name, their age,
you know loads of information about them, single page read to
return that information maybe like a couple once you look them
up in the index, then the heap page.
Nikolay: So it's very I/O optimized basically.
Efficient.
Efficient, exactly.
But if it comes to counting or calculating some sum or average,
minimum, maximum, everything, aggregates.
It's not good because usually aggregates deal with only a single
column or a few columns.
And we don't need all the other columns this table has.
If we have a row store, as Postgres is, in this case, it means that
we need to do much more I/O, many more buffer operations, hits
and reads to get information for like to calculate aggregates
for a single column.
For example, count them, right?
And it means if you want, for example, very, very, very common
ask in startups, in social media, in SaaS, in e-commerce, let's
just show how many new users we have every day.
We need to count daily, right?
And this means, like, imagine if we have only a few records in
an 8-kilobyte page, few users records, few users tuples because
we have many columns.
It means 1 buffer operation will allow us to count only like
4 users, 6 users, and that's it.
Compared to column store where each column is stored in separate
files, and if this file is about ID, it's only ID.
If it's about creation time, it's only creation time, datetime.
In this case, counting is much more efficient because 1 page
can bring us many more users, and we can count them much better.
This is quite straightforward and very simple, a trivial comparison
of ColumnStore and RowStore and why in ColumnStore aggregates
and counts specifically are much faster than in RowStore, just
because of IO, right?
Michael: Yes, and that before you start to think like, I think
because column stores are used for these aggregation and analytics
use cases so much, I think they also have, they tend to have
more optimizations around these aggregations as well than at
least like the "old school" transactional processing databases
have.
I think lines started getting blurred a little bit with some
of the extensions, like some of the newer extensions in Postgres
world that also have some of these optimizations.
But yeah, I think that's a great starting point as some of Postgres'
strengths working against it.
I think another 1 that is not just RowStore versus ColumnStore
is because of how Postgres does visibility.
I think there's if you look at some other transactional process
databases, because they don't do like the same marking pages
as not because they do undo logs, basically.
They can also cheat a little bit for certain counts.
Like, if you want to count all of the users, which if you have
some specific use cases, like let's count all of the records
in this table, they can keep, they cheat and do some of the solutions
that we're going to
Nikolay: discuss maybe at the day to day level.
Well, they're not cheating.
They put new row version in the same place as old row version,
it means that compactness, if we have already compactness of
storage, it's not lost.
Compared to Postgres, where new version comes to a new place, maybe
a different page, in many cases it's a different page.
Maybe it's a place which was taken by another tuple which became
dead and then vacuumed, deleted by a vacuum, right?
Or autovacuum.
It means that we might end up having very scattered storage for
our tuples, especially if we need to count only specific tuples.
We have the WHERE clause in our SELECT COUNT.
It means that we need to, or we might have an index scan for this,
but it's kind of random access, right?
We need to count many tuples which are stored in random places,
and we don't use partitioning.
Our table is 1 terabyte in size.
It's super bad case.
An UPDATE randomizes the storage, right?
The new version is stored in random locations.
And it becomes worse and worse and worse.
It's very scattered, and we lose compactness.
This is how MVCC in Postgres works, unfortunately.
We oftentimes say it's more like rollback-focused than commit-focused.
Because in case of rollback, the new version becomes basically dead
eventually and cleaned up, and the old version remains in the same
place, and we don't lose compactness.
If we have a commit, we move rows all the time.
We move tuples, the physical versions of rows, all the time, and we
lose compactness eventually.
And this is bad for count, bad for other aggregates as well.
Michael: Yeah, so I think that's like a good overall summary
of why count is slow, or at least why
Nikolay: it scales.
Michael: I think while it's not bad on tiny datasets, it's fine,
right?
You probably won't notice it too much, but as data scales, even
in the blog post I was reading, they only did a dataset of a
million rows and already, even like the fastest optimizations
at the time could only get an accurate count in about 100, 200
milliseconds, something like that.
So we're already talking about 10x what you would consider a
fast query.
So even at a million records, which is not many in many use cases,
we're talking about a potentially meaningful length of time.
So yeah, I think, should we move on to what we can do about it?
Nikolay: Let me share the history of this blog post.
I might somehow provoke that blog post that John Nelson wrote a little
bit, and I'm not taking full responsibility for that blog post,
definitely.
But I remember he published it in 2016, right?
And I started using it all the time.
I shared, like, if you want to understand count, just read that
blog post.
Of course, right now I would say it's missing BUFFERS in the
EXPLAIN plans. For sure, because as we just discussed, I always
reason number 1 of why things are slow, right?
We should not talk about timing only. We should talk about BUFFERS
as well all the time.
But back 1 year earlier, in 2015, I started looking at PostgREST,
which right now is 1 of the main building blocks
Supabase has.
And I was impressed, like, great, we can ditch Django and Rails
applications completely in simple, at least in simple cases,
and do everything on server-side and front-end.
This is actually the same time React became popular.
I think it was started in 2013, '14, and so on.
So it skyrocketed in 2015.
And a lot of business logic went to frontend, of course.
Also React Native.
Great.
So we need to get rid of middleware, keep data-oriented business
logic in the database, keep UI-oriented business logic on the
client side, and this is great.
So I found PostgREST, and I quickly realized I came from already
quite heavily loaded systems.
I remember, for example, first time I saw integer for primary
key exhausted, it was in my own project in 2008.
So I already realized some data volumes and how they work in
Postgres, And I quickly found that in PostgREST, they had already
some pagination, very good, but on each GET request, you say,
I want only 25 items on my page, but they also show you how many
overall items you have for this request with all your filters
and so on.
And I thought, well, that's not good.
I know Postgres has slow count, right?
If I every time they counted, so it's written in Haskell, so
I started digging inside source code and I found indeed it runs
count all the time, so I helped them to get rid of it by default.
So I remember I created issue, let's get rid of count by default,
and they quickly agreed and removed it.
So I'm happy I helped a little bit to realize that count should
not be used by default.
And also at the same time, Joe Nelson, the original creator
of PostgREST, started to dig into this topic and found several
alternative ways how to make count fast, including approximate
method.
I like this method.
It doesn't work all the time.
It works very well if you have autovacuum well-tuned, so it
runs auto-analyze frequently, right, because you need fresh statistics.
And it also works if you don't play too much with filtering.
So, for example, if you have a users table and you know to display
overall count of rows in this table, you can use just approximate
approach, which, in a few words, you just run explain without
analyze, you just run explain, select star from users, right?
And the planner will tell you how many rows it expects.
And this is already an approximate number you can use.
This is all trick I first learned maybe in 2004 or 5 when I started
with Postgres.
We used it.
Michael: I think, yeah, I think this trick comes into its own
for things that are harder to find out from the statistics or
from other methods.
I think, like for example, if you have dynamic filters that users
can set, or if you, basically, if you have,
Nikolay: if you
Michael: need to be able to set arbitrary where clauses or you
want to be able to join relations and get estimates then, This
is an extremely powerful estimation tool because you're basically
using Postgres's planner to do it.
Nikolay: But you need to make sure it's up to date most of the
time.
Michael: Well, that's not even enough though because we've had
plenty of episodes where we've talked about times where the plan's
estimations can be way off.
Nikolay: So yeah, just have 2 columns and 2 filters and don't
have multi-column statistics, that's it.
Exactly.
Michael: It's a super easy way.
Yeah, or in 2 different tables, like, where you can't even.
So yeah, so we've talked about the limitations of that before,
but in the real world, I've seen very few, I mean, it's probably
a limitation of my experience rather than because I've seen how
popular these estimation tools are.
I've seen very few cases where people would rather have an estimated
count than no count at all.
In lots of user facing applications, I've seen people want an
exact count, or they don't want to show it.
And there seems to be this...
I cannot agree.
Nikolay: I cannot agree.
For example, I...
What do you see?
Michael: Where do you see
Nikolay: it used?
Well, for example, I had this.
We have a social network, for example, and we just want to display
the total number of registered users and the total number of,
I don't know, like groups created or anything created, posts,
comments on the front page.
And it's just overall number.
Definitely this approach will work there very well.
And we rounded it.
We don't show, well, next idea let's round it but also show some,
if we know current number and 1 week ago number so we can understand
the velocity, the speed of growth and we can also pretend it's
growing right now.
It's like fake, but this is what social media do all the time,
right?
Michael: But this is kind of my point that you wouldn't use the
actual...
So the number when it's an estimate could go down.
Like even if it's like a number of you, like in that case, you
wouldn't want to show a number this week that was lower than
the number last week.
Nikolay: Right.
You're right.
Michael: So there are you wouldn't you still wouldn't use the
exact like.
So to be more specific, there's some really cool algorithms.
There's a really popular extension called HyperLogLog, extension
called Postgres HyperLogLog.
Yeah.
Nikolay: Count distinct.
Michael: Yes, so it's for estimating count distinct, you're right.
So more specific than just count, but it's available on pretty
much every, every cloud provider, which makes me think it must
be pretty popular.
Like it must have been requested on a bunch of these.
Nikolay: Not necessarily.
Things work in life differently.
It might be just, you know, like, we don't know how popular it
is overall.
It might be just cool to have it at some point.
You know, sometimes people have high expectations.
And also in case of managed Postgres services, in any areas of
business, any segments of market, if some big player added it,
others also added it.
Michael: Or even if, let's say only 1 big customer needs it or
wants it.
If they're big enough, maybe you add it.
Nikolay: Yeah.
Also might happen.
Yeah.
Michael: So yeah, good point.
But I do think this whole category of estimated counts is super
interesting.
And that feels like 1 big solution that you can say, look, your
count is slow, 1 option is estimating.
And I think it's really interesting intellectually, and I'd love
to hear from people that do use it in production for real things.
But very often I see people say, okay, that's cool, but what
are the other options?
What else can I do?
Nikolay: Let's finish with when to apply this approximate approach.
I think if you need to count rows in 1 table, it's easy, no filtering,
it's definitely an option.
But you need to make sure autovacuum is configured to trigger,
like autovacuum analyze scale factor is configured properly
so statistics are up to date most of the time, I would say.
But also, if you have filters, these filters should be like single
column, no joins.
In this case, it's good.
In some cases, it can be not good as well.
For example, if you need a daily, it might be off completely.
Or if your project already is old, you have many years of data,
daily might be also challenging because we know by default Postgres
keeps only statistics for 100 buckets.
Michael: Oh, interesting.
Nikolay: I
Michael: see where you're coming from.
Nikolay: But we can raise it.
We can raise it for specific tables, even specific columns.
And this can help as well in this case.
If we know we do daily or monthly counts, we can change this
setting and have more precise statistics, right?
And still use an approximate approach.
Make sense?
Michael: Yeah.
I think it might even be simpler, like it might be that most
people don't end up going this route because they actually find
they can live with the trade-offs of the other approaches.
Like it feels like a last resort estimation.
If you've got a slow count, maybe you're thinking, well, can
we make it like there are ways of making it faster, but they
come with certain trade-offs.
So maybe we should discuss those first and then come back at
the end to say, if you can't live with those trade-offs, then
you have to then, and you still want to display something or
show something.
Nikolay: For me it's vice versa actually.
Approximate count is super easy to implement.
I would consider it first.
Would it work well enough in our case or we exclude it completely?
That's it.
Because it's super easy to implement.
But let's move on.
You already started talking about HyperLogLog, right?
This is for distinct count and this extension is indeed available
almost everywhere.
So if you have a count distinct, why not consider it, right?
Because it might be very good, fast, and so on.
And Joe Nelson and this old article explains it.
So what I would do, I would consider it in this case.
I would do some benchmarks with current data volumes, but also
10x for example, predicting future growth with focus on I/O.
I definitely would look at buffers numbers in the plans and understand
how, like, what about scalability here?
If we scale 2x, 5x, 10x, will still I/O numbers be fine and leading
to acceptable latencies or no?
This is the question, right?
Michael: But this is, we're still in estimation, right?
Nikolay: Yeah, but it's more like it's smarter already, right?
Michael: Yeah, true.
Smarter than just relying on Postgres statistics.
Right.
Yeah, true.
Nikolay: And next, like, I know the article also just, like,
it's interesting that we discussed the article which is 9 years
old or how many 9 right?
Well,
Michael: I actually I did as part of my prep for this I did 1
they Conveniently they provided like code snippets for each step
and I didn't I did a little a few changes But it was very very
easy for me to run this on Postgres 17, actually, just because
I was doing some testing.
Nikolay: What changed?
Michael: Well, so the first thing I noticed was his plans didn't
even have parallel.
Like, bear in mind, we're talking about doing a count over a
million rows.
These days you just expect to see the first query plan have a
gather and...
Nikolay: 3 workers by default, basically.
Michael: Yeah, exactly.
And I didn't.
And I was like, wait, so his blog post was pre-Postgres having
parallel workers.
And that was
Nikolay: the first thing.
And it means roughly it should be by default 3 times faster,
because aggregation, like, append, how it's called, it's easy
in this case, it's just sum of numbers, That's it, right?
Michael: Yeah, count should parallelize really well.
Because I don't know his hardware, and I didn't go back and try
and get a really old version of Postgres, I didn't actually.
Actually, I probably could have just tested by turning off parallelization.
But yeah, probably about 3 times faster.
Nikolay: Good.
This is a good point.
Yeah.
Michael: Yeah, that was the first thing I noticed.
And then there were a couple of other differences in the nitty
gritty, but nothing like fundamental, nothing like too big.
So most of the points still stood really well, even though, as
I said, it was like a lot's changed, but also not that much has
changed.
So yeah, the other 1 was in the past, you had to do a workaround
for you needed to use a subquery trick for like count distinct
to get an index only scan.
So instead of being able to do count distinct on column and get
an index only scan, you had to do like a select within that.
So you had to select distinct to get the index only scan, don't
need to anymore.
That was quite cool to see.
Nikolay: Yeah, that's a good point as well.
Michael: But yeah, like a bunch
So actually index-only scans, we should probably talk about that.
Nikolay: Yeah, we just had the episode about it a month ago.
So it's very related to...
The ideal case for Postgres in its raw store situation, it's
index-only scan with low heap fetches, ideally 0.
Which means, again, that leads to the need to configure autovacuum
to make it work more frequently and move on faster.
In this case, if it's an index-only scan, this is the best we
can do with Postgres.
Parallel index-only scan, maybe, as you said.
And this is good.
I mean, this is the best thing you can see in plans index-only
scan with low heap fetches.
The only better situation would mean you need to start denormalization,
caching, and so on.
I would consider it as a heavy solution to the problem.
Because this would give you a few buffer operations to retrieve
your count.
If you have fully denormalized, maybe, for example, materialized
view.
I would not recommend using materialized view in complex projects
at all.
Default materialized view.
But consider we have it already, and we have proper index on
it.
Finding proper count would be just finding maybe 1 row, that's
it, 1 value.
It's like primary key lookup, right?
Or maybe just index lookup to find 1 record.
That's it.
It's super easy but to achieve that, First of all, it requires
effort.
You need probably triggers or asynchronous triggers somehow.
And second of all, if it's not materialized view, you need to
have something probably with desire to support partial refresh
because default materialized view supports only full refresh,
which is not efficient in most cases and can lead to bloat itself.
Michael: So I've seen people implement versions of this and use
off the shelf things that are kind of in Postgres extensions
for this as well.
So this is actually the that's the approach I've seen most commonly
used for this because it doesn't scale with volume.
I think it's really attractive because even an index only scan,
if you double the data size, you're going to roughly double the
time it takes to do the count.
So you're going to keep bumping into this problem down the line.
Whereas...
Nikolay: Well, yeah, you're right.
If you double...
Yeah, even with the index-only scan, we still need to...
If we, for example, if we forget about structure completely,
talk about only leaf nodes, if we have 2 times more values to
store, we need 2 times more pages, right?
2 times more leaf nodes.
And it translates to 2 times more I/O.
Operations.
And of course...
Michael: And even if it's not 2 times, it's still like, it still
degrades over time.
And these counter-caches...
Nikolay: LRU, not better than 2 times.
It can be even worse, but not better.
Because if you need to keep references to 2 times more tuples,
you do need more space in leaf nodes, right?
2 times more leaf nodes, at least, maybe more actually, if we
have a rebalancing situation and again, not compact storage of
tuples and so on.
Michael: Yeah.
So on the index-only scan, actually, I think it's worth, before
we move on from that, we're thinking about some, because I think
that goes back to the beginning of the conversation.
You talked about rowstore versus columnstore.
If we have an index only scan on only a single, relatively small,
like let's say a primary key, for example, that we can use to
count, it's ordered as well.
So it can help us with count and count distinct.
It's almost columnstore-like in a way, because if we only have
to search the index, we're searching like a single column effectively.
So we're almost getting that columnstore benefit without some
of the aggregation tricks that some of these columnstores
Nikolay: have.
Leaf nodes are doubly linked in both directions, so we avoid
traversing the whole tree.
We just start at the beginning of our range, the first value, and then
we just go scan leaves only, and that's great.
I mean, you're right.
If it's an index-only scan, we don't care about other columns.
But interesting point that we need to make sure we select count
star, we'll probably choose some index, most likely primary key,
right?
But if you count some column which allows NULLs, like we had
a very long time ago, we had an episode about the dangers of NULLs.
And this is one of the dangers, because count, if it's NULL, it
doesn't count.
Count doesn't count NULLs, right?
This thing might be surprising in some cases, even for experienced
folks.
I surprise myself with this once every couple of years at least.
Michael: I see this so often in people talking from a computer
science perspective.
People asking, like, what's faster, count(*) or count(1) or
count(ID)?
But I've never in practice seen any use for anything other than
count(*).
Have you?
Nikolay: Well, in some cases I specifically count not null values
in some column.
So I put the column there and that's it.
In many cases I count with additional filtering.
You know this, it's already not new, it's like 20 years old,
but you know filter extension.
Like you say, count(*), this is our overall count.
And then instead of doing these very heavy structures in SQL,
like case when blah blah, case when else, I don't like them.
So instead of that, you just say count(*) or column filter
and then in parentheses where.
And this gives you opportunity to count many things in 1 go,
right?
You just have
Michael: 1 scan.
Nikolay: Yeah, so it's filtering on the fly.
If it suits your filter conditions, then it will increment your
additional counter.
So you have multiple results in 1 select, multiple numbers returned,
right?
That's a great thing.
And in there, sometimes I use colon, understanding that nulls
won't be counted in this approach.
For example, I want to understand how many people fill this value,
again, user stable and we have, for example, Facebook ID.
It's null if it's not, if Facebook was not connected, in this
case, you say count(*) means overall number of users registered
daily, for example, and then you say count(Facebook ID), counting
only those people who connected their Facebook accounts.
Makes sense, right?
Yeah.
In this case, you don't even need filter.
So it's kind of just maybe syntax sugar, right?
Michael: Yeah, I have seen an argument that * is misleading
because it doesn't mean what it means in other contexts.
And maybe we'd have been better off if it was like count and
then parentheses but nothing in the parentheses would have been
Nikolay: a getable.
Lev Tolstoy Danylovich I actually agree with this.
Because if we propagate this logic about nulls, the * should
mean none of the columns are null.
And this is always so.
Lev Tolstoy Danylovich Yeah,
Michael: but All of the columns are not null.
Nikolay: So let's talk about this denormalized approach.
I think this is most powerful but requires effort.
Michael: And well, it depends.
I think there's like some really simple approaches that have
some big trade-offs and then there's some more complicated approaches
that have like fewer performance trade-offs but they were more
complex to set up.
Like for example I think adding a trigger that lets say you
know what you want to count and you're able to maybe you're doing
a bunch of reads but not many writes to this table and it's already
quite big.
So you're okay to pay a little bit of overhead on each write
in order to maintain just literally a counter.
Nikolay: Yeah, I've done it many times.
But you know what will happen, right?
Under load.
Okay.
Especially if you have foreign key, as I remember, there are
issues because multixact ID and contention.
And imagine you have count, like, you know this popular anti-pattern
many people do in the beginning of their career, when they try
to develop some accounting part of their systems.
Michael: Like debit and credit.
Nikolay: Yeah, yeah.
And they have like common, how to say, common account or something,
like counting all the money flow.
And this requires updates to single row in some table.
Any transaction, financial transaction, triggers this update
synchronously.
And this becomes quickly a hotspot, obviously, because if you
have 2 SQL transactions, already SQL transactions, doing some
financial transactions, that by triggering, they try to update
a single row, they compete.
Like, I mean, this 1 will be blocked until the other finishes
with commit or rollback, right?
And this doesn't scale at all.
Michael: Well, I've seen solutions to that.
I've seen somebody, I think it might have even been Tobias Petry,
It might have been one of his Twitter tips.
All you do is you add more.
If you split-
Nikolay: Depends on the table, right?
Michael: No, no.
So you have, say, 10, keep it small, counts.
And each right triggers, updates one of those, but you don't know
which one.
It gets random, it updates one of them, and then when you want to count
everything,
You have to sum all 10.
Nikolay: Right.
Michael: So yeah, it removes the hot, like it spreads the
Nikolay: The heat.
Yeah, this is one approach.
Second approach, if you, for example, have some...
Well, it's not easy, I would say.
And batching is already a good thing, but it's also like, okay,
you basically reduce contention, but you don't eliminate it.
Full elimination would require a synchronous trigger.
Michael: All I meant is it's quite simple at low scale.
It only gets complex at higher scales.
Nikolay: Yeah, I also did it.
Let's increment by 10 all the time or by 100 in jumps, but it
becomes already not real-time, it's okay.
But ideally, in heavily loaded systems, it should be asynchronous.
So normal writes don't trigger immediately.
I mean, they don't lead to increments at all.
And they just register an event, something happened, right?
Or they just are there, and we will retrieve them by selects
and that's it.
Yeah, synchronously we will do processing with batches as well.
Do you
Michael: know my favorite, the favorite thing I've seen for this
is, well, other than maybe some proprietary solutions is simple
roll-up tables.
So maintaining a count of older stuff in bigger batches.
So you might do a yearly count, and then a monthly count, then
a daily count, and then anything since that day, you go through
all of the data.
But it means that over time, you can roll things up into bigger
aggregates.
And I think that, to me, scales really well.
Nikolay: Yes, but the main approach, naive, simple approach,
let's do synchronous updates and we will immediately have a hotspot
it's bad.
Then okay asynchronous approach there are two like sub approaches.
One is pool like every minute we pool look oh do we have some new
data If we have it in one batch, we issue update every minute.
It's okay approach, actually.
And reduction of it, it's actually regular, it utilizes you and
you just refresh it fully from time to time.
It's again, it's super simple, relatively.
And it lacks partial updates, this is probably very annoying.
So multi-lives view is a very rough approach, regular multi-lives
view.
If it's pg_ivm or how it's called, it should be better.
I haven't tested it myself and I needed to convince.
I had big plans to test Hydra, this new thing with DuckDB, right?
pg_duckdb or something.
Yeah.
This pg_ivm.
I have so many things on my plate to test for maybe already not
months but years.
But unfortunately, I must confess, customers with managed Postgres,
with their own needs, occupy our time lately most of the time.
Because we try to do things which don't require extensions.
RDS, CloudSQL, and others don't have.
I wanted to mention PGQ.
PGQ is present in CloudSQL, right?
It's interesting.
So CloudSQL users can use it and benefit from it because it's
very old and very well battle-tested solution from Skype.
So imagine if we have some things we can register in PGQ, it
will take care of scalability because it has partitioning inside
and so on.
And then we just, it's like push events, push events, and then
consumers of those events, they increment count, but also maybe
in batches and so on.
So like, so issue just 1 update to increment by some number,
right?
And this improves things a lot and makes the system more resilient
because lags will be lower.
It can be not PGQ, but something like Kafka or I don't know,
Sidekiq or what you have, like celery, if you're a Python guy,
right?
But with this asynchronous nature is really good and nobody likes
actually delays like 1 hour because you start with 1 minute,
you say, okay, it's acceptable, but the system grows.
And then you say, okay, we need to reduce frequency to like once
per 5 minutes now.
But it then grows again once per 10 minutes now.
But if you have asynchronous push, like through an asynchronous
message queue or something like an event processing system, In
this case, you can build a good asynchronous kind of trigger,
right?
I wish Postgres had something internally, and every managed service
provider supports it, so all our customers already had something.
But right now, unfortunately, it requires effort, and you need
to build with things you use, sometimes outside Postgres, like
Sidekiq or Kafka or something.
Michael: Yeah, I think we've mentioned pretty much everything
from, like, vanilla Postgres without extensions at the moment.
But there are a bunch of other interesting projects.
I agree.
I think the DuckDB stuff, especially that cross-company initiative
seems super interesting, but there's at least a couple of projects
at the moment pushing some of these analytical queries to a column-oriented
file.
Nikolay: But we need to keep in mind that this also brings additional
challenges.
Again, this means you need denormalization because you have original
table as a source of truth and you need to bring data to extra
table or something and maintain this relationship all the time
so it's consistent and not lagging too much, right?
Michael: Yeah, but I think the optimizations they add might then
be worth it in a lot of cases.
Like the column store and then the vectorization, like there
are so many benefits once you pay that, like if you're willing
to pay the upfront cost.
Nikolay: You can also do more like, I would say it's already
traditional architecture when you have analytical system in addition
to Postgres.
It can be, I don't know, like it can be even ClickHouse.
It can be Vertica if it's old or these days Snowflake.
And we know PeerDB was recently acquired by ClickHouse.
And it means, imagine we have our OLTP storage with users table,
and then through logical replication, it goes to ClickHouse,
for example, and there you can do any accounts with good speed,
right?
Any analytical, you can move analytical workloads, which are
maybe even user-facing to there, right?
It's also worth considering, but it moves us outside of Postgres,
and I don't like it personally.
Michael: To stay kind of within Postgres, I think the work the
Citus team did was super interesting, because I think they were
largely used for these large analytical workloads where they
used the fact they had sharded to multiple nodes to let you parallelize
some of these queries.
So it was, it gained from some of that extra parallelization
And then the other company doing some interesting stuff in the
space, I think is Timescale, who've added some optimizations,
added some functions.
But I think...
Nikolay: Continuous aggregates is a great thing.
Michael: Yes.
And continuous aggregates means that they've done a lot of that
heavy lifting.
If you have access to Timescale, if you're self-hosted or on
their cloud, then you don't have to do all the complicated stuff.
Nikolay: And if you have not only access to Timescale, but to
proper Timescale, because when some company says we support Timescale,
but it's only, how is it called, like community edition?
Michael: Apache.
Yeah, I get confused.
I think they actually community edition is the one that you can't
use.
It's the Apache 2 one that you can use on other clouds.
Nikolay: Yeah, some clouds support Timescale, but only this reduced
option.
But full-fledged Timescale is a great thing to have.
Definitely compression plus continuous aggregates.
We have it in a couple of places, it's so great.
And this is it, like, it's a flavor of Postgres, which I do like,
but it's not available if you're already deep inside RDS or so,
you need to consider migration and so on, yeah.
Timescale Cloud or self-hosted.
Michael: Exactly.
So, yeah, and then the last thing to bring us back to the topic
we talked about last week is they have an implementation of loose
index scan for count distinct.
You can use your own implementation of loose index scan
to speed up some
of these.
Nikolay: Like in
Wiki, right?
Yeah, exactly.
Nikolay: With recursive.
Michael: Yeah, so that's pretty much everything I had.
Nikolay: Yeah.
So I think we covered the basics.
If you want to deal with billions of rows with hundreds of thousands of transactions per second
It's not an easy topic actually.
Michael: Thanks so much Nikolay. Catch you next week.