Postgres FM - Time-series considerations

Episode Date: April 11, 2025

Nikolay and Michael discuss time-series considerations for Postgres — including when it matters, some tips for avoiding issues, performance considerations, and more. Here are some links to things they mentioned:Time series data https://en.wikipedia.org/wiki/Time_seriesTimescaleDB https://github.com/timescale/timescaledb13 Tips to Improve PostgreSQL Insert Performance https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performanceWhy we're leaving the cloud (37 Signals / Basecamp / David Heinemeier Hansson) https://world.hey.com/dhh/why-we-re-leaving-the-cloud-654b47e0UUID v7 and partitioning (“how to” by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.mdpg_cron https://github.com/citusdata/pg_cronpg_partman https://github.com/pgpartman/pg_partmanOur episode on BRIN indexes https://postgres.fm/episodes/brin-indexesTutorial from Citus (Andres Freund and Marco Slot) including rollups https://www.youtube.com/watch?v=0ybz6zuXCPoIoT with PostgreSQL (talk by Chris Ellis) https://youtube.com/watch?v=KnUoDBGv4aw&t=58pg_timeseries https://github.com/tembo-io/pg_timeseriesDuckDB https://duckdb.org~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork

Hello, hello. This is Posgast.fm. My name is Nikolai from Posgast.ai and as usual, my co-host is Michael, Pj Master. Hi Michael, how are you doing? Hello Nikolai, 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 around everything we store in databases 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 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 and 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 things we want to know the differences of, or like the averages of, or the min, 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. They have a year of creation. Sometimes it's not clear, actually, but for day-to-day operations, it's not that important. Maybe population is more important and so on. It depends. Maybe position is more important. So it's hard to say this data is time serious, although it has creation timestamp.

Each part of this has it, right? So, and 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 metrics and we log these metrics every minute, for example.

metrics and we log these metrics every minute for example. In this case it's definitely it can be considered time serious and in many cases not always but in many cases fresh data 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 five years ago, for example. It also might happen. Depends. It depends on the concrete project product you are dealing with or building.

But what matters is we obtain data periodically from some source. And this is definitely time-serious to me. Yeah. Unlike cities. Yeah. I think for the purpose of this conversation, it's probably worth distinguishing like at what point we start caring because I mean, 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 set up 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 two or three sensors or maybe like four or five devices logging every, even every second say, you might as well, you can, 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 to Postgres. 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. Every update means new physical row, which we call tuple or tuple. We're still not sure after almost three years of discussing. Anyway, physical row version is created on every update, unless this update is a 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 one row, but physically it might be many many physical versions and you have a lot of dead tuples. And then Otovacuum comes, which is a 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. But I completely agree.

But I think for this use case, time series use case normally is append only, right? Normally you're just inserting new rows. So I don't think it applies here. Well I see updates in time seriesseries data all the time. Some. Okay, tell me about that. Because I don't see, I hear mostly about not, like, it's a pen 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 drops 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 Q like thing but it's not just Q like you need to log everything and present it. So you might create some record and then update its status. Yeah. create some record and then update its status. Yeah. Right?

But let's say my example is that... Is it time serious or no? 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, OK. Let want readings over time. We want to graph. Yeah, okay.

Let's agree on this. We need to distinguish a pad-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 row volume. Good point. Right. Right. And yeah, so, and you like, the problem is not huge scale, but I think it's great idea to think in advance how many records you expect per some time, per day, per hour, per,

I don't know, 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 raw, roughly, very

roughly, depends a lot, it can be 5x to both sides, right? But for our raw storage, Postgres, it's roughly so. For real, for many time series database systems, which are only for time series, like Metrics or you can consider Clickhouse as well. One billion rows is maybe 100 gigabytes, maybe 10 gigabytes even depending on compression. Very compression is much better. 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 one millisecond lookup.

But then if you ignore MVCC in Postgres, you might end up having a lot of dead doubles or bloat, and latency will drop a lot as well, and that's not good. lot as well and that's not good. Yeah. Cool. Well, on the... if we go back, if we go to the kind of the append-only scenario, what are the kind of 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, like in the room, like let's recognize it. There is a great product called Time ScaleDB. Oh, yes. Yeah. Absolutely great. They have, in my opinion, two major things they have is compression,

unbeatable compression for Postgres, for time series, and continuous aggregates, which is also good, it's convenient. You can build continuous aggregates, but is also good. It's convenient. You can build continuous aggregates, but it's super hard to build good compression. The problem is with time scale DB, it's too great, but it's not part of core Postgres. It's extension which is available only either on time scale cloud or self-hosted if you are okay to use non-open source. It's source available. It's very open.

I know they allowed to use it to many, many companies, but it's still like if you're going to build some platform, you probably will be not allowed to use it and so on. With compression, I mean. 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 recommend to read a couple of blog posts before. I recommend it again.

