Now, so, I mean, the next session is, I mean, sort of continuing on talking about both post-gressive
and my sequel, and we're going to talk about compatibility, starting with Jimmy, talking
about post-gress, and then we'll turn things over to Daniel to talk about my sequel families.
Can anyone hear me?
Cool.
Okay.
So, thanks for coming to our talk with Daniel, it was a great collaborating with him,
because we're going to be going over some important topics.
I think compatibility is really important.
It's a word that's thrown around semi-randomly these days.
Everything is compatible with everything else, and it's usually not decided on technical merit.
It's usually decided by marketing people.
So, this being a technical chiefly, a technical community conference, we'd like to set a few things
straight and just offer our opinion on this.
So, my name is Jimmy Angelacos.
I'm based in Edinburgh.
I work for PG Edge.
I've been doing things around open source for a long time.
I contribute to the PostgresQL project.
I've written a book called PostgresQL Mistakes and how to avoid them, and I'm the maintainer of
Postgres extension called PG Statiles.
Yeah, so I'm Daniel.
I'm based in the Netherlands, and I'm actually working for PINCAP on TIDV.
TIDV is a MySQL compatible database, so I really care about compatibility.
And I've also been working with companies that run MySQL.
I've been running MySQL for a long time.
So, I know where compatibility matters and where it breaks down.
I'm also working on the Wireshark projects on the MySQL protocol decoder.
So, also there, it's really important to be compatible and figure out what all the features
are that we are actually using on the wire and also to see how different connectors
and the server implementations all behave and talk to each other.
I was also awarded to MySQL Rockstar Award in 2023, so I'm well-verk and nice and old MySQL ecosystem.
So, let's go over the current landscape, right?
I mentioned briefly that compatibility is let's say point of contention.
There is a lot of Postgres and MySQL compatible databases out there.
The absolute success of open source databases has made this possible.
Postgres and MySQL are always the top two databases, and this has caused people to want to claim compatibility with them.
I think this causes a bit of user confusion.
When you see things like Amazon DCQL saying it's Postgres compatible.
When it's a non-relational database that doesn't even support sequences, triggers, views.
So, how is it compatible?
I think it's reasonable for users to be confused when marketing makes these decisions.
And I think this is also a source of brand dilution when something that doesn't work as well as Postgres says it's Postgres compatible.
I think that detracts from the actual merits of the Postgres scale project.
compatibility, of course, is not yes, no, right?
It's not true false, it's not an absolute.
You can be compatible with some aspects of a database and not compatible with other aspects.
Now, this is why we don't like 90% compatible or 99% compatible because the one feature that you want may not be there.
So, even within the same database, you know that different versions of the same database platform might not be 100% compatible.
And as matter of fact, Postgres says clearly that versions are not compatible between them, right?
So, if you have a Postgres 11 and a Postgres 12, they cannot talk to each other.
So, in that sense, they're not compatible.
But also, we're talking about features.
Features are getting added and removed all the time.
And this is what makes versions not exactly compatible with one another.
But what does Postgres or my SQL compatible actually mean?
So, from a user perspective, it's like, will my application be able to work with this database, right?
I bought a piece of software that says it works with Postgres, will it work with this Postgres compatible database?
What database drivers do I need?
What APIs can I use to connect to this database?
Very importantly, can I replicate out of this database to some other system?
And can I replicate from some other system into this database?
So, if it's Postgres compatible, is it replication compatible with Postgres?
And if I use a cloud offering, like, let's say from a hyperscaler,
I buy my SQL or Postgres compatible service, right?
Is that the same experience?
Is it the same as installing it?
Myself on, let's say, bare metal, will it work exactly in the same way?
And of course, can I use the existing tools and drivers that I am used to using with this database flavor for backups, for replication, for any sort of thing?
So, there are two perspectives.
I'm not going to talk about strict Postgres compatibility.
I'm going to talk about more the project governance aspect of this thing.
So, as the Postgres scale project on the Postgres scale community, we got together to discuss this issue,
because we started noticing that people were saying, we're compatible with your thing.
And afterwards Daniel is going to talk about the implementation side.
So, how they actually made sure that TIDB was compatible with my SQL.
Now, at PGCon for you, the biggest Postgres conference in the world, which took place in Riga in Latvia last year.
We had a session that was called the Stublishing the Postgres scale standard, what's Postgres compatible?
It was a working session.
We gathered opinions from the community with discussed things, and we came to some consensus before we left there.
So, the goal was to find a practical framework of criteria and tests for Postgres compatibility.
And people have said that Postgres is becoming so ubiquitous that it's becoming the new Linux,
because everything that's running on a database in production is most likely running on Postgres these days.
So, the word standard, right?
You all know that instead of having a pass or fail, like this is Postgres compatible, this is not Postgres compatible,
we will have a checklist of features, and they, of course, will have weights and importance,
like how important this is to be replication compatible versus have the exact same syntax for this function.
We also have the realistic use case that some Postgres services are managed, right?
So, let's say on a hyperscaler, you wouldn't get super user access.
You would get some user that they created that has some of the privileges, but wouldn't be a super user.
And, of course, you can't read the files from the disk.
You don't have direct access, you only have access to a database connection.
And also, we mentioned silent failures.
It's like, okay, you accept the command, create index, but does it actually create an index when you type it in, right?
So, everything that's in the Postgres documentation part two, SQL commands, I think that's all required,
even rarely used features.
There is implicit behavior in Postgres that you must understand, to understand compatibility.
Things like insert with a select statement that doesn't order by.
What does that do?
Yes, it guarantees that the order of the select will be exactly as you specify or they're by,
but it doesn't guarantee that the data will get written to disk in that order, right?
So, that's an implicit behavior that we understand the Postgres users, but it's not specified anywhere.
So, data types, of course, array by the array, JSONB and so on.
And, of course, we talked about feature dependencies.
So, you cannot have triggers without PLSQL, PLPGSQL, excuse me.
So, of course, you must be compatible with the transaction isolation levels,
the Postgres offers in order to claim compatibility with Postgres.
And, of course, when the software is the same behavior, if you claim to support the same isolation level.
Error codes are a good thing to have, you know, consistency with error codes, system catalogs,
like when you're using a monitoring tool, it attempts to connect to the PG catalog.
So, if the PG catalog isn't there in your compatible version, what is there to monitor?
And, a really important point, if your database allows identifiers that are 256,000 long, right?
And, then you can't backport that data into Postgres, because you've broken compatibility by adding a feature.
So, server string, it must be recognizable by existing tools.
Standard drivers must work.
Can you PG dump data in and out of the database in a standard way?
We said the execution plans don't need to be identical, but they need to do the same thing.
So, if you're using a partition table, it must be able to prune the same way that Postgres can.
So, replication, about the ability we mentioned briefly before, it must be bi-directional.
It must be observable, like from standard views like PG, stat replication.
And, of course, any vendor extensions must not break compatibility with vanilla Postgres.
So, you should be able to replicate two and from a standard Postgres node.
You may or may not support physical replication in the sense of taking the data files and dumping them on some other server.
You may or may not support hybrid clusters, like if vanilla Postgres node and a vendor Postgres node can be talked to each other.
That would be part of the same HA cluster.
And, of course, point in time recovery is a key Postgres feature.
And, for some of these things, including point in time recovery, you need access to the wall files.
So, we said the test suite must reside outside of the Postgres URL code base.
We're testing for compatibility against the specific versions, so are you compatible with Postgres 14?
Are you compatible with Postgres 17 and so forth?
Vendors, of course, must provide build targets because we don't know how they build their flavor of Postgres.
And, of course, lastly, if you fix a Postgres bug, that doesn't mean that the test.
If you fix a MySQL network protocol, it works, but like no one is going to be helped with this.
So, is that Postgres compatible?
Because it just doesn't network protocol.
I don't know.
Yeah.
So, the examples that I built are written with go MySQL, go to link that allows you to build your own MySQL protocol server.
And do whatever you want with the backend.
This is just like a fun project, but a cloud provider could do the same.
They could say, well, we have like a SQL server in the cloud, but we make it talk to MySQL network protocol.
Now we're MySQL compatible.
No, you're not.
So, you should be really careful with what are you calling MySQL compatible and what not.
So, TIDB is written in Go.
So, there are some things that are easy to do in Go.
MySQL is written in C, C++.
So, sometimes that makes things a bit weird because we want to implement exactly the same thing.
Our storage layer is actually written in Rust.
And one of the things that's really different is like if you create a table in MySQL, you use like engine equals inner DB.
Well, with our system, that really doesn't make any sense.
So, we accept the syntax and ignore it.
But we're really careful with accepting syntax and then ignoring it because that might break a lot of things.
Signantly, and that's not what we want, but in some cases to just get a functional thing.
You need this because otherwise, if you do a dumb promo MySQL system, you cannot load it.
And that's one of the important things to do.
Feature compatibility.
We are really open on what features we support, what features we don't support.
So, that comes back to the core and optional part that we talked about before.
Like, we do not do any geospatial.
We do have a vector support, XML, for example.
It's a nice feature, but no one is using it. Everyone is using JSON.
So, we're not supporting XML.
And I think for any database vendor, it's really important to be very open.
If you're compatible, like are you exactly compatible, mostly compatible, what features do you support and don't you support?
Because if you're actually running an application, only then finding out that's really a crappy user experience.
So, in TIDB, we try to be MySQL compatible, but we also do support sequences because sequences are very useful.
But that's not in MySQL. So, we implemented in a MariaDB compatible way, because MariaDB did implement sequences.
So, then you get a bit of like a Frankenstein solution.
But yeah, it's still a feature we want to implement.
And we cannot just say, well, we're not going to implement it because MySQL didn't.
We are not going to limit ourselves.
And I think the whole solution is still MySQL compatible, because you don't have to use this feature.
And it's not going to break replication, et cetera.
So, a few words on like MariaDB, they originally try to be a fully dropping replacement for MySQL.
They were using the same codebase, so you could just easily take your data files,
store MySQL, start MariaDB, everything would work.
Over time this changed, so now you have to export your data, import your data,
some new features that were added by MariaDB and MySQL are implemented in slightly different ways.
So, MariaDB is not fully MySQL compatible.
They have very good documentation on like, what is compatible, what's not compatible.
We did have to fork the MySQL connector J for some TIDB specific authentication mechanisms.
But most of our customers are using plain vanilla connector J because they don't,
they're not interested in this new authentication method that we implemented.
So, that's a place where we are compatible, but we have one optional feature that requires us to do something different.
So, as well as syntax and type friction, reserve keywords is always a pain,
because new versions of the database might introduce new keywords,
so there's certainly your statements that we're working with, a previous version, still working.
And when it comes to explain formats, that's one of the things where we decided not to be compatible with MySQL.
So, if you do an explain in TIDB, like to get the same outputs as MySQL would not really make sense,
because it's being executed in a distributed way, and like to try and morph everything to look exactly the same.
It just doesn't help anyone because you don't get the right information.
And also, the explain that's not anything that an application usually does, it's what a DBA does.
And they are the amount of information that you're showing is way more important than having exactly the same format.
For the JSON data type, my readb implemented this as just a text data field, and they have functions like JSON,
and if check constraints etc.
In mySQL, this was implemented differently with the UUID data type.
With the JSON data type, so the JSON data type actually holds your data, if you insert it automatically,
you have better dates etc.
And then MariaDB did the exact opposite with the UUID data type.
MariaDB as UUID data type, MySQL does not, but they have functions to change things.
So, this is a quick demo on the difference of the explain formats.
It's just a very simple query, so don't give you too much things.
I will just skip over the binlog.
Yeah, errors, limits, and bugs.
So one of the things is that, like, of course, we are a MySQL compatible database,
but we also have to have our own error messages.
We try to have the same error messages for the same situation,
but there might be new and different situations, but there's not really any, like,
like, reins of error codes that MySQL is saying that they're never going to use.
So that's quite difficult to actually avoid conflicts, but we try by just using a quite high number.
That was everything I had for now. Thank you.
I think we have time for questions.
I think we have a little bit of time for questions.
I'm the just side we don't.
So we don't have, you can reach us outside.
Yes.