 It talks about JDBC. My name is Dave Kramer. I'm currently working for Pivotal on the Green Plugging Database. It's an MPP database that came out of Postgres. We just open sourced it this year, or I guess last year at this point. I've been the maintainer of the driver since about 1999. So this talk was kind of born out of actually this convention last year. I actually heard somebody talking about how badly the driver performed because of how it was built. And I was kind of curious if that was the case. So I started doing some performance testing. And we'll find out what happened here. Allegedly, it was very CPU intensive because of all the inheritance. So things have changed a lot. We rewrote the driver at one point. I'll talk about a little bit how that happened and why that happened and why I didn't want to do it. I got talked into it. So let's go. So part of what we're going to do here is a little bit of history of the driver. Look at how it was laid out before, the way it's laid out now. Look at some underutilized features that people aren't aware of, I guess, and then some performance tips. And then we just released a new release. And there's some exciting new features in there that I'll talk about. So historically, the driver was written by a guy named Peter Mount in 1997. It supported one version of the JDBC spec, which was JDBC 1.2. Then you can see the etymology there all the way through 2017. Hopefully, we'll get Java 9 out. The important takeaway here is that the next iteration of the spec kept on adding functions to the specification. And what that does is it means that you have to implement all of these functions in your concrete implementation of the driver. And then we also were tasked with being able to support Java. At any given time, we support three of these. So currently, we support Java 6, 7, and 8. So that means your driver has to provide an implementation for the lowest component animator, which would be Java 6, which has the least number of functions in it, and then Java 7, which has a little bit more, and Java 8. So each one of them, these things are incremental additions to the interface. And you have to implement them all. And we try to keep that all in one code base, because we really don't want to have to deal with three different versions of it. So here's the source. Sorry. So before Maven, we use something called Ant. If you haven't been in the Java world for a long time, you won't know what that means. But it was sort of like a make file. But now there's something a little better called Maven, or at least some people think it's better. So before Maven, this is what the code looked like. You had JDBC2, JDBC3, JDBC3G, 4, and 4, 2. That was to produce a driver for 4, 2. So all of those classes extended each other. Each one of these things had abstract implementations and concrete implementations. And how we decided what was going to be built, for which version of the driver, was all done using filters in Ant. Ant had the ability to filter various pieces of code out, which was rather convenient, in some ways, I guess. The lion share of the code was in the JDBC2 package, right at the bottom. So there was one big class for doing statements, or for doing connections, or whatever. Almost all the code was in there. And then a little bit of decoration in sort of JDBC3 or JDBC4 that added the actual implementation for that particular part of the spec. So at the end of the day, if you wanted to put out a JDBC4.2 driver, you would have a connection object that extended an abstract 4, 2 connection, which extends. You can read the slide yourself. It extended a lot of classes. So that was the argument that somebody was making, this was going to be intensely CPU intensive. And this is what it kind of looks like in a diagram. You can see there was a lot of code, a lot of things to maintain. Not a whole lot of interest there, but it's not very exciting. So what we should have done, in retrospect, after we thought about it, which we didn't, obviously, why don't you just use minus, minus target in the Java 8 and compile previous versions with the latest compiler. The truth is, we just didn't think about it. It's kind of like when I first inherited the driver back in 97, it built one thing. And then somebody had actually had a couple of iterations. So it actually had a couple of abstractions in it. And it was kind of easier to just add another abstraction as opposed to sort of rethink it and just do it all. So we kind of went down that path. And then up until Java 8, this was possible. The JDBC spec is never supposed to add a class that a lower version of the JDBC driver can't load. Unfortunately, they added Java dot time. And one of the methods called set object requires you to have Java dot time as a argument. So that means that any of the previous JDKs would get a, sorry, would get a class not found exception if they tried to load that driver. So it turns out we couldn't do this even if we wanted to now. We could have done it sort of Java 6, Java 7, et cetera. But anyways, there was a lot of conversation on the mailing list about using Maven. I guess mainly because a lot of people were starting to use it, started to become more popular. It's a lot easier to include in your project. It's a lot easier to include to debug. The reason I was hesitant was, excuse me, the reason I was hesitant to do it as a maintainer of the driver, I kind of view the driver as being kind of like wheels on a car. They're not very sexy. They're not the engine. They're not the cool stuff. It's not like the Postgres server. But you can't drive a car without wheels. If the driver doesn't work, the cool server doesn't work either. So I was kind of hesitant to break it. And rewriting the driver essentially put a lot of risk in as far as breaking it was. Fortunately, we had enough tests in the project that I felt pretty safe taking on the project. The other mitigating factor was we now had Git. Git gives you ability to have a public branch for a lot of people. And so somebody was working on the branch. So we could actually all go out and test it. And so I had a fair amount of confidence that it would actually work. So the good part is, once you're done and actually the backup just a bit, it actually did work. And there was almost no bugs, surprisingly. Good credit to the guy who did it. So anyways, once you get this, once you get it into Maven, you have a dependency you can just bring into your project. We still have some of the same problems. They're just solved a little differently. Ant had filters to filter out which files are compiled. We use a little bit of another piece of Java code which pre-processes the files. And we just have this little if maven.project property in there and that filters out parts of the code. We still have some of the same problems because we have to support lots of versions of the JDBC spec. So we reorganized it in December 2015. All of the abstract class machinations have been removed. There's far fewer source files for all versions of JDBC. It reduces CPU load. There's a bit of an asterisk there which we'll get to. The real advantage of the project, of majorizing the project, the code is much simpler. So it's a lot easier for more people to get involved in the project. And we've actually seen that quite a bit. It's easier to debug because it's easier to load into an IDE. You just pull it in and boom, you've got it running. And this is what that previous, I showed you a previous slide with all the source files that it had. This is what that looks like now, which is obviously a lot simpler. I don't know if everybody was here for the keynote and the guy talked about convenience. And I had totally underestimated that. And just how much more convenient it was to use Maven. And it did turn out that as somebody maintaining the driver, I'm going to have to start thinking a lot more about that. So now I want to test the hypothesis that it's actually faster or slower using fewer classes. So I borrowed some code from a friend of ours, I don't know if you've ever heard of ToroDB. It's actually a Java program that talks Mongo protocol and stores the Mongo data into a Postgres database. It's very cool. So it's a little plug for him because I borrowed his code. It takes JSON and breaks it into relational data. You don't know the difference. It just runs a lot faster. So anyways, the table is really quite simple. There's just an integer, a text, and a text. That's the whole point of this slide. So what the tests do, basically selecting one number, selecting a text, a number and a text column, select a JSON string, select using a column number. I don't know, a little bit of a detail inside the JDBC driver, but if you use, the JDBC driver allows you to access something by name or by column. If you use name, it takes just a little longer because it has to look up the column. And finally, doing it without using, doing it inside a transaction, selecting a string inside a transaction, so using auto commit off. We ran the test suite for a number of different JDBC drivers, 1204, 1208 through 42.0.0, which we'll get to, talk a little bit about. I put this up just so you can see it. I'm not going to go through the code. The slides will be available. You can read the code. It's actually in the driver and the performance benchmark inside the driver. So here we go. Didn't do anything. Version 1204 is just about as fast as version 42.0. There isn't really any speed difference whatsoever. But it really is a lot easier to understand. We're getting a lot more people working on it. I'm getting a lot higher quality pull requests, like bigger features as a result. And the driver's not going to take credit for this, but this is the downloads over the last 12 months. See, this time last year, 200,000. This time this year, we're looking at 800,000 downloads. I attribute that more to Postgres, but I'll take credit for it. So that's sort of the history of why I started doing the talk. Let's talk about some things that really did improve performance. There are some four major things here, setting fetch size properly. We found there was two bugs that we found and one better way to insert large amounts of data. And we'll talk about each one of them. So here's the code that does the set fetch size. Again, I'll let you read this on your own. But basically, it's just various selects, large selects with different fetch sizes. The important part is that we do it inside a transaction. The set fetch size does not work outside of a transaction. It only works inside a transaction. That's more to do with how the server works than how the driver works. It uses a portal internally. But here's the time it takes to fetch 1 million rows. And you can see that the knee occurs right around 100. The set fetch size at 100 drastically. And the time on the left is in seconds. And it's 1 million rows. So at 10, it's about 30 seconds to fetch 1 million rows. Or fetch size for 10. And fetch size 100, it's about 6. And then it drops off, but not dramatically. It is affected by the row width. I'll talk about that a little bit. Obviously, if the width is much wider, it's going to change things. I'll talk about it a little bit, but there is a patch in there doing an adaptive fetch size based on width. It's not in yet, though. So that's the one thing that we did actually get quite a bit of performance benefit out of. There was another problem that we saw a lot of reports of this on the mailing list. And people were inserting large numbers of batches. And all of a sudden, the whole system would lock up. We would get a deadlock. And it wasn't a deadlock inside a Postgres. It was a deadlock more or less on the network. So ideally, what we'd want to do is just be able to insert a large number of rows and parse the statement once. That's what parse S1 means, parsing a statement on the server side. And then bind an exec, which is a part of the protocol inside a Postgres, end times and then deallocate. So you essentially just parse it, run through it, a million times, and then deallocate the statement. So what we found, though, was what would end up happening is the driver is pushing all this data to the server. And it hasn't received any responses yet. And it's because binding an exec only sends data. It doesn't receive it just the way the protocol works inside the driver. And the server on the other side, he's been receiving all these data. So he's sending all the acts back. And we're sending and it's sending and neither one is receiving. So every so often we have to sync. So every once in a while now, about every 64K of data, we stop and we synchronize the data. So that's one thing that we found in the last little while that improved our fixing batch inserts. So the other thing, a little bit of background on how to insert a lot of data, what are the options for doing it? The common way that people tend to do is just insert in a loop and they insert and execute, insert and execute. Then the other way that the JDBC spec allows you to insert in batches. And ideally we would like to just be able to insert in batches and insert a thousand and then execute the batch, insert a thousand, execute the batch. Unfortunately, the way the driver was originally written, it really just did what it does in the first row there, inserts and executes each time. There's another way you can do it, the SQL standard allows you to actually insert and then, so insert into foo and name two columns and then put the values in there and then separate n rows by parentheses. You could do that, you'd have to write your own code to do that. Or you can write a copy, copy can also do it and postgres copy into the table from standard in. So the test that I wrote does a couple of things. It inserts a batch one row at a time, so each row insert after n rows execute the batch. In normal mode, this isn't any faster than just inserting n times. And then another part that we wrote some code that actually did the hand rolled part where we're inserting multiple values per row. So we can insert a batch n rows at a time. This is considerably faster and the reason it's faster is because more data is inserted per execution and less statements are being run. And then the advantage in this version of the new version of 42.0 is that one of the guys has contributed some code which changed insert batch to do the rewrite internally in the driver. So it actually executes. If you do the insert batch with insert rewrite on, it mimics this inside the driver. So it rewrites each insert so that you're inserting multiple values per batch. Makes sense? And then copy is basically the same thing. You create a string that you copy in to the driver. There's also a, this is a bit of a weird thing for Postgres. You can create a structure that allows you to insert. So anyways, here's some of the test results. So the number of rows is fairly high. You can see that a plain insert is relative, is pretty slow. It's actually, you know, 50 times slower than either insert batch or insert rewrite. As you get down to a smaller number of rows, though it doesn't really change that much. And then using different batch sizes though with insert rewrite, we can see that insert rewrite, even with one row, or with the batch size equal to one is quite fast. Any questions about that? Before I move on. So one thing we found, another, in the middle was somebody had actually disabled batching completely by creating a, there was two constants inside the driver that were set to the same thing and effectively got rid of our batching completely. So that's sort of the performance benefits that we were seeing with set batch, insert batch, set fetch size, that kind of thing. So one of the things that, so some of the things that people do that are not very good is, you know, they open a connection, prepare a statement, select, and then they execute the query and then close the statement. And then actually close the connection. So because Postgres is process based, opening a connection is a fairly heavyweight operation and actually it's going to get a lot worse because we've just implemented another authentication mechanism called SCRAM, which this driver doesn't have it and it's coming out in version 10, but that will make opening a connection considerably slower. So doing this will be much worse. So without using a connection pool, creation is a very heavyweight operation and it's going to get a lot worse. And then, sorry? So the question is, is it better to use a Java based connection pool or is it better to use PG Bouncer? That's a very interesting question which is very difficult to answer. If your application is purely Java and there's nothing else talking to your database and you don't have any other reason for using PG Bouncer, then I would say yes. PG Bouncer has a lot of features in it that can't be replaced by a Java only connection pool. PG Bouncer allows you to do things like pause the connection pool, reconfigure the connection pool to a different server, start the connection pool back up again and your application will never know. That can't be, I've never seen a Java connection pool do that. People, so I used to think that Java connection pools were the only way to go but then I've been doing some DBA work where they have lots of different systems that were not only Java, they had batch systems, they had stuff written in Perl, they had stuff written in Java, Python stuff, so having a non-Java based connection pool kind of made more sense. But I would probably put a Java connection pool in front of the one Java that talked to the PG Bouncer. As I said, it's not a simple, there's no simple answer to that question. Yeah, so without the connection pool or without using a connection pool you don't take any advantage of any of the caching that we have whether it's statement caching or connection caching. So the better solution here is open a connection, prepare the statement. So one thing that's, this is also a little under the covers thing. There's a, Postgres has a notion of a named statement and unnamed statement. An unnamed statement is sort of a default statement that gets created when I do a create statement or prepare statement. And it's very easy to prepare it. And if you do a prepare statement once and you do a prepare statement again, they're both unnamed and they just overwrite the other one. After five executions we actually create a name statement. And the biggest difference is after we name the statement, after five executions we will switch from text mode in the protocol to binary mode. So in binary mode we can receive data a lot quicker because we don't have to parse the data. And the reason we wait till five, or actually it's configurable but it's defaults to five. The reason we wait till five statements is after five statements we figure you really want to reuse the statement. The other piece of the puzzle here is that it takes an extra trip, an extra round trip to describe the statement so that we can get the types of the columns that are coming back. So we don't wanna pay the price for the described statement if you're only gonna use it once. Now if you all went on the hackers and asked them for a protocol change so that you could tell the server ahead of time that we want these particular types to all come back as binary, that would help us. We wouldn't have to be able to do that. That's a request that we have for the protocol change. That make sense? Yeah? Okay. So then never close a statement if possible. Ideally, if you could run your code in such a way that you never close a statement, that'd be the best. Little bit of querying. There is a query cache inside the driver. There are some sort of little things that will invalidate it using generated queries from code that generates its queries using some kind of ORM, for instance. Well, it won't be able to use them because the hash for the query changes. If you don't use place markers, so inserting and actually giving the values in there, it can't use the statement cache either. So make sure you use place markers. And changing the type of the parameter. It's kind of possible to change the type of the parameter by doing something like this. You're setting the parameter one to an int, and then because set null works for all different types of parameters, you can actually change the type of the parameter by changing it to a bar chart, and then you invalidate the cache. We've obviously, we've all seen this. This example didn't come out of thin air. Somebody actually did this, and they were wondering why it wasn't using the cache anymore. So as I said, less obvious issues. The server prepare is activated after five executions. You can change that. You can just say prepare threshold is equal to five, or you can set it to whatever you want. There's a little trick inside. You can force binary by setting it to minus one. If you read my test code, you'll find that in there somewhere. So after five executions, we switch to a names statement and start using binary. We don't use binary for everything. There are some things that are hard to use binary for, such as timestamps, and set fetch size. It only works inside a transaction. Make sure it's set above 100. And if you think about it, we've seen this on the, I've seen this comes up like once or twice a year. Somebody comes up and says, I did a big select on a million rows, and I got it out of memory error. You can't read all that data into your two gig of memory on your machine. And we are looking at putting in adaptive fetch size, as you mentioned. It's coming in probably next version, and it will try to change the fetch size based on the width of the row. I'm running a little light on time, so I'm gonna try to... So, any questions on the performance or issues before I go into the new releases? Just before I move along, is there a change in pace here? No? So we changed the version number. As you can see, it went from 9.4.1212 to 42.0.0. The reason we did that, we knew that Postgres was changing to 10. So we thought it was an opportunity time to change our numbering scheme as well. We chose 42, sort of at random, other than the fact that it's the answer to the question. There was a lot of confusion. We would get this question on the mailing list a lot of times. What version do I use? For which version of the server? Is 9.4 gonna use that for 9.3, or should I use a 9.3 driver for 9? Basically, the answer was always use latest driver. So we completely divorced ourselves from the server version number. And we also introduced semantic versioning. That's also becoming sort of apropos. Sorry? That is absolutely available. 42 is the latest driver. Yep. Yes, you will have to switch to that. Support notable changes. Support drop for versions before 8.2. We had a handwritten logger and we replaced it with Java.util.logging. I'm not sure that's a good idea yet. I'm the jury's still out. We've had some issues with it, but I guess we're moving forward in that direction. But the most exciting thing, whoever was in the logical decoding stuff that we've seen in a few other presentations, we've added the replication protocol API for inside the driver. We now have it inside the driver. So let's go quickly over the first two because the last one's much more exciting. So you can set up logging just using the URL or our properties file. The root logger is org.postgresql. There's really only two, or sorry, three logger levels off debug and trace. They correspond to fine and off finest and finest. Logs, the real advantage of this is for people that are running Java application servers such as Tomcat, Jetty, Glassfish, et cetera, they can log into their container logging as opposed to, our logging was really meant for debugging the driver. It was really just went out to standard out. How many people are familiar with what logical replication is or isn't? Everybody knows what it is? Oh good, I don't have to do that part of this. Cool, so in logical replication in the driver, these are the high level steps that you have to actually do to create a replication to read changes inside the driver. To create a replication slot, or replace, excuse me, create a replication connection which is somewhat different than a normal connection. It has to be told to be replication. You have to create a slot and then read the changes, send the confirmation of the changes read and then go to read more changes. That's the overview, big picture kind of you. How do we create a replication connection? Basically the same as a regular connection. The only two things that have to be done is you have to set the property replication, sets it to database. That instructs the wall sender to connect to the database in the URL. That's how you connect to the replication stream. And the other one is you have to set query mode inside the driver to simple because the replication protocol does not understand the extended protocol which is what the driver uses if it's not set to simple. Those are the two biggest differences between any other connection that you make. We create a replication slot. So the biggest thing here is that you have to give a slot a name and you have to tell it what output plugin to use. So the output plugin is how the transaction is decoded and how the information is sent to you. In this particular example, it uses the test decode output plugin which is what comes with Contrib or for sure is that what comes with Contrib burn? Anyways, then there's a couple other ones you can use. So any unique name will work but the end the output plugin has to be previously compiled and installed in the driver or in the server, sorry. Makes sense? Okay, then we create a replication stream. So the stream is just a, this is completely outside of the JDBC spec. Get the replication API. The stream, tell it that it's logical. Give it the slot name that you provided before and with start position LSN. LSN is the logical sequence number. You can start anywhere in the logical sequence number. You can start for one previously. You can start current that you can give it minus one which means start now. And then with slot options or a number slot options and the slot options are specific to the particular output plugin that you use. Okay, so the test decode output plugin understands this include XIDs. The wall to JSON has some other ones, et cetera. So that's specific to the plugin. And this is how you read the changes. You just read from the stream. The data will be in a byte buffer. After reading the data, send the confirmation message. And this code is available. So on my GitHub repo, feel free to rip it off, put it, make it into something fantastic, do whatever you want with it. So I'll BSD license, have at it. The important part here, the other piece here is that we, at the end you have to provide feedback to tell the last two lines are setting the feedback. And that tells the logical decoding mechanism on the server that you've successfully read the particular piece of information or read the transaction you're done with it. It can remove it out of the wall logs. Here's the scary part. So that's the logical decode stuff. I'm just gonna run it. And it actually does a, you'd think I would have done this before, eh? Okay, so this is the wall to JSON plugin and the output. Can you all see that on the right hand side? Chose it, it was an insert. It shows the XID number. It shows the X log position, the table, everything else. Now on the left hand side, I'm connected to the database and I'm going to change something. And you can see it was echoed on the right hand side. So now we can do real change data capture inside the driver, inside your Java. No triggers. You don't have to worry about where you left off. It'll all be persisted on the server, et cetera. Which one are you talking about, sorry? There's the call. It is blocking. You can do it without blocking though. Oh no, sorry, this one is not blocking, sorry. So the question is, was it blocking? But halfway down, I'm at line 144 there, if you can see it, about halfway down the screen. Goes into, well true, does stream.readpending. Readpending is non-blocking. If it comes back with null, it goes back to sleep. If it doesn't, it processes it. Sorry? So the question was, is it required to send the information back? Is that, yes, it's very much so required to send the information back. If you do not send the information back that you've consumed the data, it will keep that data, it'll build wall logs. One other thing. Yeah, and you know, run out of disk space. So yes, you do have to, if you have your replication slot open, you have to consume it. Yep, okay. Okay, wrapping up, credit where credit is due. Well, I didn't write most of this stuff, I just take credit for it. So no, sorry, seriously. Much of the optimization work on the driver was done by a guy named Vladimir Stitnikov. He's, his normal job is a performance engineer for Java. So he's very familiar with how to make things go fast and how not to make things go fast. Most of, if not all of the work to convert the bill to Maven was done by Stephen Nelson in the UK. Rewriting batch statements was done by Jeremy Whiting, who's worked for Red Hat. And the replication support was provided by Vladimir Gordechuk. That's pretty much it, questions? Yes. The question is, is there any way to bring the, synchronize the table inside the Java application? The answer is no. We're not doing, we're not doing replication. We're doing change data capture. So there are, there have been a couple of talks already on logical replication, and they all have that, that facility is already there. What you're really trying to solve in the Java world, which is, I forget your name, what is it? You guys, from simple, they're actually doing this. Except they wrote their, the piece that I showed you, they wrote in C. Now the rest of their code is all written in Scala. So I'm told that there's gonna be an effort to rewrite everything in Java. But what they're doing is, the front end is capturing transactions in Postgres. And then they use change data capture to push the data into Kafka, which then pushes it all over to the rest of their application, which is, if I remember, talk rate, it was into Redshift and into S3. So, this is a tool in your toolbox. This is not a solution. I understand. Yes, sir, set prepared thresholds minus one. Don't do this. There's a reason why we chose not to do it. So the reason is, if you're not going to continually use the statement in binary mode, or if you're not gonna continually use the statement over and over again, you're gonna take another round trip back to the server just to find out what the types are. So if it's a very simple query, we can do it in two trips, like a request receive. If you force it to binary, there's gonna be a request, a describe, and then a receive. I don't have a strong opinion about any of them. I hear Kari CP is pretty good, but I don't have a strong opinion. As I said, connection pooling is complicated. It depends more on your architecture than anything. Another question, sorry, go ahead. 10, yes, yes. Works on 9.4 and up. The mechanism that this uses has been available since 9.4. Logical replication was not possible till 10. Sorry? I mean, BDR, you mean? This will work with BDR. Yep. If this was, so this will work back to 9.4. I can see where that's misleading. Yeah, this is not replication, but we're using the replication stream. Right. Yep, JMH, the Java benchmark. Yeah, if you look at the, if you go to the repo for the driver, all of our benchmark tests are in there. Okay. Yep. And the test that I borrowed from ToroDB, he actually uses PGSTAT statements to figure out how much time is actually spent in the server and then some tracks from the JVM. Is there another question over here or somewhere? Go ahead. It won't, no, it's not gonna, it's not F-syncing that's the problem. If you do not, so if you don't consume something and you keep the slot open, the server will not remove the wall log until you consume it. That's how, that's actually a bonus. Because now that means the log is durable, right? So you can, your thing could crash and you could come back up and say, okay, start back where I left off and continue to read all the changes. Yes. I am not familiar enough with spring data to answer that question. That's it. Thank you. Thank you.