Nikolay: Hello, hello.
This is Postgres.FM.
And my name is Nikolay from Postgres.AI.
And as usual, my co-host is Michael, pgMustard.
Hi, Michael.
How are you doing?
Michael: Hello, Nikolay.
I'm good.
Thank you.
How are you?
Nikolay: I'm fantastic.
And looking forward to our discussion today, I think it's really
important because, you know, some people think everything around,
everything we store in database is probably can be considered
as time series.
At least any small piece of data, any datum, right?
Was born with some creation timestamp, for example, right, so
we could consider it.
Let's discuss what time series is.
Michael: Yeah, I have heard, I've definitely heard people claiming
that all data is time series data but they tend to work for time
series databases so I think there's some correlation or some
incentive.
Nikolay: I'm not sure what's the reason, what's the consequence
here, it's not obvious.
Michael: Exactly but yeah for the purposes of this though I like
to think of time series data as much more rigidly things that
are very very useful to log at points in time and then compare
and look back at, you know, things we want to know the differences
of or like the averages of or the mins, the maxes, like that
kind of stuff feels much more useful to me to think of in these
terms, because it brings certain challenges, especially at scale.
So I think if we consider everything time series, this episode
will be very difficult.
Nikolay: Yeah, I agree with you that We can look at this like
this.
If anything can have creation or modification timestamps, but
sometimes timestamps matter a lot, like for analysis and for
operations.
Sometimes it matters less.
For example, if we want to work with some geographical data,
maps and so on, we put cities on the map, for example.
Yeah, they have a creation year, or year of creation.
Sometimes it's not clear actually.
Right.
But for day to day operations, it's not that important.
Maybe population is more important and so on.
It's depends.
Maybe position is more important.
So it's hard to say this data is time series, although it has
creation timestamp.
Each part of this has it, right?
So an opposite example is when we log something from something,
from Postgres or from systems or from drivers, I mean, cars,
which are constantly moving on the map or temperature or anything,
right?
This kind of data.
There we log many, many, many times for the same source.
We log something many, many times and we obtain data from the
same source.
For example, there is some sensor or something or some source
of, of metrics and we log these metrics every minute, for example,
in this case, it's definitely, it can be considered time series.
And in many cases, not of, not always, but in many cases, fresh
data, we, we, we wish we want to be more detailed, like ideally
every second, for example, or at least every minute And all data
we still want to have, but it can be more rough, right?
Less precise and can be already aggregated and so on.
And sometimes we can even say, this data is not relevant anymore.
If it's some logs, we don't care about logs from operations from
5 years ago, for example.
It also might happen.
It depends.
It depends on the concrete project product you are dealing with
or building, right?
So but what matters is like we obtain data periodically from
some source, and this is definitely time series to me.
Unlike cities.
Michael: I think for the purpose of this conversation, it's probably
worth distinguishing at what point we start caring, because if
we're logging every second, it only takes a few hundred, maybe
a few thousand devices before that significant scale but if we're
if even if we're logging every hour if we've got let's say a
million devices logging every hour, that's also a lot of scale.
But a lot of use cases won't have that.
So there are smaller cases where whilst they are time series,
like let's say you've got a little home setup where you are monitoring
the weather outside, maybe in the garden and maybe at the back
of the house, like maybe you've got like 2 or 3 sensors or maybe
like 4 or 5 devices logging every, even every second, say, you
might as well, you don't, probably don't need this episode, right?
You don't need to do any tuning, you don't need to do that much.
Okay, interesting.
You think
Nikolay: maybe.
It depends.
There is a big misconception for new Postgres users.
They think if table size is small, in terms of row count, it
should be very fast, no problem.
But we know how MVCC is organized in Postgres, right?
Every update means new physical
row, which we call tuple or tuple.
We're still not sure after 2 years,
almost 3 years of discussing.
Anyway, physical row version is
created on every update, unless
this update is special case, like
HOT update.
It depends.
So it's basically delete and insert
all the time.
By default, it's so, right?
It means if you update the same
row many times, with SELECT,
you still see 1 row, but physically
it might be many, many physical
versions, and you have a lot of
dead tuples, and then autovacuum
comes, which is, autovacuum is
converter of dead tuples to bloat
and you have a lot of bloat and
then you have problems with performance
although you think table is small.
Michael: I completely agree but
I think for this use case you
don't time series use case normally
is append only right normally
you're just inserting new roads
so I don't think it applies here
Nikolay: well I see updates in
time series data all the time.
Michael: Some.
Okay tell me about that because
I don't see I hear mostly about
not like
Nikolay: all right
Michael: it's append mostly let's
say
Nikolay: imagine for example CI
pipelines.
I won't tell the name of the company,
but some listeners from
that company should recognize it.
And you have pipeline and some
steps like jobs or something inside
the pipeline.
And when you create this pipeline
and then you update it a few
times, if status has changed, it
reminds queue like thing, but
it's not just queue like, you need
to log everything and present
it.
All right.
So you might create some record
and then updated status.
Yeah.
Right.
Michael: But, but let's say my,
Nikolay: my example,
Michael: Well, maybe by some definitions,
but I'm thinking more
like you mentioned logging or you
mentioned monitoring, maybe
real-time analytics, like these
kinds of things where time moves
on and we just want readings over
time, we want to graph.
Nikolay: Yeah, okay, let's agree
on this, we need to distinguish
append-only time series and time
series with some modifications
yeah
Michael: maybe but yeah really
good point though if you've got
modifications you might have to
worry about these tips and tricks
quite a bit earlier in terms of
your raw volume.
Good point.
Nikolay: Right, right.
And yeah, so and you like the problem
is not huge scale, but
I think it's a great idea to think
in advance how many records
you expect per some time, per day,
per hour, per minute, per
day, per hour, per month, per year,
and then what is your plan
to do with it and understanding
that it's approaching billion
or hundred gigabytes or something.
You need to...
Michael: By billion, you mean like
a billion rows?
Nikolay: Billions.
Billions of rows or hundreds of
gigabytes, terabyte.
For me, billion is terabyte for
our row.
Roughly, very roughly.
It depends a lot.
It can be 5X to both sides, right?
But for our row storage Postgres,
it's roughly so.
For real, for many time series
database systems, which are only
for time series, like VictoriaMetrics,
or you can consider ClickHouse
as well.
1 terabyte, 1 billion rows is
maybe 100 gigabytes, maybe 10
gigabytes even depending on compression.
Their compression is much better.
So, but this is like overall capacity
planning, right?
But we can have performance problems
locally thinking, oh, this
is a small table.
It should be like 1 millisecond
lookup.
But then you, if you, if you ignore
MVCC in Postgres, you might
end up having a lot of dead tuples
or bloat, and latency will
drop a lot as well, and that's
not good.
Michael: Cool.
Well, if we go back, if we go to
the append only scenario, what
are the main problems you tend
to see in that setup?
Nikolay: Well, for me, the main
problem is that let's pull the
elephant from our room, in the
room.
Let's recognize it.
There is a great product called
TimescaleDB.
Michael: Oh yes, yeah.
Nikolay: Absolutely great.
They have, in my opinion, 2 major
things they have is compression,
unbeatable compression for Postgres,
for time series, And continuous
aggregates, which is also good,
like kind of, it's convenient.
You can build continuous aggregates,
but it's super hard to build
good compression.
And the problem is with TimescaleDB,
it's too great, but it's
not part of core Postgres.
And it's an extension which is
available only either on Timescale
Cloud or self-hosted if you are
okay to use non-open source.
It's source available.
It's very open.
They, I know they allowed it to
use it to many, many companies,
but it's still like if you go into
build some platform, you probably
will be not allowed to use it.
And so on with compression.
I mean, not, I'm not talking about
Apache TimescaleDB, but I'm
talking about Timescale licensed
up TimescaleDB, which has compression
and continuous aggregates.
Compression is great.
Again, I recommended to read a
couple of blog posts before, I
recommend it again, they are amazing.
But it's not core of Postgres,
Michael: so...
But I actually think, I think the
list of TimescaleDB features
are a really good list of things
that are tricky with time series
data yeah like in general so they've
also done great blog posts
on things like How to copy data
into post or how to ingest data
as fast as possible So that's 1
of the 1 of the trickier things
about extreme volume and would
do maybe use A few yeah, but not
from they this was actually a tips
and tricks blog post, this
was actually like 13 tips for ingesting
and all of these types
of blog posts come out with oh
you should use Postgres copy feature,
but like there are some exceptions,
but basically if you can
get it in in a way that it's making
the most efficient use of
writing data in batches, then you
can get much much better throughput.
But then there are other tips and
tricks like not having many
indexes on the table that you're
ingesting into.
Maybe none, but probably at least
1.
Nikolay: Also foreign keys can
slow you down a lot and also check
pointer behavior.
So you need to, if you expect a
massive ride coming like 1 time,
it's worth thinking about Checkpoint,
checkpointer tuning like
for this window only accepting
risks that if we are crushed,
recovery will take longer.
So you raise checkpoint_timeout
and max_wal_size, and it should
speed up because checkpointer will
put less pressure on disk
to write more.
Michael: Yeah, nice.
Well, on that note, and I know
this is something I think we're
probably going to talk about in
the future, but because you can
set it on a, like for a single
one-off thing, you could even
consider in that case turning off
synchronous_commit for those
ingests, like if you want to...
Nikolay: I don't think it will
help with throughput at all.
Really?
Yes.
Well, how many commits per second
are we going to have?
Michael: Yeah, no, good point.
Nikolay: But also, thinking about
checkpointer tuning, I think
it depends, because in a PAN-only
specific case, probably we
won't have a lot of winning, a
lot of benefits from checkpoint
tuning because checkpoint tuning
for massive writes shines when
we have random inserts to many
pages and we revisit page.
If in append only,
Michael: the thing
Nikolay: is that we're constantly
writing to the end of data,
of heap and also B-tree index.
It's only to the right, to the
right, and it's great.
Michael: Well, that's a good point
then.
Yeah, we need a primary key that
is right.
Nikolay: Yes, that's why UUID v4
is a terrible idea, right?
Yeah, yeah, yeah.
Right.
So you want UUID v7 or just
bigint or something else which
has similar like growing...
Like ULID type thing.
So B-tree inserts will happen only
to the right, always to the right,
right and so on.
And it will be packed and so on.
But this also leads us to the question
about partitioning, right?
Because partitioning is the key.
So let me finish my, my rattling
session a little bit about Timescale
and how sad, how sad it is that
they are not part of fully open
of Postgres because I know this
is like, this is their business.
I fully respect that at the same
time.
I just have companies who come
to us asking for help.
And many of these companies, we
do health check, we improve health,
we level up.
And then like our typical client
is a startup which grows and
starts having some issues, very
often they are on some managed
Postgres service.
So backups are solved, replication
is solved, and so on.
But then they have some problems.
And It's typical that many of them
consider partitioning.
And then we realize, okay, partitioning
is needed because tables
are already large and we have simple
rule, like if you exceed
100 gigabytes, you need to do partitioning.
But then we see, oh, this data
is actually time serious, right?
And it's inevitable to consider
TimescaleDB, but they, for example,
if they are RDS or somewhere else
similar, they cannot use it.
All they can use is just to start
doing partitioning themselves,
maybe with Partman, maybe not.
Or migrate to Timescale Cloud.
And we sometimes can help them
to consider.
Michael: Or migrate to self-host,
or like, you know, self-managed
with TimescaleDB?
Nikolay: Well, it depends.
For some companies it's a good
path, but if they have a very
lean approach in terms of how much
engineers they want to have,
how much of them they want to be
involved in maintaining backups,
applications, and so on.
Like it's, it's, it's a big question.
Honestly, I expect, I fully like
agree with people like 37 Signals,
migration back to, from cloud,
right, even.
Or at least from managed service.
I think it will be happening, and
if crisis hits us and so on,
it will be happening for sure.
Many people will try to go away
from RDS because honestly I think
these days we have all the pieces
already battle proven to have
cluster working with backups and
replication, autofailover, everything
like that.
It may be Kubernetes, maybe not
Kubernetes, doesn't matter actually.
We have many pieces already battle
proven and we can live without
RDS or any managed service and
spend not a lot of efforts to
maintain this.
But still, there is also business
for Timescale.
They want to have paid customers.
I, again, I respect that.
And sometimes we have to consider
moving there, but if you move
there, also complications because
Timescale Cloud has limitations,
like just single logical database,
right?
It's kind of, in some cases it's
not convenient to have to, like
you, you have a cluster, but you
cannot run additional create
database and have multiple logical
databases inside.
This is limitation of Timescale
Cloud.
And anyway, we, we, it's like middle
age in Europe.
We have many, many, I don't know,
like dukes or so, and many
counties, right?
Counties.
Yeah.
It's called counties in your country
and in our country as well.
Like I'm living in San Diego County.
So yeah, this is, this is like
fragmented space.
Right.
And, and if you want good TimescaleDB,
you want, you need to
go either there or here, you cannot
do it on any other managed
service.
It's, it's a bad, I don't understand
why they cannot reach some
agreement and have some, some business
agreement.
I mean, AWS and Timescale company
and Google and Azure, I mean,
Microsoft and so on, it would be
good for all.
Right, it's ridiculous.
Because I'm telling everyone, like,
compression is great.
Read these posts, you understand,
just make experiments, it's
so great, right?
This is like, Without it, my advice,
okay, maybe not Postgres
for this kind of data, maybe ClickHouse.
ClickHouse was created to handle
the load for Google Analytics-like
load.
You ingest a lot of logs into it,
a lot, a lot of time series
data and store and so on.
While TimescaleDB shines and compression
because they apply
like 2 dimensional compression,
they also compress column wise,
right?
That's great.
And for, if you're, if you have
metrics from some sensor coming
to you at every second, for example,
they cannot change quickly
a lot, right?
So they change a little, change
like a temperature or position,
right?
Position of some driver.
And it means that we can convert
it to deltas and then additionally
compress and additionally compress
and TimescaleDB reaches like
30x compression for example.
I remember some case.
It's great without it Wow, it's
a lot of data, And it's not only
storage, but it's also memory,
buffer pool, WAL,
Michael: WAL.
OK, yeah.
Let me pull us back, because I
think you're right at a certain
scale.
Even you care about pure disk size,
it really, really matters.
But I think there's a lot of use
cases, I'd say most use cases
that I see are smaller.
Like, there are a lot of time series
use cases that are much,
that are, like, in that middle
ground, right?
Like, they're big enough to be
considering partitioning some
certain like maintenance optimizations
some set like some really
considerate schema design but they're
not but they still manageable
within Postgres core Postgres
quite healthily If
Nikolay: you have a project which
you predict won't explode in
terms of growth, it's okay.
Michael: Yeah, or it's like growing
not exponentially, but like
steadily.
Nikolay: If you expect you won't
have more than like 10 terabytes
of data in the next 10 years.
Oh, it's okay.
Maybe 5 years.
It's okay to implement partitioning
yourself and maybe aggregating
all data and repacking it somehow,
for example.
Partitioning is great because it's
many things.
Like it's, it's gives you ability
to, for example, if you have
partition, which is like one day
from previous month, okay, it's
time to repack it and store only
aggregates, rough, rough data,
per hour, for example, not per
second anymore.
It's a huge reduction.
And if you don't have partitioning,
it will be nightmare to deal
with updates or deletes and then
MVCC again right a vacuum will
come and you have empty space and
then some insert happens to
like from new new insert will go
there and now this page is out
of visibility map and it's not
all visible it's not all frozen
so what the vacuum needs to chew
it again it's like if we have
partitioning we have locality and
by the way it's a like I talked
to some someone recently and we
discussed partitioning, custom
partitioning without TimescaleDB and discussion was, should
it be partitioned by customer or
project or time-wise.
Of course, time-wise, if we talk
about this kind of data, it
must involve time consideration
into partitioning because in
this case, you will have fresh
partition where all inserts coming
to one partition, right?
And then you have archived data
and you can deal with it separately.
So you can repack partition, detach
it, detach old partition,
attach new partition, whatever
you want, fully online.
And autovacuum will be fine and
no new inserts will come to
this partition, right?
Unless you support some occasional
updates of old data.
Even then, if it's not massive,
those updates, it's still good.
You can do that, you can repack
by updating basically.
You have a second resolution partition
for all the day from the
previous month, and then you convert
it to, for example, hour
resolution.
3600 times smaller.
It's great.
And actually, this is a great recipe.
This is not compression, but it's
like kind of replacement for
alternative, right?
And then you have-
Michael: I hear this get called
roll-up tables quite often, which
I really like.
And you could have multiple granularities,
like you could have
hourly, but you could also have
daily, monthly, like depending
on how fast you need those queries
to be or what you're using
them for, what your dashboards
are showing or what customers
need to see.
You can have a few different ones
and each one is going to be an
order or orders of magnitude smaller
than the previous one and
therefore faster to query.
Exactly.
Yeah.
So I think this is a really good
solution and the other thing
about partitioning I'm not sure
you mentioned yet is is not just
kind of rolling up but you but
also just purely dropping the
Nikolay: yeah yeah yeah that's
Michael: getting rid of that
Nikolay: solution yeah or well
all there is also an attempt to
have tiered storage.
So maybe all partitions should
go to S3 or GCS or some blob storage
on Azure, how it's called, they're
still trying to remember.
Anyway, like object storage and
where we can choose the level
like S3 Glacier, right?
It's, it's cheaper.
It's slower, but people rarely
access this data and so on.
Yeah, there are several options
here.
How you need to, you need some
strategy for data management and
long, longer term, right?
For this data.
I see how companies struggle when
they're already hitting some
limits and they need to do something
about it.
But it's a lot of effort to redesign
things.
If you have earlier, like, think
earlier, maybe it will save
some.
It's harder in the beginning, as
usual.
More like, bigger challenge to
solve.
But then it
Michael: pays off.
Yeah.
I think this is such a difficult
1.
Like, you've got all of the traps.
You've got traps in both directions,
right?
You've got all of the premature
optimization traps, but then
also...
I don't...
Nikolay: Well, yeah.
If it was...
Or just use TimescaleDB, that's
it, right?
Michael: So I do think there is
an argument for it, and not just
because of the scalability, but
I think, like, developer experience-wise,
you don't have to...
Like, even with pg_partman, there's
a bit of setup.
Like, with continuous aggregates,
there's quite a lot of setup
for you if you want to
Nikolay: do that.
But this is available pg_cron and
pg_partman are there are available
everywhere.
Michael: I completely agree I completely
agree I'm just saying
from a UX point of view, like developer
experience point of view,
there's also a bunch of functions
that come with TimescaleDB
that are really useful, make certain
things much simpler to write
queries for.
So I do think there's an argument
for that but I do think you
are then trading off freedom like
there are then only so many
managed services you can use and
I think even there was a recent
alternative that stuck that popped
up from the team at Tembo,
I don't know if you, I haven't
had a chance
Nikolay: to look at
Michael: it properly yet, but pg_timeseries?
Nikolay: Ah no, this I haven't
tried.
Michael: Yeah it's new and they
left the exact same reason in
the readme, you can see the exact
reason they've done it is because
they could only have used Apache
TimescaleDB and they realized
they needed something a bit more
than that.
Nikolay: And then additional complexities
hit you when you grow
and at some scale schema changes
become a problem.
Right, For partition tables they
are much more harder, they're
not easy to do at scale anyway.
But with partition tables...
Michael: But this is, this was kind of my point, that you're
paying some complexity up front for doing this and it is kind
of a premature optimization to partition a table that is absolutely
tiny and yes it will be a pain to move from a single table to
a partitioned table later but you've also got away with some simplicity
for maybe a year or 2 maybe longer
Nikolay: well anyway I think if you have partition if you have
timescale time-serious situation yeah and you expect some terabytes
of data in a few years, I would implement partitioning right
away anyway, because this would avoid me of using some queries
which are not compatible with partitioning.
And these query is really hard to redesign later.
It's better to have some partitioning right away and partitioning
key should be involving timestamp.
And that's great.
I mean, yes, it's some inconvenience in the beginning, but it's
already like, it's not rocket science at all these days, like
many people done it and there are blog posts.
I have some how to recipe, how to partition a table with
UUID v7.
And I use, it has like example before TimescaleDB, but it's
agnostic to version of Postgres, so it can be without TimescaleDB, same thing.
And yeah, I think partitioning
is a must if you expect terabytes
of data, especially if dozens of terabytes become absolutely
must in the beginning.
And it's a pity that we don't have, like do you think partitioning
could be improved in Postgres?
In which areas?
Michael: Well, to be fair, it has been improving.
Like, there's so...
Each release, every release, there's quite significant improvement.
Well, in
Nikolay: Postgres 10 we got declarative partitioning, right?
Yes.
And then it was improving only, like, evolution-wise, like, polishing
things.
Michael: Okay.
Nikolay: Do you think UX wise big step could be done, UX wise
to simplify?
Like for example, I remember we are helping very famous company
to partition like during a weekend.
Like I think it was more than 1 year ago.
It was a AI company, super popular.
We helped them and it was great, but it was interesting experience
actually to help like to do it very quickly without downtime
and so on.
Michael: Without downtime is the hard part.
Nikolay: Yeah, but for me it's
easy because I know the, like
for me, not so easy, but I know
how to do it and how to verify
it before deployment and so on.
Hold my focus on that part.
What we missed, I'll tell you the
story, it's funny.
I was washing my car and I was
chatting to my friend also from
from a Bay Area, like I told him,
you know, we helped this company
partition, to implement partitioning.
He's, ah, because he mentioned
he's using the company.
He's using products of the company.
Okay.
He's using product.
I mentioned, okay, we just implement,
help them implement partitioning.
And he said, Oh, the service is
down right now.
I said, really?
And I'm looking at my watch and
I see 5 p.m.
And I realized, 5 p.m.
Probably this is the time, it's
like 5-0-0, right?
It's the time probably when new
partition must be created.
And this we completely missed.
Creation of new partitions.
Michael: Wow.
Nikolay: It's like we focused on
hard topics and failed in
Michael: simple topics.
Nikolay: You must create partition
in advance.
And this is on shoulders of developer.
That's not all right, right?
We need pg_cron or something.
Why is it like...
DX, developer experience, is not
excellent, unfortunately.
I think some things can be improved
in this area.
But of course it could be good
to do it evolutionarily as well.
Like maybe pg_cron should be part
of Postgres, first thing.
Cool.
Yeah, maybe.
At least, at least, contrib module
or something, I don't know.
And then probably partition creation
could be part of declarative
syntax, you define it when you
create your partition schema,
you say, okay, new partitions should
be created hourly or daily
or something.
Yeah.
Right?
Right now it's terrible, everything
on main.
I mean, it's not terrible, it's
much better when I implemented
partitioning for my first RDS project
and it was inheritance-based.
I did it with 0 downtime, it was
great, but I spent like a couple
of months.
Yeah, this is time when I started
understanding that clones are
great because you can experiment
and check everything.
Michael: Going back, I think what
I meant when I said no downtime
is the hard part, I mean new features
for partitioning generally
come in and there's so many kind
of like foot guns of what you
can do without a heavy lock and
what you can what needs
Nikolay: 1 keys
Michael: like even dropping an
index yeah so there are so many
tricky things that get implemented
first in a way that has heavy
locks and then later in a way that
can be done without, so therefore
in a 0 downtime fashion.
So I do see it as probably it's
going to only improve incrementally,
but I don't see why automatic creation
couldn't be a complete
thing.
It could also help, yeah, remember
we were talking about pg_squeeze
and that's another feature that
even though I think it was vacuum
full concurrently could come in.
With glass
Nikolay: option maybe, right?
Michael: Yeah but the other feature
it has is scheduled, let's
say like repacking.
Repacking.
Or whatever, squeezing.
So scheduled squeezing.
Well, it's different.
It would need the same kind of
logic though, right?
It would need the same, I guess
that's trigger based instead
of time based, but it's a similar
kind of Background worker type
thing.
Nikolay: Oh, yeah, maybe you're
right.
Actually.
Yeah.
I see evolution here.
First of all, I fully agree, this
whole class of problems that
are gradually solved in every major
release related to locks,
partitioning versus locking, right?
Indexes, foreign keys, detaching,
attaching, we remember every
release like last 10 years maybe.
When the 10 version was released
it was 2017 I guess.
Michael: I like to do, 17 is the
most recent so it's 7 years
ago.
Nikolay: Yes, I'm old guy, I still
remember that 95 was 15, 96
was 16, okay.
So I think you're right, and some
steps could be done here, and
experience could be improved, And
I mean in smaller steps.
And yeah, but you're talking about
repacking for to mitigate
bloat.
Previously, we talked about repacking
to make partition like
100 times smaller and having more
or less precise data, different
precision level of precision.
I think this the latter should
be on schedule.
And to be on schedule right now,
there is no option in Postgres.
You need pg_cron, right?
That's why I think pg_cron could
help to open some new features
for partitioning if it was in the
core.
Yeah, maybe we have gone sideways
a little bit from time series,
but time series and partitioning
they are very strongly connected
in my head, in my mind.
Michael: Yeah, I completely agree.
I think you mentioned Timescale
has a couple of important features,
but I would add the automatic partitioning
as another thing.
Nikolay: Oh, it's great, Yeah,
yeah.
It's just, it's very declarative.
You just say I want every hour
and then you just forget about
it.
It works really great.
By the way, also, if we, for TimescaleDB, we don't, we're not
scared to have a lot of partitions.
They're called chunks, right?
Yeah.
And it's interesting that if you
have time series, you want partitioning,
you should explore what will happen
if you have thousands of
partitions, for example.
And this provokes you to use pooling
and establish connection
less often.
Remember, I have a blog post and
mistake was made that real cache,
real cache related mistake.
So every time Postgres connects
again, first query will be slow
if you have a lot of partitions.
But subsequent queries will be
fast.
So if you have many, many partitions,
you should have connection
pooling and establish connection
not often.
Or somehow accept that the planner
needs more time for the very
first planning.
Michael: I guess it depends on
what queries, but yeah, it seems
to me like that would be...
It doesn't depend
Nikolay: on queries.
Because it's like real cache, we
need to load the metadata for
all partitions anyway.
Michael: Yeah, but let's say they're
analytical queries.
It won't matter a few milliseconds
of planning.
Nikolay: Analytical queries, time series, without TimescaleDB,
please go out.
It's not Postgres case.
Well, unless it's a small project.
If it's a small project fine but
if it's not small let's not
fool ourselves.
It's not
Michael: for Postgres.
What about the
Nikolay: roll up tables?
Michael: The roll-up tables are
exactly for that case.
Nikolay: Well yeah if you do a
very strict strategy and very
aggressive roll-up and three-stage
approach and maybe your counts
and sums and means, maxes, aggregates
will be fast, I don't know.
Yeah.
But it's easy to experiment, actually.
It's a one-day project to experiment
and check for your particular
case, I mean, to explore what latencies
will be and so on.
This is definitely worth it if
you plan some new thing to explore
latencies.
Michael: Yes, yes, yes.
Nikolay: Okay, okay.
I actually, I described this roll-up
approach not knowing it's
called roll-up myself, and then
I'm still pulling us out of Postgres.
But, well, Postgres is great.
I know when you go dig deeper,
you see some problems, you talk
about them, and then people come
to you and say you are Postgres
hater.
It happened to me not once.
Yes that's why I spent like 20
years and still not stopping working
with Postgres and thinking and so
on.
Yeah I'm Postgres hater.
Michael: Let's do, should we do
a quick fire, kind of a few last
things in terms of considerations
while we're here?
I think,
Nikolay: well, we didn't talk about
updates, unfortunately.
Yes, but I think if updates are
in place, definitely first thing
I would try to localize with them
with partitioning again, partitioning
localization.
My new fresh idea, not mine, I
actually borrowed it from a good
guy who is expert longer than me.
So idea is that if you have localized
writes, thanks to partitioning,
it also helps to backups, incremental
backups, because you don't
touch old pages.
So this makes incremental backup
lighter, smaller, right?
And recovery as well.
It's also good.
It's not about storage only.
It's not only about the buffer
pool and WAL rates, full-page
writes and so on.
It's also about backup.
So everything is connected here.
It's great to have partitioning
for large tables exceeding like
100 gigabytes.
So this is first thing, compression,
any kinds of, or this roll-up
idea, this is second 1, right?
Yeah, a couple of
Michael: things we haven't talked
a lot about because of the
volume here I think disk space
can be a big thing I know compression
really helps there but another
thing that can help is if you
do have any secondary indexes like
not not the primary key if
you do want another index, maybe
consider BRIN as well, especially
in the append-only case, but even,
we had a whole episode about
it, but with the new operator classes,
with min-max multi, it's
really quite powerful and much
much smaller than B-tree.
Nikolay: That's a
Michael: great point.
Nikolay: Do you remember the version
when it was added?
16, 17, 15?
Michael: I think it was roughly
when we started, maybe like a
year or 2 before we started the
podcast.
So maybe, yeah.
Nikolay: Anyway, in very fresh
versions.
Yeah, and also continuous aggregates
in TimescaleDB is a great
thing.
If you don't have TimescaleDB,
then something should be done,
like probably incremental materialized
views and so on.
Michael: Yeah, there's that extension.
But also there's a really great
talk by the team at Citus on
roll-ups.
So I will share that in the show
notes.
There's also on this topic there's
with a lot more practical
tips.
There's a talk by a fellow Brit
of mine, Chris Ellis.
He's done a talk on, it was about
Internet of Things, so sensor
data, but it's basically just time
series, right?
So I'll share a link to that talk
as well.
Nikolay: 4 points.
What's the last 1?
Number 5.
Michael: What about language stuff?
Like, Chris, in his talk, I remember,
talked quite a bit about
window functions and interpolating
gaps.
There was probably some considerations
there that you probably
still want access to SQL.
Postgres is so powerful there that
I think some of the other
specialist databases, it's slightly
harder to do some of that
stuff or it's a lot more it's a
lot more learning.
Nikolay: My favorite function is
lag.
Yeah.
I deal with A lot with lags in
various kinds of replication.
And here we have a window function
called lag, right?
I thought you were going to
Michael: say your internet connection,
but yeah.
Nikolay: Well, this is as well.
Lags everywhere.
Anyway, let's consider this number
5.
Postgres has great SQL, standard
SQL support, window functions,
a very rich set of them, and here
it can help as well.
Michael: Yeah, last thing, we have
mentioned Timescale a lot,
but there are other extensions
like Citus, Hydra as well, worth
considering for specific use cases.
And pg_timeseries, I'm gonna check
it out.
I'm not sure quite what they've
got in terms of features yet
but it's Postgres licensed.
Nikolay: And pg_duckdb episode which
is new thing we had pg_duckdb
episode.
Michael: Yeah but I haven't considered
it for time series data.
Nikolay: Do it!
Yeah, why not?
Well, all things are fresh.
I just saw how my team member used
DuckDB just locally to read
some CSV logs, snapshots actually
collected from Postgres.
It was great, like it was some
SQL, DuckDB SQL, and super
convenient as well.
So I think we have a lot of things
and tools which are open source
and that's great.
Michael: 1 more idea.
Even the topic from last time was,
or 2 times ago, was pg_dog,
like sharding.
If you're getting to extreme, light,
heavy workloads, you could
shard by, you could have time series
partitioning on every shard,
but then maybe a range of device
IDs for each 1 and shard based
on something other key.
Nikolay: Right, yeah.
Thank you for this little brainstorm.
It was a pleasure to talk to you
as usual.
Michael: Likewise.
