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.