Scaling Time-Series Workloads on Postgres

0:00Many real-world processes produce data as a continuous stream rather than as isolated records. Sensor readings, financial markets, and application telemetry all generate data this way. This kind of time series data has a distinctive shape. It's written far more often than it is updated, it accumulates continuously, and it is usually queried across ranges of time. Time series databases are built specifically for this kind of workload. TimescaleDB is an open-source database from Tiger Data that adds time series capabilities to PostgreSQL. It's implemented as a PostgreSQL extension, so it introduces new functionality while preserving standard PostgreSQL behavior and SQL.

0:46This lets a single PostgreSQL-based system handle both transactional and analytical workloads without splitting data across multiple tools. Brandon Purcell is the Director of Product Management at Tiger Data. In this episode, Brandon joins Kevin Ball to discuss why time series data breaks conventional databases, how hyper tables and hyper core scale Postgres, zero copy database forking for agent-based workflows, and much more. Kevin Ball, or Kate Ball, is the Vice President of Engineering at Mento and an independent coach for engineers and engineering leaders. He co-founded and served as CTO for two companies, founded the San Diego JavaScript Meetup, and organizes the AI in Action discussion group through Latent Space.

1:32Check out the show notes to follow KBall on Twitter or LinkedIn, or visit his website, kball.llc.

1:51Brandon, welcome to the show. Yeah, thanks for having me, Kevin. Yeah, I'm excited to learn a little bit more about what you're up to. So let's start with you. Can you give us a quick overview of your background and then how you ended up at Tiger and what you're doing there? Yeah, no, absolutely. I've been working in the tech industry for a very long time. Spent my early years in the military, even writing code, and then started working at Adobe soon after that, working in a number of different roles there. And met a couple of guys where we built a startup. The startup was called Spatial Key. So the background in my area and experience in Postgres goes back almost 20 years.

2:28We built a geospatial analytics platform that was built on top of Postgres, primarily initially focusing across a broad range, horizontal, but then switching that into insurance and everything from underwriting to event response and portfolio management type use cases. And probably after about 13 years, we sold the company and I spent some time, took some time off afterwards and decided to come back and actually take a very different role. I was doing most of the leadership. I was the CTO of the company and really wanted to get back into the tech side of it and product. And I love Postgres and so ended up coming over to Tiger Data because I believe they have a great product and it really takes Postgres to the next level.

3:07That's a good intro. So can you tell us a little bit about the product at Tiger and what is it that it handles that Vanilla Postgres doesn't? No, absolutely. And so just kind of taking a step back, you have Tiger Data as the company and the organization. And for most that are familiar with Timescale, which is the product TimescaleDB that Tiger Data has built, it's an extension of Postgres. And so by an extension, it's not a fork, it's an actually extension. And so it's offered as an open source product. And the challenge is a lot of times when you have repeated time series type data readings, you end up growing that data pretty extensively.

3:46And oftentimes, as that grows in vanilla Postgres, you start to run into challenges. And those challenges yield themselves as read and jest, or ingest bottlenecks, read start to slow down as your data starts to bloat. And you run into challenges with an index that's growing with a single table, time range queries get challenged. And so TimescaleDB addresses that by building this extension. And we'll talk about it in a number of the technologies that sit below the surface that address that. But from a developer perspective, it's the same SQL, no differences at all, but there's some intricate workings of the database that makes it easier to use and actually scale a lot better.

4:21And so from an offering perspective, we have a couple of different offerings within that. But most people start with the open source product. And we also have our Tiger Cloud solution as well. Yeah. So let's talk a little bit about time series. And what are the problem domains and use cases? If you're someone who's coming from a web background, maybe you've only ever dealt with relational classic REST objects and things like that. What are the types of problems that get you into saying, hey, actually, I need a different data structure here. I need something that looks like a time series. People typically do start with Postgres.

