A concrete guide to time-series databases with Python

Speaker 1 [00:02]

Thank you for coming. We at Altami are using a time series database. We noticed a couple of months ago that we need something like that, and we went through a bit of a process to see what technology we should use, how to deploy it, and a couple of general decisions were in there that I would like to talk about. So I hope that from this talk you will go away with knowledge about what time series databases are, if you don't know already, but also with a bit of, yeah, some different angles maybe on how to look at these kind of databases if you would like to use them. So yeah, I found this website, DB engines.com, that tracks the popularity of database systems, And apparently in the last ten years the popularity of time series databases has been rising, which is also a great occasion to give a talk about that, second only to graph databases. But really what I want to introduce with this plot is what time series data is. And time series data is really any kind of data that is, like, indexed, graphed by, sorted by time. And so whenever you see that time is on the x-axis, then it's time series data. And that's quite broad. And as you would expect, it's really everywhere, like weather, traffic, it's everywhere where you take data in a sequential way. And so you have probably worked with it. And now continuing to what a time series database is, well, it's a database, of course. got that from the name, but then it is basically only optimised for time series data, so the strong ordering of date times gives you the ability or enables the ability to do better indexing and thus better compression and achieve higher write and query speeds. Right, so this This is already everything about what time-series data and the time-series databases are. Now I want to switch gears a little bit and go into what we at Altami do and why we have the need for a time-series database. And for that, I have to talk a little bit about concrete. So concrete is what you get when you mix cement in the upper right with sand and water. And then it first looks like in the upper left picture, so a little of the flubbery mass, and then it hardens. And I mean, look around you, it is basically everywhere. There's a pretty cool video by Veritasium that just came out last Friday. Yeah, I liked it. Go ahead and watch it. And, yeah, it's, I mean, it's, in a way, if you think about it, it's quite awesome. We can build massive objects with it. Bridges, twisted towers, yeah, of course, it's awesome. But as you're guessing, there must be a catch. And the catch in this case is that cement, the production of cement, which you really need, produces 8% of greenhouse gases that humans produce. So this is quite a lot, and as you also know, greenhouse gases, well, heat the atmosphere, boil your ice caps, give us penguin soup, forest fires and flooding, and that's all things. It kills life, and we don't want that. So what can we do about it. And actually, there is something we can do about it, and already today it's possible to make the same amount of concrete with just half the amount of cement, which would, like, right off the bat, slash the amount of greenhouse gases that are emitted of that, because it's like this 8% is just from cement, it's not from the transport or anything else. Why is nobody doing that? Well, as so often, when things get complicated, and in this case, or difficult, there are two ways to do it. At least here, you could do it with more workforce because you have to very precisely steer the recipes, but it turns out you can also do that with process control, and this is what we do at Altami. So, in order to exercise process control, we need process data, and this is what you see by the dotted lines, Like on the left side we take data from the plant, we also take data from these concrete mixes on the road, and sometime in the future we will also take some data from the construction sites, like from these pumps that bring the concrete then to their final position in the building. Now that is, like all of the data we collect basically is time series data, but that doesn't mean that we need a time series database for all of the data, especially the data from the plant, for example, is nested, there are many relations, so we use a relational database for that. But if we look at the data that comes from the concrete mixing trucks, this data is actually very homogenous, and there's lots of it. So let's have a look at the concrete mixing trucks. There are three sensors that we have. First and foremost, in the middle is the oil pressure sensor. That is the most important. We'll get to that. There's a sensor that measures the drum rotation, because, like, if it's going one direction, it's pulling in the concrete and mixing it. The other rotation is basically unloading it. We track the addition of water because that's forbidden, and there's a telemetry device that collects all the data from the sensors and sends it to us. This is what the data looks like. It's all time series data. Let's look at the bottom first. We have an indicator of if the truck is standing or moving. In the beginning, it's standing in this case. This is all one delivery, I should say. In the beginning, it's just standing and chilling at the plant. Then it's driving to the construction site, and then it's at the construction site, and later driving home. In the middle we have the drum rotation, and if it's positive, it means that the drum is rotating inwards, it's pulling in the concrete, and you see what it does there, and then in the middle of that construction site bit, it's negative, and that means this is where it's unloading. And then we have the oil pressure. So the oil pressure curve is interesting because there you see that it's loaded in four or five batches, which the oil pressure increases then in the beginning there in the plant, and then you have some time on the road. I don't know if you can... No, you can't see my mouse, but what you see is that at this last bit at the construction site where it's waiting there, the oil pressure is essentially rising a little bit. And this is really, really interesting to concrete technologists, because this is a data point that they don't usually get. They usually have laboratory data on the concrete, and now they have real-life data, and they have so many more data points than before. And that helps them to optimise the recipes and to adapt them, and drive down the amount of cement per cubic metre of concrete. So this is where the business value lies essentially. Back to tech or back to say what we do with the data and why we need a time series database. So at this point where the line is, we basically know, hey, because the truck is sending data continuously, right? It's just when you look at the truck only, we don't know basically where it's going, what delivery it's doing. But at that point, we get data from the plant as well that tells us, okay, we have to map that truck to this delivery. But unfortunately, we have to go back for half an hour because we need this data in the beginning, especially for the oil pressure, to do some normalisation. And that means if you think about the data system, we can't just have a system that flows the data somewhere, but we need some sort of buffer. And then what we do is to, like, five minutes odd, we go and essentially load everything that we think belongs to the delivery and put that on the main database of delivery. So this is a case where we have to query Windows and time again and again and again, and this is something that a time series database is really good at. Some more consideration, so it's also a lot of data, like one track gives you 30 megabytes a day. At the moment there are around 60 trucks driving which are equipped and somewhere next year we'll hit the 1,000 trucks mark, hopefully. So that will be 30 gigabytes a day, which, you know, it's not Facebook or Google level, but it's also something you don't want to be doing on your laptop. And, of course, it has to be integrated to our system. So there are multiple dependent apps. As I said before, it's time-series data and all of the apps query Windows in time, so we really want to have that boost from a time-series database. Now, when we realised that we have this use case, we essentially had three people who wanted to look in parallel in 2Ds and wanted to do spikes on different ways of doing that, meet Alice, Bob, and Chris, not their real names, and they looked into self-hosted dedicated time series database, because there's a lot of technology out there, and it's kind of interesting to see if there's a very specialised product that would do this best. Bob wanted to see if we could adapt managed Postgres, because Postgres is what our team is using all of the time, and everybody knows it, so there is something there that is important as well. And then Chris wanted to see if, yeah, what the fully managed options are, because we hope that the business is scaling a lot in the future, and the fully managed option would probably be the best one for that. So let's start with self-hosted dedicated There are lots of technologies out there, and lots of them are integrated with one or the other tool already, for example graphite is the database that's behind Grafana, and then also one comment here, there are relational, there are databases that are based on the relational database, for example Timescale, which is based on Postgres, but there are other ones as well. Now, Alice looked into InfluxDB because it's the most popular one, and it's also available as a managed service on AWS, has Python bindings, and it's quite easy, easily installed and easy to be used. However, it does have a custom query language, so that's something to keep in mind. But, yeah, it goes down to the layer where it returns Pandas DataFrame, which of course is handy. However, then, Alice went to talk to our DevOps guy, our DevOps guy, and it turns out that for DevOps self-hosting, critical infrastructure is probably not the best thing to do. First of all, you need a number of people who know the technology, especially also with a custom query language, it's better to have more than one person on board, and then there is some considerable DevOps capacity bound by something like that. We do have one service that we self-host, and it has been down a couple of times, so we have experience with self-hosting in a way, and it is difficult to guarantee high uptime. So, yeah, that's the story for self-hosting. Next up we have Bob looking into managed Postgres, and Bob found this intriguing article on how to improve the performance of Postgres for time series data. I think it looks quite spectacular in blue on the left side, you have the performance of vanilla Postgres, and the performance of course goes down with the number of records, And then after these performance tweaks, the performance stays at a very, very high level. And so what they did in this article is like on the right side you can see on the left most bar is the original Postgres, they have some optimised data types which I think isn't really worth mentioning. They use a block range index then which is optimised for time series data, so that's something you can do, but, yeah, you see the name of the game is partitioning. And partitioning is something, yeah, I'll get a little bit more into that, but this is something you have to do yourself. Now, Bob was pretty excited, and, yeah, as I said before, the really cool thing about this is that we only use existing tech. We use PostgreSQL and SQL Alchemy heavily in our company, and we can do everything with that if we went down this road. However, as I said before, partitioning is the name of the game, and what partitioning means is that you essentially take a table and make multiple tables from it along one or even along multiple variables. So if you do that by time, for example, you could say you do one table for each day, however, yeah, that wouldn't really help us, as we would be writing always on the same petition as the data comes in live, and also for query performance, it wouldn't really help us, because we query, like most of the time, we just want to query the most recent data. And so, yeah, we didn't really want to go with partitioning by time. And then by device ID, that's the other way to go, to really split what the workers could do on the data when it's distributed. Again, here is some issue with scaling, because at At some point, in the beginning we would maybe say we start with five petitions or ten, and then at some point we would say, okay, now we need 50 or 100 petitions, and when you do that, or at least for us, there was no easy way to do this kind of migration. We would have had to fully migrate the database. And so what Bob and Devopsky realised is that there is some upfront decision-making necessary, And we don't know if it actually works with scaling until we have seen that. And so this is something to keep in mind with this approach. Yeah, now, Chris went for the fully managed. Here there are some, yeah, the options, the two options, because we are on AWS, that we had is influxDB and AWS Timestream. And fully managed in this sense means, like, if you think of S3, you don't petition any disk or something, you don't provision a disk or workers that would store or write the data for you, you really say, I want the bucket, and in this case, you would say, I want a database and a table, and that's really all you do to set up the system. And similarly, you have granularly billing. So your billing is you write, you pay for every write, for all the data you have there, you also pay for the querying? And of course, so Claire looked into this, but of course the question is then can we actually pay for this? This was the main question they had. So more or less the first thing they did is make an exit table and see if we, yeah, how much data it is, how much the writing, storing, querying will cost. And what we saw is, well, This is just a number that came out there, but what we saw is that we can project this cost. It's not going to be cheaper over the time that we use it, however, we know that it does fit our budget, and there's no further DevOps required, and it scales automatically. Spoiler alert, we continued with using Timestream, and we measure the cost of Timestream now. That's somewhat difficult to measure because the number of truck is rising all of the time and it's, yeah, sometimes they're on holiday and stuff like that, and there's, yeah, there's more optimisation of the cost necessary if we think we should do that. And of course a fair word of warning here, the service, of course, scaled seamlessly and so does the cost. Right. And so everybody reconvened and looked at the options, and so this is basically the final categories that we had. So we wanted something that is Python integrated, or at least where the team doesn't have to learn too much. Managed Postgres, of course, would be the best option for Python integration, then also Also for no new tech, because both other options have new technology included, but then for how easy the DevOps is and the scaling, the fully managed solution, and that is Timestream definitely wins out the others. Operating cost is highest, we think, for Timestream. We didn't really check the others, but as I said before, we projected that it works for us, and so that's what we went with. A few words on the implementation now. This is what it looks like. As you can see, it's a relatively small diagram. From the trucks, it goes into S3 first, and then with a Lambda function to SNS, and in the future hopefully directly to SNS, and then it goes into Kinesis because it doesn't stay forever in time stream, and this is like in German we would say Endlager, I guess. And really what we use for all of the day-to-day work is then Timestream, to which it is posted with a Lambda function. And then we have all these apps on the right side that query the data from Timestream. Quickly wanted to show how to write with the Lambda function and a few of the apps. So this is the code of the Lambda function. On the left side, you basically see the function that's called fast. There's of course some transformation of the data necessary that I don't show here. And then on the right, this is how you write to Python, to time stream. And this is all Python. There is no special dialect needed. And this is one of the actual queries that we use. And here you can see that for querying you still need a language different than Python. This is so they have an SQL dialect, which incidentally this is a good thing because many of our developers already know SQL. And there's a tool, the AWS data wrangler that returns the Pandas data which makes it really, really convenient. Right. And yeah, that's really all the code that I'm showing. There are a few apps, like this is a very simple app that we built with Plotly Dash for debugging. It uses a query similar to the one that I showed you before, and that's just a very nimble project written in little time, a couple of hundred lines, and that's it. There's the app for the mechanics who install the sensors, which is also cool because they they essentially install the sensors, and then see, can check right away if what they installed there works. And then you can also directly query it from Grafana, which is important for us to see that all of the data from all of the trucks comes in as we wish. Right. So yeah, that brings me to the end of my talk. Yeah, I saw I could have shown, like, time Time-series data is something that's everywhere, and maybe you have a need for a time-series database. We looked into a couple of options for time-series databases, and basically more like general approaches to how to solve these. Self-hosting, adapting managed Postgres, and fully managed option. The categories that helped us evaluating is how well, how good is the Python integration or how is there Python integration. We wanted to have as little new tech as possible, easy DevOps, easy scaling, and the operating cost is of course a question as well. And I would also like to say that we are hiring. If you want to join us for a journey in reducing the carbon emissions of cement, then please talk to us on the Discord channel. Currently, we only have a position open for a junior position, but there's many, many positions to come soon, so please check out our career page.

