What I'm going to show is, oh, we can analyze transactions, do some replication, and do some time travel.
Right?
So, the agenda for today, introduction, some requirements, some design implementation, of course, open source.
And maybe if we have time for questions, I wanted to do a demo, but it's impossible here.
I don't have the Wi-Fi.
So, who am I?
I'm Arno.
I was born in Belgium, so I'm a local here.
And I actually work in Chicago as a database team link for job trading.
Jump trading is a trading firm.
And so we trade all kinds of assets on all kinds of horizons.
And so we do research, so we have research facilities like HPC and also cutting-edge technology databases.
So, my talk is about transactional database, talking to clickos, as an example of a null app database.
And what we're going to do today is we're going to, it's agnostic, basically.
You can do it with mySQL, Postgres, MRDB, those are very known example of transactional databases.
And we're going to do a terro genus replication to a very different kind of database.
Clickos is completely different.
And so we're going to try to do that to different paradigm today.
So, what is clickos? Any of you know what clickos is here in the room? Oh yeah, a lot of people.
So, we add first them, so it's also open source, it's an open source database.
It's different from the transactional ones which are a row based, so it's colonner.
And the most, the fundamental data structure that is under clickos is the merge tree data structure, the merge tree engine.
And clickos was created in Russia in 2009.
It currently has 45.5 stars, 1000 stars.
As of now, I verify today if someone is in the room and see 0.6 is possible.
So, that should be the, so it's extremely popular and it's very good at doing real-time analytics.
If you want to know more about it, you can just scan this QR code.
I will leave you a few seconds to scan it.
But it has over 2,000 contributors and it's a very successful project.
So, that's why I chose it as an example for replication.
And this is an example, a query that run today on some clickos instances I run on or on our HPC environment.
I'm not sure everybody sees, but I'm, it's a select constar with some data aggregation.
And it's on a 100-retar by dataset, we manage to get 11.3 terabyte per second.
Obviously, it's not reading that much data, but the, it's an illustration.
All fast, the colonner database can go if they only read a subset of the data, just the decolon year.
So, it's pretty impressive. It has 20 nodes and it runs on Apache as well.
So, it's pretty cool stuff.
So, what is replication?
Replication is synchronization between primary and replica.
Everybody knows what it is in this room.
Usually, homogeneous replication is easier and that's the main stream.
And heterogeneous replication is really challenging because, you know, you need to translate from one paradigm to the other.
One database to the other, they may not understand the same data types.
I don't have the same time zone.
And so, it's really a challenge.
And what we want to leverage here, there was a talk before on logical replication.
That's really key for this operation.
And of course, it's going to be log base.
So, we're going to read the binary log in mySQL and we're going to read the world, for example, in postgres.
And this is known as CDC. So, change data, capture.
So, you capture the changes of the database and basically you replicate it to another system.
It can be Kafka, it can be anything.
So, why is replication so useful and so important?
It's because it scales the scale or the read.
So, you can have one primary and maybe 100 read replicas with log it and see you can achieve it.
You can achieve very high read throughput.
It's useful to migrate from one system to another if you want to migrate from say,
Murray DB to mySQL, you can just use replication to do that and fail over and don't lose any data.
It's good for continuous synchronization between system, four tolerance.
If for example, you have two region, one is failing, you can fail over to the other region.
And this is impossible without replication.
And so, it's a very important building block.
For this project, what do we need?
We really want to not lose any bit of data.
We want to be low latency and the one second is nice.
We don't want duplicates, so exactly one delivery.
We want something really simple.
And we want to be able to do four tolerance, so we want to be able to run that in several days.
Is it data center?
And of course, we had first them fully open source, freedom of choice.
So what did we design this?
We wanted to keep it simple stupid.
So we just have one container and a YML file.
And we leverage things that are really working well.
The binary logs.
Oncler is a nice project.
I saw that postgres still uses bison, which is like very old.
Antler is fantastic.
Also, we use PG logical replication.
Yeah.
And then that's it.
And we didn't want to reinvent the wheel doing this because they are already projects that do that.
And this project is dbysium.
I saw some dbysium stickers.
And I put one on my laptop.
So dbysium is developed by Reddit.
And it's a very nice open source project to change it.
I capture.
And why we picked that is because we got access to a lot of sources.
And so for this design, we also wanted to have one to one table mapping.
We wanted to leverage the primary key.
Yeah.
To replicate this better to have primary keys.
We wanted to convert the data type without data loss.
And I mentioned already the merge tree engine.
But in this particular context, it turns out that the replacing merge tree engine was the best.
Why?
Because it turns all operation into inserts.
So if you have an insert, it inserts.
If you have an update, you can just increase the version.
And the replacing merge tree will only show you the latest version for the primary key on when you're reading.
By default, nobody can use a variable called final.
And you can have that by default in your profile.
And so it does similar to iceberg does on some stuff merge on reads.
And it does it very quickly.
And over time, those operations have been.
The performance has improved dramatically in the latest version of clickers.
And also to for the story, I was the one that introduced the final variable.
The few years back when we work on this project.
And one other thing that we wanted is that the same query should run and return the same result.
Also something that's not very common with CDC is we wanted to have full DDL support.
So it means that if you create a table, it should work.
If you rename a table, it should work.
If you drop it, etc., which is not easy.
So I can explain all we did that.
And of course, it's challenging because the time zones you may have a server that is new to see.
And other servers in Chicago.
And you don't want to lose.
You don't want to corrupt the data types there, especially time stamps.
And data.
So to design this, we decided to replicate the state in the target.
As it's done, usually for my school or for postgres.
And we wanted to have an interface that will be super simple like my score replication.
So show replica status, show replica, start and stop or so as a common line.
We would want it to retry on failure because if there's a failure, you don't want to corrupt your data.
So you retry until the arrow is fixed.
We have replication filters, we can choose a table, we can choose a database, we can choose several tables in the database.
Very importantly too, we have checks on this project to verify that what we do doesn't lose data because it's one of the goal.
And also we developed efficient dumpers and loaders that can dump terabytes of data and restore terabytes of data in sometimes minutes.
And so we did that using tools that already exist like the MySQL shell.
I think it's possible with PG dump as well.
So in terms of architecture, what I'm going to present is the component in the middle.
So I already told you transaction database on the left, MongoDB is one of them, MongoDB etc.
It works also with Oracle, it works with SQL Server because as long as DB is going to understand it, it can turn it into events that you can apply and the connector can apply.
And so keep it simple, stupid, a single binary, a single jar because it's developed in Java and goes to clickers.
So the project is there.
I scroll the Altinity sync connector because we developed it with Altinity, a provider of support for clickers.
And so as I said, only one container, Docker compose can run it.
You can also run it as a service.
It works with multiple threads, just like MySQL replication or yes.
What we also wanted to have is something that was not fully transactional because it's impossible.
Clickers is super performant, but by default it's not transactional.
So what you want is that eventually, if you wait for replication to catch up, it gives you the same data.
And also what we wanted is low latency and we achieved less than second replication.
So in terms of table structure, so you have the the MySQL on the side, mySQL as an example, it could be another database.
And the way we translated is of course we need to translate the name, the data types.
I already mentioned the replacing merge tree engine as the target, which is here replaces in ODB with a primary key.
The system, because of Antler and the passing understands the partitions and can turn the partition list here into partitioning clothes.
The uniqueness is achieved with the order number here, order by, so order by is the equivalent of the primary key, but they can be duplicates in clickers.
There are no duplicates if you run final knowing that you have a version and knowing that you can delete data with the easily deleted.
So that's all we did, and that's all I think the competition is doing and a similar project.
There's not many different ways to do this, especially if you want high performance, you need to turn a null tp workload into an insert workload, insert only workload,
and then resolve the difference at query time, but also at merge time, because as the name indicates it's a merge tree.
So if you have two parts or two partitions or two parts, they can merge together and they can remove the duplicates for you.
So if you finalize that using merge is going to trigger that, then you are guaranteed to have zero duplicate eventually.
So the talk would not be complete if I didn't mention what we did recently with the history, because you have all the changes coming through the transaction log, which is like the binary log or the well.
And so we figured out that we could use that, we could add a list or a story mode, because if you replicate in a different schema, not like for like in a history schema, you can have the full transaction log as a big fat table, and this is very useful for analytics.
And you can also create one history table per table in a separate schema, and this gives you the possibility to know what the state of the table was yesterday or a week ago.
And you still have the current version, so it's still synchronized as the previous design, and this is called slow changing dimension number two.
So I'm going to show you the transaction log here, so just a simple table with all the events and you see the before image and the after image.
It's also replacing much tree, because if we rewind, we don't want duplicates in there.
And then we figured out for this particular database, what would be the unique key, and so here's the server ID, the log file, the position, some sequence number, and we have the time, because this is a time series.
And yesterday I presented the graph and a dashboards on that, you can run AI on top of this table, and so you can know a lot about your work workload by analyzing, by turning something that is just transient in a regular system into something that you can persist, and where you can run analysis on top of it.
And of course you can have a TTL, because as a TTL, so after 30 days you can discard it.
Okay, so all do we do time travel?
We do time travel this way, well this is another slide that shows that you can even compress the data.
So if you want to compress using this standard, you can add a recompression clause, and the slides on time travel is just here.
For the history table, I already mentioned the version and this deleted for the RMT to get zero duplicate on the primary key.
But if you are just three columns, actually two, valid from and valid two, you can rebuild the state of the table over time.
And so that's called the temporal table, some database have them, you know, within the source database itself.
Here we'd show that it was smarter to put them where we had no choice, the applications were already there.
But if you treat them as an analytics table that you can have an analytic database, then you can reconstruct the state of the table.
And so we have the same primary key, but we use the valid two in the primary key.
And also we partition not by the partitioning order that is original, but we partition it by valid two.
This way, valid two being the future, we always have the current version in the last partition.
And then as the data updates, it trickles down the past partitions, which will have different data valid two.
And then you can run a TTL on that, which is very, very interesting.
So you're not going to run out of space with those kind of history tables.
And of course, this is fantastic, the system that ate after one day, not only saved you space, but also removes all the intermediate versions that you may have and delete the data.
Okay, so last slide about the development status of this, there's a QR code if you want to know more about the project, it has 300 stars.
And so I'm glad that I can present it here.
One to one table mapping works as GA development started four years ago, so it's bullet proof, we use it in production.
The binary table is just a new ID and it's in beta state at the moment.
And I wanted to mention that there are similar open source projects that appeared later, and PRDB, for example, was launched last year for my skills.
So if it doesn't work for you, you can, you have open source alternatives as well.
So no demo, and if I've time for some questions, I think I have two minutes.
Any questions in the audience?
How do you deal with the tick table systems?
They need share, it's not sure.
Oh yeah, I mentioned that, so I mentioned that, well, you can take a consistent snapshot in any database, like Postgres or MySQL.
And then you restore that, and then you start replication from the position that's within that dump.
MySQL would give you that position, and you would have the LSN in the transaction number in Postgres, because it's very important building block.
If you don't want to lose data, you need to know exactly where to start.
So yeah, it's supported, of course.
And for very big database, another question?
You then overload the source database?
No, because you can use a replica to do that.
Yeah, you can use a replica, so.
Yeah, replication is super important.
Yes.
No other questions?
Oh, you?
Yeah.
Sorry.
I completely discard them because it's eventually consistent.
Thank you.