4:52So I think 60 % of the world, you start with a relational database, Postgres being the one that's probably used the most by developers. but you end up having a particular device or set of devices that are emitting readings. And that can be across a number of different areas as well. Manufacturing or robotics, medical devices, finance and crypto are also used there where you have a number of different transactions, smart buildings and industrial systems. So it's a number of different areas. And I think it's a growing area as well as we look at what we call the physical world where you have solar, energy, any number of things that are all emitting readings on a periodic basis.

5:28And the number of devices within that is growing as well. So you end up with hundreds of thousands or even millions of devices. We have one customer that has 22 million devices, and they're all emitting readings every few seconds, every second, in some even sub-second as well. So the volume of data that you're ingesting and the volume that's growing over time gets larger and larger, right? And so that challenges the databases in new and unique ways. The other piece of that is, is that you still need the transactional relational part of the database as well. And so as you start to grow, and oftentimes people start with Postgres and as you start to outgrow it, your query slows down, your ingest slows down.

6:07You have two options, really, right? You can separate it off into another solution. So you bifurcate with some type of analytical database, and then you retain your transactional database. Or you can adopt a system like TimeskillDB that gives you the best of both, where you have co-located into the same system, we can extend it. And this is with zero application changes at all. And we can talk about what that migration path looks like, but you're not having to make application changes where if you bifurcate it, you have to manage multiple data pipelines between this other system. And typically, most of these systems have some evolution of SQL that is a little bit different.

6:45So you end up having to rewrite portions of your application. Yeah, that makes sense. So, I mean, I guess looking at that piece as an application developer, And presumably the underlying data model looks a little bit different when you start to go to timescale. Is there any difference at all? I need to know in terms of - Zero. Zero difference at all. And so you think about, you're starting with the table, you have a reading, you have a timestamp, and then you have any number of attributes that are associated with that device. So that stays exactly the same as you move into timescale. What happens under the covers is you have a concept of everyone understands tables in existing databases.

7:21We have something called a hyper table, which takes that same concept and it starts with the partitioning, the automatic partitioning of that data. So breaking based upon those time steps and partitioning it in chunks that separate it over time. So you're not reading all of the data at once. And there's a number of pieces that are around that. And so within those chunks as well, we have something called HyperCore, which allows us to have a hybrid row and column store. So typical database in Postgres, you have a row store and your data is organized in rows just like traditional databases. But below that as well, and this happens through policies and automatically, and developers have control over this as well, data is migrated into a column store as well.

8:05And this is getting the value of compression. You know, we've seen customers in 98 % compression. Yeah. And that column store is, as we've evolved it over time, it used to be very immutable. And as we've moved and we improved that, it's very immutable as well. So the data can change within there as well. And so you get the benefit of the space savings. Typically, we see users with 90%. We've seen as high as 98%, where you have really duplicative readings oftentimes. And then you also get the value of vectorized query acceleration as well. So that allows the underlying processor through a feature called SIMD to execute multiple batches against the column store at a time.

8:46And the result is you think compression is slower, right? But when you're compressing the data combined with a vectorization, we've seen acceleration in queries 10 to 100 to 1 ,000 times. Then beyond that a bit, we also, you think of normal B3 indexes that you have in a row or store. We have something called sparse indexes as well, which are like min max and bloom filter indexes that allow for execution of queries to pull from the metadata that is in these batches or in these chunks. So you're not reading all the way down to the row level and pulling data. So common, some query first, last things that you would have in this world would be much, much faster because they're reading from metadata versus from the underlying rows and having to decompress it.

9:32And so the combination of those things from a developer perspective is, you know, you migrate data over, there's a couple of commands you run to turn a normal table into hypertable or create a hypertable from scratch. But when you insert data or when data is migrated, it automatically, based upon those timestamps, partitions it in the appropriate chunk. And that partitioning extends beyond just time series. We have numeric partitioning as well. And also like UID v7, which has a time-based component as well. We also have support for that as well. So your data ends up being broken up into these chunks.