Speaker 2 [22:45]

Thank you for your presentation. I have seen many people taking pictures here and I want to encourage you to share your impressions on all social media like tweeting with the hashtag PyCon.de or hashtag PyDataBerlin. And now we have some few questions. The first question came in very early and is why is there a drum rotation even after unloading the concrete on the construction site?

Speaker 1 [23:19]

Yeah, well, they simply return inwards so that they don't lose anything. And also, you want to keep it rotating so the rotation, essentially, the movement keeps it from hardening. It's simple as that.

Speaker 2 [23:40]

Next question is, what is different in a typical time series dB in comparison to normal relation dBs?

Speaker 1 [23:54]

Well, as I said in the beginning, really the big difference, and I only said that in a nutshell, and I'm probably not the expert to answer detailed questions on that, but really it's a question of optimization. When you know that you have flat data structures that are equally spaced in time, that would be ideal. That just allows you to do very efficient indexing. Yeah, but I can't add more than that on the slides. I'm sorry about that.

Speaker 2 [24:31]

Next question is, can you elaborate a bit on time series databases with a multidimensional database? Why should one choose one over the other?

Speaker 1 [24:44]

Sorry, I don't know what a multidimensional database is.

Speaker 2 [24:50]

Then we will go to the next question. Have you considered having flat file database with a custom backend for queries?