They are amazing. But it's not Core of Postgres. But I actually think the list of timescale DB features are a really good list of things that are tricky with time series data in general. So they've also done great blog posts on things like how to copy data into Postgres or how to ingest data as fast as possible.

So that's one of the trickier things about extreme volume and would maybe... Do you remember numbers? A few, yeah, but not from... 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, there are some exceptions, but basically if you can get it in, in a way that it's making the most use, like most efficient use of writing data in batches, then you can get much, much better throughput. But then there were other tips and tricks, like not having many indexes on the table

that you're ingesting into, maybe none, but probably at least one. Also foreign keys can slow you down a lot, and also check-pointer behavior, so you need to... If you expect massive write coming one time, it's worth thinking about check-pointer tuning for this window only accepting risks that if we are crushed recovery will take longer. So you raise checkpoint timeout and max for size and it should speed up because

check pointer 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 if we... If in the pen-only specific case, probably we won't have a lot of winning, with a lot of benefits from checkpoint tuning,'s checkpoint tuning for massive writes shines when we have random inserts to many pages and we revisit page. In append only the thing is that we're constantly writing

to the end of data of heap and also bitre 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 white. Yes, that's why UUID version 4 is a terrible idea, right? Yeah, yeah, yeah. Right, so you want UUID version 7 or just Bigint or something else which has similar, like growing. Like ULID type thing, yeah. so b3 inserts will happen on only to the right always to the right right and so on and it will be packed and so on

yeah but this also leads us to the question about partitioning right because yes partitioning is the key so let me finish my my rattling session a little bit about time scale and 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. We do health check, we improve health, we level up. Our typical client is a startup which grows and starts having some issues.

Very often they are on some managed post-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, OK, partitioning is needed because tables are already large. And we have a simple rule. If you exceed 100 gigabytes you need to

do partitioning but then we see all this data is actually time serious right and it's it's inevitable to consider time scale DB but they are 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, pgpartman 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, 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, it's a big question. Honestly, I fully agree with people, like 37 signals, migration back from cloud, 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 to fail over everything like that. Maybe 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 thing. But still, there is also business for Timescale. They want to have paid customers. Again, I respect that. And sometimes we help consider moving there. But if you move there, there are also complications, because Timescale Cloud has limitations,

like just single logical database. It's kind of, in some cases, it's not convenient to have to, like 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, 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 like fragmented space. And if you want good time scale DB, you need to go either there or here. You cannot do it on any other managed service. It's bad. I don't understand why they cannot reach some agreement and have some business agreement. I mean, AWS and TimeScale Company and Google and Azure, Microsoft and so on. It would be good for all.

It's ridiculous because I'm telling everyone compression is great. Read these posts, you understand that just make experiments. It's so great. 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 of time series data, and com store and so on. Wildtime scale DB shines in compression because they apply two-dimensional compression. They also compress column-wise. That's great. And if you have metrics from some sensor coming to you

at every second, for example, they cannot change quickly a lot. 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 compressed and additionally compressed and Timescale DB 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, right? Wall. Wall. Okay, yeah. Let me pull us back because I think you're right at a certain scale, even like 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 in the 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're still manageable within just 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 growing not exponentially, but steadily. If you expect you won't have more than 10 terabytes of data in the next 10 years, it's okay. Maybe 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. It gives you the ability to, for example, if you have partition which is one day from the previous month, it's time to repack it and store only aggregates, 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 a 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 like I talked to someone recently and we discussed locality. And by the way, I talked to someone recently and we discussed custom partitioning without TimeScaleDB. The discussion was, should it be partitioned by customer or project or timewise? Of course, timewise, 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 auto vacuum will be fine, and no new inserts will come to this partition. Unless you support some occasional updates of all 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 the old day from the previous month, and then you convert it to, for example, our 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's 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. The other thing about partition partition, 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. Yeah. That's the solution. Yeah. Or, well, all the results for attempt to have tiered storage. So maybe all partition 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 glass here, S3 glass here, right? It's cheaper, it's slower, but people rarely access this data and so on. Yeah, there are several options here. You need some strategy for data management in the longer term 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 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. Oh, I think this is, yeah. I think this is such a difficult one. 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-

Well, yeah. If it was, or yeah, or just use time scale DB, 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. At least it's available. PGKrone and PGPartman 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 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 time series. Ah, no, this I haven't tried. Yeah to look at it properly yet. PGTime series? Ah, no, 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 Time Scale DB 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 partitioned tables they are much more harder to... they're not easy to do at scale anyway. But with partitioned tables. This was kind of my point that you're paying some complexity upfront 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 partition table later, but you've also got away with some simplicity for maybe a year or two, maybe longer.