10:07When you're querying it from a top level, it appears as one. And oftentimes, one of the biggest values of that is when you have billions and billions of rows that maybe go over 10 plus years, if you're querying more near term data, the query planner is only going to hit the chunks that are necessary for that. And so it's reducing that footprint that you're querying from. Yeah, no, it's interesting. This is bringing me back in more recent years have dealt with smaller scale data, but yeah, a decade ago was dealing with trying to, at that point it was hundreds of millions of rows where we started to have to think about all these different things and we're planning out partitioned tables and stuff.

10:44So drawing back on that, asking some of the questions I ran into then to see how you handle it. Like one of the big questions there is around, is this all time partitioned versus do you have other partitions? Is this non-time? So I'm guessing from the name of this, This is all focused on time bucket based on day, month, year, or something along that? Or are there ways to utilize this underlying infrastructure for non-time-based partitioning? You can use additional partitioning schemes that come with Postgres. But for the most part, I find they're not necessary. And in the specific problem domain, people are typically querying by time as one attribute.

11:22And then if you're looking at in device world, by device or other data as well, And there's a concept that we use internally called segment by for how you segment the data into these batches, usually by a device ID, some type of tenant ID, user ID. And that allows the engine to group the data intelligently so that those batches compress better and also are more queryable and faster when you're querying the data. And that's a big part of that row store, column store hybrid approach where we're grouping those and creating those logical batches. Yeah, super interesting. Now, another thing that at that time was challenging was, yeah, anytime you, if you wanted to join from this over into something else, because at that point I was dealing with partition logic at the app layer, you're down in Postgres.

12:07Can I just interact with other tables and joins and things like that, how I might expect? Yeah, it works exactly. So think of it as from a developer perspective, really no different than what your traditional experience would be in Postgres or any other relational database. and users typically have metadata that's associated with these devices, let's say for example, or users that are stored in traditional normal tables that aren't partitioned by time. And you can join directly against those tables as well. And that's a pretty common need and use case where if you're going to bifurcate that and have it across multiple systems, you have to do that at the application layer versus the database layer.

12:46And so you have to query it and then unify that back at the application layer where you can just write a query, join it across the two, aggregate the values you want and then display that back to a user. That's fascinating. Because yeah, I remember that being a huge pain point once we got to the place we were having to partition is suddenly all these things that we used to be able to do at the SQL layer, we had to pull into the application. Is there any secret sauce to how you do that and keep it performant? Because under the hood, you've got all these different partitioned things. Like do you plug in directly into the query engine?

13:16What does that look like to make that work? It's intercepting those queries, and the query planner is actually executing, and this is part of the extension. And I think really the difference in the value of Postgres with its extensibility. I used PostGIS for years, and I've been amazed at what you could do, accomplish from a GIS perspective. And on timescale, DB is using those same concepts with the extensibility of Postgres, and the ability to interact from a query planner, automatically route to the appropriate request against the right chunks, And we've spent a lot of time optimizing that path as well.

13:50So you touch as few chunks as possible for your queries. And so you can have a lot of historical data. And under the covers, these chunks are really just tables. And then there's a layer of metadata that Timescale is being able to use to organize that. And because of that too, your indexes, if you're talking about in the row store within those chunks, are broken up across those as well, which is one of the big challenges when you have a huge table, that index grows very substantially. And then you have an insert and has to update that index. And so in this case, and one of the tunable parameters that users have to figure out oftentimes, and we're making this easier and easier is what is the appropriate chunk size, right?

14:32Like we come out of the box with seven days, but if you have a really high volume data, you can maybe go down to even an hour, right? And so there's some different optimizations techniques around that. But for the most part is pretty, from a developer perspective, it's pretty automatic and simple to get started and configured and get it right out of the box. That's interesting. So I'd love to kind of understand the underpinnings that you're giving those handles to. In particular, so one angle into this, you mentioned people often start looking at this when they run into challenges at the ingest time.

15:00And then when we were talking about performance optimizations, you talked about query speed, which is read time. So like, how does this help on the ingest time? And like, if I'm running into ingestion problems, like does changing that bucket size help? Are there things I can tinker with what type of index I'm doing? Because I know, yeah, definitely in vanilla Postgres, some indexes are better for write-heavy applications versus not. What are the knobs and levers here? The same things really apply to Postgres. The difference really comes down to, I think, the chunk size as well. So you end up with the active, let's say, chunk that you're inserting if you're just append-only type workload.

