Time Series Databases: A Deeper Look
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Eisentraut, Peter
The term "time series" is popular (again) in database circles. What is it and what's the point? Clearly, a traditional relational database like PostgreSQL can deal with time and with series. So why is time series a special use case? In this presentation, I want to look beyond the marketing a bit and analyze what the true technical characteristics of a time series database are and what use cases drive it. Then we can analyze how PostgreSQL handles these requirements, which workarounds are handy, and where improvements would be necessary. In PGCon tradition, this presentation is both guidance for users and a call to action for developers.
 Hello everyone, this is Peter Eisenthraut recording my presentation for PGCon 2020 from home in Germany. So let's get right to it. So today I want to talk about time series databases and as I usually do, especially at PGCon, I don't want to talk so much about a specific way of doing things or any kind of particular products, but I want to take a topic and actually ask myself, what does this even mean? And then further ask, you know, what does it mean for me as a user or the users that I support and what does it mean for me as a developer of the Postgres Core itself? And that's what I want to do today. So my first premise here is that time series is a use case. So time series is a term that's often thrown around in database circles, it sort of comes and goes over the years as a trend. And oftentimes it comes sort of with a big marketing message that it's a new way of doing databases and it's a new paradigm or what have you, right? So my sort of, yeah, premise here is I consider time series to be a use case, just like we have, for example, OLTP. That's a term that is used a lot, but it doesn't mean anything very specific if you think about it. If you are, let's say, a database administrator or a consultant and you come to a new database system and somebody tells you this is an OLTP system, that tells you something. It doesn't give you any specifics of what the system is doing or any of its performance metrics, but it gives you an idea of what the system is for and what the characteristics are, right? So it probably has a fair amount of updates and a fair amount of reads, usually a smaller transactions, but a lot of them and sort of made a mainly continuous load and probably sort of pretty good uptime requirements and the data in there is valuable, so it has to be probably backed up regularly and so on. And that is understandable and that's sort of a handy term to use and so at the almost opposite end of that, you have OLAP, which also tells you things, it tells you it's probably a very big database, it probably has a lot of reads, maybe not so many writes or the writes maybe bulk loaded depending on the setup. So those kind of terms help us understand in general what the system is doing and then how we have to think about it and how we have to work with it, but they don't tell you anything specific about what products are you using or exactly what the system is doing. So and I think Time Series is useful to think of it in that sense also that it is a use case that gives you sort of a hint of what the characteristics of the system are, but Time Series is not a new database paradigm or a new way of doing things or things like that, so I think that's a good way to frame it and I want to analyze it in that specific way. So what Time Series database is useful for, so here are sort of the typical things you could find, the most obvious one and perhaps in a way that a lot of people don't think about it is anything to do with server logs or web server logs or any kind of logs that have a time stem and some information on it, that's the basic idea of Time Series data. And that stuff has been around obviously forever, but where it becomes more popular over more recent times is anything having to do with measuring real world equipment either in industry or weather sensors or other sensors, you can obviously measure more than weather but that's a good example to think about and you can a lot of measurement in terms of air quality and things like that are popular nowadays so you can do that and as those sensors become more widely available and cheaper there's more interest in that. But also anything having to do with the financial world, those are, that's the data that has time attached and obviously there's a lot of interest in that and just because there's a lot of business in that. And other use cases in science where people need to keep track of things over time and do analysis of that. So I think that's pretty clear where this is coming from. If you want to do a little bit more of a buzzword approach to this, sometimes when you're trying to maybe sell your idea, you need to have a couple of buzzwords, you can also deploy these, the Internet of Things is popular, that's basically the same idea as having lots of sensors connected to the Internet so that's not very different. Another interesting use case that is mentioned from time to time was self-driving cars mainly because there's a lot of data in those that needs to be analyzed either in real time or just during the development process. And that's a, there's a lot of sensors, a lot of data and obviously the data has to be also processed very quickly so that the cars can react in split seconds as they should. So that's an interesting area of research and development where a time series database is relevant. I'm probably not going to put any post-codes database in the self-driving cars anytime soon but the same ideas apply there. So another thing to clarify is that time series database is not the same thing at all as a temporal database and it's perhaps a little bit confusing as both of those terms have time or form of time in their name and they are also popular terms from time to time in the database field but they're not the same at all. Today I want to talk about time series obviously if you're interested in temporal I could recommend last year's PGCon, had a good talk about temporal databases. So you can just pull that up. So to have a simple way to tell them apart perhaps is that in time series you have records with time stems which is the time of when that piece of information was recorded or observed. Whereas in a temporal database you have time stem ranges which in post-codes could be an actual range type or in a general sense a start and end time stem and that is the time stem range when that piece of information was valid or is valid or will be valid depending on what you do. So those are entirely different. You could possibly even have both of them in the same database might be a little bit complicated but so let's keep those apart. So now what makes a time series database? That's the most important thing I want to talk about. It's a use case, a fuzzy term for a collection of characteristics and I have collected here based obviously on other people's research and so on. My analysis I've collected six characteristics that I want to go through of what makes a time series database and then how is that relevant to Postgres and how can Postgres satisfy these criteria. So I'll go through them in detail. So time stem is part of the key that seems kind of obvious. Time stem is usually increasing order. Data is usually more inserted, not so much updated. That obviously becomes interesting for certain performance characteristics. Usually time series databases have a lot of data. Usually individual records and older data matter less. It's more the aggregate that is more interesting. And time series database usually tries to do analytics based on time so there needs to be some support for that. So let's look into that in detail. So number one time stem is part of the key. So this is sort of the obvious thing that makes a time series database. You have data and every row has a time stem associated with it which is when the value is recorded or measured or something like that. And a very straightforward design in a relational database would look like this. Perhaps some ways you can optimize this and do it differently but just as an abstract thing it would look like this. You have a row with the time stem and then usually you have some kind of indication of the sensor in this case or what you're measuring. Now if you're measuring only a single thing or you only have one point of measurement you don't need that but usually you have all of those obviously. So you have some kind of a reference to what the device was and then you have some data that you're measuring and usually not only one you have probably quite a few of those usually so the rows could be quite wide. And then usually the combination of the time stem and some kind of indication of what the measurement point was would be the primary key. Whether you actually put a primary key on it or not or you index it differently is beside the point now. So that is how you would set up a time series database in a very straightforward way. So this does not mean that every single table has to have a time stem as a key. There could also be some reference data. So in this case the sensor table would not have a time stem but the main tables with the data would have that. So that's straightforward. How does Postgres support that? Postgres is great for that. It has really good support for anything having to do with time and date. That is widely regarded for that. Time stem, time stem TZ, time data type that you can use for that. Or if you somehow don't like that you can define your own way of recording time. So maybe I'm not necessarily recommending that here right now but it's something you could also do if you don't want to maybe spend the space on a time stem type. You can measure time somehow yourself and use the integer or big integer if that's the thing you want to do or define your own time stem type somehow. So for the most part all the data types in Postgres are not special. Time stems are not that special. You can put in whatever you want. This is how Postgres as a more general system is different from specialized time series databases that with the notion of time stems is really usually quite heavily baked in. So you can't really use those systems very well for something that's not a time series database. So it's often optimized for a specific way of doing things but if you somehow want to mix different approaches in a database and they're not very good at that in Postgres everything is more generalized. Second point, time stems are usually an increasing order. So that makes sense as you record data, as you get data from the input points, time increases so the time and the values that you record also increases. It's not an absolute because different sort of clients could report out of order depending on latency and all the things like that. So you can build your system around requiring that but it's a good way to optimize for. It will come to that later in terms of what queries and planners could do but this is an easy thing to optimize for that. You think that the values are generally pre-sorted on disk. So how does that work in Postgres? The B3 implementation in Postgres has a special optimization for this append use case. It caches the write most leaf page. So that basically means if you just keep appending values it has already sort of pre-cached the insert point for that. This is only an optimization. It doesn't have, obviously B3 is also fine for other use cases but this specific case is already optimized for that was put in a couple years ago. Also if you have mostly stored data that's great for range partitioning, obviously we'll come to that a little later but obviously in a lot of these time series use cases you want to do partitioning probably by time-stem, that would make sense to do that way. And so if you have ordered data then you usually only writing to one partition so one partition can be hard and kept in memory or the index memory and all the old partitions don't have to be kept in memory so that's how you want to use partitioning so that works well. And also brindindexes are useful for that. If you have brindindexes rely on data being sorted on disk basically so that it can index ranges that's what the name basically means right? Or in brindindex range so if you have entirely mixed data brindindexes can't help, brindindexes summarize ranges so if data is very well sorted you can use brindindexes as an alternative to B3 in certain cases and then you have a much smaller index and that's good. So this works all quite well in Postgres. Some room for improvement here especially this is maybe a minor point here but brindindexes are very useful often ignored by users and there could be more work being done in the planner to use brindindexes in more situations. For example if you want to order values and you have brindindexes that already gives you hints of the order of things and the planner could make more use of that so that's something maybe you think about. Other than that I think this point is quite well covered by Postgres. So then we said new data is inserted not updated. This is key to this whole design approach if you are setting up a time series database. In a let's say in a normal OLTP business database you usually only keep the current state. What is your current set of customers, your current set of inventory, your current set of orders and things like that. And when things change you run updates to change the address of a customer or the price of a product and things like that. In a time series database you basically don't do that and in the really extreme case you never update anything you keep all historical data and when there's a new information you just add a new record with a new timestamp. Now if you do this in a very extreme way that leads to problems that in terms of space usage and application performance. So there obviously has to be usually a middle ground has to be found but that is the idea. You don't update data but you keep all data to some degree. In a way this also overlaps with temporal databases. Here you hear those kind of ideas touch a little bit. The time series way of looking at it is recording of historical state up to the current time whereas temporal database is intentionally describing the range of when information is valid or will be valid also into the future. So this is a little bit where this overlaps. But the general idea is once you have recorded something in a time series you don't change it because that is a fact of the past. Now it could sometimes be that maybe corrections have to be applied if it turns out maybe a sense of us faulty or the clock was off or things like that. So you can't build a system around never allowing any updates but you can certainly optimize for not allowing any updates or not having usually not having updates. So how can Postgres work with that? Postgres is great for that right to a fault and that Postgres is relatively bad if you need to update a lot but if you just append the current heap is great for that. So this is sort of surprisingly not a problem. So then as we mentioned in alluded to time series databases often have or usually have a lot of data because we never change anything as we just said we just keep recording everything new. And as business usually goes these days the more data we have the more value we have whether that is actually factually true. It's to be debated but this is sort of how business is focusing often these days to collect a lot of data and then do some kind of analysis on it. But also because it's possible basically you storage can be had relatively cheaply so it is more feasible than it was maybe in the past to keep a lot more old data. A lot of these sensors and measurement points are relatively cheap. You can set up your own weather stations for really cheap money nowadays and that also applies and then all the other use cases that actually having more measurement points is relatively inexpensive. And you can also say if you want more data and more value you can also measure more often maybe instead of measuring the weather once a day or once an hour you measure it every minute or five minutes or every minute or all the time there is hardly any reasonable limit there. The main interesting point here is that in a time series database there is no natural limit of how much data you could have. If you think about this in a OLTP database in a business database there is some predictable limit of how much data you will have. So let's say if you have a hotel and you record your hotel reservations you know your hotel has a certain number of rooms and you can only have so many people in a room and only like one party and night and they usually stay a couple nights so if you multiply this even if you have a thousand rooms you know there you are going to have a thousand records per day times 300 or so days so that there is a limit of how much data to expect even if you have a chain of hotels and you record all kinds of other things who has over breakfast and things like that there is still sort of an upper limit to the multiplier of how much data you will have even if you are a big retailer and online and there is a limited number of customers you have and a limited number of items in your inventory so you can plan your database whereas in a time series database because of all these factors you know there is almost no limit you can collect as much data as you want or you can afford and whether that is sensible and valuable is perhaps another question that you know how much how useful is it to measure whether every five minutes versus every hour you know that is something that domain experts would have to answer but certainly the pressure is there to always get more and more into these kinds of databases often very difficult to plan for because you do not really know how much data you might want to get. So how can Postgres help with lots of data? So as we know he is not great for space usage mainly because of tuple header and there could be some more optimizations there there are other storage engines being either considered or have already been put out for that reason related reasons to have just more compact storage. Certainly partitioning is there to help with space usage and then based on partitioning sharding which partitioning over multiple hosts is something that there is some support in Postgres for that. But there are certainly lots of ways to improve that so here is a long list of how do we make handling large data battle in Postgres so we could work on making the table storage itself more compact with different storage engines or different tuple header representation or various details like that that work is underway. There could be a lot of work in the area of compression in all kinds of ways. So toast compression is used as an outdated compression algorithm so putting something better in there would certainly help somehow is toast applicable to a time series use case that depends if you use the schema I showed earlier where you have mostly numbers being recorded then probably not so much but in practice people also store JSON data maybe that's the JSON data that they get from whatever their measurement point is and then you just record that it's obviously not optimal at all but that's what people use so some toast optimization would certainly be useful and then there's other ways maybe to do compression to think about on a block level you can use file systems that can compress. All there's also perhaps ways to compress in a way that's specific to the data so let me explain that if you record a time series records consisting of a timestamp and then some data items that is the items that are being measured between adjacent records the data is probably not going to be very different so if you measure every minute you have that the next timestamp is only one minute different from the previous one but also the data being measured is probably not that different if you measure let's just keep it a simple case to measure the temperature again the temperature is probably either not going to change at all or it's going to be very similar so you could use some kind of a run length encoding or something similar to that and there's certainly more specific ideals there to optimize the storage of that so instead of you know recording everything explicitly timestamp value value you could optimize that somehow and say you know the first row you store timestamp value value and then the next row you just store the difference and then you don't need to use the full you know 12 bytes for you know the timestamp or the full 8 bytes or whatever you have for the values you can just store like a small difference and that could certainly have massive storage savings exactly how to represent that especially in Prozegaz how would you do that in Prozegaz what would that be is that you know a compression method is that a storage method how would you fit that into the system I don't know yet you know that sort of system is also you know would be very difficult to make updatable so that's more sort of for the archival end of the data and you would have to query it in a specific way so this would all be slower but it would be a very good way to compress things for the cold end of your data so that's something to think about so so again lots of options there for compression certainly the partition management could be improved I think partitioning itself is you know has evolved really well with the last few releases and the performance there is pretty good now but the management of partitions is has not been considered all that much so for example a very straightforward use case would be I would like one partition per month or one partition per day this is not easy to set up right now you have all the commands and there's some extensions that can help you manage that but it should perhaps be simpler and just say make the next partition or make the partitions for the next week or something of that sort that would sort of be I think that would be helpful and then on the bigger scale anything to do with charting is obviously you're currently only building blocks and you know the improvements there are there are a number of things that could be improved there so that's a long list of its own so in individual records met or less usually people are interested in the aggregated data and also older data matters less so there's a two sub points here and so the fact that the temperature reading at a specific time a week ago was that is not that interesting if you lose that one record and you have the record of you know five minutes before after that's not a disaster what matters is the aggregated data or the bulk data so that gives you certain options for optimizing crabs now this depends on the use case sometimes if this is system of record obviously you can't do that if you're recording financial data somehow perhaps then probably of other requirements but if you're just you know measuring stuff out there in the world and you're trying to make a little bit of analysis and modeling then one individual record is not the most important thing also as you know older data is usually not that important again that depends on the use case if you are doing you know historical analysis of weather patterns or air pollution patterns and you need the old data just as much as the new data but if you are observing you know your web traffic or your server traffic and you're trying to find maybe performance deviations then the new data is more relevant the old data is access less and maybe only therefore reference so how can pros and cons help with that again partitioning as I mentioned already before is good for that because then you can keep the hot data and the cold data separate if that's the kind of use case you can asynchronous commit is very applicable to this so that you don't have to you know if you have a lot of data being you know insert all the time so single row by single row you can turn asynchronous commit on or synchronous commit off the way it is said and then you get a little bit of performance boost and you know the risk of losing valuable data is slow or eliminated because you can obviously set that also per transaction so it's very very flexible and very useful for this yeah and you can also perhaps this is sort of depends on really the use case materialized views can be useful that if you just want to throw old data away and just compact it and pre-compute materialized views that's useful and you can also then maybe use tablespaces if you have sort of you know want to put sort of older data maybe on slower storage and things like that. Wombful improvement again partition management and in this case specifically the lifecycle management of the partition themselves not so much as we said in the previous point you know making new partitions but you're getting rid of the old partitions basically we have an open item that in postgres currently you can attach new partitions without a heavy lock but you cannot detach so that's well known that's being worked on that's definitely an open item and then just in general maybe you might think how about I just want to automatically throw away all partitions that are older than six months or something like that that seems an obvious use case you currently have to do that manually all the tools are there so this is you know not hard for someone to script or automate but maybe it could be easier. Also you know the management of tablespaces is just very basic so maybe there's some ideas there to improve that and if you want to use materialized views the often discussed materialized view incremental materialized view refresh could perhaps be useful so that you can still have new data arriving but instead of storing it all individually you could just update the materialized view so depending on the use case I think that's also useful and that's something that's also already being worked on so that might just come in handy. And then so finally the previous points for all like how is the database being set up and how is the data being put in there and then the question is what would you actually do with the data and that obviously really depends on the application but generally people want to do time-based analytics and usually also depending again but usually sort of quick analytics so these are not long analytics queries that might run for minutes or hours usually one sort of quick summaries of what was my traffic in the last five minutes, 15 minutes, what was my last traffic, what was my traffic per hour over the last day or two that's certainly the if you're processing log files and website traffic that's interesting but even if you are monitoring let's say industrial equipment you want to know quickly if anything is wrong or what's happening if it's over-eating or whatever the case may be so you want quick queries but you still want analytics queries basically. But then there could also be of course long you know longer running queries if you're doing again weather analysis over a longer time those could be heavy queries and very specific in domain specific queries. What you usually don't do is just a single row lookup unless it's maybe you're looking at financial data and you need to have you know need to look up something specific generally you don't use this system to look up like one record and really quickly it's mostly aggregation and then on top of that generally you want some tooling you know either to you know enter those queries or explore data or you know for any kind of visualization or front-ends or more advanced math on top of data so there's some requirements to have higher level tooling as well. So how does Postgres support this? You know obviously analytics support in SQL in general is pretty good and Postgres implements most of that most of what would be relevant obviously grouping is pretty basic. Other functions are well supported in Postgres now so that's great and that's really what makes an SQL database really useful for a time series use case compared to maybe more specialized databases that have a very limited query language that might be really quickly of specific use case but if you then want to you know break out of what they support really well then you might not have any support at all or you have to program that yourself a client side so an SQL database is you know is obviously really powerful query functionality. There is some support in Postgres for doing daytime processing but actually surprisingly not a lot and that's an area to improve in. So for example the use case that I alluded to a moment ago if you want to have you know show me my traffic which would be maybe account show me my account per hour over the last couple days. That's a pretty straightforward group like where you could use day trunk to truncate the timestamps to the hour and that would work. What doesn't work in the same way is if you want to have traffic by every 15 minutes. So how do you truncate a timestamp to around or truncate it as you might want to 15 minute intervals there's no built-in support for that. You'd have to somehow make you know you could write that yourself but it's complicated in the details so there's better support there would obviously be useful. But the good thing is Postgres is extensible especially in this area of functions and operator so whatever you need you can add yourself. But room for improvement especially what I just mentioned that taking day trunk and expanding that to support arbitrary intervals and there could also be more advanced functionality if you want to do maybe histograms based on times and things like that but there's some more basic gaps that are missing which would be relatively easy to plug so there's some work already going on on that. And then another necessary improvement in that area for that same use case give me you know information by some interval like an hour or 15 minutes. If you use day trunk or something similar this kind of this loses information in the planner so if you're in a complex query and you have sorted input what the planner thinks is sorted input or mostly sorted input and then you run you know as in the case of the time stamps the time stamps are we said are mostly sorted and then you run day trunk you know the over them the planner doesn't know anything about that and so it doesn't think that the output of that is sorted anymore and then it would have to use other plans so this could be sort of the difference between a group aggregate and a hash aggregate even though we know as implementers we know that something like day trunk would preserve the order of the input but the planner doesn't have any information on that so that would be something that's also already been vaguely discussed some time ago to add another function attribute of some sort to tell the planner that this is an order preserving function or whatever you want to call it but order preserving is probably a good way to describe it so those two first things are would be really useful for you know just supporting basic time bucketing queries and they're well within reach and not too hard so and then there's you know on the tooling side there's I think many opportunities outside of Postgres core for visualization frameworks or any kind of query construction frameworks or additional is or extended math extensions and things like that so that's not something that necessarily belongs in the postgres itself but that's certainly you know external tools for that would be useful okay so those is my discussion of those time series characteristics here's a summary of the development projects that I mentioned and proposed sort of ordered in a way that maybe I would do them this is not necessarily priority but a combination of most useful and easiest to do perhaps and a lot of those are already in progress in one way or another not all of them under the time series banner but also you know addressing other use cases but there's a lot of interesting things that could be done to make Postgres better for this so and you know as I mentioned some of these are already in progress some of them not so much so basically most of these are on my radar somehow but certainly someone else wants to look into that or collaborate that be welcome all right so to summarize so I think Postgres is is great four time series and it's like a universal database system and it has shown itself to be adaptable to many different use cases that as we've shown but you know key value stores JSON and things like that I think it's a it's a has sound fundamentals and can be extended in different ways for different use cases that said there is there are always going to be more specialized systems out there that are you have a specific use case in mind and then we'll beat any universal system we know that very well there's you know key value stores that are in memory perhaps and super optimized and you know beat any generalized database system certainly but that's not the point right that's not what we should aim for the aim is to be a general universal database system but also have facilities that cater to specific use cases and the you know lots of improvements are possible but they're all within reach and they're all reasonable they are mostly already conceived and in progress so if all of that comes together or many of those come together then be even greater time series database okay so that is my presentation at the actual conference there will be no questions so I'll look forward to answering those if you're not there then also feel free to reach out to me you know via my contact information if you want to chat about some of these development projects or you have some comments on my classification of things or you just want to chat about postcards feel free to reach out that said I hope to see all of you soon again at a postcards conference and until then take care bye and cut is here live for the Q&A go ahead Peter oh hello everyone so I'll just go through the IRC here the question of how the B3 append optimization works was already answered in the chat that's automatic that was added a couple of releases ago so you don't have to do anything about that also the point that was made in the chat about the new feature in postcards 13 that auto vacuum is triggered appropriately for insert only tables which actually it's a very good point I didn't even address that in my talk it's obviously very applicable to these kinds of workloads so that's that's a great improvement and yeah that's great and thank you for pointing that out question about about brin indexes so if you are doing updates on records that will potentially break brin indexes what would you have to do in that case so there's nothing really automatic about that to answer that question what exactly should you do in that case really kind of depends on the exact pattern of what updates you would do you know if you if it's affecting maybe your entire partition then maybe you can just we you know cluster that table possibly there's also special functions for brin indexes that can so they call brin summarize range and bring summarize new values to to make sort of specific changes there so perhaps do look into the documentation of brin indexes to find out details about that but yeah in principle in general if you do updates on brin indexes then you could run into slight problems you might have to rebuild question from I believe Vic right about anyone working on automatic partition creation so the way I would see that working is I don't know exactly what you what you envision there is the what I think we will not do is create the partition sort of at the time of insertion I think that people have agreed that that's it would create all kinds of bizarre locking concurrency problems and rail cash and that kind of stuff so if you're looking for that I don't think anyone's really working on that what I would envision as next steps would be sort of indicating at the time you create the partition table to say you know I want this to be partitioned by month and then you just have a command that says alter table or you know something like that so make me the next four partitions and then it kind of knows what pattern you want or you could also imagine that for hash partitions which is not applicable here but you could say I want 16 partitions just grade them all so that is something that I think we've looked into internally at second quadrant and we might work on that for post-cars 14 that's sort of a soft you know written a biped item other than that I don't know any other work on that question from Don dropping an old partition is the same as a detached basically so that also requires the heavy lock that's something Alvaro is looking at fixing in post-cars 14 that the initial implementation of the concurrent attached turned out to be different from his initial implementation that's why he had to rework the entire concurrent detach but that's something it's our best but yeah right now it requires a heavy lock okay what else yeah more work more stuff on the auto vacuum question from Jesper do you think that with the new feature index in 13 that's that store become should become an option for time series well that's I didn't think about that at all I think that's certainly an option to to think about depending on how you use it right if you have especially sort of very wide rows it's an option or I haven't really thought about it too much but certainly I wouldn't dismiss it now and with this with the feature deduplication I think that's what you're referring to yeah I did watch the Zet Store talk yesterday and then try to make sense of it so there's there's more learning to be done there but yeah okay more questions coming in great time scale to be extension already exists does it already implement a lot of things you mentioned could be proved yeah so time scale to be is an extension that addresses a lot of these points you know you can use it it's as a different license in post-cars so you know just evaluate that for yourself I think that's great and they're certainly you know doing a lot of good work there what I would look for is a little bit more sort of general solutions like for example the the planner so the way I describe the planner a function attributes to hit the planner the way I describe that my understanding is that time scale DB kind of does it in a more sort of hard-coded hacked way and say like this function treated that way you know that sort of thing so yeah you can use it and it's very you know it seems to people like people seem to like it but I would look in the long run for more you know so generalized post-cars like extensible solutions in all of these areas but yeah that's that's us I guess a one step in the right direction that timescale to be recently implemented and he did with with buckets four times that easy I was gonna submit as a patch yes submit that patch I don't have anything I don't know anyone else is working on that you are aware of the work by John Naylor to implement the sort of enhanced version of date trunk that I mentioned there's no other work in progress right now yeah do look at the timescale to be just you know I'm not gonna look into source code because there's licensing differences so I can't really comment more about that I'm just reading their documentation and they certainly you know asked a lot of the right questions just maybe different opinions and how the answers should be implemented let's talk about automatic partitioning automatic partitioning is sounds good but it's nobody's really working on that I missed a bunch of your answer first bit of video was cut off I'm sorry Russ your question was about the tree a bit of brin yeah brin is no it doesn't do that automatically read the documentation sorry I'm going through the IRC I should probably read the questions read the questions I like yeah okay so you re-asked the question about brins yeah the question about brin it does yeah those updates do break there's nothing automatic that fixes it you but there are functions specifically for brin to do certain adjustments depending on what you're doing question about custom type creation documentation says types are implemented using mostly C is it possible to create type using SQL language the answer to that is I believe no not right now because you you do need to basically take input string input and convert it to a byte pattern for storage so that's girl can't do that you could maybe you know one example that is probably currently not possible but you can imagine you're doing it in something like rust where you can sort of lay out bits and write them so on that level but not in just plain as girl that's not possible right now question from David Federer any wins to be had from s I am the I'm sorry I don't know what that is if anyone we still have a couple minutes if anyone got their question cut off please ask it again I'm just monitoring the IRC also a good question that I just answered from Marius 46 about wanting to convert units like the gallons there's actually an extension for that post grass girl hyphen units I believe it's called so just check that out if you're interested in that yeah I don't know if you got my answer to your question but with bucket please just submit that patch yep there you go thank you anyone know it as I am the single instruction multiple data so is that sort of vector processing I am I'm sorry I still don't know really what that is I've heard that before but I have not thought about that probably even if I knew what that was I don't have an answer but if it's sort of there's certainly further improvements in execution execute optimization possible that go beyond what I have really thought about here that's all the questions now yeah vector processing yeah yeah so I think it would I think it could definitely benefit from that that's you know goes a little bit beyond what I had thought about and obviously that would also affect you know a bunch of other it mostly analytics use cases in general so definitely also yeah maybe that those are not the sort of the top issues but yeah what's that to the bottom of the list yeah good point thank you now all right so the you know a lot of the items I mentioned are in progress as we have also just discussed now some of those are already in the next very next commit fast some of those are you know being worked on here and there if anyone wants to know specifically like who is working on what maybe you know just drop me a line and otherwise I'll you know hope to see you all soon again or I'll see you at the next commit fast thank you Peter thanks thanks Dan thanks everyone goodbye bye you
 Hello everyone, this is Peter Eisenthraut recording my presentation for PGCon 2020 from  home in Germany.  So let's get right to it.  So today I want to talk about time series databases and as I usually do, especially  at PGCon, I don't want to talk so much about a specific way of doing things or any kind  of particular products, but I want to take a topic and actually ask myself, what does  this even mean?  And then further ask, you know, what does it mean for me as a user or the users that  I support and what does it mean for me as a developer of the Postgres Core itself?  And that's what I want to do today.  So my first premise here is that time series is a use case.  So time series is a term that's often thrown around in database circles, it sort of comes  and goes over the years as a trend.  And oftentimes it comes sort of with a big marketing message that it's a new way of  doing databases and it's a new paradigm or what have you, right?  So my sort of, yeah, premise here is I consider time series to be a use case, just like we  have, for example, OLTP.  That's a term that is used a lot, but it doesn't mean anything very specific if you think about  it.  If you are, let's say, a database administrator or a consultant and you come to a new database  system and somebody tells you this is an OLTP system, that tells you something.  It doesn't give you any specifics of what the system is doing or any of its performance  metrics, but it gives you an idea of what the system is for and what the characteristics  are, right?  So it probably has a fair amount of updates and a fair amount of reads, usually a smaller  transactions, but a lot of them and sort of made a mainly continuous load and probably  sort of pretty good uptime requirements and the data in there is valuable, so it has to  be probably backed up regularly and so on.  And that is understandable and that's sort of a handy term to use and so at the almost  opposite end of that, you have OLAP, which also tells you things, it tells you it's probably  a very big database, it probably has a lot of reads, maybe not so many writes or the  writes maybe bulk loaded depending on the setup.  So those kind of terms help us understand in general what the system is doing and then  how we have to think about it and how we have to work with it, but they don't tell you anything  specific about what products are you using or exactly what the system is doing.  So and I think Time Series is useful to think of it in that sense also that it is a use  case that gives you sort of a hint of what the characteristics of the system are, but  Time Series is not a new database paradigm or a new way of doing things or things like  that, so I think that's a good way to frame it and I want to analyze it in that specific  way.  So what Time Series database is useful for, so here are sort of the typical things you  could find, the most obvious one and perhaps in a way that a lot of people don't think  about it is anything to do with server logs or web server logs or any kind of logs that  have a time stem and some information on it, that's the basic idea of Time Series data.  And that stuff has been around obviously forever, but where it becomes more popular over more  recent times is anything having to do with measuring real world equipment either in industry  or weather sensors or other sensors, you can obviously measure more than weather but that's  a good example to think about and you can a lot of measurement in terms of air quality  and things like that are popular nowadays so you can do that and as those sensors become  more widely available and cheaper there's more interest in that.  But also anything having to do with the financial world, those are, that's the data that has  time attached and obviously there's a lot of interest in that and just because there's  a lot of business in that.  And other use cases in science where people need to keep track of things over time and  do analysis of that.  So I think that's pretty clear where this is coming from.  If you want to do a little bit more of a buzzword approach to this, sometimes when you're trying  to maybe sell your idea, you need to have a couple of buzzwords, you can also deploy  these, the Internet of Things is popular, that's basically the same idea as having lots  of sensors connected to the Internet so that's not very different.  Another interesting use case that is mentioned from time to time was self-driving cars mainly  because there's a lot of data in those that needs to be analyzed either in real time or  just during the development process.  And that's a, there's a lot of sensors, a lot of data and obviously the data has to  be also processed very quickly so that the cars can react in split seconds as they should.  So that's an interesting area of research and development where a time series database  is relevant.  I'm probably not going to put any post-codes database in the self-driving cars anytime  soon but the same ideas apply there.  So another thing to clarify is that time series database is not the same thing at all as a  temporal database and it's perhaps a little bit confusing as both of those terms have  time or form of time in their name and they are also popular terms from time to time in  the database field but they're not the same at all.  Today I want to talk about time series obviously if you're interested in temporal I could recommend  last year's PGCon, had a good talk about temporal databases.  So you can just pull that up.  So to have a simple way to tell them apart perhaps is that in time series you have records  with time stems which is the time of when that piece of information was recorded or  observed.  Whereas in a temporal database you have time stem ranges which in post-codes could be an  actual range type or in a general sense a start and end time stem and that is the time  stem range when that piece of information was valid or is valid or will be valid depending  on what you do.  So those are entirely different.  You could possibly even have both of them in the same database might be a little bit  complicated but so let's keep those apart.  So now what makes a time series database?  That's the most important thing I want to talk about.  It's a use case, a fuzzy term for a collection of characteristics and I have collected here  based obviously on other people's research and so on.  My analysis I've collected six characteristics that I want to go through of what makes a  time series database and then how is that relevant to Postgres and how can Postgres  satisfy these criteria.  So I'll go through them in detail.  So time stem is part of the key that seems kind of obvious.  Time stem is usually increasing order.  Data is usually more inserted, not so much updated.  That obviously becomes interesting for certain performance characteristics.  Usually time series databases have a lot of data.  Usually individual records and older data matter less.  It's more the aggregate that is more interesting.  And time series database usually tries to do analytics based on time so there needs to  be some support for that.  So let's look into that in detail.  So number one time stem is part of the key.  So this is sort of the obvious thing that makes a time series database.  You have data and every row has a time stem associated with it which is when the value  is recorded or measured or something like that.  And a very straightforward design in a relational database would look like this.  Perhaps some ways you can optimize this and do it differently but just as an abstract  thing it would look like this.  You have a row with the time stem and then usually you have some kind of indication of  the sensor in this case or what you're measuring.  Now if you're measuring only a single thing or you only have one point of measurement you  don't need that but usually you have all of those obviously.  So you have some kind of a reference to what the device was and then you have some data  that you're measuring and usually not only one you have probably quite a few of those  usually so the rows could be quite wide.  And then usually the combination of the time stem and some kind of indication of what the  measurement point was would be the primary key.  Whether you actually put a primary key on it or not or you index it differently is beside  the point now.  So that is how you would set up a time series database in a very straightforward way.  So this does not mean that every single table has to have a time stem as a key.  There could also be some reference data.  So in this case the sensor table would not have a time stem but the main tables with  the data would have that.  So that's straightforward.  How does Postgres support that?  Postgres is great for that.  It has really good support for anything having to do with time and date.  That is widely regarded for that.  Time stem, time stem TZ, time data type that you can use for that.  Or if you somehow don't like that you can define your own way of recording time.  So maybe I'm not necessarily recommending that here right now but it's something you  could also do if you don't want to maybe spend the space on a time stem type.  You can measure time somehow yourself and use the integer or big integer if that's the  thing you want to do or define your own time stem type somehow.  So for the most part all the data types in Postgres are not special.  Time stems are not that special.  You can put in whatever you want.  This is how Postgres as a more general system is different from specialized time series databases  that with the notion of time stems is really usually quite heavily baked in.  So you can't really use those systems very well for something that's not a time series  database.  So it's often optimized for a specific way of doing things but if you somehow want to  mix different approaches in a database and they're not very good at that in Postgres  everything is more generalized.  Second point, time stems are usually an increasing order.  So that makes sense as you record data, as you get data from the input points, time increases  so the time and the values that you record also increases.  It's not an absolute because different sort of clients could report out of order depending  on latency and all the things like that.  So you can build your system around requiring that but it's a good way to optimize for.  It will come to that later in terms of what queries and planners could do but this is  an easy thing to optimize for that.  You think that the values are generally pre-sorted on disk.  So how does that work in Postgres?  The B3 implementation in Postgres has a special optimization for this append use case.  It caches the write most leaf page.  So that basically means if you just keep appending values it has already sort of pre-cached the  insert point for that.  This is only an optimization.  It doesn't have, obviously B3 is also fine for other use cases but this specific case  is already optimized for that was put in a couple years ago.  Also if you have mostly stored data that's great for range partitioning, obviously we'll  come to that a little later but obviously in a lot of these time series use cases you  want to do partitioning probably by time-stem, that would make sense to do that way.  And so if you have ordered data then you usually only writing to one partition so one partition  can be hard and kept in memory or the index memory and all the old partitions don't have  to be kept in memory so that's how you want to use partitioning so that works well.  And also brindindexes are useful for that.  If you have brindindexes rely on data being sorted on disk basically so that it can index  ranges that's what the name basically means right?  Or in brindindex range so if you have entirely mixed data brindindexes can't help, brindindexes  summarize ranges so if data is very well sorted you can use brindindexes as an alternative  to B3 in certain cases and then you have a much smaller index and that's good.  So this works all quite well in Postgres.  Some room for improvement here especially this is maybe a minor point here but brindindexes  are very useful often ignored by users and there could be more work being done in the  planner to use brindindexes in more situations.  For example if you want to order values and you have brindindexes that already gives you  hints of the order of things and the planner could make more use of that so that's something  maybe you think about.  Other than that I think this point is quite well covered by Postgres.  So then we said new data is inserted not updated.  This is key to this whole design approach if you are setting up a time series database.  In a let's say in a normal OLTP business database you usually only keep the current state.  What is your current set of customers, your current set of inventory, your current set  of orders and things like that.  And when things change you run updates to change the address of a customer or the price of  a product and things like that.  In a time series database you basically don't do that and in the really extreme case you  never update anything you keep all historical data and when there's a new information you  just add a new record with a new timestamp.  Now if you do this in a very extreme way that leads to problems that in terms of space usage  and application performance.  So there obviously has to be usually a middle ground has to be found but that is the idea.  You don't update data but you keep all data to some degree.  In a way this also overlaps with temporal databases.  Here you hear those kind of ideas touch a little bit.  The time series way of looking at it is recording of historical state up to the current time  whereas temporal database is intentionally describing the range of when information is valid or will be valid also into the future.  So this is a little bit where this overlaps.  But the general idea is once you have recorded something in a time series you don't change  it because that is a fact of the past.  Now it could sometimes be that maybe corrections have to be applied if it turns out maybe a  sense of us faulty or the clock was off or things like that.  So you can't build a system around never allowing any updates but you can certainly optimize  for not allowing any updates or not having usually not having updates.  So how can Postgres work with that?  Postgres is great for that right to a fault and that Postgres is relatively bad if you  need to update a lot but if you just append the current heap is great for that.  So this is sort of surprisingly not a problem.  So then as we mentioned in alluded to time series databases often have or usually have  a lot of data because we never change anything as we just said we just keep recording everything  new.  And as business usually goes these days the more data we have the more value we have whether  that is actually factually true.  It's to be debated but this is sort of how business is focusing often these days to  collect a lot of data and then do some kind of analysis on it.  But also because it's possible basically you storage can be had relatively cheaply so it  is more feasible than it was maybe in the past to keep a lot more old data.  A lot of these sensors and measurement points are relatively cheap.  You can set up your own weather stations for really cheap money nowadays and that also applies  and then all the other use cases that actually having more measurement points is relatively  inexpensive.  And you can also say if you want more data and more value you can also measure more often  maybe instead of measuring the weather once a day or once an hour you measure it every  minute or five minutes or every minute or all the time there is hardly any reasonable  limit there.  The main interesting point here is that in a time series database there is no natural  limit of how much data you could have.  If you think about this in a OLTP database in a business database there is some predictable  limit of how much data you will have.  So let's say if you have a hotel and you record your hotel reservations you know your hotel  has a certain number of rooms and you can only have so many people in a room and only  like one party and night and they usually stay a couple nights so if you multiply this  even if you have a thousand rooms you know there you are going to have a thousand records  per day times 300 or so days so that there is a limit of how much data to expect even  if you have a chain of hotels and you record all kinds of other things who has over breakfast  and things like that there is still sort of an upper limit to the multiplier of how much  data you will have even if you are a big retailer and online and there is a limited number of  customers you have and a limited number of items in your inventory so you can plan your  database whereas in a time series database because of all these factors you know there  is almost no limit you can collect as much data as you want or you can afford and whether  that is sensible and valuable is perhaps another question that you know how much how useful  is it to measure whether every five minutes versus every hour you know that is something  that domain experts would have to answer but certainly the pressure is there to always  get more and more into these kinds of databases often very difficult to plan for because you  do not really know how much data you might want to get.  So how can Postgres help with lots of data?  So as we know he is not great for space usage mainly because of tuple header and there could  be some more optimizations there there are other storage engines being either considered  or have already been put out for that reason related reasons to have just more compact  storage.  Certainly partitioning is there to help with space usage and then based on partitioning  sharding which partitioning over multiple hosts is something that there is some support  in Postgres for that.  But there are certainly lots of ways to improve that so here is a long list of how do we make  handling large data battle in Postgres so we could work on making the table storage itself  more compact with different storage engines or different tuple header representation or  various details like that that work is underway.  There could be a lot of work in the area of compression in all kinds of ways.  So toast compression is used as an outdated compression algorithm so putting something  better in there would certainly help somehow is toast applicable to a time series use case  that depends if you use the schema I showed earlier where you have mostly numbers being  recorded then probably not so much but in practice people also store JSON data maybe  that's the JSON data that they get from whatever their measurement point is and then you just  record that it's obviously not optimal at all but that's what people use so some toast  optimization would certainly be useful and then there's other ways maybe to do compression  to think about on a block level you can use file systems that can compress.  All there's also perhaps ways to compress in a way that's specific to the data so let  me explain that if you record a time series records consisting of a timestamp and then  some data items that is the items that are being measured between adjacent records the  data is probably not going to be very different so if you measure every minute you have that  the next timestamp is only one minute different from the previous one but also the data being  measured is probably not that different if you measure let's just keep it a simple case  to measure the temperature again the temperature is probably either not going to change at  all or it's going to be very similar so you could use some kind of a run length encoding  or something similar to that and there's certainly more specific ideals there to optimize the  storage of that so instead of you know recording everything explicitly timestamp value value  you could optimize that somehow and say you know the first row you store timestamp value  value and then the next row you just store the difference and then you don't need to  use the full you know 12 bytes for you know the timestamp or the full 8 bytes or whatever  you have for the values you can just store like a small difference and that could certainly  have massive storage savings exactly how to represent that especially in Prozegaz how  would you do that in Prozegaz what would that be is that you know a compression method is  that a storage method how would you fit that into the system I don't know yet you know  that sort of system is also you know would be very difficult to make updatable so that's  more sort of for the archival end of the data and you would have to query it in a specific  way so this would all be slower but it would be a very good way to compress things for  the cold end of your data so that's something to think about so so again lots of options  there for compression certainly the partition management could be improved I think partitioning  itself is you know has evolved really well with the last few releases and the performance  there is pretty good now but the management of partitions is has not been considered all  that much so for example a very straightforward use case would be I would like one partition  per month or one partition per day this is not easy to set up right now you have all  the commands and there's some extensions that can help you manage that but it should perhaps  be simpler and just say make the next partition or make the partitions for the next week or  something of that sort that would sort of be I think that would be helpful and then on  the bigger scale anything to do with charting is obviously you're currently only building  blocks and you know the improvements there are there are a number of things that could  be improved there so that's a long list of its own so in individual records met or less  usually people are interested in the aggregated data and also older data matters less so there's  a two sub points here and so the fact that the temperature reading at a specific time  a week ago was that is not that interesting if you lose that one record and you have the  record of you know five minutes before after that's not a disaster what matters is the  aggregated data or the bulk data so that gives you certain options for optimizing crabs now  this depends on the use case sometimes if this is system of record obviously you can't do  that if you're recording financial data somehow perhaps then probably of other requirements  but if you're just you know measuring stuff out there in the world and you're trying to  make a little bit of analysis and modeling then one individual record is not the most  important thing also as you know older data is usually not that important again that depends  on the use case if you are doing you know historical analysis of weather patterns or  air pollution patterns and you need the old data just as much as the new data but if you  are observing you know your web traffic or your server traffic and you're trying to  find maybe performance deviations then the new data is more relevant the old data is  access less and maybe only therefore reference so how can pros and cons help with that again  partitioning as I mentioned already before is good for that because then you can keep  the hot data and the cold data separate if that's the kind of use case you can asynchronous  commit is very applicable to this so that you don't have to you know if you have a lot  of data being you know insert all the time so single row by single row you can turn asynchronous  commit on or synchronous commit off the way it is said and then you get a little bit of  performance boost and you know the risk of losing valuable data is slow or eliminated  because you can obviously set that also per transaction so it's very very flexible and  very useful for this yeah and you can also perhaps this is sort of depends on really  the use case materialized views can be useful that if you just want to throw old data away  and just compact it and pre-compute materialized views that's useful and you can also then  maybe use tablespaces if you have sort of you know want to put sort of older data maybe  on slower storage and things like that.  Wombful improvement again partition management and in this case specifically the lifecycle  management of the partition themselves not so much as we said in the previous point you  know making new partitions but you're getting rid of the old partitions basically we have  an open item that in postgres currently you can attach new partitions without a heavy  lock but you cannot detach so that's well known that's being worked on that's definitely  an open item and then just in general maybe you might think how about I just want to automatically  throw away all partitions that are older than six months or something like that that seems  an obvious use case you currently have to do that manually all the tools are there so  this is you know not hard for someone to script or automate but maybe it could be easier.  Also you know the management of tablespaces is just very basic so maybe there's some ideas  there to improve that and if you want to use materialized views the often discussed materialized  view incremental materialized view refresh could perhaps be useful so that you can still  have new data arriving but instead of storing it all individually you could just update  the materialized view so depending on the use case I think that's also useful and that's  something that's also already being worked on so that might just come in handy.  And then so finally the previous points for all like how is the database being set up  and how is the data being put in there and then the question is what would you actually  do with the data and that obviously really depends on the application but generally  people want to do time-based analytics and usually also depending again but usually sort  of quick analytics so these are not long analytics queries that might run for minutes or hours  usually one sort of quick summaries of what was my traffic in the last five minutes, 15  minutes, what was my last traffic, what was my traffic per hour over the last day or two  that's certainly the if you're processing log files and website traffic that's interesting  but even if you are monitoring let's say industrial equipment you want to know quickly if anything  is wrong or what's happening if it's over-eating or whatever the case may be so you want quick  queries but you still want analytics queries basically.  But then there could also be of course long you know longer running queries if you're  doing again weather analysis over a longer time those could be heavy queries and very  specific in domain specific queries.  What you usually don't do is just a single row lookup unless it's maybe you're looking  at financial data and you need to have you know need to look up something specific generally  you don't use this system to look up like one record and really quickly it's mostly  aggregation and then on top of that generally you want some tooling you know either to you  know enter those queries or explore data or you know for any kind of visualization or  front-ends or more advanced math on top of data so there's some requirements to have  higher level tooling as well.  So how does Postgres support this?  You know obviously analytics support in SQL in general is pretty good and Postgres implements  most of that most of what would be relevant obviously grouping is pretty basic.  Other functions are well supported in Postgres now so that's great and that's really what  makes an SQL database really useful for a time series use case compared to maybe more  specialized databases that have a very limited query language that might be really quickly  of specific use case but if you then want to you know break out of what they support  really well then you might not have any support at all or you have to program that yourself  a client side so an SQL database is you know is obviously really powerful query functionality.  There is some support in Postgres for doing daytime processing but actually surprisingly  not a lot and that's an area to improve in.  So for example the use case that I alluded to a moment ago if you want to have you know  show me my traffic which would be maybe account show me my account per hour over the last  couple days.  That's a pretty straightforward group like where you could use day trunk to truncate  the timestamps to the hour and that would work.  What doesn't work in the same way is if you want to have traffic by every 15 minutes.  So how do you truncate a timestamp to around or truncate it as you might want to 15 minute  intervals there's no built-in support for that.  You'd have to somehow make you know you could write that yourself but it's complicated in  the details so there's better support there would obviously be useful.  But the good thing is Postgres is extensible especially in this area of functions and operator  so whatever you need you can add yourself.  But room for improvement especially what I just mentioned that taking day trunk and expanding  that to support arbitrary intervals and there could also be more advanced functionality  if you want to do maybe histograms based on times and things like that but there's some  more basic gaps that are missing which would be relatively easy to plug so there's some  work already going on on that.  And then another necessary improvement in that area for that same use case give me you  know information by some interval like an hour or 15 minutes.  If you use day trunk or something similar this kind of this loses information in the  planner so if you're in a complex query and you have sorted input what the planner thinks  is sorted input or mostly sorted input and then you run you know as in the case of the  time stamps the time stamps are we said are mostly sorted and then you run day trunk you  know the over them the planner doesn't know anything about that and so it doesn't think  that the output of that is sorted anymore and then it would have to use other plans  so this could be sort of the difference between a group aggregate and a hash aggregate even  though we know as implementers we know that something like day trunk would preserve the  order of the input but the planner doesn't have any information on that so that would  be something that's also already been vaguely discussed some time ago to add another function  attribute of some sort to tell the planner that this is an order preserving function  or whatever you want to call it but order preserving is probably a good way to describe  it so those two first things are would be really useful for you know just supporting  basic time bucketing queries and they're well within reach and not too hard so and then  there's you know on the tooling side there's I think many opportunities outside of Postgres  core for visualization frameworks or any kind of query construction frameworks or additional  is or extended math extensions and things like that so that's not something that necessarily  belongs in the postgres itself but that's certainly you know external tools for that  would be useful okay so those is my discussion of those time series characteristics here's  a summary of the development projects that I mentioned and proposed sort of ordered in  a way that maybe I would do them this is not necessarily priority but a combination of most  useful and easiest to do perhaps and a lot of those are already in progress in one way  or another not all of them under the time series banner but also you know addressing  other use cases but there's a lot of interesting things that could be done to make Postgres  better for this so and you know as I mentioned some of these are already in progress some  of them not so much so basically most of these are on my radar somehow but certainly someone  else wants to look into that or collaborate that be welcome all right so to summarize  so I think Postgres is is great four time series and it's like a universal database  system and it has shown itself to be adaptable to many different use cases that as we've  shown but you know key value stores JSON and things like that I think it's a it's a has  sound fundamentals and can be extended in different ways for different use cases that  said there is there are always going to be more specialized systems out there that are  you have a specific use case in mind and then we'll beat any universal system we know that  very well there's you know key value stores that are in memory perhaps and super optimized  and you know beat any generalized database system certainly but that's not the point  right that's not what we should aim for the aim is to be a general universal database  system but also have facilities that cater to specific use cases and the you know lots  of improvements are possible but they're all within reach and they're all reasonable they  are mostly already conceived and in progress so if all of that comes together or many of  those come together then be even greater time series database okay so that is my presentation  at the actual conference there will be no questions so I'll look forward to answering  those if you're not there then also feel free to reach out to me you know via my contact  information if you want to chat about some of these development projects or you have  some comments on my classification of things or you just want to chat about postcards feel  free to reach out that said I hope to see all of you soon again at a postcards conference  and until then take care bye and cut  is here live for the Q&A go ahead Peter oh hello everyone so I'll just go through the  IRC here the question of how the B3 append optimization works was already answered in  the chat that's automatic that was added a couple of releases ago so you don't have to  do anything about that also the point that was made in the chat about the new feature  in postcards 13 that auto vacuum is triggered appropriately for insert only tables which  actually it's a very good point I didn't even address that in my talk it's obviously very  applicable to these kinds of workloads so that's that's a great improvement and yeah  that's great and thank you for pointing that out question about about brin indexes so if  you are doing updates on records that will potentially break brin indexes what would  you have to do in that case so there's nothing really automatic about that to answer that  question what exactly should you do in that case really kind of depends on the exact pattern  of what updates you would do you know if you if it's affecting maybe your entire partition  then maybe you can just we you know cluster that table possibly there's also special functions  for brin indexes that can so they call brin summarize range and bring summarize new values  to to make sort of specific changes there so perhaps do look into the documentation of  brin indexes to find out details about that but yeah in principle in general if you do  updates on brin indexes then you could run into slight problems you might have to rebuild  question from I believe Vic right about anyone working on automatic partition creation so  the way I would see that working is I don't know exactly what you what you envision there  is the what I think we will not do is create the partition sort of at the time of insertion  I think that people have agreed that that's it would create all kinds of bizarre locking  concurrency problems and rail cash and that kind of stuff so if you're looking for that  I don't think anyone's really working on that what I would envision as next steps would  be sort of indicating at the time you create the partition table to say you know I want  this to be partitioned by month and then you just have a command that says alter table  or you know something like that so make me the next four partitions and then it kind of  knows what pattern you want or you could also imagine that for hash partitions which is  not applicable here but you could say I want 16 partitions just grade them all so that  is something that I think we've looked into internally at second quadrant and we might  work on that for post-cars 14 that's sort of a soft you know written a biped item  other than that I don't know any other work on that question from Don dropping an old  partition is the same as a detached basically so that also requires the heavy lock that's  something Alvaro is looking at fixing in post-cars 14 that the initial implementation  of the concurrent attached turned out to be different from his initial implementation  that's why he had to rework the entire concurrent detach but that's something it's our best  but yeah right now it requires a heavy lock okay what else yeah more work more stuff on  the auto vacuum question from Jesper do you think that with the new feature index in 13  that's that store become should become an option for time series well that's I didn't  think about that at all I think that's certainly an option to to think about depending on how  you use it right if you have especially sort of very wide rows it's an option or I haven't  really thought about it too much but certainly I wouldn't dismiss it now and with this with  the feature deduplication I think that's what you're referring to yeah I did watch the  Zet Store talk yesterday and then try to make sense of it so there's there's more learning  to be done there but yeah okay more questions coming in great time scale to be extension  already exists does it already implement a lot of things you mentioned could be proved  yeah so time scale to be is an extension that addresses a lot of these points you know you  can use it it's as a different license in post-cars so you know just evaluate that for  yourself I think that's great and they're certainly you know doing a lot of good work  there what I would look for is a little bit more sort of general solutions like for example  the the planner so the way I describe the planner a function attributes to hit the planner  the way I describe that my understanding is that time scale DB kind of does it in a more  sort of hard-coded hacked way and say like this function treated that way you know that  sort of thing so yeah you can use it and it's very you know it seems to people like people  seem to like it but I would look in the long run for more you know so generalized post-cars  like extensible solutions in all of these areas but yeah that's that's us I guess a one step  in the right direction that timescale to be recently implemented and he did with with  buckets four times that easy I was gonna submit as a patch yes submit that patch I don't have  anything I don't know anyone else is working on that you are aware of the work by John  Naylor to implement the sort of enhanced version of date trunk that I mentioned there's no  other work in progress right now yeah do look at the timescale to be just you know I'm not  gonna look into source code because there's licensing differences so I can't really comment  more about that I'm just reading their documentation and they certainly you know asked a lot of  the right questions just maybe different opinions and how the answers should be implemented  let's talk about automatic partitioning automatic partitioning is sounds good but it's nobody's  really working on that I missed a bunch of your answer first bit of video was cut off  I'm sorry Russ your question was about the tree a bit of brin yeah brin is no it doesn't  do that automatically read the documentation sorry I'm going through the IRC I should probably  read the questions read the questions I like yeah okay so you re-asked the question about  brins yeah the question about brin it does yeah those updates do break there's nothing  automatic that fixes it you but there are functions specifically for brin to do certain  adjustments depending on what you're doing question about custom type creation documentation  says types are implemented using mostly C is it possible to create type using SQL language  the answer to that is I believe no not right now because you you do need to basically take  input string input and convert it to a byte pattern for storage so that's girl can't do  that you could maybe you know one example that is probably currently not possible but you  can imagine you're doing it in something like rust where you can sort of lay out bits and  write them so on that level but not in just plain as girl that's not possible right now  question from David Federer any wins to be had from s I am the I'm sorry I don't know  what that is if anyone we still have a couple minutes if anyone got their question cut off  please ask it again I'm just monitoring the IRC also a good question that I just answered  from Marius 46 about wanting to convert units like the gallons there's actually an extension  for that post grass girl hyphen units I believe it's called so just check that out if you're  interested in that yeah I don't know if you got my answer to your question but with bucket  please just submit that patch yep there you go thank you anyone know it as I am the single  instruction multiple data so is that sort of vector processing I am I'm sorry I still  don't know really what that is I've heard that before but I have not thought about that  probably even if I knew what that was I don't have an answer but if it's sort of there's  certainly further improvements in execution execute optimization possible that go beyond  what I have really thought about here that's all the questions now yeah vector processing  yeah yeah so I think it would I think it could definitely benefit from that that's you know  goes a little bit beyond what I had thought about and obviously that would also affect  you know a bunch of other it mostly analytics use cases in general so definitely also yeah  maybe that those are not the sort of the top issues but yeah what's that to the bottom  of the list yeah good point thank you now all right so the you know a lot of the items  I mentioned are in progress as we have also just discussed now some of those are already  in the next very next commit fast some of those are you know being worked on here and  there if anyone wants to know specifically like who is working on what maybe you know  just drop me a line and otherwise I'll you know hope to see you all soon again or I'll  see you at the next commit fast thank you Peter thanks thanks Dan thanks everyone goodbye  bye  you
Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_0_107.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_108_215.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_216_1271.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_1272_2867.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_2868_6527.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_6528_8267.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_8268_8579.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_8580_10175.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_10176_12287.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_12288_12419.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_12420_17435.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_17436_17795.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_17796_18827.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_18828_20195.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_20196_21035.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_21036_22439.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_22440_22487.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_22488_22559.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_22560_25667.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_25668_31811.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_31812_31859.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_31860_32255.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_32256_32663.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_32664_34055.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_34056_35123.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_35124_36791.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_36792_38015.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_38016_38411.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_38412_38783.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_38784_39167.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_39168_39551.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_39552_43007.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_43008_43907.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_43908_45779.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_45780_48467.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_48468_50051.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_50052_51887.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_51888_51971.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_51972_53507.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_53508_54911.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_54912_56447.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_56448_57863.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_57864_58115.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_58116_60287.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_60288_62783.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_62784_69875.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_69876_70019.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_70020_70139.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_70140_70355.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_70356_74783.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_74784_75599.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_75600_75647.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_75648_76487.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_76488_77243.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_77244_77579.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_77580_77783.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_77784_78227.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_78228_85343.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_85344_85391.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_85392_86291.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_86292_86351.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_86352_86879.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_86880_88079.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_88080_88319.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_88320_88463.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_88464_88511.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_88512_88571.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_88572_89015.png Time_Series_Databases__A_Deeper_Look_-_Peter_Eisen_SkWnKa1l6hg_89016_89494.png