Temporal Databases: Theory and Postgres
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.
Jungwirth, Paul
Temporal databases let you record history: either a history of the database (what the table used to say), a history of the thing itself (what it used to be), or both at once. The theory of temporal databases goes back to the 90s, but standardization has only just begun with some modest recommendations in SQL:2011, and database products (including Postgres) are still missing major functionality. This talk will cover how temporal tables are structured, how they are queried and updated, what SQL:2011 offers (and doesn't), what functionality Postgres has already, and what remains to be built. Many solutions exist for tracking changes to the database, e.g. for auditing or compliance. Often they use triggers to record changes in a separate schema, and better approaches also let you easily query for what the database said at a certain date. (For example see Magnus Hagander's 2015 talk "A Tardis for your ORM".) On the other hand, if you want to record a history of the thing itself, few solutions are available, and the problem is more complicated. The historical dimension can't be built automatically by triggers, but users need a way to edit old "versions" of the thing. They want to ask questions not only like "as of time t" but also preserving the historical changes across filtering, projecting, and joining. This kind of functionality is much harder to build without first-class support from your RDBMS. SQL:2011 includes support for both these dimensions, respectively "system time" and "application time", or even both together ("bitemporal"). We'll touch on system time but focus more on application time: how to declare a temporal table, how to set up temporal primary and foreign key constraints, how to insert and update it, and how to query it. My hope is to get the Postgres community aware of SQL:2011 temporal features so that we can start building support for them. We already have some great building blocks in place, especially ranges, exclusion constraints, and GiST indexes, so I'd like to explore how we can use those to support temporal functionality. I'd like to share my own work on a patch for temporal primary and foreign keys, and also briefly point out some work by others on writing temporal queries.
 Hi, everybody. Thanks for coming. My name is Paul Jungworth. I'll be talking about temporal databases. I'm an independent consultant programmer from Portland, Oregon. I've used Postgres since about 2010. I've done plenty of work with Oracle, MySQL, MS SQL Server, and I have to say that Postgres is the best. I'm very humbled to be here. There's a lot of related work that I'll mention today, much of it done by people who are at this conference, and I'm just amazed by all of the things that people have done here. I'm not really a contributor myself. I've written a bunch of extensions over the years. Since I'm from Portland, I get to say they're pretty obscure. You probably haven't heard of them. I did dip my toe in the contributor waters a few years ago, submitting a patch to add UUID support to the Btree GIST extension, which as we'll see is actually sort of related to temporal databases. I've been interested in temporal data for a few years now. I've read three out of four of the books. I'm planning on finishing the last one on the flight home today or tomorrow. Almost every project I work on, I wish that it had temporal database support. Now that SQL 2011 has standardized what some of these features look like, I'm hopeful that we can begin to add these features to the Postgres project. I want to give an overview of the temporal concepts and then talk about SQL 2011 and about Postgres. Finally, time permitting, I'll give a survey of how other RDMSs are supporting SQL 2011 out there. The first thing I want to say is that temporal databases are not the same thing as time series databases. Nowadays, time series is all the rage with web analytics, IoT, sensor data, finances, et cetera. Temporal is something else. In time series, typically every record has one time stamp and is used to store events. In temporal, every record is going to have two time stamps and is used to store the history of a thing that changes over time and each record is going to be one version of that thing. Instead of events, we're tracking things, you could say. Temporal really has two distinct independent purposes or applications. You could track the history of your database itself as it changes, for instance, for auditing and compliance reasons. You may also be interested in tracking the history of the thing out there. These are really usually not related to each other. You could even track both of them together if you wanted. With time series, you probably have a pretty simple schema, but you're big challenges with the scale of the data. With temporal, you will have a more conventional scale, most likely, but the challenges are with the complexity of the problem. Postgres has several built-in tools that give us an excellent foundation for temporal data, especially ranges and exclusion constraints. There are not a lot of features on top of that, offering higher level concepts, however. If you want to support a temporal database in Postgres right now, you need to look at outside extensions such as the temporal tables extension. Magnus Higander also has a talk from a couple years ago showing a very good pattern you can use. It's called a TARDISphere ORM. What I like about both of those solutions is that they make it very easy to query the data how it used to be. There's lots of other solutions out there that will stuff your data somewhere, but good luck actually reading it later. The nice thing about these two solutions is they make it easy to access it. Temporal has many applications, as I said. I think that one of the problems is that you are going along in an OLTP database, you are overriding your old data, and you are losing the history of how things used to be and how your database used to be. This is especially acute in an OLTP system. Almost every project I see has had this need, I believe. As an example, maybe you have an e-commerce site and you have a products table where you are storing the price, then you change the price and now your orders don't end up anymore. Of course, all of you are thinking, well, duh, you just copy the price over to the line items table, which is true, but the problem is this is an ad hoc solution. You are just copying over the price. You are probably not copying over the products tags or the products various shipping methods at the time or the products vendors cost your inventory. All of these things aren't being copied. This is one of the issues that you run into when you have these ad hoc solutions that aren't informed by the research that's out there. In practice, very few companies can do something like regenerate their 2016 sales report and get the same answer that they got back two years ago. Another example is maybe you have a real estate site like Zillow.com and you want to record the history of houses as they change. This is a good example to show the independence of these two dimensions I mentioned because you might find out today that there was a house renovation that happened six months ago and you want to be clear about when the house changed and when your database changed. You can't really give a talk about databases without an employee's example. For employees, you might want to track their title, their salary history, their different role on projects over time. There's lots of history that we care about there. For questionnaires, I've seen this many times where you are asking questions and then someone comes along and rewords the question and now all of the old answers are invalid. Not just the question text but if it's a multiple choice question, maybe they change the text of the possible answers. If you're asking ethnicity, for instance, and someone comes along and adds some new ethnicities or breaks one into two, you have to deal with this problem. The problem is that retaining correct history today requires foresight. You have to know that you're going to care about this data so that you can do something like copy the price over into your line items table. Oftentimes, you don't know that you're going to need this history until much later. Someone told me a story of a well-known fast food chain that wanted to brag about how their mayonnaise had become healthier over time. They went to go look up the information about the old mayonnaise and discovered that they had been overriding it over the years and they didn't know what was in the old mayonnaise. Even when people solve this problem, they aren't aware that there's a lot of research around it and there are some patterns that make it easier to capture this data and do complicated things with it in your database. I mentioned OLTP but it really does affect OLAP databases as well. Of course, you're capturing history in your facts table but in your dimensions table, those things change over time too and you would like to capture that information. Ralph Kimball talks about slowly changing dimensions and originally we had type 1, type 2, and type 3 slowly changing dimensions. Nowadays, we also have type 0, type 2.5, type 4, type 5, type 6, and type 7 which I don't want to get into too much detail here but I think that goes to show that the temporal remains a challenge even for these OLAP databases. If you start reading about temporal databases, you will surely encounter this book which also comes as a free PDF. It's really the seminal book for temporal databases. As you can probably guess from the word time-oriented in the title, it comes from the 90s. It pulled together at the time, I think several thousand papers from the research community and it's extremely comprehensive. It gives you lots of SQL including various dialects. It can be a little bit tough to get through because it tackles this combinatorial explosion of these different dimensions, even a bi-temporal table, different sorts of questions you might ask, snapshot query, sequence query, non-sequence query, interjoins versus outer join, select versus insert versus update versus delete, oracle versus DB2 versus access versus informix versus ms-sql server versus unisequel. It really, as I said, it's very comprehensive so it can take a while to get through but it's incredibly valuable. It's not just the author actually tried to get his ideas into the SQL standard back in the 90s. I was pretty annoyed at first to discover that we could have had this stuff 20 years ago. But really the criticisms that were made back then had some very good points and in my opinion still kind of apply in some ways to the SQL 2011 standard. So we'll get to that. Okay, so what does a temporal table even look like? What we're going to do, first I'm just going to talk about one dimension of temporal data, the valid time dimension. We're going to add a couple columns here, valid from and valid till for the start and end time of each record. If a conventional table makes a true propositional statement for each row on the table in sort of this timeless now, in a temporal table will make a true propositional statement qualified for this time range. Here I'm going to use months just for presentation sake but it could be whatever granularity of time that you want. More usually it would be seconds, milliseconds, days, whatever. Blank value and null value will mean indefinite or unbounded. So in this case we have two records, two versions of product one and the latter one is saying that this is what the product is as of January 2019. It's often nice to plot this data on a timeline to help you understand what's going on. A question that often comes up is why can't we make do with just one time stamp? You know, we're supposed to be normalizing our data or removing redundancies but really the time that one version ends is not necessarily the same as the time that one version starts. You might have a gap where a product is offered and then removed from the market for a year then offered again and also you just need a way to close the history, right? You need a way to delete a product. So we do actually need both columns. Also it would be a real hassle if you needed to query outside of the current row to find out when its valid time concludes. One shocking thing about this picture is that the primary key is no longer unique, right? Now we have ID one in our database twice and that should be raising alarm bells. We can make a nice refinement here in Postgres and replace these two columns with just one column using our range types. Postgres has these built in for date, time stamp, time stamp with time zone and a few other data types and you can even define your own range types for other data types if you like to. They are usually, in the case of these data types in particular, they are going to be closed open which means, which you can see in the notation here with a square bracket and a parenthesis which means that they include the first point and they do not include the end point. So in algebra class this would be a filled in circle and a line and an open circle. This is just a convention but it's basically used by all of the literature out there and it's really the nicest way to go because it lets your ranges snap together you could say. You don't have to have any special case logic dealing with when things start or end. Also you don't have to know what the granularity of your time stamp is. If you were using closed close it would have to be one second before new years to your ending date or one millisecond or one nanosecond and then if you upgraded your column to better granularity then all of your closed dates would be really wrong and you would have these gaps. So I'll follow that convention and it's basically the normal way of doing temporal. Range types have several advantages over two columns I think. First of all they come with lots of built in operators. As I said you can define a range on any type that you want. You could make your own float range. I've even defined an IP address range before in the past. They let you use exclusion constraints and are easy to index with just indexes. So it's really a natural fit there. Range has come quite a lot of nice operators. Here's a few of the basic ones like less than and greater than. Here are some of the most useful ones with temporal data. You can test for if a time or a smaller range is included in another range or if they overlap each other. Here are some more operators. These operators bear some resemblance to what are called the Allen relationships and you really can't have a discussion about temporal without talking about the Allen relationships. They are a list of the 13 different ways that two ranges could be related to each other. So A before B, A touches B, A overlaps sum of B, A overlaps all of B, A subsumes B, A is equal to B, etc. Apparently these weren't enumerated until the 1980s. It doesn't really seem quite sufficient to get your own Wikipedia page to list the Allen relationships. But joking aside it actually is extremely useful having a list so that you can have a systematic test of all of the different cases. Allen, A-L-L-E-N. Really it's a lot easier to be able to use these operators rather than writing the logic yourself. For instance here is the SQL for overlaps. I actually would have never guessed that you could do that in just two lines of SQL. It's pretty subtle which values we're comparing to each other and it's not obvious to me that that even means overlaps or that it is actually correct. So I've mentioned that there are two dimensions that are typically tracked in a temporal database. I've mostly talked about valid time so far which is the history of the thing out there. But you could also track the history of the changes to your database. Some authors even propose tracking more than two dimensions but I won't get into that today. You could even have both dimensions on the same table and we would call it a bi-temporal table. In that case you would have two start in pairs or two ranges and then it's getting really difficult to understand. These two dimensions don't necessarily have the same physical representation. For instance you might have be tracking all the valid time in one table but then have a separate table for storing the history of your updates to the database. Snodgris gives several different possibilities for setting up your tables and this idea of storing your transaction time history in a separate table is what is used by that temporal tables extension as well as the TARDIS for the ORM pattern as well as Microsoft SQL servers implementation of temporal databases. I'm going to talk exclusively almost about valid time today. In my opinion it's really the more interesting of the two. I want to be able to track the history of the thing out there. Tracking the history of your database is sort of a check box for compliance reasons for a lot of people but it's rare to build application functionality on top of that. On the other hand it can be desirable in many cases to want to build application functionality around the history of the entities that you care about. Also as I said there's lots of solutions for transaction time already but almost nothing out there for valid time. One issue with valid time is that because it's in the hands of its users it needs application level functionality built on it so you actually need to use some of these operators and other things and also constraints become much more important because it's not just automatically generated the same way every time but you are doing development against it. So you would really like to have temporal primary keys and temporal foreign keys. By the way nobody agrees on this terminology. These are some of the authors. Johnston who has two books about temporal databases called valid time effective time in his first book and state time in his second book. So don't feel bad if you're not sure what you're word to use. I would not stress about it too much. Just pick something. There's actually a document called the consensus glossary of temporal database concepts from 1998. The problem has only gotten worse since then. Okay so let's start with the simplest possible thing. Primary keys. Primary keys can be non-unique with temporal databases as long as their valid time doesn't overlap. If the valid time overlaps for the same record then we're making two contradictory statements and we're not allowed to do that. So in this example our green product is okay because we don't have overlap. Our red product is invalid because it's making two statements that are at the same time. Now a great way to think about this in my opinion comes from another book by Dave Darwin and Lorenzo's and what they, they propose a conceptual model where you really want one row for every second or microsecond or whatever it is that your granularity is. So you have product one at midnight and product one at midnight plus one second and product one at midnight plus two seconds. And if you set things up that way then you don't need any kind of wacky non-unique primary key. You don't need any weird foreign keys because you can actually express a normal uniqueness constraint over a structure like that. Obviously this is totally impractical and we use ranges instead as an abbreviation for this idea but I always keep this in the back of my head because it helps to answer what the behavior ought to be in more complicated cases. If you, if you recognize these names you know that they are very pure, pure strictly mathematical. They want to have no nulls, no duplicate rows and if you read their books I hope you weren't expecting to find any SQL. But I actually found this book very practical and if you read one book about temporal databases I would read this one. My understanding is that it's also very influential on Postgres range types and I, I really like the, the rigor that is provided by this conceptual model that they offer. So we want some kind of constraint for our primary keys and we can solve that with exclusion constraints. Exclusion constraints have been called generalized uniqueness constraints. There must be no other rows where all the operators evaluate to true when compared against the current row. With this audience I assume that you know how exclusion constraints work. Here's an exclusion constraint that just expresses uniqueness. Here's that first exclusion constraint that is expressing a primary key's almost uniqueness. Like a uniqueness constraint an exclusion constraint is always backed by an index to enforce it efficiently. If you are mixing a scalar type and a range type like we are here then you're going to need to use a, a gist index and you'll need to install the B3 gist extension. Exclusion constraints are really a Postgres only innovation as far as I know. You won't, you won't find them in other databases. This is what we could call a temporal primary key and you could do this today but the problem is that Postgres doesn't know that it's a primary key. So it won't let you make foreign keys against it. For instance, it doesn't, it's not aware that this is acting as a primary key for you. So that's what we would, we would like to add. By the way this is how Snodgris says you should set up a temporal primary key. This is from the, from the 90's before, before exclusion constraints. Actually he didn't have a range operator either so that part was more complicated and Postgres won't let you write a check constraint that looks at outside rows so you'd have to do this as a trigger instead but this is how you could do it. So moving on from primary keys we also have foreign keys. This table is just traditional referential integrity. You know if you have an e-commerce application your products table always needs a variance table so you can sell small to even large t-shirts. This is in this case variant four is forbidden because it's looking for product number three which doesn't exist. Here's the same thing except temporal version of it. Now things are getting a little weird because our IDs are no longer unique and this is one place where it's especially helpful to remember this conceptual model I mentioned of the primary key plus the moment in time. In this case our, our temporal foreign keys need to be, our variance can only exist so long as the product exists. So the green variants are okay, the red variant, product two exists but it doesn't exist for that entire range that the variant exists so that's not allowed. So I have a patch in the current commit fest that teaches postgres how to implement temporal primary keys and foreign keys. It supports restrict and no action. It doesn't support cascading yet because we need to add the temporal DML commands for update and delete but we'll get to that. So Nodgris back in the 90s also showed how to implement temporal foreign keys. Here it is page one, page two, page three, page four. Okay, so it's dangerous to go alone. Take this. You really want your database to be doing this for you. You know, you don't want to have to implement it yourself every time. I think this is why today it's not economical to be capturing this history but if you had support from your database for temporal foreign keys and primaries then perhaps it would be more routine to capture this stuff. You know, you wouldn't need to have the foresight and the ad hoc solutions. Okay, I didn't want to get into this index right away but there are different kinds of questions you could ask of a temporal table. You could ask what was it like at time t and in that case you're going to get back a traditional relation not a temporal relation. You might also ask, you know, give me the history for a range of time and in that case you're going to get back again a temporal relation like your input is. And Nodgris calls these snapshot queries and sequence queries. Non-sequence queries are not very interesting. It's just treating time as a non-temporal dimension. So I like to say that if you can't do joins, really all you have is a spreadsheet and joins are especially the place where these ad hoc approaches start to fall down but the research gives us a principled way to handle putting these things together. Here's an example that I've stolen from some researchers in Switzerland and Italy. Anton Dignos, Michael Bolin and Johann Gamper. I apologize for my atrocious pronunciation. We have a table. This is for a hospitality system. We have one table that stores the prices that our rooms are offered at and we have another table that stores when our guests have made reservations. Of course, we're going to change the prices because we want to jack them up on Valentine's Day. So there's some variation there. Now we want to know what each of our guests' stays costs in total. So we need to join these tables. Here's the result. The first customer's stay is split across two cost periods and same for the second customer. Note that the time stamps are not exactly from the first table and not exactly from the second table. So we have to cut up these input records so that they line up. This is a lot easier to see with a timeline. Here's our costs and our reservations and here's the answer once we join these tables together. Implementing this is really tough and even Nodgress' original ideas kind of started to fall apart somewhere around here. He did show how he could handle joins but there were other issues with the non-relational nature of his results and also the way of asking for them. He proposed putting temporal modifiers onto your entire statement like up with the select clause. So they were statement modifiers. Dave Darwin and Lorenzo criticized this and pointed out that this and some other of his work made it difficult to compose queries. So it made it difficult to take a temporal table and have its result in a CTE or a view or a subquery or a set returning function because the starting end times were not actually columns on this table. They were these extra values so they kind of existed outside of relational theory and it wasn't clear what to do with them or even how to propagate them if you were including them in a larger context. These researchers I mentioned actually have a great solution to this I believe where they have proposed a temporal variant of all of the operators in the relational calculus or algebra and they show how to easily implement all of them by adding just two new functions which in that original paper they called normalize and align. They actually implemented this against Postgres 9.1 and then later against Postgres 9.5 and they have a patch in the current commit vest and I would be really excited to get this added. In my opinion it would make Postgres the best temporal database on the market really. It's a way more important to patch them on and so I'm hoping that somebody will take a look at it. They've had several rounds of feedback already and they've made a bunch of edits and I think they're waiting for someone to look at their latest work. There is really nothing in SQL 2011 about temporal joins. You can manage to pull off an inner join by joining them and using an overlaps operator but there's no way at all to do an outer join or an aggregate or union and so having these extra operators would really let you do a lot of new things. Okay so that covers querying data. Let's talk about manipulating it. In a temporal database you want when you insert data you are asserting something for a range of time. So in this case say that we wanted to say that we were selling our shoes product for all of 2017 for $3 each. When we do our insert we will just say which range of time we care about and we'll get this new record here. Now this is pretty simple. It doesn't contradict any existing rows. Suppose we are inserting it at $5. Now we have this interesting situation where we have shoes for $5 for 2017 and shoes for $5 for 2018 but we have two rows in our database and that's kind of messy. Snodgris talks about how we should really be merging these rows together. He calls this coalescing them giving us something like this. So now our insert is magically transformed into an update you could say. This is nice because it simplifies logic elsewhere. It keeps our database from getting progressively more and more sliced up as we are editing data. It can do kind of weird things. For instance, in this case our insert is becoming into an update. A delete could even turn into an insert. Actually you don't need coalescing for that. But SQL 2011 leaves out the idea of coalescing. So possibly Postgres should also skip it. At the very least I think it might be worth having a standard trigger that people can attach to their tables that will handle the coalescing after the fact. That's kind of probably the best approach. One issue with Postgres is that not all column types have an equality operator. You would only do this if all of the columns compare equal with their neighbors, right? But JSON for instance doesn't have an equality operator. I don't know how big of a problem that is. Others more knowledgeable could probably say. In the case of JSON we could cast it to a JSON B I think. But that's not a general solution. A temporal update we also want to state which period of time we are applying the update. So we wind up with shortening these other records and putting in this new record here that asserts our changed price. This shows the coalesced result but you can imagine the non-coalesced result would just be two records. This could even cause a delete if our update spans multiple records, right? If we're, again, if we're doing coalescing. Okay, a temporal delete we also would just specify which time frame we want to apply it at. And we get a result something like this. If you are deleting in the middle of a big row you could even wind up with an insert, right? Because you have to keep the piece at the beginning and you have to keep the piece at the end. One question I have which as far as I can tell is not answered anywhere in the standard is when you do an insert update and delete which triggers fire and what are the values of the new and the old records that are passed into them? I think that probably needs to be answered. In my opinion the best answer is probably to handle those triggers before any coalescing is done. Since the SQL standard ignores coalescing that's probably the best way to comply there. But as far as I know there's no, it seems to be a surprising omission in the standard. I just haven't found it yet. Yeah, well I think using closed open interval. Yeah, well I think using closed open intervals will take care of that problem for you. That's one of the advantages of that approach. I suppose with coalescing, this isn't your question I don't think, but with coalescing you wind up having to compare float values for equality and maybe that's a risky thing to do too. Right, but that's, you have to decide that, you know, that's a problem whether you're doing temporal databases or not temporal databases. I will say that with foreign keys right now Postgres tries hard to cast the types to be the same kinds of types if they aren't the same types already. And I am kind of suspicious of how that will work if we want to also cast the ranges to be comparable. You know casting a date range to a time stamp range is probably something we don't want to do. You know because of time zones and daylight savings times you know where, how are you, that continuum of time, how are you going to slice it up into days, you know. So it is a problem sometimes. Okay another thing that you might really want is a merge or an upsert. I actually built a real application that lets you edit history and this is what I basically wind up using all the time. You know when you present a form to the user and they type in all the values and they hit save, what you really want is to let them say you know the range of time that they're saving that for and you want to implement that as an upsert. If this were an insert it would violate our primary key and would fail because it overlaps. If it were an update we wouldn't preserve the beginning slice of time because we would only be updating the existing seconds there in the database. There's nothing in SQL 2011 about this. Johnston calls this an insert whenever. I think that we can't do this right now with on conflict do update but I think that we could teach on conflict do update to do this pretty straightforwardly given that we have the concept of a temporal primary key. Okay so let's talk about SQL 2011. Now I'm going to give you some of the syntax that you actually use to do all this stuff. Here is our do it yourself temporal primary key and what we're missing is a higher concept of primary keyness. This is how you would do this in SQL 2011. In particular these last three lines here where we're expressing a primary key and the range portion of the key has this without overlaps keyword. Things are a little bit weird here because SQL 2011 doesn't have any notion of range types and so instead they introduce this idea of periods. A period is this quasi column that you declare on your table. You have to have two regular timestamp columns and you declare this period as being based on these two timestamp columns. It's a little bit like a generated on select column if you know what that means but it's not a column you know. If you do select star from T you don't get the period for instance at least as far as I know. Date Darwin and Lerner's service hate this by the way. It has similar problems with composition I think not as severe as the 90s standard because you could just say comma and add the period if you wanted to. Periods do come with a few operators but not very much really. Postgres ranges have a much richer set of operators. Periods are limited to just dates and times. You can't define your own types on them. They don't permit a null to mean unbounded like range types do so you have to use a sentinel value like 3000 AD which is probably fine if you're selling shoes but maybe not be great for astronomy or other purposes. Someone pointed out to me that Postgres does have a minus infinity timestamp values which is pretty good. It would avoid the sentinel problem although client languages don't seem to know about those. So you have problems on the client side if you're using those. In any case I suppose that we would implement periods using ranges. When it comes to making the exclusion constraint for the primary key we would just be creating a range expression for instance for that index or that exclusion constraint. With periods implementation seems like it's going to be a chore to me because it's this quasi column so now we have to add special case logic for when they appear in a select or aware or a group by or having or a function argument or various DDL commands. So we have to support them all over the place if we want to support them. Supposedly the standard authors added period types so that they wouldn't be forcing everybody to add range types but it seems like a lot more work to support this quasi column kind of thing. So in my own patch I've taken this approach of accepting a range column in place of where the period is. I would like ultimately to permit both. Since they share the same namespace you don't really have any conflict accepting a range type here or a period. Vic Fearing we don't think is here but already has a patch from a while back letting you declare periods on your tables. They don't really do anything yet but you can declare them in his patch so that's a nice start. They would let us support both ways of adding primary keys. As a user I would personally just use range types and not use periods really but it might be valuable to support them in a way to let people convert from other databases that kind of thing. Here is DML according to SQL 2011. As you can see there's no changes at all to the insert statement you just say what you want to insert for those columns. If that were a period you wouldn't be naming the period you would be naming the two source columns of course. Update and delete get this new clause for a portion of and you would say the period and then you would give the bounds for how you want to apply that. I've been kind of ignoring system time but here's the syntax for a system time period to turn on this like auditing database tracking for your table. Basically you add a period to your table and you give it the magic name system time and that is a signal as well as the with system versioning that your table should be recording this transaction time history. In this case the time stamps have to be generated which we support in Postgres 12 so that's pretty cool and this will tell the database to record when DML happens. There's also some extra syntax for querying these periods as of a point in time or for a range in time. How about in time? Okay so now I wanted to do a quick survey of how other systems have added SQL 2011 support. I will preface this by saying that I haven't actually tested all of these and this is really just from combing through the documentation so there might be mistakes in here. I am hoping to do a more thorough test and exploration and maybe write a blog post about this but this is what I've found so far is my SQL doesn't have support for any of this stuff but MariaDB does support the system time dimension. This is an abbreviated syntax but that they support but they also support the regular SQL 2011 syntax. They don't yet support any valid time so this is kind of this ongoing theme in this talk. There's lots of ways to get system time but there is not much support for getting valid time and MariaDB is no exception. Here's how Oracle does system time tables. You can see it has nothing to do with the syntax. This is a very old feature in Oracle relatively speaking so it really pre-ates the standard. Oracle does support valid time. Note that the period keyword has gained to this four keyword next to it which I suppose they had some reasons for doing. They also let you have nullable timestamp columns which is interesting. I'm not sure if they actually support nulls there. The standard says that those should be non-null columns if they support nulls there or maybe they just make them not null just like you would if you declared a primary key. That's one of the things that I would like to test and find out. MS SQL Server since 2016 has had support for this stuff. Here we're creating a system time period. In Microsoft you also specify a history table so their implementation is quite a lot like how the temporal tables extension works or the TARDIS pattern works. I imagine this would be a pretty good way of implementing this in Postgres also if we wanted to support system time. MS SQL Server also has no support for valid time periods. IBM DB2 has the most complete support I think. Let's see. It's interesting that you can leave off the clause at the bottom about with system versioning. Also they use begin instead of start. I don't really know what the TXID column is about. It's in the documentation but it's not clear to me if that's required or just optional. I'd like to find out more about that. They do support valid time which is pretty cool. You have to name your period business time. You can't name it whatever you want. That's okay. There's always business time. Here's one proposal of how Postgres might support a syntax for adding system time periods. You could imagine using ranges but you could imagine different alternatives. All right. Well thank you for your attention. Here are links to a bunch of things that I mentioned today. I don't expect you to read that but you can find that list and all the other slides and mark down in PDF format on GitHub here. Are there any questions? Yes? I might. At least I would think about it for each table whether I'm going to capture that or not. Maybe. I don't know. One of Snodgrass's goals and his original approach was to let people seamlessly convert a non-temporal database into a temporal database just declaratively and not have to rewrite any of the queries. I've always found that to be maybe a little bit overambitious and maybe it distorted some of the ways that you would use it. It's pretty close actually. You could almost get there even in SQL 2011. Yes? I couldn't find any temporal related additions in 2016. I looked. I only have access to the draft standards you know. So there's that caveat too. But I didn't see anything new that was related to temporal stuff in 2016. Yes? Okay anything else?、 not only when that transaction commits, but depending on your repeatable read and isolation, you also have to know when another transaction actually started. And I don't see any way that you can actually do that with time savings. Am I missing something there? Yeah, well I think that's a different level of abstraction, so to speak. You know, I think once your statement has committed, then the people trying to place orders are going to see that updated record, and they'll be using their own current time stamp or whatever when they do the query. So I think that it will be... If they started, that's where you run into this problem of if there's transaction, it started first, like there. If their transaction has already started, then they wouldn't see the update, but that's no different from a conventional table, right? Right, right. But that's just why it doesn't appear to me that it's safe to just use time stamps. For that scenario, and certainly there's a lot of scenarios where it's like, no, I'm going to tell you what the thing is about it, and that's all there to it. It sounds like you have serialized bytes, I suppose you have a different bail along with that. Yeah, those serialized bytes are going to work. Okay, I'm going to come back to what you said at the beginning, but I thought it would be able to use a transaction time stamp or a lot of things with a client, and then all we're going to do is ensure that it's really right, and that we're already using time stamps, and we can't put too much of the bail on it, so it might not be as safe. I do think that if you have the notion of some kind of a history circuit, so when you're creating these new version records, you can then do a warranty, oh, well, I'm placing it in order, here's exactly which version I used, I showed the customer when they placed it, even at the times, don't make sense of that point. Right, I guess I'm not seeing the problem, maybe I'm just missing it, you know, but I think that I'm not sure if it requires going all the way to serializable to use temporal databases, I don't think so, but I've heard that suggestion here already once before, so it needs some thinking about that. It seems to me that the problem you're describing doesn't come from adding temporal, it's the same problem that you would have in any case, you know? No, it's not new, it's just the thing that concerns me greatly about temporal is it leads people to believe that they can define the state that was in play for a particular transaction, and you would have to also report the snapshot that was used for the other transaction. What you could actually do is to support that, to the other than epic, all of these awful snapshots. Well, presumably not changing the price, we'll find that you're probably going to know you're going to take the price, so maybe before you put it in that right, so you don't want to be shocked, that's what we're going to do. Yeah, I mean, there's a moment, yeah, I don't think you'd be changing it too frequently. The frequency of updates is one reason that people store the audit history in a separate table, I think. There is a moment at which you change the time, and so that kind of is the issue. I meant to say, by the way, that I'm very grateful for any opinions that people have about the syntax that should work, because that would help me a lot building this stuff, you know, just so that I'm building it, how people expect an inner reasonable way that is suitable for the Postgres community. There were a couple of questions here. Can you give an example where for Valentine, what the standard offers would be easier than what Postgres currently offers? Well, then what we currently offer certainly lets you create temporary, temporal primary keys and temporal foreign keys, do these temporal updates and deletes. That's sort of what you would gain. Right now, we have ranges and exclusion constraints, but we have to, the database doesn't know that those are serving as primary keys or foreign keys, so SQL 2011 would let us gain that intelligence in the database. Does that answer your question? Yeah, sort of maybe something a little bit more concrete, like what does it do with that knowledge, how they build the more declarative, you know, and kind of tell it something, but then maybe just a little bit more specific to creating a foreign key or anything like that. Right now, it would prove that you're declaring a foreign key to that thing, right? Right. You couldn't, a foreign key can only target something that has a uniqueness constraint, you know. So, actually, you have another extension that lets you create temporal foreign keys with stored procedures. It was sort of a proof of concept. It's not very good. No one should use it. But, you know, your scalar column has to be an integer named ID and your range has to be a timestamp range named Validat. So, you want some way to concisely express these constraints, you know, and have the database understand what they're supposed to mean. Right now, you couldn't add a temporal foreign key at all because it will look for a uniqueness constraint. There actually is no uniqueness constraint on the temporal table, you know. Okay, well, thank you, everybody. Oh, sorry. One more question. Early on, you said something about indexing UIDs, how do you temporal element? Oh, yeah. Well, you can index a UID with a B tree, you know, but before this patch, you couldn't index a UID and a range together with B tree gist, you know. Just like if you want to create this uniqueness constraint. This is fine if ID is an integer because B tree gist knows how to handle integers, but if ID were a UUID, B tree gist would say, hey, I don't know how to deal with UUIDs. And so you weren't allowed to do this before. Okay, thank you, everybody. Thank you.
 Hi, everybody. Thanks for coming. My name is Paul Jungworth. I'll be talking about temporal  databases. I'm an independent consultant programmer from Portland, Oregon. I've used Postgres  since about 2010. I've done plenty of work with Oracle, MySQL, MS SQL Server, and I  have to say that Postgres is the best. I'm very humbled to be here. There's a lot of  related work that I'll mention today, much of it done by people who are at this conference,  and I'm just amazed by all of the things that people have done here. I'm not really a contributor  myself. I've written a bunch of extensions over the years. Since I'm from Portland, I  get to say they're pretty obscure. You probably haven't heard of them. I did dip my toe in  the contributor waters a few years ago, submitting a patch to add UUID support to the Btree GIST  extension, which as we'll see is actually sort of related to temporal databases. I've been  interested in temporal data for a few years now. I've read three out of four of the books.  I'm planning on finishing the last one on the flight home today or tomorrow. Almost every  project I work on, I wish that it had temporal database support. Now that SQL 2011 has standardized  what some of these features look like, I'm hopeful that we can begin to add these features  to the Postgres project. I want to give an overview of the temporal concepts and then  talk about SQL 2011 and about Postgres. Finally, time permitting, I'll give a survey of how  other RDMSs are supporting SQL 2011 out there. The first thing I want to say is that temporal  databases are not the same thing as time series databases. Nowadays, time series is all the  rage with web analytics, IoT, sensor data, finances, et cetera. Temporal is something  else. In time series, typically every record has one time stamp and is used to store events.  In temporal, every record is going to have two time stamps and is used to store the history  of a thing that changes over time and each record is going to be one version of that  thing. Instead of events, we're tracking things, you could say. Temporal really has two distinct  independent purposes or applications. You could track the history of your database itself  as it changes, for instance, for auditing and compliance reasons. You may also be interested  in tracking the history of the thing out there. These are really usually not related  to each other. You could even track both of them together if you wanted. With time series,  you probably have a pretty simple schema, but you're big challenges with the scale of the  data. With temporal, you will have a more conventional scale, most likely, but the challenges  are with the complexity of the problem. Postgres has several built-in tools that give us an  excellent foundation for temporal data, especially ranges and exclusion constraints. There are  not a lot of features on top of that, offering higher level concepts, however. If you want  to support a temporal database in Postgres right now, you need to look at outside extensions  such as the temporal tables extension. Magnus Higander also has a talk from a couple years  ago showing a very good pattern you can use. It's called a TARDISphere ORM. What I like  about both of those solutions is that they make it very easy to query the data how it  used to be. There's lots of other solutions out there that will stuff your data somewhere,  but good luck actually reading it later. The nice thing about these two solutions is they  make it easy to access it. Temporal has many applications, as I said. I think that one  of the problems is that you are going along in an OLTP database, you are overriding your  old data, and you are losing the history of how things used to be and how your database  used to be. This is especially acute in an OLTP system. Almost every project I see has  had this need, I believe. As an example, maybe you have an e-commerce site and you have a  products table where you are storing the price, then you change the price and now your orders  don't end up anymore. Of course, all of you are thinking, well, duh, you just copy the  price over to the line items table, which is true, but the problem is this is an ad hoc  solution. You are just copying over the price. You are probably not copying over the products  tags or the products various shipping methods at the time or the products vendors cost your  inventory. All of these things aren't being copied. This is one of the issues that you  run into when you have these ad hoc solutions that aren't informed by the research that's  out there. In practice, very few companies can do something like regenerate their 2016  sales report and get the same answer that they got back two years ago. Another example  is maybe you have a real estate site like Zillow.com and you want to record the history  of houses as they change. This is a good example to show the independence of these two dimensions  I mentioned because you might find out today that there was a house renovation that happened  six months ago and you want to be clear about when the house changed and when your database  changed. You can't really give a talk about databases without an employee's example. For  employees, you might want to track their title, their salary history, their different role  on projects over time. There's lots of history that we care about there. For questionnaires,  I've seen this many times where you are asking questions and then someone comes along and  rewords the question and now all of the old answers are invalid. Not just the question  text but if it's a multiple choice question, maybe they change the text of the possible  answers. If you're asking ethnicity, for instance, and someone comes along and adds some new  ethnicities or breaks one into two, you have to deal with this problem. The problem is  that retaining correct history today requires foresight. You have to know that you're going  to care about this data so that you can do something like copy the price over into your  line items table. Oftentimes, you don't know that you're going to need this history until  much later. Someone told me a story of a well-known fast food chain that wanted to brag about  how their mayonnaise had become healthier over time. They went to go look up the information  about the old mayonnaise and discovered that they had been overriding it over the years  and they didn't know what was in the old mayonnaise. Even when people solve this problem, they  aren't aware that there's a lot of research around it and there are some patterns that  make it easier to capture this data and do complicated things with it in your database.  I mentioned OLTP but it really does affect OLAP databases as well. Of course, you're  capturing history in your facts table but in your dimensions table, those things change  over time too and you would like to capture that information. Ralph Kimball talks about  slowly changing dimensions and originally we had type 1, type 2, and type 3 slowly changing  dimensions. Nowadays, we also have type 0, type 2.5, type 4, type 5, type 6, and type 7  which I don't want to get into too much detail here but I think that goes to show that the  temporal remains a challenge even for these OLAP databases. If you start reading about  temporal databases, you will surely encounter this book which also comes as a free PDF.  It's really the seminal book for temporal databases. As you can probably guess from the  word time-oriented in the title, it comes from the 90s. It pulled together at the time,  I think several thousand papers from the research community and it's extremely comprehensive.  It gives you lots of SQL including various dialects. It can be a little bit tough to  get through because it tackles this combinatorial explosion of these different dimensions, even  a bi-temporal table, different sorts of questions you might ask, snapshot query, sequence query,  non-sequence query, interjoins versus outer join, select versus insert versus update versus  delete, oracle versus DB2 versus access versus informix versus ms-sql server versus unisequel.  It really, as I said, it's very comprehensive so it can take a while to get through but  it's incredibly valuable. It's not just the author actually tried to get his ideas into  the SQL standard back in the 90s. I was pretty annoyed at first to discover that we could  have had this stuff 20 years ago. But really the criticisms that were made back then had  some very good points and in my opinion still kind of apply in some ways to the SQL 2011  standard. So we'll get to that. Okay, so what does a temporal table even look like? What  we're going to do, first I'm just going to talk about one dimension of temporal data,  the valid time dimension. We're going to add a couple columns here, valid from and valid  till for the start and end time of each record. If a conventional table makes a true propositional  statement for each row on the table in sort of this timeless now, in a temporal table  will make a true propositional statement qualified for this time range. Here I'm going to use  months just for presentation sake but it could be whatever granularity of time that you want.  More usually it would be seconds, milliseconds, days, whatever. Blank value and null value  will mean indefinite or unbounded. So in this case we have two records, two versions of  product one and the latter one is saying that this is what the product is as of January  2019. It's often nice to plot this data on a timeline to help you understand what's going  on. A question that often comes up is why can't we make do with just one time stamp?  You know, we're supposed to be normalizing our data or removing redundancies but really  the time that one version ends is not necessarily the same as the time that one version starts.  You might have a gap where a product is offered and then removed from the market for a year  then offered again and also you just need a way to close the history, right? You need  a way to delete a product. So we do actually need both columns. Also it would be a real  hassle if you needed to query outside of the current row to find out when its valid time  concludes. One shocking thing about this picture is that the primary key is no longer unique,  right? Now we have ID one in our database twice and that should be raising alarm bells.  We can make a nice refinement here in Postgres and replace these two columns with just one  column using our range types. Postgres has these built in for date, time stamp, time stamp  with time zone and a few other data types and you can even define your own range types  for other data types if you like to. They are usually, in the case of these data types  in particular, they are going to be closed open which means, which you can see in the  notation here with a square bracket and a parenthesis which means that they include the first point  and they do not include the end point. So in algebra class this would be a filled in  circle and a line and an open circle. This is just a convention but it's basically used  by all of the literature out there and it's really the nicest way to go because it lets  your ranges snap together you could say. You don't have to have any special case logic  dealing with when things start or end. Also you don't have to know what the granularity  of your time stamp is. If you were using closed close it would have to be one second before  new years to your ending date or one millisecond or one nanosecond and then if you upgraded  your column to better granularity then all of your closed dates would be really wrong  and you would have these gaps. So I'll follow that convention and it's basically the normal  way of doing temporal. Range types have several advantages over two columns I think. First  of all they come with lots of built in operators. As I said you can define a range on any type  that you want. You could make your own float range. I've even defined an IP address range  before in the past. They let you use exclusion constraints and are easy to index with just  indexes. So it's really a natural fit there. Range has come quite a lot of nice operators.  Here's a few of the basic ones like less than and greater than. Here are some of the most  useful ones with temporal data. You can test for if a time or a smaller range is included  in another range or if they overlap each other. Here are some more operators. These operators  bear some resemblance to what are called the Allen relationships and you really can't  have a discussion about temporal without talking about the Allen relationships. They are a  list of the 13 different ways that two ranges could be related to each other. So A before  B, A touches B, A overlaps sum of B, A overlaps all of B, A subsumes B, A is equal to B, etc.  Apparently these weren't enumerated until the 1980s. It doesn't really seem quite sufficient  to get your own Wikipedia page to list the Allen relationships. But joking aside it actually  is extremely useful having a list so that you can have a systematic test of all of the  different cases. Allen, A-L-L-E-N. Really it's a lot easier to be able to use these operators  rather than writing the logic yourself. For instance here is the SQL for overlaps. I actually  would have never guessed that you could do that in just two lines of SQL. It's pretty  subtle which values we're comparing to each other and it's not obvious to me that that  even means overlaps or that it is actually correct. So I've mentioned that there are  two dimensions that are typically tracked in a temporal database. I've mostly talked  about valid time so far which is the history of the thing out there. But you could also  track the history of the changes to your database. Some authors even propose tracking  more than two dimensions but I won't get into that today. You could even have both dimensions  on the same table and we would call it a bi-temporal table. In that case you would have two start  in pairs or two ranges and then it's getting really difficult to understand. These two  dimensions don't necessarily have the same physical representation. For instance you  might have be tracking all the valid time in one table but then have a separate table  for storing the history of your updates to the database. Snodgris gives several different  possibilities for setting up your tables and this idea of storing your transaction time  history in a separate table is what is used by that temporal tables extension as well  as the TARDIS for the ORM pattern as well as Microsoft SQL servers implementation of  temporal databases. I'm going to talk exclusively almost about valid time today. In my opinion  it's really the more interesting of the two. I want to be able to track the history of  the thing out there. Tracking the history of your database is sort of a check box for  compliance reasons for a lot of people but it's rare to build application functionality  on top of that. On the other hand it can be desirable in many cases to want to build  application functionality around the history of the entities that you care about. Also  as I said there's lots of solutions for transaction time already but almost nothing out there  for valid time. One issue with valid time is that because it's in the hands of its users  it needs application level functionality built on it so you actually need to use some of  these operators and other things and also constraints become much more important because  it's not just automatically generated the same way every time but you are doing development  against it. So you would really like to have temporal primary keys and temporal foreign  keys. By the way nobody agrees on this terminology. These are some of the authors. Johnston who  has two books about temporal databases called valid time effective time in his first book  and state time in his second book. So don't feel bad if you're not sure what you're word  to use. I would not stress about it too much. Just pick something. There's actually a document  called the consensus glossary of temporal database concepts from 1998. The problem has  only gotten worse since then. Okay so let's start with the simplest possible thing. Primary  keys. Primary keys can be non-unique with temporal databases as long as their valid  time doesn't overlap. If the valid time overlaps for the same record then we're making two  contradictory statements and we're not allowed to do that. So in this example our green product  is okay because we don't have overlap. Our red product is invalid because it's making  two statements that are at the same time. Now a great way to think about this in my opinion  comes from another book by Dave Darwin and Lorenzo's and what they, they propose a conceptual  model where you really want one row for every second or microsecond or whatever it is that  your granularity is. So you have product one at midnight and product one at midnight plus  one second and product one at midnight plus two seconds. And if you set things up that  way then you don't need any kind of wacky non-unique primary key. You don't need any  weird foreign keys because you can actually express a normal uniqueness constraint over  a structure like that. Obviously this is totally impractical and we use ranges instead as an  abbreviation for this idea but I always keep this in the back of my head because it helps  to answer what the behavior ought to be in more complicated cases. If you, if you recognize  these names you know that they are very pure, pure strictly mathematical. They want to have  no nulls, no duplicate rows and if you read their books I hope you weren't expecting to  find any SQL. But I actually found this book very practical and if you read one book about  temporal databases I would read this one. My understanding is that it's also very influential  on Postgres range types and I, I really like the, the rigor that is provided by this conceptual  model that they offer. So we want some kind of constraint for our primary keys and we  can solve that with exclusion constraints. Exclusion constraints have been called generalized  uniqueness constraints. There must be no other rows where all the operators evaluate to true  when compared against the current row. With this audience I assume that you know how exclusion  constraints work. Here's an exclusion constraint that just expresses uniqueness. Here's that  first exclusion constraint that is expressing a primary key's almost uniqueness. Like a  uniqueness constraint an exclusion constraint is always backed by an index to enforce it  efficiently. If you are mixing a scalar type and a range type like we are here then you're  going to need to use a, a gist index and you'll need to install the B3 gist extension. Exclusion  constraints are really a Postgres only innovation as far as I know. You won't, you won't find  them in other databases. This is what we could call a temporal primary key and you could  do this today but the problem is that Postgres doesn't know that it's a primary key. So it  won't let you make foreign keys against it. For instance, it doesn't, it's not aware that  this is acting as a primary key for you. So that's what we would, we would like to add.  By the way this is how Snodgris says you should set up a temporal primary key. This is from  the, from the 90's before, before exclusion constraints. Actually he didn't have a range  operator either so that part was more complicated and Postgres won't let you write a check constraint  that looks at outside rows so you'd have to do this as a trigger instead but this is how  you could do it. So moving on from primary keys we also have foreign keys. This table  is just traditional referential integrity. You know if you have an e-commerce application  your products table always needs a variance table so you can sell small to even large  t-shirts. This is in this case variant four is forbidden because it's looking for product  number three which doesn't exist. Here's the same thing except temporal version of it.  Now things are getting a little weird because our IDs are no longer unique and this is one  place where it's especially helpful to remember this conceptual model I mentioned of the primary  key plus the moment in time. In this case our, our temporal foreign keys need to be,  our variance can only exist so long as the product exists. So the green variants are  okay, the red variant, product two exists but it doesn't exist for that entire range  that the variant exists so that's not allowed. So I have a patch in the current commit fest  that teaches postgres how to implement temporal primary keys and foreign keys. It supports  restrict and no action. It doesn't support cascading yet because we need to add the temporal  DML commands for update and delete but we'll get to that. So Nodgris back in the 90s also  showed how to implement temporal foreign keys. Here it is page one, page two, page three,  page four. Okay, so it's dangerous to go alone. Take this. You really want your database  to be doing this for you. You know, you don't want to have to implement it yourself every  time. I think this is why today it's not economical to be capturing this history but if you had  support from your database for temporal foreign keys and primaries then perhaps it would be  more routine to capture this stuff. You know, you wouldn't need to have the foresight and  the ad hoc solutions. Okay, I didn't want to get into this index right away but there  are different kinds of questions you could ask of a temporal table. You could ask what  was it like at time t and in that case you're going to get back a traditional relation not  a temporal relation. You might also ask, you know, give me the history for a range of time  and in that case you're going to get back again a temporal relation like your input is.  And Nodgris calls these snapshot queries and sequence queries. Non-sequence queries  are not very interesting. It's just treating time as a non-temporal dimension.  So I like to say that if you can't do joins, really all you have is a spreadsheet and joins  are especially the place where these ad hoc approaches start to fall down but the research  gives us a principled way to handle putting these things together. Here's an example that  I've stolen from some researchers in Switzerland and Italy. Anton Dignos, Michael Bolin and  Johann Gamper. I apologize for my atrocious pronunciation. We have a table. This is for  a hospitality system. We have one table that stores the prices that our rooms are offered  at and we have another table that stores when our guests have made reservations. Of course,  we're going to change the prices because we want to jack them up on Valentine's Day.  So there's some variation there. Now we want to know what each of our guests' stays costs  in total. So we need to join these tables. Here's the result. The first customer's stay  is split across two cost periods and same for the second customer. Note that the time  stamps are not exactly from the first table and not exactly from the second table. So  we have to cut up these input records so that they line up. This is a lot easier to see  with a timeline. Here's our costs and our reservations and here's the answer once we  join these tables together. Implementing this is really tough and even  Nodgress' original ideas kind of started to fall apart somewhere around here. He did  show how he could handle joins but there were other issues with the non-relational nature  of his results and also the way of asking for them. He proposed putting temporal modifiers  onto your entire statement like up with the select clause. So they were statement modifiers.  Dave Darwin and Lorenzo criticized this and pointed out that this and some other of his  work made it difficult to compose queries. So it made it difficult to take a temporal  table and have its result in a CTE or a view or a subquery or a set returning function  because the starting end times were not actually columns on this table. They were these extra  values so they kind of existed outside of relational theory and it wasn't clear what  to do with them or even how to propagate them if you were including them in a larger context.  These researchers I mentioned actually have a great solution to this I believe where they  have proposed a temporal variant of all of the operators in the relational calculus or  algebra and they show how to easily implement all of them by adding just two new functions  which in that original paper they called normalize and align. They actually implemented this  against Postgres 9.1 and then later against Postgres 9.5 and they have a patch in the  current commit vest and I would be really excited to get this added. In my opinion it  would make Postgres the best temporal database on the market really. It's a way more important  to patch them on and so I'm hoping that somebody will take a look at it. They've had several  rounds of feedback already and they've made a bunch of edits and I think they're waiting  for someone to look at their latest work. There is really nothing in SQL 2011 about temporal  joins. You can manage to pull off an inner join by joining them and using an overlaps  operator but there's no way at all to do an outer join or an aggregate or union and  so having these extra operators would really let you do a lot of new things. Okay so that  covers querying data. Let's talk about manipulating it. In a temporal database you want when you  insert data you are asserting something for a range of time. So in this case say that  we wanted to say that we were selling our shoes product for all of 2017 for $3 each.  When we do our insert we will just say which range of time we care about and we'll get  this new record here. Now this is pretty simple. It doesn't contradict any existing  rows. Suppose we are inserting it at $5. Now we have this interesting situation where  we have shoes for $5 for 2017 and shoes for $5 for 2018 but we have two rows in our  database and that's kind of messy. Snodgris talks about how we should really be merging  these rows together. He calls this coalescing them giving us something like this. So now  our insert is magically transformed into an update you could say. This is nice because  it simplifies logic elsewhere. It keeps our database from getting progressively more and  more sliced up as we are editing data. It can do kind of weird things. For instance,  in this case our insert is becoming into an update. A delete could even turn into an  insert. Actually you don't need coalescing for that. But SQL 2011 leaves out the idea  of coalescing. So possibly Postgres should also skip it. At the very least I think it  might be worth having a standard trigger that people can attach to their tables that will  handle the coalescing after the fact. That's kind of probably the best approach. One issue  with Postgres is that not all column types have an equality operator. You would only  do this if all of the columns compare equal with their neighbors, right? But JSON for  instance doesn't have an equality operator. I don't know how big of a problem that is.  Others more knowledgeable could probably say. In the case of JSON we could cast it to a  JSON B I think. But that's not a general solution. A temporal update we also want to state which  period of time we are applying the update. So we wind up with shortening these other  records and putting in this new record here that asserts our changed price. This shows  the coalesced result but you can imagine the non-coalesced result would just be two records.  This could even cause a delete if our update spans multiple records, right? If we're, again,  if we're doing coalescing. Okay, a temporal delete we also would just specify which time  frame we want to apply it at. And we get a result something like this. If you are deleting  in the middle of a big row you could even wind up with an insert, right? Because you  have to keep the piece at the beginning and you have to keep the piece at the end. One  question I have which as far as I can tell is not answered anywhere in the standard is  when you do an insert update and delete which triggers fire and what are the values of the  new and the old records that are passed into them? I think that probably needs to be answered.  In my opinion the best answer is probably to handle those triggers before any coalescing  is done. Since the SQL standard ignores coalescing that's probably the best way to comply there.  But as far as I know there's no, it seems to be a surprising omission in the standard.  I just haven't found it yet.  Yeah, well I think using closed open interval.  Yeah, well I think using closed open intervals will take care of that problem for you. That's  one of the advantages of that approach. I suppose with coalescing, this isn't your question  I don't think, but with coalescing you wind up having to compare float values for equality  and maybe that's a risky thing to do too.  Right, but that's, you have to decide that, you know, that's a problem whether you're  doing temporal databases or not temporal databases. I will say that with foreign keys right now  Postgres tries hard to cast the types to be the same kinds of types if they aren't the  same types already. And I am kind of suspicious of how that will work if we want to also cast  the ranges to be comparable. You know casting a date range to a time stamp range is probably  something we don't want to do. You know because of time zones and daylight savings times you  know where, how are you, that continuum of time, how are you going to slice it up into  days, you know. So it is a problem sometimes.  Okay another thing that you might really want is a merge or an upsert. I actually built  a real application that lets you edit history and this is what I basically wind up using  all the time. You know when you present a form to the user and they type in all the  values and they hit save, what you really want is to let them say you know the range  of time that they're saving that for and you want to implement that as an upsert. If this  were an insert it would violate our primary key and would fail because it overlaps. If  it were an update we wouldn't preserve the beginning slice of time because we would only  be updating the existing seconds there in the database. There's nothing in SQL 2011  about this. Johnston calls this an insert whenever. I think that we can't do this right  now with on conflict do update but I think that we could teach on conflict do update  to do this pretty straightforwardly given that we have the concept of a temporal primary  key. Okay so let's talk about SQL 2011. Now I'm going to give you some of the syntax that  you actually use to do all this stuff. Here is our do it yourself temporal primary key  and what we're missing is a higher concept of primary keyness. This is how you would  do this in SQL 2011. In particular these last three lines here where we're expressing  a primary key and the range portion of the key has this without overlaps keyword. Things  are a little bit weird here because SQL 2011 doesn't have any notion of range types and  so instead they introduce this idea of periods. A period is this quasi column that you declare  on your table. You have to have two regular timestamp columns and you declare this period  as being based on these two timestamp columns. It's a little bit like a generated on select  column if you know what that means but it's not a column you know. If you do select star  from T you don't get the period for instance at least as far as I know. Date Darwin and  Lerner's service hate this by the way. It has similar problems with composition I think  not as severe as the 90s standard because you could just say comma and add the period  if you wanted to. Periods do come with a few operators but not very much really. Postgres  ranges have a much richer set of operators. Periods are limited to just dates and times.  You can't define your own types on them. They don't permit a null to mean unbounded like  range types do so you have to use a sentinel value like 3000 AD which is probably fine  if you're selling shoes but maybe not be great for astronomy or other purposes. Someone pointed  out to me that Postgres does have a minus infinity timestamp values which is pretty good. It  would avoid the sentinel problem although client languages don't seem to know about  those. So you have problems on the client side if you're using those.  In any case I suppose that we would implement periods using ranges. When it comes to making  the exclusion constraint for the primary key we would just be creating a range expression  for instance for that index or that exclusion constraint. With periods implementation seems  like it's going to be a chore to me because it's this quasi column so now we have to add  special case logic for when they appear in a select or aware or a group by or having  or a function argument or various DDL commands. So we have to support them all over the place  if we want to support them. Supposedly the standard authors added period types so that  they wouldn't be forcing everybody to add range types but it seems like a lot more  work to support this quasi column kind of thing. So in my own patch I've taken this  approach of accepting a range column in place of where the period is. I would like ultimately  to permit both. Since they share the same namespace you don't really have any conflict  accepting a range type here or a period. Vic Fearing we don't think is here but already  has a patch from a while back letting you declare periods on your tables. They don't  really do anything yet but you can declare them in his patch so that's a nice start.  They would let us support both ways of adding primary keys. As a user I would personally  just use range types and not use periods really but it might be valuable to support  them in a way to let people convert from other databases that kind of thing. Here is DML  according to SQL 2011. As you can see there's no changes at all to the insert statement  you just say what you want to insert for those columns. If that were a period you wouldn't  be naming the period you would be naming the two source columns of course. Update and  delete get this new clause for a portion of and you would say the period and then you  would give the bounds for how you want to apply that.  I've been kind of ignoring system time but here's the syntax for a system time period  to turn on this like auditing database tracking for your table. Basically you add a period  to your table and you give it the magic name system time and that is a signal as well as  the with system versioning that your table should be recording this transaction time  history. In this case the time stamps have to be generated which we support in Postgres  12 so that's pretty cool and this will tell the database to record when DML happens. There's  also some extra syntax for querying these periods as of a point in time or for a range  in time. How about in time? Okay so now I wanted to do a quick survey of how other systems  have added SQL 2011 support. I will preface this by saying that I haven't actually tested  all of these and this is really just from combing through the documentation so there  might be mistakes in here. I am hoping to do a more thorough test and exploration and  maybe write a blog post about this but this is what I've found so far is my SQL doesn't  have support for any of this stuff but MariaDB does support the system time dimension. This  is an abbreviated syntax but that they support but they also support the regular SQL 2011  syntax. They don't yet support any valid time so this is kind of this ongoing theme in this  talk. There's lots of ways to get system time but there is not much support for getting  valid time and MariaDB is no exception. Here's how Oracle does system time tables. You can  see it has nothing to do with the syntax. This is a very old feature in Oracle relatively  speaking so it really pre-ates the standard. Oracle does support valid time. Note that  the period keyword has gained to this four keyword next to it which I suppose they had  some reasons for doing. They also let you have nullable timestamp columns which is interesting.  I'm not sure if they actually support nulls there. The standard says that those should  be non-null columns if they support nulls there or maybe they just make them not null  just like you would if you declared a primary key. That's one of the things that I would  like to test and find out. MS SQL Server since 2016 has had support for this stuff. Here  we're creating a system time period. In Microsoft you also specify a history table so their  implementation is quite a lot like how the temporal tables extension works or the TARDIS  pattern works. I imagine this would be a pretty good way of implementing this in Postgres  also if we wanted to support system time. MS SQL Server also has no support for valid  time periods. IBM DB2 has the most complete support I think. Let's see. It's interesting  that you can leave off the clause at the bottom about with system versioning. Also they use  begin instead of start. I don't really know what the TXID column is about. It's in the  documentation but it's not clear to me if that's required or just optional. I'd like  to find out more about that. They do support valid time which is pretty cool. You have  to name your period business time. You can't name it whatever you want. That's okay.  There's always business time. Here's one proposal of how Postgres might support a syntax for  adding system time periods. You could imagine using ranges but you could imagine different  alternatives. All right. Well thank you for your attention. Here are links to a bunch  of things that I mentioned today. I don't expect you to read that but you can find that  list and all the other slides and mark down in PDF format on GitHub here. Are there any  questions? Yes? I might. At least I would think about it for each table whether I'm  going to capture that or not. Maybe. I don't know. One of Snodgrass's goals and his original  approach was to let people seamlessly convert a non-temporal database into a temporal database  just declaratively and not have to rewrite any of the queries. I've always found that  to be maybe a little bit overambitious and maybe it distorted some of the ways that you  would use it. It's pretty close actually. You could almost get there even in SQL 2011.  Yes? I couldn't find any temporal related additions in 2016. I looked. I only have access  to the draft standards you know. So there's that caveat too. But I didn't see anything  new that was related to temporal stuff in 2016. Yes?  Okay anything else?、  not only when that transaction commits, but depending on your repeatable read and isolation,  you also have to know when another transaction actually started. And I don't see any way  that you can actually do that with time savings. Am I missing something there?  Yeah, well I think that's a different level of abstraction, so to speak. You know, I think  once your statement has committed, then the people trying to place orders are going to  see that updated record, and they'll be using their own current time stamp or whatever when  they do the query. So I think that it will be...  If they started, that's where you run into this problem of if there's transaction, it  started first, like there.  If their transaction has already started, then they wouldn't see the update, but that's  no different from a conventional table, right?  Right, right. But that's just why it doesn't appear to me that it's safe to just use time stamps.  For that scenario, and certainly there's a lot of scenarios where it's like, no, I'm going to tell you  what the thing is about it, and that's all there to it.  It sounds like you have serialized bytes, I suppose you have a different bail along with that.  Yeah, those serialized bytes are going to work.  Okay, I'm going to come back to what you said at the beginning, but I thought it would be able to use  a transaction time stamp or a lot of things with a client, and then all we're going to do is  ensure that it's really right, and that we're already using time stamps, and we can't put too much of the bail  on it, so it might not be as safe.  I do think that if you have the notion of some kind of a history circuit, so when you're creating  these new version records, you can then do a warranty, oh, well, I'm placing it in order,  here's exactly which version I used, I showed the customer when they placed it,  even at the times, don't make sense of that point.  Right, I guess I'm not seeing the problem, maybe I'm just missing it, you know, but I think that  I'm not sure if it requires going all the way to serializable to use temporal databases,  I don't think so, but I've heard that suggestion here already once before, so it needs some thinking about that.  It seems to me that the problem you're describing doesn't come from adding temporal,  it's the same problem that you would have in any case, you know?  No, it's not new, it's just the thing that concerns me greatly about temporal is it leads people to believe  that they can define the state that was in play for a particular transaction,  and you would have to also report the snapshot that was used for the other transaction.  What you could actually do is to support that, to the other than epic,  all of these awful snapshots.  Well, presumably not changing the price, we'll find that you're probably going to know  you're going to take the price, so maybe before you put it in that right,  so you don't want to be shocked, that's what we're going to do.  Yeah, I mean, there's a moment, yeah, I don't think you'd be changing it too frequently.  The frequency of updates is one reason that people store the audit history in a separate table, I think.  There is a moment at which you change the time, and so that kind of is the issue.  I meant to say, by the way, that I'm very grateful for any opinions that people have about the syntax  that should work, because that would help me a lot building this stuff, you know,  just so that I'm building it, how people expect an inner reasonable way  that is suitable for the Postgres community.  There were a couple of questions here.  Can you give an example where for Valentine, what the standard offers would be easier than what Postgres currently offers?  Well, then what we currently offer certainly lets you create temporary, temporal primary keys  and temporal foreign keys, do these temporal updates and deletes.  That's sort of what you would gain. Right now, we have ranges and exclusion constraints,  but we have to, the database doesn't know that those are serving as primary keys or foreign keys,  so SQL 2011 would let us gain that intelligence in the database.  Does that answer your question?  Yeah, sort of maybe something a little bit more concrete, like what does it do with that knowledge, how they build the more declarative, you know,  and kind of tell it something, but then maybe just a little bit more specific to creating a foreign key or anything like that.  Right now, it would prove that you're declaring a foreign key to that thing, right?  Right. You couldn't, a foreign key can only target something that has a uniqueness constraint, you know.  So, actually, you have another extension that lets you create temporal foreign keys with stored procedures.  It was sort of a proof of concept. It's not very good. No one should use it.  But, you know, your scalar column has to be an integer named ID and your range has to be a timestamp range named Validat.  So, you want some way to concisely express these constraints, you know, and have the database understand what they're supposed to mean.  Right now, you couldn't add a temporal foreign key at all because it will look for a uniqueness constraint.  There actually is no uniqueness constraint on the temporal table, you know.  Okay, well, thank you, everybody. Oh, sorry. One more question.  Early on, you said something about indexing UIDs, how do you temporal element?  Oh, yeah. Well, you can index a UID with a B tree, you know, but before this patch, you couldn't index a UID and a range together with B tree gist, you know.  Just like if you want to create this uniqueness constraint.  This is fine if ID is an integer because B tree gist knows how to handle integers, but if ID were a UUID, B tree gist would say, hey, I don't know how to deal with UUIDs.  And so you weren't allowed to do this before.  Okay, thank you, everybody.  Thank you.
Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_0_47.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_48_131.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_132_2639.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_2640_6071.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_6072_8147.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_8148_8735.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_8736_9071.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_9072_11015.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_11016_11339.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_11340_11867.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_11868_11939.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_11940_12023.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_12024_13919.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_13920_19871.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_19872_20111.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_20112_20435.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_20436_21599.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_21600_22319.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_22320_25535.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_25536_26435.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_26436_27287.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_27288_27335.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_27336_29375.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_29376_31163.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_31164_31295.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_31296_31655.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_31656_32267.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_32268_33839.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_33840_34031.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_34032_34079.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_34080_34127.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_34128_34199.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_34200_34883.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_34884_35675.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_35676_36923.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_36924_37463.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_37464_37595.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_37596_41519.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_41520_44687.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_44688_44903.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_44904_45359.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_45360_45623.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_45624_45731.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_45732_45851.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_45852_50087.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_50088_51251.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_51252_51659.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_51660_51779.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_51780_51947.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_51948_52127.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_52128_53195.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_53196_53255.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_53256_53327.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_53328_53375.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_53376_53423.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_53424_53819.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_53820_53963.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_53964_54203.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_54204_54887.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_54888_55031.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_55032_55271.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_55272_55955.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_55956_56063.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_56064_56555.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_56556_57899.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_57900_58715.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_58716_59747.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_59748_60083.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_60084_61379.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_61380_61811.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_61812_62663.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_62664_63575.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_63576_64175.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_64176_64655.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_64656_65051.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_65052_65195.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_65196_78995.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_78996_79043.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_79044_79091.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_79092_79151.png Temporal_Databases_Theory_and_Postgres_-_Paul_Jungwirh_79152_79945.png