15:36You want to think about the size that that chunk is going to be as a row store before it's converted into the column store and compressed. And so the same kind of rules apply, but it allows you because you have many chunks, you only have to worry about that one in terms of the indexes and that. And so what we've seen from that is, as you have a normal Postgres table and you ingest over time, you see this linear degradation of actually the amount of rows per second that you can ingest. But with timescale DB, if you're tuning things appropriately and you have the right chunk size as you work through that, you're not going to see a drop-off in that at all.

16:16And on ingest, the routing algorithm for how it goes to the appropriate chunk, so it allows you to backfill old historic data as well. And that's going to be routed to old chunks as well. And by default, it goes into the row store and then is converted for a policy into the column store, we do have the ability to even to write directly to the column store. And that's something we're evolving over time. And what we found is you can on nominal hardware, get to a million rows per second, plus from an ingestion standpoint, which is very difficult to do in vanilla Postgres. And that's going to drop off pretty quickly as your data starts to grow.

16:53Yeah, for sure. So would it be fair to say like, in vanilla Postgres, you see this linear degradation with the size of the table. In timescale DB, is that essentially the size of the chunk? It's pretty much flat, I've found, is what you can see. And I think, I forget if it was a Microsoft study or another one that did an actual independent test and benchmark of it, and we see that line is fairly flat. And the chunk size does matter more for probably query performance than it does for ingest performance. Because most of your chunks, unless you set like 30 days or two months for one, and you have a ton of data, it's not going to grow substantial like you would see tens of millions or hundreds of millions in a single one.

17:32You're going to start to spread those out. And so you don't run into those same problems that you would see in normal Postgres. Makes sense. So another thing that I start thinking about when I think about time series style data is aggregates, right? I want to look at things aggregated by day or by week or maybe many different versions of that. And if I'm coming from a vanilla Postgres world, maybe I have some materialized views that are computing that for me, or I have a stored procedure that's happening, or some application logic that's running this and dumping it into a table. How does that work in the timescale DB world?

18:06So taking the same concept of materialized views, which are a commonly used concept in relational databases, we've built an extension of that called continuous aggregates, which behind the scenes really are a materialized view. It's creating an additional hypertable, but it's aggregating data based upon, we have a function called time bucket. And I always forget the equivalent in Postgres. It's basically bucketing based upon a time range. And those are self-updating as well, based upon policies as users you set for how often they're updated, how far back you want to update that. That allows you to have an aggregate level view of your data that is automatically self-updating when any changes to the hypertable occur.

18:50and users will even often take those and build those hierarchical. So they build off of one another. So let's say you had a device that was emitting every minute and you wanted a roll up that was five minute, one hour, one day, one week. And you could have four different aggregates that build on each other that are scheduled automatically, that update. And that allows you to have, will take what could be hundreds of billions of rows and then breaking that down into individual time buckets and get sub-low millisecond query times to be able to query against them. And we have customers that are in our Tiger Cloud environment that have 20 ,000, 30 ,000 queries a second that are hitting billions of queries a day against what I would consider nominal hardware, not crazy specialized hardware.

19:39Yeah. Well, that maybe is a good opportunity to start talking about Tiger Cloud, right? Because everything we've talked about to date, I could get open source, I put a plugin in my running Postgres, and as you highlight, I don't need very fancy hardware to run this. Why would I go to Tiger Cloud? What do you all add there? Yeah, so Tiger Cloud has a couple of different features around it. One is just the overall managed service, the ability to take the pain off the team to run a managed service, much like you would have with RDS on AirBus, which I use pretty extensively before coming to Tiger Data for our geospatial product.

20:13But within that as well, there's a couple of other features beyond that. So the ability to have read replicas, HA replicas, all self-managed by their team, having a connection load balancer that sits on the outside. So if you need to scale up, if a single instance isn't going to suffice across many different instances, the ability to scale across those many different read replicas as well. One of the other features I think that's worth calling out that makes a big difference for users that want to retain a long history of data without keeping all of that data hot is tiered data. And what it does behind the scenes is it takes a table and let's say you have 10 years of history and you want the first two years to be in hot queryable and then you want the other eight years to be tiered.

