{"id":79893,"date":"2020-05-01T13:43:12","date_gmt":"2020-05-01T11:43:12","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov"},"modified":"2020-05-01T13:43:12","modified_gmt":"2020-05-01T11:43:12","slug":"postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","title":{"rendered":"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>I invite you to read the transcript of Vladimir Sitnikov's report from early 2016, \"PostgreSQL and JDBC: Maximizing Performance.\"<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9d94c8a024bd2821e431c525aae0127d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/030666abda53aa388b1cb1d0c46a7524.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Good afternoon! My name is Vladimir Sitnikov. I've been working at NetCracker for 10 years, primarily focusing on performance. Everything related to Java and SQL is what I love. <\/p>\n<p><\/p>\n<p>Today, I will discuss the challenges we faced at the company when we started using PostgreSQL as a database server. We primarily work with Java, but what I will talk about today is applicable in other languages as well. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b01a214d782c7e32797a6c6466457979.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>We will talk about:<\/p>\n<p><\/p>\n<ul>\n<li>data retrieval. <\/li>\n<li>data storage. <\/li>\n<li>as well as performance. <\/li>\n<li>And the pitfalls that lie beneath. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/57da0c6aa4bb62e1beb77160f5aee7e7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Let's start with a simple question. We are selecting a row from a table by the primary key. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/d5fcb1172aef402a87064498bf8a5218.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>The database is on the same host. And this process takes 20 milliseconds.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/103b7f9736b82a3313491b2f0d2a5824.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Those 20 milliseconds are quite significant. If you have 100 such requests, then you are wasting time in seconds to process those requests.<\/p>\n<p><\/p>\n<p>We don't like that and look at what the database offers us for this. The database provides us with two options for executing queries. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b467e86a0f8c4c32cea182fe27a28e6e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>The first option is a simple query. What's good about it? We just take it and send it, and nothing more. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7e333a62ba68feb0781ff96e39486591.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/pgjdbc\/pgjdbc\/pull\/478\">https:\/\/github.com\/pgjdbc\/pgjdbc\/pull\/478<\/a><\/noindex><\/p>\n<p><\/p>\n<p>The database also has an extended query, which is more sophisticated but more functional. You can send requests separately for parsing, execution, variable binding, etc. <\/p>\n<p><\/p>\n<p>Super extended queries are not the subject of our current report. We may have a wishlist for the database that is somewhat formed, i.e., things we want that are not possible right now or in the near future. So we just write them down and will keep asking the main people.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/19094729074fd28c5b9f236833a9a266.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>What we can do is a simple query and an extended query.<\/p>\n<p><\/p>\n<p>What is the peculiarity of each approach? <\/p>\n<p><\/p>\n<p>A simple query is good for one-time execution. Execute it once and forget it. The problem is that it does not support binary data format, making it unsuitable for high-performance systems.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/062c0e45cefd91ece331a306a7651bde.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Extended query helps save time on parsing. This is what we did and began to use. It has been extremely helpful for us. There's not just savings on parsing; there's also savings on data transmission. Transmitting data in binary format is much more efficient. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/6df83a2f3736568668cf9d74de5d9758.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Let's move on to practice. This is what a typical application looks like. It could be Java, etc. <\/p>\n<p><\/p>\n<p>We created a statement. Executed the command. Created a close. Where's the error here? What's the problem? There are no problems. That's how it's written in all the books. That's how you should write. If you want maximum performance, write it this way. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/6923293946dec45b3fd508235728ab95.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>But practice has shown that this doesn't work. Why? Because we have a method called 'close'. And when we do this, it ends up being like a smoker working with the database. We said 'PARSE EXECUTE DEALLOCATE'.<\/p>\n<p><\/p>\n<p>Why all these unnecessary creations and unloading of statements? They're of no use to anyone. But usually, in PreparedStatement, when we close them, they close everything in the database. That's not what we want. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5a357a209e414024c437d042f600f251.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>We want to work with the database as healthy individuals. We prepare our statement once, and then we execute it many times. In reality, many times in this context means parsing it once for the entire application's life. We use the same statement ID for different REST calls. This is our goal. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ac4aed702a624ad9f9addc4362daf760.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>How do we achieve this? <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/0f101d890d1a2af8e99f8a3a8a06fd5a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Very simply \u2013 we shouldn't close statements. We write it this way: 'prepare' 'execute'. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/0b3fd8fd4f5861d4a76cad5a8421ddad.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/724646bfc55b5e2b611b1584ca8ba6aa.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>If we run something like this, it's clear that something will eventually overflow. If it's not clear, we can measure it. Let's write a benchmark with such a simple method. Create a statement. Run it on some version of the driver and see that it crashes pretty quickly due to a loss of all the memory we have. <\/p>\n<p><\/p>\n<p>It's clear that such errors are easy to fix. I won't talk about them. But I will say that in the new version, it works much faster. The method is meaningless, but nonetheless. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b232a6205e708c70f52be0024bbe9980.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>How to work correctly? What do we need to do for this?<\/p>\n<p><\/p>\n<p>In reality, applications always close statements. All the books say to close them, otherwise memory leaks occur. <\/p>\n<p><\/p>\n<p>And PostgreSQL cannot cache queries. Each session needs to create its own cache. <\/p>\n<p><\/p>\n<p>And we also don't want to waste time on parsing. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/85b9574938aa6e6bf59c956e3728bdc3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>And as usual, we have two options. <\/p>\n<p><\/p>\n<p>The first option is that we take it and say, let's wrap everything in PgSQL. There is caching. It caches everything. It should turn out great. We looked at this. We have 100,500 queries. It doesn\u2019t work. We refuse to manually turn queries into procedures. No way. <\/p>\n<p><\/p>\n<p>We have a second option \u2013 to take it and build it ourselves. We open the source code, start building. We build and build. It turned out that it\u2019s not so difficult to do. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/501620b014800406167d20668baef709.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/pgjdbc\/pgjdbc\/pull\/319\">https:\/\/github.com\/pgjdbc\/pgjdbc\/pull\/319<\/a><\/noindex><\/p>\n<p><\/p>\n<p>This appeared in August 2015. Now there is a more modern version. And everything is great. It works so well that we don\u2019t change anything in the application. We even stopped considering PgSQL, that is, this was enough for us to reduce all overhead to practically zero. <\/p>\n<p><\/p>\n<p>Accordingly, Server-prepared statements are activated on the 5th execution to avoid wasting memory in the database on each one-time query. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/8ee95e71f92187941989ad0c18ece0c0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>You might ask \u2013 where are the numbers? What do you get? And here I won\u2019t provide numbers because each query has its own.<\/p>\n<p><\/p>\n<p>Our queries were such that we spent about 20 milliseconds on parsing for OLTP queries. There was 0.5 milliseconds for execution, 20 milliseconds for parsing. The query was 10 KiB of text, 170 lines of plan. This is an OLTP query. It requests 1, 5, 10 rows, sometimes more. <\/p>\n<p><\/p>\n<p>But we absolutely didn\u2019t want to spend 20 milliseconds. We reduced it to zero. Everything is great. <\/p>\n<p><\/p>\n<p>What can you take away from this? If you have Java, then you take the modern version of the driver and enjoy. <\/p>\n<p><\/p>\n<p>If you have some other language, then think \u2013 maybe you need this too? Because from the standpoint of the end language, for example, if PL 8 or you have LibPQ, it\u2019s not obvious that you are wasting time not on execution but on parsing, and this is worth checking. How? It\u2019s all free. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/fda6b1c1b126cb85c970e42335e1dc24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Except for the fact that there are errors, some features. And we will talk about them right now. Most of it will be about industrial archaeology, about what we found, what we stumbled upon. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9a3b383f83c5e227cd02952c5825b64f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>If a query is generated dynamically. That happens. Someone concatenates strings, resulting in an SQL query.<\/p>\n<p><\/p>\n<p>What\u2019s wrong with it? It\u2019s bad because in the end, we get a different string each time.<\/p>\n<p><\/p>\n<p>This different line needs to recalculate hashCode. This is indeed a CPU task \u2013 finding a long query text in even an existing hash isn't easy. Therefore, the simple output is \u2013 don't generate queries. Keep them in a single variable. And enjoy.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/bcd4f729204cecdb572a98f357bcc33f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>The next issue. Data types are important. There are ORMs that claim any NULL is fine, just take any. If it's Int, we use setInt. But if it's NULL, let's assume it will always be VARCHAR. In the end, what's the difference with NULL? The database will understand everything on its own. But this picture doesn't work. <\/p>\n<p><\/p>\n<p>In practice, databases care a lot. <strong>If the first time you declared it as a number, and the second time as VARCHAR, you can't reuse Server-prepared statements. In such cases, you have to recreate our statement.<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/552d9c2e25cf9abbdfef12c98d027740.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>If you are executing the same query, monitor that the data types in the column do not get mixed up. You need to keep an eye on NULL. This is a common mistake we encountered after we started using PreparedStatements.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5d73c8f5dfa281c3bda962fc3e5edd84.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Okay, we turned it on. Perhaps we took a driver. And performance fell. Everything went bad. <\/p>\n<p><\/p>\n<p>How does this happen? Is it a bug or a feature? Unfortunately, it's unclear whether it's a bug or a feature. But there's a quite simple reproduction scenario for this problem. It caught us completely off guard. It involves querying literally from a single table. Of course, we had more such queries. They usually included two or three tables, but there's this specific reproduction scenario. Take any version of your database and reproduce it.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/fa0fdd037c2eaa31667d5a527bb71f9c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/gist.github.com\/vlsi\/df08cbef370b2e86a5c1\">https:\/\/gist.github.com\/vlsi\/df08cbef370b2e86a5c1<\/a><\/noindex><\/p>\n<p><\/p>\n<p>The point is that we have two columns, each indexed. In one column, there are a million rows with the value NULL. And in the other column, there are only 20 rows. When we execute without bound variables, everything works fine. <\/p>\n<p><\/p>\n<p>If we start executing with bound variables, i.e., we execute the sign '?' or '$1' for our query, what do we ultimately get?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e91c397796c7ddcbade3bc090719e9d1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/gist.github.com\/vlsi\/df08cbef370b2e86a5c1\">https:\/\/gist.github.com\/vlsi\/df08cbef370b2e86a5c1<\/a><\/noindex><\/p>\n<p><\/p>\n<p>The first execution - as expected. The second - a bit faster. Something got cached. The third, fourth, fifth. Then bam - and it just goes like that. And the worst part is that this happens on the sixth execution. Who knew you had to do exactly six executions to understand what the actual execution plan is?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/893c955bc1e2e15a6986690e516f72a9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Who is to blame? What happened? The database contains optimization. It is somewhat optimized for a generic case. And accordingly, starting from some point, it transitions to a generic plan, which, unfortunately, may turn out to be different. It may be the same or it may be different. And there is some threshold value that leads to such behavior. <\/p>\n<p><\/p>\n<p>What can be done about this? Here, of course, it's more complicated to make assumptions. There is a simple solution that we use. This is +0, OFFSET 0. Surely, you're familiar with such solutions. We just take and add \"+0\" to the query, and everything works well. I will show it later. <\/p>\n<p><\/p>\n<p>And there is another option \u2013 to take a closer look at the plans. The developer must not only write the query but also say \"explain analyze\" six times. If it's five, then it won\u2019t do. <\/p>\n<p><\/p>\n<p>And there is a third option \u2013 to write a letter to pgsql-hackers. I wrote, though it's still unclear whether this is a bug or a feature.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/8dc8d3a78e0b794e1ceba20f6ca914ae.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/gist.github.com\/vlsi\/df08cbef370b2e86a5c1\">https:\/\/gist.github.com\/vlsi\/df08cbef370b2e86a5c1<\/a><\/noindex><\/p>\n<p><\/p>\n<p>While we think about whether it's a bug or a feature, let's fix it. We'll take our query and add \"+0\". Everything is fine. Just two characters, and you don\u2019t even need to think about how it works. It's very simple. We simply forbade the database from using the index on this column. We don't have an index on the column \" +0\" and that's it; the database doesn't use the index, and everything works well. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5261d80d084786a15b9764f6367014cd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>This is the rule about six explain analyses. Currently, in recent versions, it needs to be done six times if you have related variables. If you don't have related variables, then we do it this way. Ultimately, this specific query fails. It's not complicated.<\/p>\n<p><\/p>\n<p>It may seem like it just keeps happening. There's a bug here, a bug there. In reality, there are bugs everywhere. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/674dc7e7b4624baa5c79eee9e1e89db0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Let's take another look. For example, we have two schemas. Schema A with table X and Schema B with table X. The query is to select data from the table. What will happen? We will get an error. We will experience everything mentioned above. The rule is \u2013 bugs are everywhere; we will encounter all of the above.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/dd6f544bb9e930c39e36523c9afe6003.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Now the question is: \"Why?\". It seems there is documentation stating that if we have a schema, there is a variable called \"search_path\" that indicates where to look for the table. It seems the variable exists.<\/p>\n<p><\/p>\n<p>What is the problem? The issue is that server-prepared statements do not suspect that someone might change the search_path. This value remains somewhat constant for the database. And some parts might not pick up the new values. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/f738301606d84d72bfa7cc5b568c0df8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Of course, it depends on the version you are testing. It depends on how significantly your tables differ. Version 9.1 will simply execute the old queries. Newer versions may detect discrepancies and indicate that there is an error.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4761cd92835b94acbf9d6c9d21817316.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/message-id\/CAB=Je-GQOW7kU9Hn3AqP1vhaZg_wE9Lz6F4jSp-7cm9_M6DyVA@mail.gmail.com\">Set search_path + server-prepared statements =<br \/>\ncached plan must not change result type<\/a><\/noindex><\/p>\n<p><\/p>\n<p>How do we fix this? There\u2019s a simple rule \u2013 don't do that. You shouldn't change search_path while the application is running. If you must change it, it's better to create a new connection.<\/p>\n<p><\/p>\n<p>We can discuss this; that is, open it up, discuss, and write more. Perhaps we can convince the database developers that when someone changes a value, the database should inform the client: 'Look, your value has changed. Maybe you need to reset the statements and recreate them?'. Right now, the database behaves quietly and doesn't inform at all that some statements have changed internally. <\/p>\n<p><\/p>\n<p>And I want to emphasize again \u2013 this is not typical for Java. We will see the same thing in PL\/pgSQL one-to-one. But it will reproduce there.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a4e7b201d0c0aa4a0092d2b6ff152df7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Let\u2019s try to select data again. We\u2019re selecting and selecting. We have a table with a million rows. Each row is around one kilobyte. Approximately a gigabyte of data. And we have a working memory of 128 megabytes in the Java machine. <\/p>\n<p><\/p>\n<p>As recommended in all books, we are using streaming processing. That is, we open resultSet and read data from it gradually. Will this work? Will it run out of memory? Will it read a little bit at a time? Let\u2019s put our trust in the database, trust in Postgres. Do we trust? We don't. Will we face OutOfMemory? Who has faced OutOfMemory? And who managed to fix it afterward? Has anyone fixed it? <\/p>\n<p><\/p>\n<p>If you have a million rows, you can't just select them like that. You must use OFFSET\/LIMIT. Who supports this option? And who supports the idea that you should play around with autoCommit? <\/p>\n<p><\/p>\n<p>Here, as usual, the most unexpected option turns out to be the right one. And if you suddenly turn off autoCommit, it will help. Why is that? Science doesn\u2019t know. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2e5d9d2a8450a9069155de86a8ec387a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>But by default, all clients connecting to the Postgres database fetch data entirely. PgJDBC is no exception; it fetches all rows.<\/p>\n<p><\/p>\n<p>There is a variation on the FetchSize theme; that is, you can specify at the individual statement level that it should fetch data in chunks of 10 or 50. But this doesn\u2019t work until you turn off autoCommit. Once you turn off autoCommit, it starts working. <\/p>\n<p><\/p>\n<p>Walking through the code and setting setFetchSize everywhere is inconvenient. That's why we've implemented a setting that will provide a default value for the entire connection.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c9ac8e483dc6bfdbbc300972ee1ab1e0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>So we said that. We configured the parameter. And what did we achieve? If we select a small amount, such as 10 rows, the overhead is significant. Therefore, this value should be around a hundred. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/073e7a4af63688396eefe332ba8261d6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ideally, we should also learn to limit it in bytes, but the recipe is this: set defaultRowFetchSize to over a hundred and be happy. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e3078faf2078fcaec10d6d99872a9990.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Let's move on to data insertion. Insertion is simpler and there are different options. For example, INSERT, VALUES. That's a good option. We can also say 'INSERT SELECT.' In practice, they are the same. There is no difference in performance. <\/p>\n<p><\/p>\n<p>Books say to execute Batch statements, and they mention that you can perform more complex commands with multiple parentheses. And in Postgres, there is a wonderful function\u2014you can use COPY, which makes it faster. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4d281c9515bdf8de7fbf1e9922d192d0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>If we measure, we can make several interesting discoveries again. How do we want this to work? We want to avoid parsing and unnecessary commands. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7b2e4343c51dab3ddb6919bf0f117c4d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>In practice, TCP doesn't allow us to do that. If the client is busy sending a request, the database doesn't read requests while trying to send us replies. As a result, the client waits for the database to read the request, while the database waits for the client to read the reply. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/17a062c30ddd83cb890775ec1722044f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>That's why the client is forced to periodically send a synchronization packet. This results in unnecessary network interactions and wasted time.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ae7a4f32e2949c1d0550da051937de02.jpg\" style=\"display:block;margin: 0 auto;\" \/>The more we add, the worse it gets. The driver is quite pessimistic and adds them fairly often, roughly every 200 rows, depending on the size of the rows, etc. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/82b1d597986633a8c4b7952517ca6477.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/pgjdbc\/pgjdbc\/pull\/380\">https:\/\/github.com\/pgjdbc\/pgjdbc\/pull\/380<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Sometimes, adjusting just one line can speed everything up tenfold. It happens. Why? As usual, a constant was already used somewhere. And the value '128' meant not to use batching.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7b6bcd95591b36441037b4c4631d2ad0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"http:\/\/openjdk.java.net\/projects\/code-tools\/jmh\/\">Java microbenchmark harness<\/a><\/noindex><\/p>\n<p><\/p>\n<p>It's good that this didn't make it into the official version. We discovered it before we started releasing the version. All the values I mention are based on modern versions. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/28c82d9e11f6bdaf3cf62b49fc074d68.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Let's measure. We measure the simple InsertBatch. We measure the multiple InsertBatch, that is, it's the same but with many values. It's a clever approach. Not everyone can do this, but it's a very simple trick, much easier than COPY.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/72f7cf6c3b9d9175d3e5a6d5410fe209.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>You can use COPY.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2b0d8bbcc50f1293c9a8a1eb8eb70d2e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>And this can be done on structures. Declare User default type, pass an array, and INSERT directly into the table. <\/p>\n<p><\/p>\n<p>If you open the link: pgjdbc\/ubenchmsrk\/InsertBatch.java, you'll find this code on GitHub. You can see exactly what queries are being generated there. It's not the core issue.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/28dede211fe401286685ca62081453de.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>We ran it. And the first thing we understood is that not using batch processing is simply not an option. All batching options are equal to zero, meaning execution time is practically zero compared to single execution. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4ea68f712bbdeadd35f5baa4ddaeff33.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>We are inserting data. It\u2019s quite a simple table. Three columns. And what do we see here? We see that all three options are roughly comparable. And COPY, of course, is better.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2686f1d7eea347a839ac23aef2a27e53.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>This is when we insert in chunks. When we were talking about one value VALUES, two values VALUES, three values VALUES, or we specified 10 of them separated by commas. This is exactly what is currently horizontal. 1, 2, 4, 128. It\u2019s clear that Batch Insert, which is shown in blue, benefits greatly from this. That is, when you insert one at a time or even when you insert four at a time, it becomes twice as efficient, simply because we packed a bit more into VALUES. Fewer EXECUTE operations.<\/p>\n<p><\/p>\n<p>Using COPY on small volumes is extremely unpromising. I didn\u2019t even draw on the first two. They go into the sky, that is, these green numbers are for COPY.<\/p>\n<p><\/p>\n<p>COPY should be used when you have at least over a hundred rows of data. The overhead for opening this connection is significant. And, to be honest, I haven't delved into that direction. I optimized batching; I didn\u2019t optimize COPY. <\/p>\n<p><\/p>\n<p>What do we do next? We measured. We understand that we need to use either structures or a clever batch that combines several values. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL and JDBC, we squeeze every ounce. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e02fa2574e2d1b678382db15064610d3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>What should we take away from today's report?<\/p>\n<p><\/p>\n<ul>\n<li>PreparedStatement is everything to us. It greatly enhances performance. It brings a large barrel of tar. <\/li>\n<li>And we need to do EXPLAIN ANALYZE 6 times.<\/li>\n<li>And we need to dilute OFFSET 0, and use tricks like +0 to fix the remaining percentage of our problematic queries.<\/li>\n<\/ul>\n<p>Source: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/499794\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440\u0430 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432\u0430 &quot;PostgreSQL \u0438 JDBC \u0432\u044b\u0436\u0438\u043c\u0430\u0435\u043c \u0432\u0441\u0435 \u0441\u043e\u043a\u0438&quot; \u0414\u043e\u0431\u0440\u044b\u0439 \u0434\u0435\u043d\u044c! \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432. \u042f \u0440\u0430\u0431\u043e\u0442\u0430\u044e 10 \u043b\u0435\u0442 \u0432 \u043a\u043e\u043c\u043f\u0430\u043d\u0438\u0438 NetCracker. \u0418 \u0432 \u043e\u0441\u043d\u043e\u0432\u043d\u043e\u043c \u044f \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0441\u044c \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e. \u0412\u0441\u0435, \u0447\u0442\u043e \u0441\u0432\u044f\u0437\u0430\u043d\u043e \u0441 Java, \u0432\u0441\u0435, \u0447\u0442\u043e \u0441\u0432\u044f\u0437\u0430\u043d\u043e \u0441 SQL \u2013 \u044d\u0442\u043e \u0442\u043e, \u0447\u0442\u043e \u044f \u043b\u044e\u0431\u043b\u044e. \u0418 \u0441\u0435\u0433\u043e\u0434\u043d\u044f \u044f \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":79894,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-79893","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440\u0430 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432\u0430 &quot;PostgreSQL \u0438 JDBC \u0432\u044b\u0436\u0438\u043c\u0430\u0435\u043c \u0432\u0441\u0435 \u0441\u043e\u043a\u0438&quot;\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47PostgreSQL \u0438 JDBC \u0432\u044b\u0436\u0438\u043c\u0430\u0435\u043c \u0432\u0441\u0435 \u0441\u043e\u043a\u0438. \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440\u0430 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432\u0430 &quot;PostgreSQL \u0438 JDBC \u0432\u044b\u0436\u0438\u043c\u0430\u0435\u043c \u0432\u0441\u0435 \u0441\u043e\u043a\u0438&quot;\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-05-01T11:43:12+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-05-01T11:43:12+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47We are squeezing everything out of PostgreSQL and JDBC. Vladimir Sitnikov | ProHoster","description":"I propose to review the transcript of Vladimir Sitnikov's report from early 2016 \"PostgreSQL and JDBC: Getting the Most Out of It\".","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47PostgreSQL \u0438 JDBC \u0432\u044b\u0436\u0438\u043c\u0430\u0435\u043c \u0432\u0441\u0435 \u0441\u043e\u043a\u0438. \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432 | ProHoster","og:description":"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440\u0430 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432\u0430 &quot;PostgreSQL \u0438 JDBC \u0432\u044b\u0436\u0438\u043c\u0430\u0435\u043c \u0432\u0441\u0435 \u0441\u043e\u043a\u0438&quot;","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-05-01T11:43:12+00:00","article:modified_time":"2020-05-01T11:43:12+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"79893","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 16:29:39","updated":"2022-10-10 00:32:43","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/79893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=79893"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/79893\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/79894"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=79893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=79893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=79893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}