{"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\/et\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","title":{"rendered":"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Tutvustan 2016. aasta alguse Vladislav Sitnikovi ettekande \"PostgreSQL ja JDBC \u2013 pigistame v\u00e4lja k\u00f5ik mahad\" sisu.<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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 ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/030666abda53aa388b1cb1d0c46a7524.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Tere p\u00e4evast! Minu nimi on Vladislav Sitnikov. Olen t\u00f6\u00f6tanud 10 aastat ettev\u00f5ttes NetCracker. Peamiselt tegeleme tootlikkusega. K\u00f5ik, mis on seotud Java ja SQL-iga, on minu kirg. <\/p>\n<p><\/p>\n<p>Ja t\u00e4na r\u00e4\u00e4gin sellest, millega me ettev\u00f5ttes silmitsi seisime, kui alustasime PostgreSQL kasutamist andmebaasiserverina. Peamiselt t\u00f6\u00f6tame Java-ga, kuid see, millest ma t\u00e4na r\u00e4\u00e4gin, ei piirdu ainult Java-ga. Praktika on n\u00e4idanud, et see esineb ka teistes keeltes. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b01a214d782c7e32797a6c6466457979.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>R\u00e4\u00e4gime:<\/p>\n<p><\/p>\n<ul>\n<li>andmete valimisest. <\/li>\n<li>Andmete salvestamisest. <\/li>\n<li>Ja ka tootlikkusest. <\/li>\n<li>Ja varjatest probleemidest, mis seal v\u00f5ivad olla. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/57da0c6aa4bb62e1beb77160f5aee7e7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Alustame lihtsast k\u00fcsimusest. Valime tabelist \u00fche rea primaarv\u00f5tme alusel. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/d5fcb1172aef402a87064498bf8a5218.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Andmebaas asub samas hostis. Ja k\u00f5ik see v\u00f5tab aega 20 millisekundit.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/103b7f9736b82a3313491b2f0d2a5824.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Need 20 millisekundit on t\u00f5esti palju. Kui teil on 100 sellist p\u00e4ringut, siis kulutate sekundite kaupa aega nende t\u00f6\u00f6tlemiseks, st raiskate aega.<\/p>\n<p><\/p>\n<p>Me ei armasta seda teha ja vaatame, mida andmebaas meile pakub. Andmebaas pakub meile kahte v\u00f5imalust p\u00e4ringute t\u00e4itmiseks. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b467e86a0f8c4c32cea182fe27a28e6e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Esimene variant on lihtne p\u00e4ring. Miks see on hea? Sest me v\u00f5tame selle ja saadame, ja midagi rohkemat ei ole vaja. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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>Andmebaasil on ka laiendatud p\u00e4ring, mis on keerulisem, kuid funktsionaalsem. Saame saata eraldi p\u00e4ringu parsingu, t\u00e4itmise ja muutujate sidumise jaoks jne. <\/p>\n<p><\/p>\n<p>Super laiendatud p\u00e4ring \u2013 seda me ei kata selgituses. Meil v\u00f5ib olla midagi, mida me soovime andmebaasist, ja on olemas soovide nimekiri, mis on mingil kujul koostatud, st see, mida me tahame, kuid mis ei ole praegu ja l\u00e4hima aasta jooksul v\u00f5imalik. Seega lihtsalt kirjutame \u00fcles ja k\u00e4ime peamisi inimesi k\u00f5igutamas.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/19094729074fd28c5b9f236833a9a266.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Aga see, mida me teha saame, on lihtne p\u00e4ring ja laiendatud p\u00e4ring.<\/p>\n<p><\/p>\n<p>Milline on iga l\u00e4henemise erip\u00e4ra? <\/p>\n<p><\/p>\n<p>Lihtsat p\u00e4ringut on hea kasutada \u00fchekordseks t\u00e4itmiseks. \u00dcks kord t\u00e4idetud ja unustatud. Probleem on selles, et see ei toeta binaarset andmeformaati, st m\u00f5ne suure j\u00f5udlusega s\u00fcsteemi jaoks see ei sobi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/062c0e45cefd91ece331a306a7651bde.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Laiendatud p\u00e4ring - v\u00f5imaldab s\u00e4\u00e4sta aega sql-parsingu protsessis. Just seda me tegime ja hakkasime kasutama. See on meile \u00e4\u00e4rmiselt, \u00e4\u00e4rmiselt kasulik olnud. Seal on mitte ainult s\u00e4\u00e4st sql-parsimisel. On ka andmete edastamise s\u00e4\u00e4st. Andmete edastamine binaarformaadis on palju t\u00f5husam. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/6df83a2f3736568668cf9d74de5d9758.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Liigume praktika juurde. Nii n\u00e4eb v\u00e4lja t\u00fc\u00fcpiline rakendus. See v\u00f5ib olla Java jne. <\/p>\n<p><\/p>\n<p>Me l\u00f5ime statement'i. T\u00e4itsime k\u00e4su. L\u00f5ime close. Kus on viga? Mis on probleem? Pole probleeme. Nii kirjutavad k\u00f5ik raamatud. Nii tuleb kirjutada. Kui soovite maksimaalset j\u00f5udlust, kirjutage nii. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/6923293946dec45b3fd508235728ab95.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Aga praktika on n\u00e4idanud, et see ei t\u00f6\u00f6ta. Miks? Sest meil on meetod \u201eclose\u201c. Ja kui me nii teeme, on see andmebaasi vaatenurgast nagu suitsetaja t\u00f6\u00f6 andmebaasiga. Me \u00fctlesime \u201ePARSE EXECUTE DEALLOCATE\u201c.<\/p>\n<p><\/p>\n<p>Miks need liigsed statement'id ja andmete v\u00e4ljav\u00f5tmine? Need ei ole kellelegi vajalikud. Kuid tavaliselt juhtub PreparedStatement'iga just nii, et kui me need sulgeme, suletakse need andmebaasis k\u00f5ik. See ei ole see, mida me tahame. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5a357a209e414024c437d042f600f251.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Me tahame, et tervetena saaksime andmebaasiga t\u00f6\u00f6tada. \u00dcks kord tegime ja koostasime oma statement'i, mille p\u00e4rast t\u00e4idame seda mitu korda. Tegelikult on palju kordi \u2013 see on \u00fcks kord, kui rakenduse jooksul statement'it parsiti. Ja erinevates REST-ides kasutame sama statement id-d. See on meie eesm\u00e4rk. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ac4aed702a624ad9f9addc4362daf760.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kuidas me seda saavutame? <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/0f101d890d1a2af8e99f8a3a8a06fd5a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V\u00e4ga lihtsalt \u2013 statements ei tohi sulgeda. Kirjutame nii: \u00abprepare\u00bb \u00abexecute\u00bb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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 ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/724646bfc55b5e2b611b1584ca8ba6aa.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kui me sellise k\u00e4ivitame, on selge, et meie sees midagi \u00fclet\u00e4itub. Kui ei ole selge, siis saame m\u00f5\u00f5ta. Teeme b\u00e4nkimise, milles on see lihtne meetod. Loome statement'i. K\u00e4ivitame mingisugusel draiveri versioonil ja saame, et see kukub \u00fcsna kiiresti kokku kogu m\u00e4lu kaotusega, mis meil seal on. <\/p>\n<p><\/p>\n<p>Selge on, et selliseid vigu on lihtne parandada. Ma ei hakka neist r\u00e4\u00e4kima. Kuid ma \u00fctlen, et uues versioonis t\u00f6\u00f6tab see palju kiiremini. Meetod on rumal, kuid siiski. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b232a6205e708c70f52be0024bbe9980.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kuidas \u00f5igesti t\u00f6\u00f6tada? Mida me peame selleks tegema?<\/p>\n<p><\/p>\n<p>Tegelikult suletakse rakendustes alati statements. K\u00f5ikides raamatutes kirjutatakse, et need tuleb sulgeda, vastasel juhul lekkib m\u00e4lu. <\/p>\n<p><\/p>\n<p>Ja PostgreSQL ei oska p\u00e4ringute vahem\u00e4lu talletada. Iga sessioon peab ise selle vahem\u00e4lu looma. <\/p>\n<p><\/p>\n<p>Me ei soovi ka aega raisata parsimisele. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/85b9574938aa6e6bf59c956e3728bdc3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ja nagu tavaliselt on meil kaks varianti. <\/p>\n<p><\/p>\n<p>Esimene variant \u2013 me \u00fctleme, et laseme k\u00f5ik PgSQL-i kokku panna. Seal on vahem\u00e4lu. See k\u00f5ik salvestab. Tuleb suurep\u00e4rane lahendus. Oleme seda kontrollinud. Meil on 100500 p\u00e4ringut. Ei toimi. Me ei n\u00f5ustu \u2013 me ei muuda p\u00e4ringuid protseduurideks. Ei-ei. <\/p>\n<p><\/p>\n<p>Meil on teine variant \u2013 v\u00f5tta ja ise v\u00e4lja t\u00f6\u00f6tada. Avame l\u00e4htekoodi, hakkame skriptima. Skriptime-skriptime. Selgub, et see pole nii keeruline. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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>See ilmus augustis 2015. Praegu on olemas kaasaegsem versioon. Ja k\u00f5ik on suurep\u00e4rane. See t\u00f6\u00f6tab nii h\u00e4sti, et me ei muuda rakenduses midagi. Oleme isegi l\u00f5petanud m\u00f5tlema PgSQL suunas, st sellest on piisavalt, et v\u00e4hendada k\u00f5ik halduskulud praktiliselt nulli. <\/p>\n<p><\/p>\n<p>Seega aktiveeritakse Server-prepared statements viiendal t\u00e4itmisel, et mitte raisata m\u00e4lu andmebaasis iga \u00fcheainsa korduva p\u00e4ringu peale. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/8ee95e71f92187941989ad0c18ece0c0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V\u00f5ib k\u00fcsida \u2013 kus on numbrid? Mida te saate? Siin ma numbreid ei anna, sest igal p\u00e4ringul on oma numbrid.<\/p>\n<p><\/p>\n<p>Meil olid sellised p\u00e4ringud, et me kulutasime OLTP-p\u00e4ringutel umbes 20 millisekundit parsimiseks. Seal oli 0,5 millisekundit t\u00e4itmiseks ja 20 millisekundit parsimiseks. P\u00e4ring \u2013 10 KiB teksti, 170 rida plaani. See on OLTP p\u00e4ring. See k\u00fcsib 1, 5, 10 rida, m\u00f5nikord rohkem. <\/p>\n<p><\/p>\n<p>Kuid me ei soovinud kulutada 20 millisekundit. Me viimistlesime selle nulli. K\u00f5ik on suurep\u00e4rane. <\/p>\n<p><\/p>\n<p>Mida saate siit \u00f5ppida? Kui teil on Java, siis v\u00f5tate kaasa moodsa versiooni draiverist ja naudite. <\/p>\n<p><\/p>\n<p>Kui teil on m\u00f5ni muu keel, siis m\u00f5elge \u2013 v\u00f5ib-olla peaksite seda ka kaaluma? Sest l\u00f5ppkeele, n\u00e4iteks PL 8 v\u00f5i LibPQ puhul, ei pruugi olla ilmne, et kulutate aega mitte t\u00e4itmisele, vaid parsimisele, ja seda tasub kontrollida. Kuidas? K\u00f5ik on tasuta. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/fda6b1c1b126cb85c970e42335e1dc24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V\u00e4lja arvatud juhul, kui esinevad vead v\u00f5i m\u00f5ningad erip\u00e4rad. Ja just neist me praegu r\u00e4\u00e4gime. Suur osa on t\u00f6\u00f6stuslikust arheoloogiast, sellest, mida me avastasime, millele me sattusime. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9a3b383f83c5e227cd02952c5825b64f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kui p\u00e4ring genereeritakse d\u00fcnaamiliselt. Nii ka juhtub. Keegi liidab read kokku ja see teeb SQL-p\u00e4ringu.<\/p>\n<p><\/p>\n<p>Miks see halb on? See on halb selle poolest, et iga kord saame me l\u00f5puks erineva stringi.<\/p>\n<p><\/p>\n<p>Ja selle erineva stringi hashCode tuleb uuesti arvutada. See on t\u00f5eliselt CPU \u00fclesanne \u2013 leida pikka p\u00e4ringuteksti isegi olemasolevas hash\u2019is ei ole lihtne. Seega j\u00e4reldus on selge \u2013 \u00e4rge genereerige p\u00e4ringuid. Hoidke need \u00fches muutuja. Ja nautige.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/bcd4f729204cecdb572a98f357bcc33f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>J\u00e4rgmine probleem. Andmet\u00fc\u00fcbid on olulised. On ORM-e, mis \u00fctlevad, et pole oluline, milline NULL, las olla mingi. Kui on Int, siis me \u00fctleme setInt. Ja kui on NULL, siis las VARCHAR on alati. Ja mis vahet seal on, mis NULL seal l\u00f5puks on? Andmebaas m\u00f5istab ise k\u00f5ike. Ja selline pilt ei toimi. <\/p>\n<p><\/p>\n<p>Praktikas ei ole andmebaasil sugugi \u00fcksk\u00f5ik. <strong>Kui te esmakordselt \u00fctlete, et see on number, ja teiseks \u00fctlete, et see on VARCHAR, siis ei saa Server-prepared statements\u2019i uuesti kasutada. Ja sellisel juhul tuleb meie statement uuesti luua.<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/552d9c2e25cf9abbdfef12c98d027740.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kui teete sama p\u00e4ringut, siis hoidke silma peal, et andmet\u00fc\u00fcbid teie veergudes ei seguneks. Tuleb j\u00e4lgida NULL-e. See on sage viga, millega oleme kokku puutunud p\u00e4rast PreparedStatements\u2019i kasutusele v\u00f5tmist.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5d73c8f5dfa281c3bda962fc3e5edd84.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>H\u00e4sti, l\u00fclitasime sisse. V\u00f5tsime v\u00f5ib-olla draiveri. Ja j\u00f5udlus langes. K\u00f5ik l\u00e4ks halvaks. <\/p>\n<p><\/p>\n<p>Kuidas nii juhtub? Kas see on viga v\u00f5i omadus? Kahjuks ei \u00f5nnestunud m\u00f5ista \u2013 kas see on viga v\u00f5i omadus. Kuid on t\u00e4iesti lihtne stsenaarium, kuidas seda probleemi uuesti tekitada. See tabas meid t\u00e4iesti ootamatult. Ja see seisneb tegelikult teabe t\u00f5mbamises \u00fchest ainust tabelist. Meil on muidugi olnud rohkem selliseid p\u00e4ringuid. Need sisaldasid tavaliselt kahte-kolme tabelit, kuid on selline stsenaarium, mida saab reproduktsiooniks kasutada. V\u00f5tke oma andmebaasis mis tahes versioon ja proovige uuesti.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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>Oluline on see, et meil on kaks veergu, millest iga\u00fcks on indekseeritud. \u00dches veerus on NULL v\u00e4\u00e4rtusega miljon rida. Ja teises veerus on vaid 20 rida. Kui me teeme p\u00e4ringu ilma seotud muutujateta, t\u00f6\u00f6tab k\u00f5ik h\u00e4sti. <\/p>\n<p><\/p>\n<p>Kui me hakkame p\u00e4ringut tegema seotud muutujatega, st kui kasutame m\u00e4rki \u00ab?\u00bb v\u00f5i \u00ab$1\u00bb oma p\u00e4ringus, siis mida me l\u00f5puks saame?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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>Esimene t\u00e4itmine \u2013 nagu tavaliselt. Teine \u2013 veidi kiiremini. Midagi on vahem\u00e4_BUFFERd_atud. Kolmas-neljas-viies. Siis aga plaks! \u2013 ja nii see l\u00e4heb. Ja k\u00f5ige halvem on see, et see juhtub kuuendal t\u00e4itmisel. Kes oleks teadnud, et tuleb teha just kuus t\u00e4itmist, et aru saada, milline on tegelikult t\u00e4itmisplaan?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/893c955bc1e2e15a6986690e516f72a9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kes vastutab? Mis juhtus? Andmebaas sisaldab optimeerimist. Ja see on \u00fcldiselt optimeeritud tavalise juhtumi jaoks. Seega, alates teatud hetkest, l\u00e4heb see \u00fcle \u00fcldisele plaanile, mis kahjuks v\u00f5ib olla erinev. See v\u00f5ib olla sama, aga ka teine. Seal on mingi k\u00fcnnise v\u00e4\u00e4rtus, mis viib sellise k\u00e4itumiseni. <\/p>\n<p><\/p>\n<p>Mida sellega teha? Siin on muidugi keerulisem midagi eeldada. On olemas lihtne lahendus, mida me kasutame. See on +0, OFFSET 0. Kindlasti tunnete selliseid lahendusi. Lihtsalt v\u00f5tame ja lisame p\u00e4ringusse \u201e+0\u201d ja k\u00f5ik on h\u00e4sti. N\u00e4itan hiljem. <\/p>\n<p><\/p>\n<p>Ja on veel \u00fcks variant \u2013 vaadata hoolikamalt plaane. Arendaja peab mitte ainult p\u00e4ringu kirjutama, vaid ka 6 korda \u00fctlema \u201eexplain analyze\u201d. Kui 5, siis ei sobi. <\/p>\n<p><\/p>\n<p>Ja on veel kolmas variant \u2013 kirjutada pgsql-hackers'i kirja. Ma kirjutasin, aga hetkel pole selge, kas see on bug v\u00f5i funktsioon.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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>Kui me m\u00f5tleme, kas see on viga v\u00f5i funktsioon, siis parandame \u00e4ra. V\u00f5tame meie p\u00e4ringu ja lisame \"+0\". K\u00f5ik on korras. Kaks s\u00fcmbolit ja isegi ei pea m\u00f5tlema, kuidas seal ja mis seal on. V\u00e4ga lihtne. Me lihtsalt keelasime andmebaasi indeksi kasutamise selle veeru puhul. Meil pole indeksi veerus \"+0\" ja k\u00f5ik, andmebaas ei kasuta indekseid, k\u00f5ik on korras. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5261d80d084786a15b9764f6367014cd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>See on reegel 6 explain'i kohta. Praegustes versioonides tuleb seda teha 6 korda, kui teil on seotud muutujad. Kui seotud muutujaid ei ole, siis teeme nii. Ja meil on l\u00f5puks just see p\u00e4ring, mis kukub. Pole keeruline.<\/p>\n<p><\/p>\n<p>Tundub, et kui palju v\u00f5ib olla? Siin on viga, seal on viga. Tegelikult on viga igal pool. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/674dc7e7b4624baa5c79eee9e1e89db0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Vaadakem veel. N\u00e4iteks on meil kaks skeemi. Skeem A, kus on tabel Y, ja skeem B, kus on tabel Y. P\u00e4ring on \u2013 valida andmed tabelist. Mis siis juhtub? Meil tekib viga. Meil on k\u00f5ik eelnevalt loetletud. Reegel on selline \u2013 viga igal pool, meil on k\u00f5ik eelnevalt loetletud.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/dd6f544bb9e930c39e36523c9afe6003.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00fc\u00fcd on k\u00fcsimus: \"Miks?\". Tundub, et on dokumentatsioon, et kui meil on skeem, siis on muutuja \"search_path\", mis \u00fctleb, kus tuleb tabelit otsida. Tundub, et muutuja on olemas.<\/p>\n<p><\/p>\n<p>Mis probleem on? Probleem on selles, et serveri ettevalmistatud lausete puhul ei arvata, et search_path v\u00f5ib keegi muuta. See v\u00e4\u00e4rtus j\u00e4\u00e4b andmebaasile nagu konstantseks. M\u00f5ned osad ei pruugi uusi v\u00e4\u00e4rtusi \u00fcles korjata. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/f738301606d84d72bfa7cc5b568c0df8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Muidugi s\u00f5ltub see versioonist, millel te testite. See s\u00f5ltub ka sellest, kui palju teie tabelid erinevad. Versioon 9.1 lihtsalt t\u00e4idab vanu p\u00e4ringuid. Uuemad versioonid v\u00f5ivad n\u00e4ha petu ja \u00f6elda, et teil on viga.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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>Kuidas seda parandada? Lihtne retsept \u2013 \u00e4rge tehke nii. \u00c4rge muutke search_path'i rakenduse t\u00f6\u00f6 k\u00e4igus. Kui te muudate, on parem luua uus \u00fchendus.<\/p>\n<p><\/p>\n<p>Saame arutada, st avada, arutada, t\u00e4iendada. V\u00f5ib-olla suudame veenda andmebaasi arendajaid, et juhul, kui keegi muudab v\u00e4\u00e4rtust, peaks andmebaas klienti teavitama: \"Vaadake, teil on siin v\u00e4\u00e4rtus uuendatud. Kas peaksite statements'i v\u00e4rskendama, uuesti looma?\" Praegu k\u00e4itub andmebaas peidus ja ei teavitada kuidagi, et kuskil sees on statements muutunud. <\/p>\n<p><\/p>\n<p>Ja r\u00f5hutan veel kord - see ei ole Java jaoks t\u00fc\u00fcpiline. Me n\u00e4eme seda sama PL\/pgSQL-is \u00fcks \u00fchele. Kuid seal see taaselustub.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a4e7b201d0c0aa4a0092d2b6ff152df7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Proovime veel andmeid valida. Valime, valime. Meil on tabel, kus on miljon rida. Iga rida on \u00fcks kilobait. Umbes gigabait andmeid. Ja meil on Java masinas 128 megabaidi t\u00f6\u00f6m\u00e4lu. <\/p>\n<p><\/p>\n<p>Kasutame nagu k\u00f5ikides raamatutes soovitatakse, voogedastust. St avame resultSet-i ja loeme sealt andmeid j\u00e4rk-j\u00e4rgult. Kas see t\u00f6\u00f6tab? Kas m\u00e4lu puhub \u00fcles? Kas loeb natuke? Usume, et Postgres t\u00f6\u00f6tab. Ei usu. Kas saame OutOfMemory? Kes on saanud OutOfMemory? Ja kes suutis p\u00e4rast seda parandada? Kas keegi suutis parandada. <\/p>\n<p><\/p>\n<p>Kui sul on miljon rida, siis ei saa lihtsalt niimoodi valida. Peab kindlasti olema OFFSET\/LIMIT. Kes on sellise variandi poolt? Ja kes on variandi poolt, et tuleb autoCommit'iga m\u00e4ngida? <\/p>\n<p><\/p>\n<p>Siin on nagu tavaliselt k\u00f5ige ootamatum variant \u00f5ige. Ja kui sa juhuslikult autoCommiti v\u00e4lja l\u00fclitad, siis see aitab. Miks nii? Teadusele sellest ei ole teada. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2e5d9d2a8450a9069155de86a8ec387a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Aga vaikimisi valivad k\u00f5ik Postgresi andmebaasiga \u00fchenduvate klientide andmed t\u00e4ielikult. PgJDBC selles osas ei ole erand, valib k\u00f5ik read.<\/p>\n<p><\/p>\n<p>On the subject of FetchSize, there is a variation, meaning you can specify at the level of a single statement to retrieve data in chunks of 10, 50, etc. However, this doesn't work until you turn off autoCommit. Once autoCommit is turned off, it starts to function. <\/p>\n<p><\/p>\n<p>However, going through the code and setting setFetchSize everywhere is inconvenient. Therefore, we created a setting that defines the default value for the entire connection.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c9ac8e483dc6bfdbbc300972ee1ab1e0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>So, we configured this parameter. And what do we have? If we choose a small number, for example, retrieving 10 rows at a time, we incur significant overhead. Thus, this value should be set around a hundred. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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 this in bytes, but the rule of thumb is: set defaultRowFetchSize above a hundred and enjoy the benefits. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e3078faf2078fcaec10d6d99872a9990.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Now, let's move on to data insertion. Insertion is simpler, and there are different options. For instance, INSERT, VALUES. This is a good option. You can also say \"INSERT SELECT\". In practice, there is no difference; it performs the same. <\/p>\n<p><\/p>\n<p>Raamatud \u00fctlevad, et Batch-laused tuleb t\u00e4ita, raamatud \u00fctlevad, et on v\u00f5imalik t\u00e4ita keerulisemaid k\u00e4ske mitme sulgudega. Ja Postgresel on suurep\u00e4rane funktsioon \u2013 saab kasutada COPY-d, st teha seda kiiremini. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4d281c9515bdf8de7fbf1e9922d192d0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kui m\u00f5\u00f5ta, siis v\u00f5ib m\u00f5ningaid huvitavaid avastusi taas teha. Kuidas me soovime, et see toimiks? Soovime, et ei peaks anal\u00fc\u00fcsima ja mitte \u00fchtegi liigset k\u00e4sku t\u00e4itma. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7b2e4343c51dab3ddb6919bf0f117c4d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Praktikas ei lase TCP meil nii teha. Kui klient on h\u00f5ivatud p\u00e4ringu saatmisega, siis andmebaas \u00fcritab meile vastuseid saata, kuid p\u00e4ringut ei loeta. Tulemuseks on see, et klient ootab andmebaasi, kuni see p\u00e4ringu loeb, ja andmebaas ootab klienti, kuni see vastuse loeb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/17a062c30ddd83cb890775ec1722044f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Seega on klient sunnitud perioodiliselt saatma s\u00fcnkroniseerimispakette. Liigsed v\u00f5rgu\u00fchendused, liigsed ajakaotused.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ae7a4f32e2949c1d0550da051937de02.jpg\" style=\"display:block;margin: 0 auto;\" \/>Ja mida rohkem me neid lisame, seda halvemaks asi muutub. Juhtme abil on see \u00fcsna pessimistlik ja lisab neid sageli, ligikaudu iga 200 rea j\u00e4rel, s\u00f5ltuvalt ridade suurusest jne. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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>Kordagi v\u00e4ike muudatus v\u00f5ib k\u00fcmme korda kiiremaks muuta. Miks see nii on? Nagu tavaliselt, kuskil on juba kasutatud sama konstandi. Ja v\u00e4\u00e4rtus '128' t\u00e4hendas, et batchingut ei tohi kasutada.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. 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 mikrobem\u00e4rkide rakendus<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Hea, et see ametlikku versiooni ei saanud. Avastasime enne, kui hakkasime v\u00e4ljalaset tegema. K\u00f5ik v\u00e4\u00e4rtused, mida mainin, p\u00f5hinevad uusimatel versioonidel. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/28c82d9e11f6bdaf3cf62b49fc074d68.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u00f5\u00f5dame n\u00fc\u00fcd. M\u00f5\u00f5dame InsertBatch'i lihtsana. M\u00f5\u00f5dame InsertBatch'i mitmekordse, st sama asja, aga palju v\u00e4\u00e4rtusi. Nipid, mida mitte k\u00f5ik ei oska, kuid see on nii lihtne k\u00e4ik, palju lihtsam kui COPY.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/72f7cf6c3b9d9175d3e5a6d5410fe209.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Saame teha COPY.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2b0d8bbcc50f1293c9a8a1eb8eb70d2e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ja seda saab teha struktuuride peal. Deklareeri kasutaja vaikimisi t\u00fc\u00fcp, edasta massiiv ja INSERT otse tabelisse. <\/p>\n<p><\/p>\n<p>Kui avate lingi: pgjdbc\/ubenchmsrk\/InsertBatch.java, siis see kood on GitHubis. Saate vaadata, millised p\u00e4ringud seal genereeritakse. Ei ole oluline.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/28dede211fe401286685ca62081453de.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Oleme k\u00e4ivitanud. Ja esimene, mida m\u00f5istsime, on see, et batchi mitte kasutamine on lihtsalt v\u00f5imatu. K\u00f5ik batching'u variandid on null, st t\u00e4itmise aeg on praktiliselt null v\u00f5rreldes \u00fche korraliku t\u00e4itmisega. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4ea68f712bbdeadd35f5baa4ddaeff33.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kandmeed salvestatakse. Seal on \u00fcsna lihtne tabel. Kolm veergu. Ja mida me siin n\u00e4eme? Me n\u00e4eme, et k\u00f5ik need kolm varianti on enam-v\u00e4hem v\u00f5rreldavad. Ja COPY on loomulikult parem.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2686f1d7eea347a839ac23aef2a27e53.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>See on siis, kui me sisestame t\u00fckkide kaupa. Kui me r\u00e4\u00e4kisime, et \u00fchte v\u00e4\u00e4rtust VALUES, kahte v\u00e4\u00e4rtust VALUES, kolme v\u00e4\u00e4rtust VALUES v\u00f5i me panime seal 10 komaga kokku. See on just praegu horisontaalselt. 1, 2, 4, 128. On n\u00e4ha, et Batch Insert, mis on sinisega joonistatud, muudab selle lihtsamaks. See t\u00e4hendab, et kui sisestate \u00fche kaupa v\u00f5i isegi neli, siis muutub see kaks korda paremaks, kuna me VALUES sisse panime veidi rohkem. V\u00e4hem EXECUTE operatsioone.<\/p>\n<p><\/p>\n<p>COPY kasutamine v\u00e4ikeste andmemahtude korral on \u00e4\u00e4rmiselt ebaefektiivne. Ma ei joonistanud isegi esimest kahte. Need l\u00e4hevad taevasse, st need rohelised numbrid COPY jaoks.<\/p>\n<p><\/p>\n<p>COPY tuleb kasutada, kui teil on andmemaht v\u00e4hemalt \u00fcle saja rea. \u00dchenduse avamise kulud on suured. Ja ausalt \u00f6eldes, ma ei ole seda teemat s\u00fcgavamalt uurinud. Batch\u2019i ma optimeerisin, COPY-d mitte. <\/p>\n<p><\/p>\n<p>Mida me edasi teeme? M\u00f5\u00f5dame. Saame aru, et tuleb kasutada kas struktuure v\u00f5i nutikat batch\u2019i, mis \u00fchendab mitu v\u00e4\u00e4rtust. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL ja JDBC pigistame k\u00f5ik mahlad. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e02fa2574e2d1b678382db15064610d3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Mida peaks t\u00e4nasest ettekandest v\u00e4lja tooma?<\/p>\n<p><\/p>\n<ul>\n<li>PreparedStatement \u2013 see on meie k\u00f5ik. See toob palju kasu meie j\u00f5udlusele. See annab suure kausi t\u00f5rva. <\/li>\n<li>Ja EXPLAIN ANALYZE tuleb teha 6 korda.<\/li>\n<li>Ja tuleb kasutada OFFSET 0 ja trikke nagu +0, et reguleerida \u00fclej\u00e4\u00e4nud protsenti meie probleemsetest p\u00e4ringutest.<\/li>\n<\/ul>\n<p>Allikas: <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.0.1 - 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\/et\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.0.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"et_EE\" \/>\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\/et\/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 ja JDBC: pigistame k\u00f5ik, mis v\u00f5tta annab. Vladimir Sitnikov | ProHoster","description":"Pakun tutvuda 2016. aasta alguses Vladimir Sitnikovi aruande \"PostgreSQL ja JDBC - v\u00f5tame k\u00f5ik v\u00e4lja\" t\u00f5lkega","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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\/et\/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\/et\/wp-json\/wp\/v2\/posts\/79893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=79893"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/79893\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/79894"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=79893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=79893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=79893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}