21:00And that tiers that off the S3 in the AWS case and then it stores that in iceberg format behind the scenes. but the entire time range is still queryable as one table. So when you query the table, if you do the first two, if you're expanding outside of that first two years, it's actually hitting and combining those results together to allow you to query that full 10 years. Now, if you think about, if you had to do that manually, you'd have to have some archiving scheme and data pipeline that would go and move that data around. And there's a lot of that. Here you set a tiering policy and it automatically starts tiering that data based upon that date.

21:38So you can have a much longer history of retention for longer query periods. That's fascinating. Well, and that gets into, I mean, there's all sorts of different things that people do right now in terms of piping data into warehousing or analytics stores or lake houses or all these different things. Like, are you moving in that space as well? Or is this staying constrained to these like timescale bucket? No, it's, we're also moving in that space with a product that is due for full release later this year. It's still in beta right now, which is Tiger Lake. And that's to allow that bi-directional connectivity with other teams often where you would have that data in iceberg format, pushing data to Snowflake, pushing data to Databricks, and ability to use those other processes.

22:22And like I said, that's bi-directional. Maybe where you're bringing data in, where Tiger data or timescale becomes your operational database for your dashboards where you need really fast responsiveness. Maybe you have a data science team or another analytical team that's consuming that using Snowflake, Dataprix, or Athena in AWS, and the ability to have that live in both locations. And I've even seen it too for compliance reasons. Organizations need multiple copies of that data in multiple places. And so instead of having to build that data pipeline themselves, kind of make that automatic where you just enable this feature, and it automatically, based on the data that you've selected, it automatically starts writing using Tiger Lake out to S3.

23:04And we also have the equivalent on Azure as well. Yeah, super interesting. So I'm going to go a little bit more hypothetical now because we're talking about these different things and I'm starting to see the picture that this looks like. It's a lot easier to manage than my current situation. I've got stuff in Postgres in a few different places and then we're shifting things into AWS and have this whole processing pipeline. How do you see this changing the way that people manage their data? Yeah, no, I think the goal is to make it easier so people aren't focusing on the plumbing and all the work around it and are focusing on the business problems and solving them and enabling really multiple teams to be effective using some of the same data.

23:45And the intent is that you can get that right depending on where your source of record is or where you're ingesting. if you're bringing it directly into Postgres, into Tiger data, and then being able to use that downstream and other processes. Maybe a team writes their data directly to S3, and then that did hold in and operationally enabled through Tiger data. So it allows for flexibility for how you want to work and gesture data and bring it in, but then making it exposed and sort of democratizing that for the team broadly, but not having to worry about all the plumbing and all the work that needs to be done, which AI makes it easier from a development perspective.

24:21But if you don't have to manage to do that yourself, you're focusing on using the data and leveraging it both in your applications and your other downstream analytical processes. Yeah. So we talked about some of the plumbing that makes this possible, right? You have these hyper tables and the hybrid row column stuff. Are there any missing primitives? Are there primitives that you all are still working on that you feel like are needed to realize that vision? And by primitives, I mean, we really support everything that's in Postgres today, right? So it is vanilla Postgres with these extension to be able to then partition the data.

24:55So if there are other, you mean from a data sync perspective or other areas? I'm just thinking like we're talking about a future that, I mean, as someone who increasingly is like, I just want to see the answers, right? I don't want to think about the details. Like I really resonate with your vision for like, we'll focus on the business problem. Let's focus there. hyper tables, timescale DB was arising out of a thing that was a gap, right? There's a gap already. Do you feel like with this, like all the pieces are there and it's about execution? Or are there like core underlying things that you all are working on that are like, oh, actually, we also need, I don't know, maybe it's like, I keep going back to non time related partitions, because that's what I was dealing with back when I was dealing with the scope of data.