 All right, welcome everyone. And the subject of our talk is support for Postgres protocol in Envoy proxy. Next slide, please. So my name is Krzysztof Pakulski. I'm software engineer at Tetrate. And I also contribute to Envoy project. Hello, everyone. I'm Fabrizo de Wajsmielu. I'm a Postgres research and developer for Congress Company. The Congress Company means on top of Postgres. I'm also a Postgres contributor for a long time. And this is my first contribution to Envoy project. So let's start with Envoy proxy. This presentation will have several parts. So in the first part, we will look towards Postgres protocol from Envoy point of view. So this is a simple connection model. At the go, it's connecting a client to the farm of servers. So on the left side, we have a typical client that is the application. And it really wants to connect to databases. And Postgres really requires a TCP connection between the client and the server. And that's what Envoy does. If you look at this picture, it's a really simple configuration. It's just a TCP proxy. And it provides the connectivity between the client and the server. So we can use extensive back-end features, which are provided by Envoy. So we can do load balancing on the back end. We can do health tracking. We can detect that one of the nodes is behaving in a really weird way. This is something we call outlier detector. And it works. So clients, we achieved what we wanted. We wanted to connect client and the server. You can also get some statistics. But on a very gross level, so you will only see that the client connected to a database. And that's all. You won't be able to see what was going inside of that session. So TCP stats will tell you how many TCP were open, TCP sessions were open, how many bytes were exchanged. There's also an access log which will tell you that pretty much describe similar things. So you know that the client talked to a database, but you don't really know what was talking about. So in order to know what was going on inside of the session, you need one extra component in this picture. And this is a Postgres filter. One thing you have to mention right now that in order to achieve all these things, we're talking about Postgres filter needs access to a clear text protocol. So it cannot be encrypted. We'll talk about later on what we do when it's encrypted. But when I talk about this, assume that it has a clear text. So from the connectivity point of view, it's really nothing different. So client can still connect using TCP to the server. And we still use the extensive features of Envoy on the backend. So we can still use load balancing, outlier detector, health checking. But now all of a sudden, Envoy can tell us more about what's going on inside of the TCP session. And this happens by introducing that extra filter, which is able to read the packet just before sending either to a data to the backend or either send back to the client. So now, it's not only that there is a TCP session which extends between the client and the server, but we can also look inside what kind of messages are exchanged between the client and the server. So you're able to see how many selects there were, how many updates and search errors. So it's not as a one long-lived TCP session, but Postgres filter will know what was going on inside. So one thing I have to mention here that the sequence of Postgres filter and TCP proxy filter is established every time client establishes a connection to a server. So like I said, it's a long-lived. And when it ends, that two filters are destroyed. That would be next slide, please. OK, so let's see what's going on inside. So on the TCP level and the data delivery, the TCP windowing mechanism and also buffering on the network are literally completely not aware that their Postgres messages pass on top of these. So a filter receives just chance of data. And it has to make sense of this. So before the filter can start processing it, it needs to reassemble entire Postgres message. And when it receives the data, which is sent by the client or the server, it has to see whether the data contains one full message. If it contains, that's all good. We can immediately start processing. But in the other situation that can happen is that the received data does not contain a message. So it has to wait until the next data arrives in order to reassemble entire message. And that requires some buffering between the fragments as they arrive either from client or either from the server on the opposite direction. So next slide, please. So here is a filter has a state. So it always starts in the initial state. And it stays in the state as long as the coder so-called likes the messages. So I'm saying like. So it's able to find the boundary when the one message ends and when the next starts. And it can kind of make sense what's passing between client and the server. And as long as the coder likes, we stay in the in sync state. There are two states. There are two situations when the coder can leave that state. So the first one state is normal state. I'm saying there's no error. And it's going to encrypted state. And it happens when client and the server negotiate that encrypted session. And basically, it's encrypted. And filters say, hey, that's nothing I can do. And it's just basically starts passing this without looking inside, without doing any deep inspection. So the other situation when the filter can leave in sync state is when something unusual happens. So either there was a corruption. And the coder says, you know, I don't really know what's going on here. And say, you know, I better start ignoring those messages. The other situation is when it can happen is that when administrator configures everything for Postgres, so there will be a Postgres filter, TCP filter configures everything, but starts passing the traffic, which is not really Postgres. And the biggest danger here is that the filter really looks inside of the Postgres message header. There is a four byte fields, which indicates the length of the message. And as I said before, a filter can start acting upon that message. And it has to keep it in a memory. If this is corrupted and tells you some outrageously large number, filter may try to allocate that much memory and can run out of memory. And the process will be killed. So by indicating and introducing out of sync state, we're trying to prevent that situation. Next slide, please, Fabrizio. So in addition to the producing statistics, the Postgres filter can produce a metadata. So let's try to explain here what the metadata is. So this is a SQL database. So there is an SQL query passing in the messages could be quite complex. And the really only database in SQL parser really understands this. So metadata is the way of describing the quite complex SQL query using just a few labels. And those labels might be whether this SQL query is read operation, write operation, on which database, on which table, just using three or four labels to be able to describe it in a concise way what this operation is about. And that metadata is attached to a request and it travels with data as it passes different filters. So if we put another filter like RBAC, we can program RBAC to reject or stop certain operations from being sent. So you may program it would be a good simple example, you can program it that it only routes reads operation on a specific table and stop all the right operations right at this moment. So you can have the program access controller right at the network level, even before it reaches a database. There probably might be other examples of using metadata that this one is simple enough to understand. So that would be it from invoice point of view. Thank you. So thank you Christopher. Now let's talk a little bit about Postgres SQL. Here, the idea is not to talk about a lot of great features but give you a clear picture of current stats of development side of Postgres, you know? So Postgres has more than 30 years of development. It's fully open source since the beginning and we have a Postgres license as a specific license based on BSD license. It's totally driven by community, I mean driven by Postgres SQL global development growth. There are no single company driven the development. Nowadays we have 29 core committers working for different companies and nowadays we have a thousand of peoples around the world testing and reporting issues all the day. And this, and here is the result of so many years of mature development. If you see in this picture, there are a lot of great use cases. Apple, Skype, Instagram, Twitch, Spotify, IMDB and there are a lot of missing, other great missing use cases in these images like Liftee, TripAdvisor, Meijing, GitLab, Atlasian and a lot of government different scales use Postgres today, you know? So it's a great, great open source database. We often call the most advanced open source database in the world on the Postgres community side. So let's talk more now about what we did here for Envoy Network Future for Envoy. Let's start talking about the protocol versions. Today, the Postgres wide format, it's a very stable and mature protocol without major changes since 23, lasted 17 years without any major change. So since the 7.4 version, it's the same version of the protocol and the server still supports the protocol, the first version of the protocol, you know? So there are a lot of other derived projects, derived Postgres projects that can benefit of this Envoy Network Future. I can say HeadShift from Amazon was built on top of Postgres many years ago, Aurora, RDS, Yuga ByteDB, KokoroDB, Greenplan and there are a lot of projects out there that implement the Postgres wide format so you can benefit for this Envoy Network Future. So about the protocol, there are a name called Phoebe Frame Format. Phoebe is a common term in Postgres world. That means front-end and back-end. It's same like a client and server or upstream and downstream, you know? And basically we have two types of packages that traffic between the client and the server. There are a startup package and a regular package, package. And the startup package, there are three fields. The first field is the length of the message. The second field is the protocol version and there are a payload with other needed information with parameters for start the connection. And the headguard package, we have a first field is a tag, which is one byte, one character, the length of the message and the payload with the necessary data about the message. And there are a special startup package called SSL Negotiation. That is the same as startup package, but the difference is when we start SSL Negotiation, we send to server a dummy protocol version. So Postgres start the SSL Negotiation using this special startup package has, we will see more in the next slides. So here we can see the message for a new connection. On the left side is for an encrypted connection and the right side is for encrypted connection. So here we send the startup package, server response without a request, it's okay. And optionally you can send a password and server response with a lot of parameter status, backend key data and finish it really for query that send the say to client, hey client, I'm waiting for you and you can send the next comment, a query, a parse message, independent of simple or extended protocol. And the difference when we start a encrypted connection is because we send a SSL Negotiation, it's a dummy startup package and the server will response yes or no if a server is able to start a TLS, if you have, if you have enough configuration, certificates and whatever. And if the response is S, we will start the SSL Handshake and after that the message flow will be the same as an encrypted connection. And here it's other other parts of the protocol when we negotiate with the server to for example run a simple query, okay. In Postgres, why protocol? Basically there are two different ways to interact with the server. There are simple protocol and extended protocol that was introduced in version 3.0 70 years ago, okay. Here, I will just explain the simple protocol because the idea will be the same for extended. The only difference is on extended, we process the parse message and in simple we process the query message. As you can see here, the client send a query message to the server and the server get this message with SQL statement side and do all the processing, I mean the parser, here write query, planning and execute and then start to response to the client. And the first response will be the row description that contains all information about the dataset. I mean the name of the fields, the length of the fields, data type, type a line in a lot for metadata about the results set. And after that start streaming all roles fetched by query, you know. And at the end of streaming data, the server response send back to the client by special message called common complete. And the common complete, all in the code, the postal network filter, the decoding part of the postal network filter, it's around of this common complete. Of course, there are a lot of different parts there to filter and whatever, but here is the trick to the code, the wire protocol because at the end of the execution of some SQL statement, Postgres response, the back end of the server response back to the client with a tag about what happens in the query. I mean, I run a select, I insert, delete, unpedated, explicit transaction command, begin, community, DDLs, create table and whatever. And here you can see from Postgres source code there are a long common tag list there with all common tags, okay? Oh, so let's start with the demonstration part of the presentation. And we will expect some results like this. We are able to create some dashboards in Grafana to get the metrics and as you have several graphs, interesting graphics without touch to the Postgres. I mean, zero configuration on Postgres site. Just by decoding the wire format, we can expose those metrics on the voice side. It will create, we will not create a lot of overhead on Postgres side because it's another way to get this information running some queries to against the Postgres server. Requirements is very simple for this demonstration. Just get Docker and Docker compose. You know, the architecture is very simple. There are five containers here and there are a special container that generates some traffic. I mean, create some dummy workload that we are using here, some housemade descripts and the PG bench. It's a tool, a Postgres tool for benchmark. And those, this traffic is sent to Envoy and Envoy send to another container with Postgres and Prometheus to grab, to scratch the metrics from Envoy and Grafana to collect the two exposed metrics to create some dashboards. So now I have all five containers. The most important container in this case is Envoy, for sure, here we are in debugging, debugging mode. And here you can see some interesting information. For example, here, here we have a front end messaging. Query send a simple query to the backend. I mean this SQL statement and the backend response with whole description. And after that, several data holes sending back the data to the client. No, and here there are a lot of, and another important thing is common, complete. Here, this is the tricky that we did for exposed metrics, drilling down, select a different kind of statement, drilling down for a different kind of statement because the Postgres response at the end of the, the data row is streaming this tag for the command. And here, what we are doing on the Envoy side is increase a specific metric about select statement when the command completes, arrive, pass through the network filter, you know. Ah, the other important thing is here, here we have a script to generate some traffic. It's very, very simple. We do a lot of random to run some benchmarks and run within different protocols, you know, a simple, extended, prepared. So we can generate a lot of different traffic in this small demonstration to generate some graph. Here is our Graphana running and we have a special dashboard with all graphs about the traffic, TPS transactions per seconds, hidden and righties, different types of statements, front-end and back-end messages and the number of sessions per second. So let's talk about it, what we plan for the future. So we want to do a few things and some of them are already under active developments. The first one is a necessary termination and we said that in order to produce all those statistics we need an access to clear text packet. But what happens if you have a requirement that the client has to, you know, traverse a certain portion of your networking and grip that fashion. So we want to terminate as a right at the envoy level. So traffic between the client and it's gonna be encrypt and then an envoy can actually look what's inside of the packet. So it's under active development. We also want to have a better rescuer parsing. The current parsing we use it's not sufficient in all cases, so we want to switch to a better one. Like I said before, Infabrizio was demonstrating here we can provide all different statistics in an aggregated way but they are also a requirement that if some clients are using one database and the other clients are using the other but they share network, we want to basically provide those statistics per database. So when we talk to promiscues and we extract data, we just want to somehow divide it and say that's for database A, that's for database B. And we also want to maybe introduce a routing based on query and that's I said when I was talking about the metadata that metadata can be used later on during the processing. Maybe you want to route some queries to a certain host because it's a ride operation and maybe to a different host because it's a read operation. So what's going on in a community? So there is a dedicated Envoy Slack. There is a bunch of channels for development users and there is one very specific dedicated to Envoy, sorry, to Postgres development. So there is a hash here and all the issues which are raised by users or by developers, they labeled as a Postgres issue and here is a link how you can find them in Envoy project. So I guess that would be all. Thank you on my side. Thank you everyone. Bye-bye. Bye-bye. Hello everyone. No, there's absolutely no question. So I guess we everything's super clear or everybody is super familiar with it, right? Yeah, but there are one question there. Is there any plan way to use this to support connection pooling? Yeah, that would be on the back end. There would be something similar to PG pool, I guess. Yeah. PG Bouncer. To replace PG Bouncer. PG Bouncer is more specific to Postgres pooling because on PG Bouncer we have different pooling modes. I mean, we have a session pooling, the full pooling mode that was shipped by the pool. And when you leave your session, you can use the session for another session but is driven by sessions. But we have two more specific pooling modes and more aggressive pooling modes. We have a transaction mode. I mean, when transaction finish, the connection is returned back to the pool and we can create a really funial between a lot of connections using just one session. We didn't plan to do nothing about that on the invoice side yet. I don't know if it's possible. Maybe yes, I don't know Chris, you are the guy, you are the invoice guy. No. Okay, so to answer specifically the question whether there is any plan. So actually, no, it's not in the plan. It popped up a few times but we didn't make any concrete decision. And, but anyway, maintain some open session on the back end so maybe we can leverage that. Definitely, we have to look at very technicalities, how we can leverage things which already exist. Yeah, so there is a question about the CockroachDB. So all the databases which support wire protocol which is compatible with Postgres, you can benefit from this filter. So Fabrizio mentioned a long list of that. I think Cockroach was one of those. You got by it would be another one, yeah. Yeah, exactly. Even Redshift, we work, Redshift was built on top of Postgres eight version. So at that time, we already have the current protocol version. Is this... No, no, it's purely C++. There is a directory, so if you go into the source code of Envo, you will see different extensions and network filters and you will find the Postgres filter over there. There will be a bunch of others like my SQL. I think Redis will be so, it's easy to find. Yeah. Yeah. Did you just share the link? Yeah. Thank you. All right. So, all right. Great. Thank you very much for listening to our presentation. Yeah. Thank you guys. Thank you. Bye-bye. Bye-bye.