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?
Hello, Nikolay. I'm good. Thank you. How are you?
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.
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.
I'm not sure what's the reason, what's the consequence here, it's not obvious.
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.
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.
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
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.
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
well I see updates in time series data all the time.
Some. Okay tell me about that because I don't see I hear mostly about not like
all right
it's append mostly let's say
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.
But, but let's say my,
my example,
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.
Yeah, okay, let's agree on this, we need to distinguish append-only time series and time series with some modifications yeah
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.
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...
By billion, you mean like a billion rows?
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.
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?
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.
Oh yes, yeah.
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,
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.
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.
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...
I don't think it will help with throughput at all. Really? Yes. Well, how many commits per second are we going to have?
Yeah, no, good point.
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,
the thing
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.
Well, that's a good point then. Yeah, we need a primary key that is right.
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.
Or migrate to self-host, or like, you know, self-managed with TimescaleDB?
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,
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
you have a project which you predict won't explode in terms of growth, it's okay.
Yeah, or it's like growing not exponentially, but like steadily.
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-
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
yeah yeah yeah that's
getting rid of that
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
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...
Well, yeah. If it was... Or just use TimescaleDB, that's it, right?
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
do that. But this is available pg_cron and pg_partman are there are available everywhere.
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
to look at
it properly yet, but pg_timeseries?
Ah no, this I haven't tried.
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.
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...
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
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?
Well, to be fair, it has been improving. Like, there's so... Each release, every release, there's quite significant improvement. Well, in
Postgres 10 we got declarative partitioning, right? Yes. And then it was improving only, like, evolution-wise, like, polishing things.
Okay.
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.
Without downtime is the hard part.
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.
Wow.
It's like we focused on hard topics and failed in
simple topics.
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.
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
1 keys
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
option maybe, right?
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.
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.
I like to do, 17 is the most recent so it's 7 years ago.
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.
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.
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.
I guess it depends on what queries, but yeah, it seems to me like that would be... It doesn't depend
on queries. Because it's like real cache, we need to load the metadata for all partitions anyway.
Yeah, but let's say they're analytical queries. It won't matter a few milliseconds of planning.
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
for Postgres. What about the
roll up tables?
The roll-up tables are exactly for that case.
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.
Yes, yes, yes.
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.
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,
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
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.
That's a
great point.
Do you remember the version when it was added? 16, 17, 15?
I think it was roughly when we started, maybe like a year or 2 before we started the podcast. So maybe, yeah.
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.
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.
4 points. What's the last 1? Number 5.
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.
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
say your internet connection, but yeah.
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.
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.
And pg_duckdb episode which is new thing we had pg_duckdb episode.
Yeah but I haven't considered it for time series data.
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.
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.
Right, yeah. Thank you for this little brainstorm. It was a pleasure to talk to you as usual.
Likewise.