Speaker 1 [24:59]

Yeah, that's actually what we did in the beginning. We had CSV files essentially on S3, but it turns out they're not very flexible in the end and also very costly because you have to read them all of the time, right? And yeah, at some point that becomes a big, big bottleneck, especially for scaling.

Speaker 2 [25:26]

Next question is, do you think you may outgrown time stream both in terms of business requirements and size of the data set?

Speaker 1 [25:36]

I don't think so. Well, I haven't read a maximum number of what the amount of data is that it can hold, but basically it scales. So it does a lot. The partitioning that I mentioned before is done automatically by Timestream. And there's many partitions along many dimensions, so it's really, really optimized for large, large-scale data. So I don't think that our case will ever outgrow TimeStream.

Speaker 2 [26:18]

I have two questions now which are pretty similar first one is what is the frequency of input data and second how do you decide how often you send the data from the trucks you mentioned it's done every five minutes I think first one is yeah

Speaker 1 [26:35]

Yeah, so every, basically, this telemetry box on the truck sends us a JSON blob every 10 seconds, and that's for every truck. So this is roughly 9 kilobytes, the size of the payload, essentially, that we got. And then every five minutes is we read the data from Timestream to then assort it to our main database, days, which is what serves our product. So the five minutes is a step within our application.

Speaker 2 [27:13]

