 Don't want to see that Yeah So this is the talk you're winding the time with system version tables So quickly about me. My name is on the slide. I work in many corporations with the server engineering and I was called my school developer since 1998 till 2009 and since 2010 I'm working on my DB So what this talk is about it's about a feature called system version tables and before introducing it Let me describe three Rather different problems So one is the task of undoing erroneous statements, you know We made a type and the very close new patient deleted the whole table or updated everything to one stuff like that Then you want your data back The other totally different problem is analytics on historical data. You want to see Your a webshop or online game you want to compare your user base from the last year to the user base from this year To the user base from three years ago see how demographic has changed whether the user spent more time and derive probably some useful hints how to improve your business and The third problem is forensic data analysis. It's when you suddenly realize that your site was hacked and it was half a year ago and you didn't notice that and you urgently need to know what the data and to the has seen what user data has leaked and What do you need what users need to be informed and what to do about that? So what all these problems have in common is you need to access your data as it were Sometime ago some kind of a small time machine to look back and see at your data. How they were This is exactly what system version in is is So system system dash version tables. It's a standard name for the feature It's it was introduced in this girl 20 standard 2011 Implemented in many DB 10.3 System version tables is a new kind of table. So it's like normal tables that are temporary tables and there are system version tables Which are not temporary. They are consistent more like normal tables and there's new syntax for creating those system version tables and The new syntax for querying these temporal data So first look, let's see what the standard has to offer This is not a system version table. That's perfectly normal table which has some primary key and some data And we let's say we want to make a system version So this so that's the standard says we need to add two columns to the generated columns One is generated as a raw start and second is generated as our end and they have both the time stamp time type I use the maximum possible precision time time six with six microseconds and the names could be arbitrary then this standard introduce a new object inside the table so the primary keys the check constraints foreign keys columns and Since 2011 this there are periods which is also something inside the table and it has two columns and so for system version tables into defined period for system time with those two columns and Then you need to write with system versioning and after that you have a system version tables congratulation But why would you want to know that what can you do with such a table? So the new thing is Instead of just using table name in the select query you can specify Table name for system time as of some habitat timestamp This is where the magic happens because if you do that You'll see the content of the table as it was at this specific timestamp and not as it is now so this is your small personal time machine to Rewind the time and see the table as it was at some arbitrary point and time in the past There are also two other Versions of the syntax for system time you can specify for system time between two timestamps Then you will see all the rows that existed at any point in time between those two timestamps Let's get a little bit weird because if the role was added say I don't know in January 1st and deleted at January 2nd And then another was added at January 3rd and deleted at January 4th This between will show both rows although there was no point in time where those two rows existed at the same time But this statement will show them both it has some usages but it will show inconsistent view of the table it might show that and This is a very this is again the standard system It's a very slight variation of this one because between means that all and both ends are inclusive and this one Includes the first and and exclude the other one. So actually those two Quarries will return exactly the same thing But this one includes ends of this means everything up to end of January including that one This means up to well first of February not including the first of February. So it's the same thing I don't know why they did this two same things in the standard and How you can actually use this practically This is an example of this analytics on historical data that I used as a problem earlier So this is kind of online game and this is the table with users and now we join in the table of users as it was in the 2019 January 1st and with again same table as it was in 2020 and The users who spent I don't know more than 100 Hours or some units of time online and probably we could derive some useful analytics of that So so this one is joining two historical use of the table with itself and companion them and Example of the from to Presumably this is a table of some kind of bank accounts You want this query will show all accounts that had a negative balance at any point in time in last year Even if they come balance is not zero is not negative now this query will show Because it had been zero Negative balance at any point in time between well January 1st 2019 to January 1st 2020 This is again example of analytics on historical data So we've seen that this is what standard allows us to do But we added some extensions in my adb to make it more user-friendly more easier to use and Faster to use more convenient to use this kind of things So first we simplify the syntax because if you do that perfectly exactly as in this time You have need to do a lot of typing and if you do select star You will see those timestamps that you don't necessarily want to see just want to have your table system version without all this extra stuff It may be you can just write with system version without anything else and then you get system version tables The history will be tracked you can still create it as before but you don't need extra columns You don't need those period thing anything another thing. We support many new supports. It supports Non-version columns inside a system version tables again. That's not a standard feature but this is a table of users like every user has user ID and name and address and Presumably every time the user logins we increment the counter of how many times the user has logged in those data are very static this is changes like very often and It would populate the history of is unnecessary information. We don't really interested How might how many times it has logged in we only want to track how often when his address is our name is changing And this one is not really part of the history that we're interested in it will just blow up the history unnecessary So it may be you can specify that this column goes without system version in and then it will be not been a version It will not create a historical version every time the user logs in It would it allowed to save a lot of storage space this way now maybe some of you is thinking and the first time My school developers heard of this feature is Made it made a bit in three. So it's not the new feature. I definitely got this question. So for in the DB is a Transactional engine timestamp when they're all was inserted or deleted doesn't really Define the time when they're all becomes visible to other transactions because they're all may be inserted at one point in time Interest action might be maybe committed like few hours later and until it's committed. Nobody can see the changes So using timestamps this way it will allow to see the data that when before they were committed one back in history and In some points in some for some applications. It's fine Like if you do this over-the-year analytics, then few seconds for the transaction It'll it will give you slightly incorrect data, but it doesn't really matter for the time spent over the year, but if you want to do auditing or stuff like that then it might be very important and We allow to specify those Generate columns is begin on sign and then in a DB will store transaction ideas there instead of timestamps And then it'll be precisely transaction precise version and it'll show only the data that were actually committed and visible to other transactions at any given point in time and Now that's another problem the table written that way it'll store its system version tables It'll store all the data and historical data in the same table Which means well English engine on the table will the table will grow. It will never shrink It mean it and it means if you do indexes will go and it'll they go so the case will get slower and slower and If you are unlucky enough to do a full table scan for some bad query, it'll be much much slower and Also it'll take it'll take a lot more storage, but the fact is that historical data they are very really needed so most of the time you just want to use the current data and Eventually really you want to look at what happened Sometime ago, so it makes sense to store the history separately so that it won't won't slow down the day-to-day operations And our solution these two partition table by system time Then you create a partition for current data in partition for historical data historical data will start separately and Optimize as smart enough to realize that if you don't use any as of things then you only need current data Then it'll do partition pruning this will not never be opened or looked to it'll be basically as fast as Not having any system version at all You can even do You can even rotate partitioning by having many parties many history partition and say like I don't know one week of History for every partition for this one you of course would need more than one partition One more history partition and then you'll have one week of partitioning here one week one week of history here One week of history here and so on new partitions will be not added automatically at least not in Maria DB 10 3 It's a new feature. We are working on now so But every month or every few months you can add new partitions here and drop few history partitions keeping history Well reasonable and this could be of course an interval we want and couple of new Ways of querying the data one can specify in Maria DB for system time all and I think Microsoft SQL Server also is the syntax which will be like having a very wide range It'll show all the history and all the current data and Because in a DB can stop a transaction IDs and not timestamps There's an optional way of querying exactly transaction by transaction ID. It will show the data that this transaction has seen How much time? Okay, so I Know I can talk about so the new configuration variables that Useful in configuring this system version and thing first because most of the time system version in it relies on current timestamp to know when something has happened when there always changed and traditional in my school and in my adb Current timestamp within session and you can modify it arbitrarily So a user can totally subvert the history and create non-existent histories and change timestamp to five years ago And then to ten years in the future and it wouldn't make any sense If you look at the history later on so in my DB now there's a way to restrict Users who can modify the current timestamp within a session The default value is for secure timestamp is no means timestamp is not secure and behavior is backward compatible Anybody can modify the timestamp within a session, but you can also be limited to a super user So only super user can modify the timestamp within a session I turn it to the community There's an even more stricter configuration where security stamp in the city to replication meaning even the super user cannot mess That's time time stamp, but the slave will still follow the timestamp of the master and when the Slave is replicating this time stamp on the slave will be exactly the same as it was in the master and the extreme Configuration sent into secure timestamp to yes means Nothing ever can change the timestamp on the slave or on the server It will always be the system timestamp watching the well the clock of the operating system And then you can be sure that the history goes exactly as system clock say but the drawbacks are that times that the history and master and slave will be slightly different if their Clock is not synchronized Yeah, actually this is That's where I'm all thinking that porgues has a benefit over MariaDB on my squirrel because I have really real difficulties imagine a dolphin walking in or a seal Anyway, so and another related configuration variables is System version alter history with the default value of error according to the standard You cannot do alter table for system version tables. It should be an error So if you do alter table at column, it'll fail We thought that's the default behavior of MariaDB as well But to be thought it might be more user-friendly and many users might still want to support alter table. So that's a We have a new other Behavior with the system version alterable it will skip which means you can add a column and then it'll rock the history will be preserved but if you do You'll try to look at the history of the table Before you have added the column you will still see the new column that is meta data not version You still see the new metadata, but for existing columns. You'll see the old values exactly was supposed to be and How much would this feature cost in terms of performance? This is a very simple bench in benchmarks percents are comparing their performance on system version tables as compared to the performance on non system version tables And with partitioned and unpartitioned as this is you might guess this is this bench So as you can see the inserts are basically go at full speed. There's no slowdown for inserts at all if you look at deletes deletes are 20 30 percent slower and again those numbers are they just ballpark They probably would hard highly depend on your application. This probably won't this definitely will But I just want to show that it's not like ten times not two times and it's not zero percent. It's sometimes it's Five ten twenty percent. It's something you might expect realistically expecting your application How do it get slow if you turn on system version in so delete is always slower because you don't actually delete anything It's deleting the old row and inserting the history row and that's that's extra rock every time you delete something So the lead will be always inevitably a little bit slower I'll tip it only Lord is awful slow simply because the primary key got longer in the system version tables and there's an asterix meaning this a an M. Dev a feature a Plan to fix that in partition tables But this note is not possible to fix in non-partition system version tables Although again, this is very much depend on the application depending on primary key length if it's you have a very short primary key then extending it will You might exist in version might extend it will happen at times times or might extend like a few times if you have a reasonably long primary key like you ID then The slowdown will be much much less if you don't have primary key at all and rely on in a DB generated primary key There should be no slowdown whatsoever and all tpd write This one is a little bit confusing So what I've done I've run the old tpd write benchmark three times on the same data without Regenerating the data and the first time I go to slow I got a 60 percent speed like 40 percent slowdown six time. I got 50 percent Third time I got 40 percent if I would run it more and more it would be slow and slower because every time I run old tpd write benchmark the history rows are deleted and updated so history is accumulated table goes longer and That's why the benchmark system version table is getting slower compared to non system version tables and This is exactly the use case that partition table is supposed to fix because it keeps the history separately The table is not getting slow because the current data is not growing all the changes the historical data are growing but historical data are not getting queried and Incense bench benchmarks. So this is exactly the use case when petition tables is fixing It removes the slowdown that one would observe in non-partition system version table. This is it. Thank you any questions I'm not sure You had to add the blue elephant Yes Yes There's one of the use cases we are thinking about Not necessarily so Normally the master it's it includes the timestamp in every event So there's a problem when the master Includes the timestamp without microseconds and slave needs it up to the microseconds Then the slave could guess some arbitrary number of microseconds. It'll be just use increasing number Otherwise, it'll use a timestamp from the master. So it will mostly follow the master Is it possible to see Yes, but then you just don't use That actually two way to do that Yeah, so you can do you can do all and just where ID equals something Yeah Because this one is part of the table name. So this is basically the table then you could anywhere close and everything Yes, please So this is the join and so I just said this thing is basically part of the table So this is the table uses for this is another table uses for blah blah blah and then this is a join using ID and this is just the join so you can use this thing and anywhere where the table name is expected Yes, please No, you know, you cannot you can do you can you can team you can Periodically remove old partitions and keep like I don't know three months of changes and delete and drop other partitions But generally delete doesn't work because well, that's it contradicts the idea of immutable history. Yes Yes, yes, that's why that's why I said that delete is basically Delete plus insert you delete from here using insert here Yes, and update is just insert because you update here and you insert historical or over here updates are also slower inevitably No, it's update of the car of this row in here and insert of the old version in here. It's update plus insert because Periodite benchmark they are mostly doing inserts and so generally much faster than updates So that you didn't lose anything on insert or sorry inserts reads it's doing mostly reads and you Won't so much on fast reads that everything else is very hidden Exact if you asked about exact data on three runs I had 35 percent on one 38 on the other and like 102 percent of the third one So which is if look at few percent don't really matter much Yes, please This one in and is in the db because it need to know in a db transaction ideas if you time stamps it completely in the server This this is how Oracle implements it as far as I know That they use the They use the under log and Russian in it might be it's probably Can be done in inside an adb It'll be a lot of rock to do it to leverage the under log but pretend that this from the share a point of view It's still a table. Yeah Yeah, yeah, yeah, yeah, yeah, I understand so Well, every time when you purge the under log you populate the table. So then you it's Well, I think that's what oracles doing with flashback minus popularity in the table Just keep the data in the under log and then it's purge then it's purge forever Yeah, yeah Yeah, so it's it would totally make sense and unless you want to see like exactly very fresh data