{"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\/cs\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","title":{"rendered":"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Doporu\u010duji se sezn\u00e1mit s p\u0159episem zpr\u00e1vy Vladimira Sitnikova z po\u010d\u00e1tku roku 2016 \"PostgreSQL a JDBC vytahujeme v\u0161echny \u0161\u0165\u00e1vy\"<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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 a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/030666abda53aa388b1cb1d0c46a7524.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dobr\u00fd den! Jmenuji se Vladim\u00edr Sitnikov. Pracuji 10 let ve spole\u010dnosti NetCracker. Hlavn\u011b se zab\u00fdv\u00e1m v\u00fdkonem. V\u0161e, co souvis\u00ed s Javou, a v\u0161e, co souvis\u00ed se SQL - to je to, co m\u00e1m r\u00e1d. <\/p>\n<p><\/p>\n<p>Dnes v\u00e1m pov\u00edm o tom, s \u010d\u00edm jsme se v na\u0161\u00ed spole\u010dnosti setkali, kdy\u017e jsme za\u010dali pou\u017e\u00edvat PostgreSQL jako server datab\u00e1z\u00ed. Hlavn\u011b pracujeme s Javou. To, co dnes \u0159eknu, v\u0161ak souvis\u00ed nejen s Javou. Jak praxe uk\u00e1zala, objevuje se to i v jin\u00fdch jazyc\u00edch. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b01a214d782c7e32797a6c6466457979.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Budeme mluvit o:<\/p>\n<p><\/p>\n<ul>\n<li>v\u00fdb\u011bru dat. <\/li>\n<li>o ukl\u00e1d\u00e1n\u00ed dat. <\/li>\n<li>a tak\u00e9 o v\u00fdkonu. <\/li>\n<li>a o \u00faskal\u00edch, kter\u00e1 tam jsou skryta. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/57da0c6aa4bb62e1beb77160f5aee7e7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Za\u010dn\u011bme jednoduchou ot\u00e1zkou. Vybereme jeden \u0159\u00e1dek z tabulky podle prim\u00e1rn\u00edho kl\u00ed\u010de. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/d5fcb1172aef402a87064498bf8a5218.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Datab\u00e1ze se nach\u00e1z\u00ed na stejn\u00e9m hostiteli. A to v\u0161e trv\u00e1 20 milisekund.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/103b7f9736b82a3313491b2f0d2a5824.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>T\u011bchto 20 milisekund je opravdu hodn\u011b. Pokud m\u00e1te takov\u00fdch 100 dotaz\u016f, pak ztr\u00e1c\u00edte \u010das v sekund\u00e1ch na to, abyste tyto dotazy zpracovali, tedy zbyte\u010dn\u011b tr\u00e1v\u00edte \u010das.<\/p>\n<p><\/p>\n<p>Tohle nem\u00e1me r\u00e1di a pod\u00edv\u00e1me se, co n\u00e1m datab\u00e1ze nab\u00edz\u00ed. Datab\u00e1ze n\u00e1m nab\u00edz\u00ed dva zp\u016fsoby prov\u00e1d\u011bn\u00ed dotaz\u016f. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b467e86a0f8c4c32cea182fe27a28e6e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Prvn\u00ed mo\u017enost je jednoduch\u00fd dotaz. \u010c\u00edm je dobr\u00fd? T\u00edm, \u017ee ho vezmeme a ode\u0161leme, a nic v\u00edc. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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>Datab\u00e1ze m\u00e1 tak\u00e9 roz\u0161\u00ed\u0159en\u00fd dotaz, kter\u00fd je slo\u017eit\u011bj\u0161\u00ed, ale funk\u010dn\u011bj\u0161\u00ed. M\u016f\u017eete samostatn\u011b pos\u00edlat dotazy na anal\u00fdzu, proveden\u00ed, propojen\u00ed prom\u011bnn\u00fdch atd. <\/p>\n<p><\/p>\n<p>Super roz\u0161\u00ed\u0159en\u00fd dotaz - to je n\u011bco, co nebudeme pokr\u00fdvat v t\u00e9to p\u0159edn\u00e1\u0161ce. Mo\u017en\u00e1 m\u00e1me od datab\u00e1ze n\u011bjak\u00e9 po\u017eadavky a existuje seznam p\u0159\u00e1n\u00ed, kter\u00fd byl n\u011bjak sestaven, tedy to, co chceme, ale te\u010f a v p\u0159\u00ed\u0161t\u00edm roce to nen\u00ed mo\u017en\u00e9. Tak\u017ee jsme to jen zapsali a budeme chodit a t\u0159\u00e1st hlavn\u00edmi lidmi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/19094729074fd28c5b9f236833a9a266.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>To, co m\u016f\u017eeme ud\u011blat, je - jednoduch\u00fd dotaz a roz\u0161\u00ed\u0159en\u00fd dotaz.<\/p>\n<p><\/p>\n<p>Jak\u00e9 jsou zvl\u00e1\u0161tnosti ka\u017ed\u00e9ho p\u0159\u00edstupu? <\/p>\n<p><\/p>\n<p>Jednoduch\u00fd dotaz je dobr\u00e9 pou\u017e\u00edt pro jednor\u00e1zov\u00e9 proveden\u00ed. Jednou provedeno a zapomenuto. A probl\u00e9m je v tom, \u017ee nepodporuje bin\u00e1rn\u00ed form\u00e1t dat, tedy pro n\u011bkter\u00e9 vysoce v\u00fdkonn\u00e9 syst\u00e9my nen\u00ed vhodn\u00fd.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/062c0e45cefd91ece331a306a7651bde.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Roz\u0161\u00ed\u0159en\u00e9 dotazy - umo\u017e\u0148uj\u00ed u\u0161et\u0159it \u010das p\u0159i anal\u00fdze. To je to, co jsme ud\u011blali a za\u010dali pou\u017e\u00edvat. Velmi n\u00e1m to pomohlo. Nejde jen o \u00fasporu p\u0159i anal\u00fdze. Je tu tak\u00e9 \u00faspora p\u0159i p\u0159enosu dat. P\u0159en\u00e1\u0161en\u00ed dat ve bin\u00e1rn\u00edm form\u00e1tu je mnohem efektivn\u011bj\u0161\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/6df83a2f3736568668cf9d74de5d9758.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u0159ejdeme k praxi. Takto vypad\u00e1 typick\u00e1 aplikace. M\u016f\u017ee to b\u00fdt Java atd. <\/p>\n<p><\/p>\n<p>Vytvo\u0159ili jsme statement. Provedli jsme p\u0159\u00edkaz. Vytvo\u0159ili jsme close. Kde je chyba? Jak\u00fd je probl\u00e9m? Nejsou \u017e\u00e1dn\u00e9 probl\u00e9my. Tak je naps\u00e1no ve v\u0161ech knih\u00e1ch. Tak to je t\u0159eba ps\u00e1t. Pokud chcete maxim\u00e1ln\u00ed v\u00fdkon, pi\u0161te tak. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/6923293946dec45b3fd508235728ab95.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale praxe uk\u00e1zala, \u017ee to nefunguje. Pro\u010d? Proto\u017ee m\u00e1me metodu \u201eclose\u201c. A kdy\u017e to d\u011bl\u00e1me, pak z pohledu datab\u00e1ze to vypad\u00e1, jako by ku\u0159\u00e1k pracoval s datab\u00e1z\u00ed. \u0158ekli jsme \u201ePARSE EXECUTE DEALLOCATE\u201c.<\/p>\n<p><\/p>\n<p>Pro\u010d ty zbyte\u010dn\u00e9 vytv\u00e1\u0159en\u00ed a vykl\u00e1d\u00e1n\u00ed statements? Nikdo je nepot\u0159ebuje. Ale obvykle v PreparedStatement to tak funguje, kdy\u017e je zav\u00edr\u00e1me, zav\u00edraj\u00ed v\u0161echno v datab\u00e1zi. To nen\u00ed to, co chceme. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5a357a209e414024c437d042f600f251.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Chceme, jako zdrav\u00ed lid\u00e9, pracovat s datab\u00e1z\u00ed. Jednou jsme vzali a p\u0159ipravili n\u00e1\u0161 statement, pot\u00e9 ho vykon\u00e1v\u00e1me mnohokr\u00e1t. Ve skute\u010dnosti mnohokr\u00e1t znamen\u00e1 jednou za cel\u00fd \u017eivot aplikace jsme ho analyzovali. A na r\u016fzn\u00fdch REST pou\u017e\u00edv\u00e1me stejn\u00fd id statementu. To je n\u00e1\u0161 c\u00edl. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ac4aed702a624ad9f9addc4362daf760.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Jak toho dos\u00e1hnout? <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/0f101d890d1a2af8e99f8a3a8a06fd5a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Velmi jednodu\u0161e - nen\u00ed t\u0159eba zav\u00edrat statements. P\u00ed\u0161eme takto: \u201eprepare\u201c \u201eexecute\u201c. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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 a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/724646bfc55b5e2b611b1584ca8ba6aa.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pokud toto spust\u00edme, je jasn\u00e9, \u017ee n\u011bkde n\u011bco p\u0159ete\u010de. Pokud nen\u00ed jasn\u00e9, tak to m\u016f\u017eeme zm\u011b\u0159it. Vezmeme a nap\u00ed\u0161eme benchmark, ve kter\u00e9m bude takov\u00e1 jednoduch\u00e1 metoda. Vytvo\u0159\u00edme statement. Spust\u00edme na n\u011bjak\u00e9 verzi ovlada\u010de a zjist\u00edme, \u017ee docela rychle selh\u00e1v\u00e1 s ztr\u00e1tou ve\u0161ker\u00e9 pam\u011bti, kterou m\u00e1me. <\/p>\n<p><\/p>\n<p>Je jasn\u00e9, \u017ee takov\u00e9 chyby se snadno opravuj\u00ed. O t\u011bchto nebudu mluvit. Ale \u0159eknu, \u017ee v nov\u00e9 verzi to funguje mnohem rychleji. Metoda je bezv\u00fdznamn\u00e1, ale i tak. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b232a6205e708c70f52be0024bbe9980.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Jak pracovat spr\u00e1vn\u011b? Co pro to mus\u00edme ud\u011blat?<\/p>\n<p><\/p>\n<p>Ve skute\u010dnosti aplikace v\u017edy zav\u00edraj\u00ed statements. Ve v\u0161ech knih\u00e1ch p\u00ed\u0161\u00ed, \u017ee je maj\u00ed zav\u00edrat, jinak dojde k \u00faniku pam\u011bti. <\/p>\n<p><\/p>\n<p>A PostgreSQL neum\u00ed cachovat dotazy. Ka\u017ed\u00e1 sezen\u00ed si mus\u00ed sama vytv\u00e1\u0159et tento cache. <\/p>\n<p><\/p>\n<p>A \u010das na anal\u00fdzu tak\u00e9 nechceme tr\u00e1vit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/85b9574938aa6e6bf59c956e3728bdc3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A jak obvykle m\u00e1me dv\u011b mo\u017enosti. <\/p>\n<p><\/p>\n<p>Prvn\u00ed mo\u017enost \u2013 vezmeme a \u0159ekneme, \u017ee v\u0161echno zabal\u00edme do PgSQL. Tam je cache. V\u0161echno to cachuje. Dopadne to skv\u011ble. Na to jsme se pod\u00edvali. M\u00e1me 100500 dotaz\u016f. Nefunguje to. Nesouhlas\u00edme \u2013 ru\u010dn\u011b p\u0159ev\u00e1d\u011bt dotazy do procedur. Ne, ne. <\/p>\n<p><\/p>\n<p>M\u00e1me druhou mo\u017enost \u2013 vz\u00edt a sami to napsat. Otev\u0159eme zdrojov\u00e9 k\u00f3dy, za\u010dneme pracovat. Pracujeme a pracujeme. Uk\u00e1zalo se, \u017ee to nen\u00ed tak slo\u017eit\u00e9 ud\u011blat. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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>To se objevilo v srpnu 2015. Te\u010f u\u017e je to modern\u011bj\u0161\u00ed verze. A v\u0161echno je skv\u011bl\u00e9. Funguje to tak dob\u0159e, \u017ee nic v aplikaci nem\u011bn\u00edme. A dokonce jsme p\u0159estali uva\u017eovat o PgSQL, tj. toho m\u00e1me dost, abychom v\u0161echny re\u017eijn\u00ed n\u00e1klady sn\u00ed\u017eili prakticky na nulu. <\/p>\n<p><\/p>\n<p>Server-prepared statements se aktivuj\u00ed p\u0159i p\u00e1t\u00e9m proveden\u00ed, aby se \u0161et\u0159ily pam\u011b\u0165 v datab\u00e1zi na ka\u017ed\u00fd jednor\u00e1zov\u00fd dotaz. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/8ee95e71f92187941989ad0c18ece0c0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u016f\u017eete se zeptat \u2013 kde jsou \u010d\u00edsla? Co dost\u00e1v\u00e1te? A zde \u010d\u00edsla ned\u00e1m, proto\u017ee pro ka\u017ed\u00fd dotaz jsou jin\u00e1.<\/p>\n<p><\/p>\n<p>Na\u0161e dotazy byly takov\u00e9, \u017ee jsme na OLTP dotazech tr\u00e1vili asi 20 milisekund na parsov\u00e1n\u00ed. Tam bylo 0,5 milisekundy na proveden\u00ed, 20 milisekund na parsov\u00e1n\u00ed. Dotaz \u2013 10 KiB textu, 170 \u0159\u00e1dk\u016f pl\u00e1nu. To je OLTP dotaz. Dotazuje se na 1, 5, 10 \u0159\u00e1dk\u016f, n\u011bkdy v\u00edce. <\/p>\n<p><\/p>\n<p>Ale v\u016fbec jsme necht\u011bli tr\u00e1vit 20 milisekund. Sn\u00ed\u017eili jsme to na 0. V\u0161echno je skv\u011bl\u00e9. <\/p>\n<p><\/p>\n<p>Co si odtud m\u016f\u017eete odn\u00e9st? Pokud m\u00e1te Java, vezmete modern\u00ed verzi ovlada\u010de a radujete se. <\/p>\n<p><\/p>\n<p>Pokud m\u00e1te n\u011bjak\u00fd jin\u00fd jazyk, tak si promyslete \u2013 mo\u017en\u00e1 to tak\u00e9 pot\u0159ebujete? Proto\u017ee z pohledu koncov\u00e9ho jazyka, nap\u0159\u00edklad, pokud m\u00e1te PL 8 nebo LibPQ, nen\u00ed v\u00e1m jasn\u00e9, \u017ee tr\u00e1v\u00edte \u010das ne na proveden\u00ed, ale na parsov\u00e1n\u00ed a to stoj\u00ed za to ov\u011b\u0159it. Jak? V\u0161e zdarma. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/fda6b1c1b126cb85c970e42335e1dc24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>S v\u00fdjimkou toho, \u017ee jsou chyby, n\u011bjak\u00e9 zvl\u00e1\u0161tnosti. A o t\u011bch budeme mluvit pr\u00e1v\u011b te\u010f. V\u011bt\u0161ina bude o pr\u016fmyslov\u00e9 archeologii, o tom, co jsme na\u0161li, na co jsme narazili. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9a3b383f83c5e227cd02952c5825b64f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pokud se dotaz generuje dynamicky. To se st\u00e1v\u00e1. N\u011bkdo skl\u00e1d\u00e1 \u0159et\u011bzce, vznik\u00e1 SQL dotaz.<\/p>\n<p><\/p>\n<p>Pro\u010d je \u0161patn\u00fd? Je \u0161patn\u00fd t\u00edm, \u017ee ka\u017ed\u00fd \u010das m\u00e1me na konci jin\u00fd \u0159et\u011bzec.<\/p>\n<p><\/p>\n<p>I u tohoto r\u016fznorod\u00e9ho \u0159et\u011bzce je pot\u0159eba znovu spo\u010d\u00edtat hashCode. To je skute\u010dn\u011b \u00fakol pro CPU \u2013 naj\u00edt dlouh\u00fd text dotazu v ji\u017e existuj\u00edc\u00edm hashi nen\u00ed tak jednoduch\u00e9. Proto je jednoduch\u00e9 doporu\u010den\u00ed \u2013 negenerujte dotazy. Uchov\u00e1vejte je v n\u011bjak\u00e9 jedn\u00e9 prom\u011bnn\u00e9. A radujte se.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/bcd4f729204cecdb572a98f357bcc33f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dal\u0161\u00ed probl\u00e9m. Typy dat jsou d\u016fle\u017eit\u00e9. Existuj\u00ed ORM, kter\u00e9 tvrd\u00ed, \u017ee na tom nez\u00e1le\u017e\u00ed, jak\u00fd je NULL, a\u0165 je to n\u011bjak\u00fd. Pokud je to Int, \u0159\u00edk\u00e1me setInt. A pokud je NULL, a\u0165 je to v\u017edy VARCHAR. A nakonec, jak\u00fd je vlastn\u011b rozd\u00edl, jak\u00fd tam je NULL? Datab\u00e1ze si to stejn\u011b v\u0161echno pochop\u00ed. A takov\u00e1 situace nefunguje. <\/p>\n<p><\/p>\n<p>V praxi datab\u00e1ze rozhodn\u011b nen\u00ed lhostejn\u00e1. <strong>Pokud jste poprv\u00e9 \u0159ekli, \u017ee to m\u00e1te \u010d\u00edslo, a podruh\u00e9 jste \u0159ekli, \u017ee to je VARCHAR, nen\u00ed mo\u017en\u00e9 znovu pou\u017e\u00edt serverem p\u0159ipraven\u00e9 p\u0159\u00edkazy. A v takov\u00e9m p\u0159\u00edpad\u011b se mus\u00edme znovu vytvo\u0159it n\u00e1\u0161 p\u0159\u00edkaz.<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/552d9c2e25cf9abbdfef12c98d027740.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pokud prov\u00e1d\u00edte stejn\u00fd dotaz, dbejte na to, abyste m\u011bli typy dat ve sloupc\u00edch neuspo\u0159\u00e1dan\u00e9. Je t\u0159eba d\u00e1vat pozor na NULL. To je \u010dast\u00e1 chyba, kter\u00e1 se n\u00e1m stala pot\u00e9, co jsme za\u010dali pou\u017e\u00edvat PreparedStatements.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5d73c8f5dfa281c3bda962fc3e5edd84.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dob\u0159e, zapnuli jsme to. Vzali jsme mo\u017en\u00e1 ovlada\u010d. A v\u00fdkon klesl. V\u0161echno se zhor\u0161ilo. <\/p>\n<p><\/p>\n<p>Jak se to m\u016f\u017ee st\u00e1t? Je to chyba nebo funkce? Bohu\u017eel se n\u00e1m nepoda\u0159ilo pochopit \u2013 je to chyba nebo funkce. Ale existuje zcela jednoduch\u00fd sc\u00e9n\u00e1\u0159 pro reprodukci tohoto probl\u00e9mu. Naprosto ne\u010dekan\u011b n\u00e1s to p\u0159epadlo. A spo\u010d\u00edv\u00e1 to v v\u00fdb\u011bru doslova z jedn\u00e9 tabulky. Samoz\u0159ejm\u011b, \u017ee takov\u00fdch dotaz\u016f jsme m\u011bli v\u00edc. V\u011bt\u0161inou zahrnovaly dv\u011b a\u017e t\u0159i tabulky, ale existuje tento sc\u00e9n\u00e1\u0159 reprodukce. Vezmete na va\u0161\u00ed datab\u00e1zi jakoukoli verzi a reprodukujete.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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>Smyslem je, \u017ee m\u00e1me dva sloupce, z nich\u017e ka\u017ed\u00fd je indexov\u00e1n. V jednom sloupci je podle hodnoty NULL milion \u0159\u00e1dk\u016f. A ve druh\u00e9m sloupci je celkem 20 \u0159\u00e1dk\u016f. Kdy\u017e prov\u00e1d\u00edme bez v\u00e1zan\u00fdch prom\u011bnn\u00fdch, v\u0161echno funguje dob\u0159e. <\/p>\n<p><\/p>\n<p>Pokud za\u010dneme prov\u00e1d\u011bt s v\u00e1zan\u00fdmi prom\u011bnn\u00fdmi, tj. prov\u00e1d\u00edme znak \u201e?\u201c nebo \u201e$1\u201c pro n\u00e1\u0161 dotaz, co na konec dostaneme?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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>Prvn\u00ed prov\u00e1d\u011bn\u00ed \u2013 jak m\u00e1 b\u00fdt. Druh\u00e9 \u2013 trochu rychlej\u0161\u00ed. N\u011bco bylo ulo\u017eeno do cache. T\u0159et\u00ed-\u010dtvrt\u00e9-p\u00e1t\u00e9. Pak bum \u2013 a n\u011bjak to takhle. A nejhor\u0161\u00ed je, \u017ee se to st\u00e1v\u00e1 p\u0159i \u0161est\u00e9m prov\u00e1d\u011bn\u00ed. Kdo v\u011bd\u011bl, \u017ee je pot\u0159eba d\u011blat p\u0159esn\u011b \u0161est prov\u00e1d\u011bn\u00ed, abychom pochopili, jak\u00fd je skute\u010dn\u00fd pl\u00e1n prov\u00e1d\u011bn\u00ed?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/893c955bc1e2e15a6986690e516f72a9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kdo je vinen? Co se stalo? Datab\u00e1ze obsahuje optimalizaci. A je jaksi optimalizov\u00e1na pro obecn\u00fd p\u0159\u00edpad. A za\u010d\u00ednaje od ur\u010dit\u00e9ho okam\u017eiku se p\u0159esune na obecn\u00fd pl\u00e1n, kter\u00fd, bohu\u017eel, m\u016f\u017ee b\u00fdt jin\u00fd. M\u016f\u017ee b\u00fdt stejn\u00fd, ale tak\u00e9 jin\u00fd. A existuje n\u011bjak\u00e1 prahov\u00e1 hodnota, kter\u00e1 vede k takov\u00e9mu chov\u00e1n\u00ed. <\/p>\n<p><\/p>\n<p>Co s t\u00edm lze d\u011blat? Tady je pochopiteln\u011b slo\u017eit\u00e9 n\u011bco p\u0159edpokl\u00e1dat. Existuje jednoduch\u00e9 \u0159e\u0161en\u00ed, kter\u00e9 pou\u017e\u00edv\u00e1me. To je +0, OFFSET 0. Ur\u010dit\u011b takov\u00e1 \u0159e\u0161en\u00ed zn\u00e1te. Prost\u011b vezmeme a do dotazu p\u0159id\u00e1me \u201e+0\u201c a v\u0161e je v po\u0159\u00e1dku. Uk\u00e1\u017eu pozd\u011bji. <\/p>\n<p><\/p>\n<p>A je tu je\u0161t\u011b jedna mo\u017enost \u2013 pe\u010dliv\u011bji se d\u00edvat na pl\u00e1ny. V\u00fdvoj\u00e1\u0159 by m\u011bl nejen napsat dotaz, ale tak\u00e9 6kr\u00e1t \u0159\u00edct \u201eexplain analyze\u201c. Pokud 5kr\u00e1t, tak to nebude vyhovovat. <\/p>\n<p><\/p>\n<p>A existuje i t\u0159et\u00ed mo\u017enost \u2013 napsat dopis na pgsql-hackers. Napsal jsem to, ale zat\u00edm nen\u00ed jasn\u00e9, jestli je to chyba, nebo funkce.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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>Zat\u00edmco p\u0159em\u00fd\u0161l\u00edme \u2013 jestli je to chyba nebo funkce, poj\u010fme to opravit. Vezmeme n\u00e1\u0161 dotaz a p\u0159id\u00e1me \u201e+0\u201c. V\u0161e je v po\u0159\u00e1dku. Dva symboly a ani nemus\u00edme p\u0159em\u00fd\u0161let, jak to tam a co tam. Velmi jednoduch\u00e9. Jen jsme datab\u00e1zi znemo\u017enili pou\u017e\u00edt index na tomto sloupci. Nem\u00e1me index na sloupci \u201e+0\u201c a hotovo, datab\u00e1ze nepou\u017e\u00edv\u00e1 index, v\u0161e je v po\u0159\u00e1dku. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5261d80d084786a15b9764f6367014cd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Toto je pravidlo 6 explain\u016f. V aktu\u00e1ln\u00edch verz\u00edch je t\u0159eba to ud\u011blat 6kr\u00e1t, pokud m\u00e1te propojen\u00e9 prom\u011bnn\u00e9. Pokud nem\u00e1te propojen\u00e9 prom\u011bnn\u00e9, d\u011bl\u00e1me to takto. A nakonec pr\u00e1v\u011b tento dotaz selh\u00e1v\u00e1. Nen\u00ed to slo\u017eit\u00e9.<\/p>\n<p><\/p>\n<p>Zd\u00e1lo by se, kolik toho je\u0161t\u011b m\u016f\u017ee b\u00fdt? Tady je chyba, tam je chyba. Opravuji, chyba je v\u0161ude. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/674dc7e7b4624baa5c79eee9e1e89db0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pod\u00edvejme se znovu. Nap\u0159\u00edklad m\u00e1me dv\u011b sch\u00e9mata. Sch\u00e9ma A se tabulkou Y a sch\u00e9ma B se tabulkou Y. Dotaz \u2013 vybrat data z tabulky. Co n\u00e1m p\u0159i tom vznikne? Budeme m\u00edt chybu. Budeme m\u00edt v\u0161echno, co bylo zm\u00edn\u011bno v\u00fd\u0161e. Pravidlo je takov\u00e9 \u2013 chyba je v\u0161ude, budeme m\u00edt v\u0161e, co je uvedeno v\u00fd\u0161e.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/dd6f544bb9e930c39e36523c9afe6003.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Te\u010f ot\u00e1zka: \u201ePro\u010d?\u201c. Zd\u00e1lo by se, \u017ee existuje dokumentace, kter\u00e1 \u0159\u00edk\u00e1, \u017ee pokud m\u00e1me sch\u00e9ma, existuje prom\u011bnn\u00e1 \u201esearch_path\u201c, kter\u00e1 ur\u010duje, kde hledat tabulku. Zd\u00e1lo by se, \u017ee prom\u011bnn\u00e1 existuje.<\/p>\n<p><\/p>\n<p>Jak\u00fd je probl\u00e9m? Probl\u00e9m je v tom, \u017ee server-prepared statements netu\u0161\u00ed, \u017ee search_path m\u016f\u017ee n\u011bkdo m\u011bnit. Tato hodnota z\u016fst\u00e1v\u00e1 jakoby konstantn\u00ed pro datab\u00e1zi. A n\u011bkter\u00e9 \u010d\u00e1sti nemus\u00ed zachytit nov\u00e9 hodnoty. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/f738301606d84d72bfa7cc5b568c0df8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Samoz\u0159ejm\u011b, z\u00e1le\u017e\u00ed na verzi, na kter\u00e9 testujete. Z\u00e1le\u017e\u00ed na tom, jak moc se va\u0161e tabulky li\u0161\u00ed. A verze 9.1 prost\u011b zpracuje star\u00e9 dotazy. Nov\u00e9 verze mohou odhalit \u00faskok a \u0159\u00edct, \u017ee m\u00e1te chybu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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\">Nastavit search_path + server-prepared statements =<br \/>\ncached plan nesm\u00ed m\u011bnit typ v\u00fdsledku<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Jak to napravit? Je tu jednoduch\u00fd recept - ned\u011blejte to. Nem\u011bli byste m\u011bnit search_path za b\u011bhu aplikace. Pokud to zm\u011bn\u00edte, je lep\u0161\u00ed vytvo\u0159it nov\u00e9 p\u0159ipojen\u00ed.<\/p>\n<p><\/p>\n<p>M\u016f\u017eeme o tom diskutovat, tj. otev\u0159\u00edt, probrat, doplnit. Mo\u017en\u00e1 p\u0159esv\u011bd\u010d\u00edme v\u00fdvoj\u00e1\u0159e datab\u00e1ze, \u017ee v p\u0159\u00edpad\u011b, kdy n\u011bkdo zm\u011bn\u00ed hodnotu, by datab\u00e1ze m\u011bla klientovi \u0159\u00edct: \u201ePod\u00edvejte, va\u0161e hodnota se aktualizovala. Mo\u017en\u00e1 byste m\u011bli resetovat, znovu vytvo\u0159it statements.\u201c. Nyn\u00ed se datab\u00e1ze chov\u00e1 skryt\u011b a nijak neoznamuje, \u017ee se n\u011bkde uvnit\u0159 statements zm\u011bnily. <\/p>\n<p><\/p>\n<p>A znovu zd\u016frazn\u00edm - to nen\u00ed typick\u00e9 pro Java. Tot\u00e9\u017e uvid\u00edme v PL\/pgSQL jeden k jednomu. Ale tam se to reprodukuje.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a4e7b201d0c0aa4a0092d2b6ff152df7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Poj\u010fme zkusit zvolit dal\u0161\u00ed data. Vyb\u00edr\u00e1me. M\u00e1me tabulku s milionem \u0159\u00e1dk\u016f. Ka\u017ed\u00fd \u0159\u00e1dek m\u00e1 kilobajt. P\u0159ibli\u017en\u011b gigabajt dat. A m\u00e1me pracovn\u00ed pam\u011b\u0165 v Java stroji 128 megabajt\u016f. <\/p>\n<p><\/p>\n<p>Jak je doporu\u010deno ve v\u0161ech knih\u00e1ch, pou\u017e\u00edv\u00e1me proudovou zpracov\u00e1n\u00ed. Tj. otev\u0159eme resultSet a \u010dteme data po \u010d\u00e1stech. Bude to fungovat? Nepadneme z d\u016fvodu nedostatku pam\u011bti? Bude to \u010d\u00edst po trochu? Poj\u010fme v\u011b\u0159it datab\u00e1zi, d\u016fv\u011b\u0159ujme Postgresu. Ned\u016fv\u011b\u0159ujeme. Spadneme OutOfMemory? Kdo m\u011bl OutOfMemory? A kdo to po tom dok\u00e1zal opravit? N\u011bkdo to opravil. <\/p>\n<p><\/p>\n<p>Pokud m\u00e1te milion \u0159\u00e1dk\u016f, nen\u00ed mo\u017en\u00e9 vyb\u00edrat jen tak. Mus\u00edte ur\u010dit\u011b pou\u017e\u00edt OFFSET\/LIMIT. Kdo podporuje tuto variantu? A kdo podporuje variantu, \u017ee je t\u0159eba si hr\u00e1t s autoCommit? <\/p>\n<p><\/p>\n<p>Zde se jako obvykle ukazuje, \u017ee nejneo\u010dek\u00e1van\u011bj\u0161\u00ed varianta je spr\u00e1vn\u00e1. A pokud n\u00e1hodou vypnete autoCommit, pom\u016f\u017ee to. Pro\u010d? V\u011bda o tom nev\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2e5d9d2a8450a9069155de86a8ec387a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale ve v\u00fdchoz\u00edm nastaven\u00ed v\u0161ichni klienti, kte\u0159\u00ed se p\u0159ipojuj\u00ed k datab\u00e1zi Postgres, vyb\u00edraj\u00ed data kompletn\u011b. PgJDBC v tomhle sm\u011bru nen\u00ed v\u00fdjimka, vyb\u00edr\u00e1 v\u0161echny \u0159\u00e1dky.<\/p>\n<p><\/p>\n<p>Existuje varianta na t\u00e9ma FetchSize, tj. m\u016f\u017eete na \u00farovni jednotliv\u00e9ho statementu \u0159\u00edct, \u017ee zde, pros\u00edm, vyb\u00edrejte data po 10, 50. Ale to nefunguje, dokud nevypnete autoCommit. Vypnuli jste autoCommit \u2013 za\u010dne to fungovat. <\/p>\n<p><\/p>\n<p>Chodit po k\u00f3du a v\u0161ude nastavit setFetchSize je nepraktick\u00e9. Proto jsme ud\u011blali takov\u00e9 nastaven\u00ed, kter\u00e9 pro cel\u00e9 p\u0159ipojen\u00ed \u0159\u00edk\u00e1 v\u00fdchoz\u00ed hodnotu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c9ac8e483dc6bfdbbc300972ee1ab1e0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Takhle jsme to \u0159ekli. Nastavili jsme parametr. A co jsme z\u00edskali? Pokud vyb\u00edr\u00e1me m\u00e1lo, nap\u0159\u00edklad po 10 \u0159\u00e1dc\u00edch, m\u00e1me docela velk\u00e9 re\u017eijn\u00ed n\u00e1klady. Proto je t\u0159eba tuto hodnotu nastavit na p\u0159ibli\u017en\u011b sto. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/073e7a4af63688396eefe332ba8261d6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ide\u00e1ln\u011b, samoz\u0159ejm\u011b, se je\u0161t\u011b nau\u010dit omezit na bajty, ale recept je n\u00e1sleduj\u00edc\u00ed: nastav\u00edme defaultRowFetchSize na v\u00edce ne\u017e sto a radujeme se. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e3078faf2078fcaec10d6d99872a9990.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u0159ejd\u011bme k vkl\u00e1d\u00e1n\u00ed dat. Vkl\u00e1d\u00e1n\u00ed je jednodu\u0161\u0161\u00ed, jsou tu r\u016fzn\u00e9 varianty. Nap\u0159\u00edklad, INSERT, VALUES. To je dobr\u00e1 varianta. M\u016f\u017eeme mluvit o \u201eINSERT SELECT\u201c. Prakticky to je to sam\u00e9. Nen\u00ed \u017e\u00e1dn\u00fd rozd\u00edl v v\u00fdkonu. <\/p>\n<p><\/p>\n<p>Knihy \u0159\u00edkaj\u00ed, \u017ee je t\u0159eba prov\u00e1d\u011bt d\u00e1vkov\u00e1 prohl\u00e1\u0161en\u00ed, knihy \u0159\u00edkaj\u00ed, \u017ee je mo\u017en\u00e9 prov\u00e1d\u011bt slo\u017eit\u011bj\u0161\u00ed p\u0159\u00edkazy s n\u011bkolika z\u00e1vorkami. A v Postgres je skv\u011bl\u00e1 funkce \u2014 m\u016f\u017eete prov\u00e1d\u011bt COPY, tj. d\u011blat to rychleji. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4d281c9515bdf8de7fbf1e9922d192d0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pokud to zm\u011b\u0159\u00edme, m\u016f\u017eeme ud\u011blat n\u011bkolik zaj\u00edmav\u00fdch objev\u016f. Jak chceme, aby to fungovalo? Chceme nep\u0159et\u011b\u017eovat a nevykon\u00e1vat zbyte\u010dn\u00e9 p\u0159\u00edkazy. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7b2e4343c51dab3ddb6919bf0f117c4d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V praxi n\u00e1m to TCP takto nedovoluje. Pokud je klient zanepr\u00e1zdn\u011bn odes\u00edl\u00e1n\u00edm po\u017eadavku, datab\u00e1ze se pokou\u0161\u00ed poslat n\u00e1m odpov\u011bdi, po\u017eadavky ne\u010dte. V\u00fdsledkem je, \u017ee klient \u010dek\u00e1 na datab\u00e1zi, dokud si p\u0159e\u010dte po\u017eadavek, a datab\u00e1ze \u010dek\u00e1 na klienta, dokud si p\u0159e\u010dte odpov\u011b\u010f. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/17a062c30ddd83cb890775ec1722044f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A proto je klient nucen periodicky pos\u00edlat synchroniza\u010dn\u00ed paket. Zbyte\u010dn\u00e9 s\u00ed\u0165ov\u00e9 interakce, zbyte\u010dn\u00e9 ztr\u00e1ty \u010dasu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ae7a4f32e2949c1d0550da051937de02.jpg\" style=\"display:block;margin: 0 auto;\" \/>A \u010d\u00edm v\u00edce jich p\u0159id\u00e1v\u00e1me, t\u00edm h\u016f\u0159e to je. Ovlada\u010d je velmi pesimistick\u00fd a p\u0159id\u00e1v\u00e1 je pom\u011brn\u011b \u010dasto, p\u0159ibli\u017en\u011b jednou za 200 \u0159\u00e1dk\u016f, v z\u00e1vislosti na velikosti \u0159\u00e1dk\u016f atd. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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>N\u011bkdy sta\u010d\u00ed opravit pouze jeden \u0159\u00e1dek a v\u0161e se zrychl\u00ed desetkr\u00e1t. Takov\u00e9 v\u011bci se st\u00e1vaj\u00ed. Pro\u010d? Jak obvykle, n\u011bkde u\u017e byla pou\u017eita podobn\u00e1 konstanta. A hodnota \u201e128\u201c znamenala \u2014 nepou\u017e\u00edvat d\u00e1vkov\u00e1n\u00ed.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr 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>Je dob\u0159e, \u017ee to nepro\u0161lo do ofici\u00e1ln\u00ed verze. Objevili jsme to d\u0159\u00edve, ne\u017e jsme za\u010dali vyd\u00e1vat verzi. V\u0161echny hodnoty, kter\u00e9 uv\u00e1d\u00edm, vych\u00e1zej\u00ed z modern\u00edch verz\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/28c82d9e11f6bdaf3cf62b49fc074d68.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Zm\u011b\u0159me to. M\u011b\u0159\u00edme jednoduch\u00fd InsertBatch. M\u011b\u0159\u00edme v\u00edce\u010detn\u00fd InsertBatch, tj. to sam\u00e9, ale s mnoha hodnotami. Chytr\u00fd krok. Ne ka\u017ed\u00fd to um\u00ed, ale je to takov\u00fd jednoduch\u00fd krok, mnohem snadn\u011bj\u0161\u00ed ne\u017e COPY.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/72f7cf6c3b9d9175d3e5a6d5410fe209.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u016f\u017eete d\u011blat COPY.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2b0d8bbcc50f1293c9a8a1eb8eb70d2e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A to lze prov\u00e1d\u011bt na struktur\u00e1ch. Deklarovat v\u00fdchoz\u00ed typ User, p\u0159edat pole a p\u0159\u00edmo vlo\u017eit do tabulky. <\/p>\n<p><\/p>\n<p>Pokud otev\u0159ete odkaz: pgjdbc\/ubenchmsrk\/InsertBatch.java, tento k\u00f3d je na GitHubu. M\u016f\u017eete si konkr\u00e9tn\u011b prohl\u00e9dnout, jak\u00e9 dotazy jsou tam generov\u00e1ny. O to nejde.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/28dede211fe401286685ca62081453de.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Spustili jsme to. A prvn\u00ed, co jsme pochopili, je, \u017ee pou\u017e\u00edvat batch \u2013 to se prost\u011b ned\u00e1. V\u0161echny varianty batchov\u00e1n\u00ed jsou na nule, tj. doba prov\u00e1d\u011bn\u00ed je t\u00e9m\u011b\u0159 nulov\u00e1 ve srovn\u00e1n\u00ed s jednor\u00e1zov\u00fdm proveden\u00edm. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4ea68f712bbdeadd35f5baa4ddaeff33.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Vkl\u00e1d\u00e1me data. Je to tam docela jednoduch\u00e1 tabulka. T\u0159i sloupce. A co tady vid\u00edme? Vid\u00edme, \u017ee v\u0161echny tyto t\u0159i varianty jsou p\u0159ibli\u017en\u011b srovnateln\u00e9. A COPY je samoz\u0159ejm\u011b nejlep\u0161\u00ed.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2686f1d7eea347a839ac23aef2a27e53.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>To je, kdy\u017e vkl\u00e1d\u00e1me po kousk\u00e1ch. Kdy\u017e jsme \u0159\u00edkali, \u017ee jedno hodnota VALUES, dv\u011b hodnota VALUES, t\u0159i hodnota VALUES nebo jich tam m\u00e1me 10 odd\u011blen\u00fdch \u010d\u00e1rkou. To je nyn\u00ed horizont\u00e1ln\u011b. 1, 2, 4, 128. Je vid\u011bt, \u017ee Batch Insert, kter\u00fd je vyzna\u010den mod\u0159e, je t\u00edm zna\u010dn\u011b usnadn\u011bn. Tj. kdy\u017e vkl\u00e1d\u00e1te po jednom nebo dokonce kdy\u017e vkl\u00e1d\u00e1te po \u010dty\u0159ech, je to dvakr\u00e1t lep\u0161\u00ed, jen z toho d\u016fvodu, \u017ee jsme do VALUES vlo\u017eili o n\u011bco v\u00edce. M\u00e9n\u011b operac\u00ed EXECUTE.<\/p>\n<p><\/p>\n<p>Pou\u017e\u00edvat COPY na mal\u00fdch objemech \u2013 to je naprosto nevhodn\u00e9. Na prvn\u00ed dva jsem ani nenakreslil. Jdou do nebes, tj. tyto zelen\u00e9 \u010d\u00edsla pro COPY.<\/p>\n<p><\/p>\n<p>COPY byste m\u011bli pou\u017e\u00edvat, kdy\u017e m\u00e1te objem dat alespo\u0148 v\u011bt\u0161\u00ed ne\u017e sto \u0159\u00e1dk\u016f. N\u00e1klady na otev\u0159en\u00ed tohoto spojen\u00ed jsou vysok\u00e9. A up\u0159\u00edmn\u011b, do t\u00e9to oblasti jsem se v\u00edc nepod\u00edval. Batch jsem optimalizoval, COPY \u2013 ne. <\/p>\n<p><\/p>\n<p>Co d\u011bl\u00e1me d\u00e1l? Zm\u011b\u0159ili jsme. Ch\u00e1peme, \u017ee je t\u0159eba pou\u017e\u00edt bu\u010f struktury, nebo rafinovan\u00fd batch, kter\u00fd spojuje n\u011bkolik hodnot. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum. Vladim\u00edr Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e02fa2574e2d1b678382db15064610d3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Co bychom m\u011bli vyvodit z dne\u0161n\u00ed p\u0159edn\u00e1\u0161ky?<\/p>\n<p><\/p>\n<ul>\n<li>PreparedStatement \u2013 to je na\u0161e v\u0161echno. To velmi pom\u00e1h\u00e1 v\u00fdkonu. D\u00e1v\u00e1 to velkou n\u00e1dobu sm\u016fly. <\/li>\n<li>A je t\u0159eba prov\u00e9st EXPLAIN ANALYZE 6kr\u00e1t.<\/li>\n<li>A je t\u0159eba z\u0159edit OFFSET 0, a triky typu +0, abychom opravili zbytek procenta z na\u0161ich probl\u00e9mov\u00fdch dotaz\u016f.<\/li>\n<\/ul>\n<p>Zdroj: <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.3 - 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\/cs\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\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\/cs\/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\udd47PostgreSQL a JDBC z n\u00e1s vyt\u011b\u017euj\u00ed maximum. Vladimir Sitnikov | ProHoster","description":"Doporu\u010duji se sezn\u00e1mit s p\u0159episem p\u0159edn\u00e1\u0161ky z po\u010d\u00e1tku roku 2016 od Vladim\u00edra Sitnikova \"PostgreSQL a JDBC - vyma\u010dk\u00e1me z toho maximum\"","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","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\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/79893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=79893"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/79893\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/79894"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=79893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=79893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=79893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}