So, the idea is to talk about another distributed SQL database, you go by DB, which has
this popularity that to be posguest compatible, we will use the posguest code for the query
layer, and this is where I will focus on in this presentation.
But I will use the posguest code on top of a distributed storage that is completely different,
and I don't know if that works.
So, I am from Pashow, I have been working with a lot of databases.
I am developer Advocate for UGABite for five years, and one more week I will be developer
Advocate for MongoDB next week, but let's talk about distributed SQL and UGABite DB here.
Basically, if you look at the architecture of a monolithic database, so one that is not distributed,
you have a query layer, and basically the application connects to, I don't know if that works.
No, not really.
Okay, the application connects to the query layer that receives a SQL statement, pass it, look
at the dictionary, build an execution plan, to finally have something to execute, to the
storage, read and write operations, and those read and write operations go to the disk
for shared memory shared by first.
This is why it is monolithic, it is difficult to share the memory between multiple nodes.
So, that is basically a rich work, even if it is monolithic, there are multiple layers visible in the code,
otherwise it is open source, so you can look at the code.
The idea of UGABite DB is to do the same, but distributed, running on multiple nodes, so that
the application can connect to any node that you choose, load, load balance, have a query
layer of there, understanding the query building the execution plan.
The difference will be the storage layer, instead of reading and writing through the
first to the local disk, it communicates through the network with the other nodes to get
data from where it is and write data, where it should be, and it can be distributed.
So, here we will focus on this blue path that looks like the Postgres 1, so you just
take the code, you will need on multiple nodes, but you have a few things to change first, because
you will store differently, you have network calls, and maybe different layers will need
to be aware of it, optimizing an execution plan is different when it involves network calls, for
example.
So, this is what I will detail in this sort.
Basically, the idea of UGABite DB is to use to be compatible as much as possible with Postgres,
but with a storage that is similar to Google Spanner, Google ChromeDB, TIDB, YDB, those distributed
SQL databases.
Okay, I don't know why my clicker is not always working, but let's, maybe it's just my laptop.
Okay, so how it looks like from the application point of view, you connect to a Postgres
endpoint that really looks like Postgres, you can query the version.
We just add the version of UGABite DB, but this is the Postgres compatible version for
the moment it is a fork of Postgres 15.
The project started with Postgres 10, and then Postgres 11 was managed, and then for a few
years, it was telling with Postgres 11 compatibility, and last year we merged all the
versions, all the changes to the query layer from Postgres up to Postgres 15.
So that's the compatibility layer.
The idea is that if you are in Postgres 15, you have the same features in UGABite DB,
but with a distributed storage, the ability to scale out the scale horizontally.
Okay, it's not the first database that you use the Postgres code, because the Postgres
license allows it, it allows you to reuse the codes, and you can even use it for commercial
purpose, and UGABite DB is fully open source, but there are also some fork of Postgres
that are not open source.
On the Postgres weekly, you have a list of Postgres derived databases, and they can be derived
in a different way.
First, Postgres is very extensible.
There is this possibility to have some kind of plugins on Postgres extensions.
So Postgres derived database, use that, for example, timescale DB, siteers.
Do something differently on Postgres, but not forking the whole Postgres code, just adding
a plugin that can work with it.
So others are really forking it at a specific version, and then it has its own life.
This is what GreenPull did, for example, without trying to follow the Postgres versions.
And some folks are the same, except that they want to be able to merge from the version of Postgres,
just to benefit from the new features that come to Postgres, but also just to make it easier for
users who use Postgres when to be distributed or go distributed, one to move back to Postgres.
And also some folks who even allow you to choose the Postgres compatibility version, for
example in ORA, in ORA, you create a Postgres compatible database, and you even say,
I want it to be a Postgres compatible with 16.1, for example.
So different ways to do it.
I will explain quickly why we don't just use an extension, because when you look at Postgres,
you see a lot of grid extensibility, and that's not only the extensions.
You have also the foreign data wrapper that basically can connect to any database.
So if you have a very different storage, you can use that.
So that's the top layer in the query.
Where we find tables like you query local tables.
There are also the table and index access methods, kind of an API between the query layer and the storage layer,
and extensions.
So it looks like you can do everything with simply an extension.
But it also source some limitations.
The problem is that if you want to be able to plug anything that the software becomes quite complex.
So there are things that, even if you can plug very different storage, Postgres still expect it tables.
And then you go by DB, we don't store the rows in it tables, we store them in the primary key,
which changes a bit what you can do with the table access method.
I will not go into all the details, but the limitations with extensions,
and those will do something very different from Postgres with extension.
They have to work around that.
So maybe the idea is just to fork it, which makes it easier not trying to have hooks everywhere.
In this diagram from the initial architecture, we tried to show where the code must be different.
It's not a single place where you plug between the query layer and the storage.
Because for example, in you got by DB, we have threads of connection, an internal connection tool.
So there is some code that must be different there.
The planner, the optimizer must be different.
You need to take into account that it is distributed.
You have network calls and of course the execution is different.
Many different places and you don't want to have 100 of small extensions everywhere.
Maybe it makes sense to just fork.
But still try to do not change everywhere in the code to be able to merge with new version.
I just listed many things that you got by DB must do differently than Postgres.
So the user, we have seen that you just connect with PSQL.
For example, the Postgres client or PGBENS, this is an example using PGBENS, just Postgres application.
And most of the things are very similar.
You describe the table, you see it as if you are in Postgres.
Of course there are little things that must be a bit different.
For example, in Postgres, you have by default B3 indexes.
Here we store everything on LSM trees.
I will not go into the detail in this presentation.
But it fits better than the storage.
It's distributed database.
And in Postgres index, you have columns that are ascending or descending.
And we have added something asht to apply automatically asht function.
If you use a column only with equality predicate, the advantage of asht function is that we know the range of values.
So it's much easier to distribute.
Rather than arrange a shorting, where you may need to split differently depending on the data.
So those little changes and those cannot be done with an extension.
This is just one more line in the code to define B3 or LSM tree or to define the index option asht.
But there is no oak in Postgres to add that.
So different places where the code must be different.
And that's the reason for a fork.
So from the developer, if you run a query and run an explain to see the execution plan,
you will see something very similar to Postgres because most of the code from the explain or for from the plan is the same.
But with a little differences.
For example, it's an index on the scan.
So we still display it features if you know Postgres when you have an index on this plan.
It may still go to the table to see the visibility of the rows in the execution plan.
You see the number of it features from that.
We show it. It's always zero here because we do MVCC differently than Postgres.
And the index only scan reads only the index there.
But by just for compatibility, it shows the same with zero which much.
And there are also more metrics that are important in the distributed database.
For example, one thing that is must be very different is the catalog in Postgres.
You have a catalog that describes your table indexes, columns, whatever, also all the function code.
This must be shared. In a distributed database, we want to share less things to short and not share everything.
But the catalog must be shared.
And then it's important when you have a query that runs.
It's important to know how many reads requests must go to the catalog because the catalog is at a single place.
You don't read it always when you execute queries to be scalable.
If you pass a query that's to read the catalog and if you distribute your database across multiple regions,
then it can have a latency.
So more metrics needed in the execution plan.
And basically in Postgres, you run, explain, analyze with the buffer option to see the number of pages of blocks that are accessed.
This is really how you know that your query is expensive or not.
Here, we don't read from buffers because we don't have this share buffer cache because we cannot share it to multiple nodes.
But we have a dist option, distributed calls, and basically it shows the distributed request and write requests.
For example, this index on this scan has sent two reads requests to get 1000 rows here.
So this was just an example to show that it looks like Postgres, but some things must be different.
But the less possible.
Another example or something that must be done differently.
We use the Postgres query planner.
We use the same join methods.
Nestled loop has joined merge join, but we implemented a new join method.
Because the Nestled loop is really nice because it's the only join method where you can push down the join condition to the inner table.
The others have to read both and then join.
But the following in Postgres, it executes the inner access as many times as you have holes in the outer table.
Which is okay in Postgres, because that's just accessing in memory.
But here, we access through the network to a table that may be in a different place.
And then you want to batch it.
Of course, you need a network call if the table is on another node.
But you don't want one network call for each row.
You want just one that gets all rows.
This is why there is a different join method that is displayed as you go by DB.
Batch the Nestled loop there.
And you see the difference in the plan.
What it does in the plan, the push down of the join predicate.
Put the values that are batched.
Put it in an array.
And of course, the access to the LSM trees is made to be efficient with an access with an array.
And you can see that then you have less loops.
In Postgres Nestled loop, you would have more than 1,000 loops here.
You have only two loops to get all that.
So those are the kind of things that must be done differently just because you are distributed.
And when the same I performance.
Okay, and then let's look a bit at more detail about what is different in the code.
So you got by DB is fully open source.
So you can look at the code.
And if you want to see a lot of code that comes from Postgres.
And a lot of codes that is different for the storage.
Here we focus on the Postgres code where something was made differently.
So if you look at the function is YB relation.
Then this is where you have a special code.
If the table is not a Postgres table, but you got by DB1.
If you use temporary tables, this is fully the Postgres code because this runs locally.
But when it is distributed, it must be different.
And you can see that usually you have this if condition.
But everything else is implemented in another file.
That's something.
If you want to merge from new versions, you don't want to add code a different place.
You want the minimal places.
And then put the specific codes in another place.
A bit like extensions or plugins, but it has different hooks in the code there.
So those are just examples.
And if you look at the code, there are also interesting things in the comments.
One very good thing in Postgres.
The code is very clean, but the comments are very clean also and gives a lot of information.
So we tried to explain what is done differently also in the comment.
And when I was preparing this presentation, I was like, I'm really sure how much the code is different.
And there are probably better tools and better ways.
But I just got the code from the two versions.
So we have a Postgres, we've already got a DB version that is compatible with Postgres 15.
I compared the code with Postgres 15.
And then I just did a diff using diff stats to see the different on me on the C file.
And just looking at the files with most changes.
And of course there are a lot, but not so many.
And what was nice with this exercise was that just listing the files with the most changes.
I was able to explain why there are a lot of changes here.
And I will not go into all of them.
I didn't like them here.
But for example, of course table commands.
We have some commands that are different.
And some where we just need to flag that it's a different comment.
So of course many changes there.
You see the relation cache.
Of course we don't use the same cache for the tables.
Gucy does are the power matters.
We have a lot more power matters to control what is done in the storage.
What is distributed.
So make sense.
We have seen we use the same explain, but display different things.
So of course there are some some difference there.
If table sequences or so they must be done differently.
Because the sequence has a single point of truth.
So you must optimize things to be able to scale it horizontally.
And let's take one more.
Yeah, so we table spaces.
In possible use table spaces to put files in different file system.
Here we are distributed.
We use the same table spaces.
But to put different placement information.
For example, you can distribute in multiple regions.
But one table or one table partition must be in Europe.
For example.
And we use the possible feature.
But instead of saying it's different file system.
It's just a different cloud region.
So making the table spaces cloud native.
Another way to look at how it is different or not.
You run it.
And you do a perf record on it and look at the call stack.
So this was an example where I just created a table and inserted some holes.
And I looked at the call stack.
So taking a flame graph from it and looking at what is different.
So there are some functions that are actually the same as postgres.
So from there maybe it's a bit too small.
At the beginning of the touch it's the same as postgres.
There are some you go by it function in the middle.
For example, one thing that we do differently in red committed.
When the conflict is detected, postgres just reach twice the roll.
We try the whole query to be consistent.
So at that point there is a function to do that.
And when you go lower closer to the storage.
This is where things are done differently with what we call PG gate.
The gate from postgres to the UGA by the list of edge.
And finally you will have network calls RPC to another layer.
But the storage layer that is the distributed storage.
And I will go fast from it.
But it's multi-traded.
So you have threads in the storage layer that receive the reads and writes.
And we will process them.
And here if you want to see where the code comes from.
You don't want to reinvent.
And completely new database.
So in the storage layer it is distributed with raft.
And we use the Apache Kudo for that.
We can find some functions similar there.
And basically it's short on each server.
It's stored in a red DB LSM tree.
And then you see some red DB function.
This is also forked from red DB.
But without trying to merge to new version.
Because we don't need to be compatible with red DB.
We're going to become compatible with postgres.
OK. And you can see also about the raft application.
And what is coming from Apache Kudo.
Another way if you want to look at exactly what is happening between the query layer and the storage layer,
you can enable a trace.
So that's a good parameter that you can set to log the request to the storage.
And I highlighted a bit of them.
So the trace is a bit verbose.
You see basically the wall API between the storage layer and the distributed
between the query layer and the distributed storage.
So for example, you will see that you have a red or a white operation.
That it concerns something that comes from postgres,
because you go by it as also Cassandra like API.
And maybe something must be done differently in the storage to have the different behaviour.
There is a catalog version.
This is because your query relies on the version of the catalog.
If the deal happens, the deal will increase the version.
And then the query, the storage must know that it comes from different.
And there are also some push downs.
For example, if I select Canstar from a table with the architecture,
we have seen, I will read millions of rows and count them with the postgres code.
We don't want to do that.
So we just push down to the storage count this.
So the storage can also use some postgres code.
For example, if you have a function that is pushed down,
the storage will call the code also.
It's different logical layers.
But those layers are all present in all nodes here.
And there is a partition key.
This is the chart.
I don't like the term partition.
This holds to the Cassandra people.
I don't like the term partition because we have also the postgres partitioning.
Not used to distribute, because it's distributed at a lower level.
But postgres partitioning can be used here, for example, to do geo partitioning.
I have a country code for my customer table.
And I want the customers in Europe to be stored in Europe.
I use partitions and table space to map all that.
Of course, there is a whole identification.
It's not the same city idea of postgres, because the city idea of postgres is an offset in a heap table.
Here we have another same tree.
So it's a different one.
And many operations there.
OK, last slide where I just summarized why are and the benefit of it.
So the reason for using the postgres code is just to have a better runtime compatibility.
Now some other databases that build a compatibility,
but with another language.
For example, co-code DB uses go.
And yeah, why not?
Except that there are some little things that are done differently.
The arithmetic on the data type may give you a different result.
Some regular expressions are not processed the same in postgres rather than in a goal library.
So here the idea was really to use the same version of postgres just by using the same code.
Something that is already proven for years in postgres.
And how doing that, a fork, rather than an extension,
but a fork that is clean enough to be able to merge form new version.
Here from 11 to 15 it took a bit long.
The reason was that it was not just about merging feature, but being able to do a nonline upgrade from those two versions.
So it involves modifications in the catalog while the application is still reading and writing.
Now that the framework is there, future versions will be merged more quickly.
And the benefit of it is having a lot of features.
My boss, the city who was saying a postgres is the gift that gives you every day.
One user is asking, can we do that?
Yeah, you can do that on postgres.
And many things are just done the same without any effort.
So this is mostly 100% of the postgres code because this is only a query layer.
So do not reinvent something for the query layer.
Do the storage differently when needed.
So that's mostly the thing.
Better compatibility and also the C code with the root DB storage C++.
This matches very well.
That's my LinkedIn QR code and that's all I have for you.
Do you have any questions for the two minutes?
We have one time for one question.
And so your hand goes first.
Hello.
Do you have idea how large the catalog can be?
How large it becomes before it gets problematic?
I will launch the catalog can be?
Yeah, if I have 50,000 tables, it will be okay.
So for the moment, the catalog is a single tablet.
It is not distributed and that works.
Because the access to the catalog is you don't need to access always to the catalog.
Ideally, you pass a statement.
You execute the plan, the prepared statement multiple times.
So for the moment, the catalog has never been a bottleneck.
It could be also distributed but for the moment, it's not a bottleneck for its size.
It's something to take care if you are geo distributed.
Because if you pass a lot of queries and you need to win the catalog from Europe to Australia,
then you add some latency.
But the size itself is usually okay.
Thank you.
Fortunately, we'll wrap it up.
But Frank's going to be here.
I know yesterday and he is another session coming up too.
So, thank you, Frank.
Thank you.