Next question is since you also mentioned timescale and like SQL a lot would you fully manage timescale tick all your boxes?

Speaker 1 [27:26]

Good question. Yeah, it could be very interesting. We haven't tried to understand if you could use Timestream with SQLAlchemy, because we're heavy users of SQLAlchemy, that Pythonises everything. And then if yes, probably yes.

Speaker 2 [27:54]

Next question is, what are the differences between the different types of TS-DB, for example, influx DB versus graphite?

Speaker 1 [28:05]

I can't really say. I assume they are really tailored to the frameworks that are built around them.

Speaker 2 [28:17]

Last question is, do I get it right that you have to code your entire model into the pipe object?

Speaker 1 [28:29]

Don't understand the question

Speaker 2 [28:32]

Should I repeat it?

Speaker 1 [28:33]

Yeah, yeah, maybe.

Speaker 2 [28:34]

Yeah. Do I get it right that you have to code your entire model into the pipe object?

Speaker 1 [28:44]

Well, no, you don't get that right. That's not what we did.

Speaker 2 [28:51]

Last but not least, are you uploading your slides somewhere?

Speaker 1 [28:59]

That's a question to you actually I

Speaker 2 [29:01]

I think the slides will be uploaded in the schedule, but I think I can just ask for that.

Speaker 1 [29:10]

Sure. Yeah, happy to do so

Speaker 2 [29:13]

Thank you for your presentation. I think the people appreciate your work for the climate and our environment.