Well, anyway, I think if you have partition, if you have time scale, time scale, time series situation, 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 queries are 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 UID version 7. And I use... It has an 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, like I'm 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? 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? And then it was improving only like evolution-wise, like polishing things. Do you think UX-wise big step could be done, UX-wise, to simplify?

Like for example, I remember we were helping very famous company to partition like during the weekend. Like, I think it was more than one year ago. It was a YI company, super popular. We helped them and it was great, but it was interesting experience actually to help, 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...

For me, not so easy, but I know how to do it and how to verify it before deployment and so on. I 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 a Bay Area. Like I told him, you know, we helped this company to implement partitioning. Because he mentioned he's using the company.

He's using products of the company. Okay, he's using products. I mentioned, okay, we just helped 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 5pm. And I realized, 5pm, probably this is the time. It's like 5-0-0, really? And I'm looking at my watch and I see 5pm. And I realized 5pm, probably this is the time, it's like 5.00, 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 PGKrone 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 would be good to do it evolutionary as well. Like maybe PGKrone should be part of POSGUS first thing. Cool. Yeah, maybe. At least a contrib module or something. I don't know. And then probably partition creation could be a part of declarative syntax. You define it when you create your partition schema. You say, okay 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 zero downtime, it was great, but I spent like couple of months. Yeah, this is time when I started understanding that clones are great, because you can experiment and check everything, cloning, right? So yeah.

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 foreign 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 zero downtime fashion. So I do see it as probably it's gonna only improve incrementally but I

don't see why automatic creation couldn't be a complete feature thing. It could also help yeah remember we were talking were talking about PG squeeze and that's another feature that even though I think it was vacuum full concurrently could come in. With glassy option maybe, right? Yeah, but the other feature it has is scheduled, let's say like repacking or whatever, squeezing, scheduled squeezing. Well, it's different. repacking or whatever, scheduled squeezing. It's different.

It would need the same kind of logic though, right? 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 with 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 10th version was released, was 2017 I guess. I like to do... 17 is the most recent so it's seven years ago. Yes, I'm old guy, I still remember that 95 was in 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 hundred times smaller and having more less precise data, different level of precision. I think the latter should be on schedule.

And to be on schedule right now, there is no option in Postgres. You need PgChron, right? That's why I think PgChron could help to open some new features for partitioning if it was in the core. Maybe we went sideways a little bit from time series, but time series and partitioning, they are very strongly connected in my head, in my mind. I completely agree. I think you mentioned Timesco has a couple of important features, but I would add the automatic partitioning as another.

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 are 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 rel 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 pooler.

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 relcache. 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 time scale DB, 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 posthouse. What about the roll-up tables? The roll-up tables are exactly for that case. Well, yeah, if you do very strict strategy and very aggressive roll-up and three-stage approach and maybe your counts and sums and means, maxes will be, aggregates will be fast, I don't know.

Yeah. But it's easy to experiment actually. It's 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 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 the 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. This makes incremental backup lighter, smaller, and recovery as well. It's also good. It's not about storage only, it's not only about the buffer pool and wall rates, full page writes and so on. It's also about backups. So everything is connected here and it's great to have partitioning and for large tables exceeding like 100 gigabytes.

So this is first thing compression, any kinds of or this roll up second one 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 yeah yeah you it's really quite powerful and much much smaller than Beatrix. That's a great point. Do you remember version when it was added? 16, 17, 15?

I think it was roughly when we started, maybe like a year or two before we started the podcast. So maybe... Anyway, in very first versions. Yeah, and also continuous aggregates in timescaleDB is a great thing. If you don't have timescaleDB, then like something should be done, like probably like incremental materialised 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 there's a talk by a fellow Brit of mine Chris 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. Four points, what's the last one? Number five. What about language stuff? Chris, in his talk I remember, talks quite a bit about window functions and interpolating gaps. There are 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. It's a lot more learning. My favorite function is lag. 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 gonna say your internet connection, but yeah. Well, this is as well, lags everywhere. Anyway, let's consider this number five. Postgres has great standard SQL support, window functions, a very rich set of them and here it can help as well.

Oh 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 PGtime series. 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 Doug DB episode which is new thing. We had PG Doug DB episode We yeah, but I haven't considered it for time series data. That's interesting. Do it Yeah Why not? Well, it's all things are fresh. I just saw how my team member

used DougDB just locally to read some Sysfee logs, snapshots actually collected from Postgres. It was great. It was some DuckDB SQL and super convenient as well. So I think we have a lot of things in tools which are open source, and that's great. One more idea, even the topic from last time or two times ago was PG dog, like sharding.

If you're getting to extreme heavy workloads, you could have time series partitioning on every shard but then maybe a range of device IDs for each one and but partition and shard based on something other key. Right, maybe. Yeah, yeah. Thank you for this little brainstorm. It was a pleasure to talk to you as usual. Likewise. See you next week.