{"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\/ro\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","title":{"rendered":"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>V\u0103 propun s\u0103 consulta\u021bi transcrierea raportului din \u00eenceputul anului 2016 al lui Vladimir Sitnikov \"PostgreSQL \u0219i JDBC - extragem totul\"<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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 \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/030666abda53aa388b1cb1d0c46a7524.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Bun\u0103 ziua! Numele meu este Vladimir Sitnikov. Lucrez de 10 ani \u00een compania NetCracker. \u00cen principal, m\u0103 ocup cu performan\u021ba. Tot ce are leg\u0103tur\u0103 cu Java \u0219i SQL - acestea sunt lucrurile pe care le \u00eendr\u0103gesc. <\/p>\n<p><\/p>\n<p>\u0218i ast\u0103zi v\u0103 voi vorbi despre ce am \u00eent\u00e2mpinat \u00een companie c\u00e2nd am \u00eenceput s\u0103 folosim PostgreSQL ca server de baze de date. \u00cen principal lucr\u0103m cu Java. \u00cens\u0103, ceea ce voi povesti ast\u0103zi se leag\u0103 nu doar de Java. Dup\u0103 cum arat\u0103 practica, acest lucru apare \u0219i \u00een alte limbaje. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b01a214d782c7e32797a6c6466457979.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Vom discuta despre:<\/p>\n<p><\/p>\n<ul>\n<li>selectarea datelor. <\/li>\n<li>salvarea datelor. <\/li>\n<li>de asemenea, despre performan\u021b\u0103. <\/li>\n<li>\u0218i despre capcanele ascunse care sunt acolo. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/57da0c6aa4bb62e1beb77160f5aee7e7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>S\u0103 \u00eencepem cu o \u00eentrebare simpl\u0103. Alegem o linie din tabel dup\u0103 cheia primar\u0103. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/d5fcb1172aef402a87064498bf8a5218.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Baza de date se afl\u0103 pe acela\u0219i host. \u0218i tot acest lucru dureaz\u0103 20 de milisecunde.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/103b7f9736b82a3313491b2f0d2a5824.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Aceste 20 de milisecunde sunt foarte multe. Dac\u0103 ave\u021bi 100 de astfel de cereri, atunci petrece\u021bi timp \u00een fiecare secund\u0103 pentru a procesa aceste cereri, adic\u0103 pierde\u021bi timpul \u00een van.<\/p>\n<p><\/p>\n<p>Nu ne place s\u0103 facem asta \u0219i ne uit\u0103m la ceea ce ne ofer\u0103 baza de date pentru aceasta. Baza de date ne propune dou\u0103 variante de execu\u021bie a cererilor. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b467e86a0f8c4c32cea182fe27a28e6e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Prima variant\u0103 - este o cerere simpl\u0103. Ce este bun la ea? C\u0103 o lu\u0103m \u0219i o trimitem, \u0219i nimic mai mult. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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>Baza are \u0219i o cerere extins\u0103, care este mai sofisticat\u0103, dar mai func\u021bional\u0103. Se pot trimite separat cereri pentru analiz\u0103, execu\u021bie, legarea variabilelor etc. <\/p>\n<p><\/p>\n<p>Cererea super extins\u0103 - este ceea ce nu vom acoperi \u00een prezentarea curent\u0103. Poate vrem ceva de la baza de date \u0219i exist\u0103 aceast\u0103 list\u0103 de dorin\u021be, care este formulat\u0103 \u00eentr-un anumit fel, adic\u0103 acestea sunt lucrurile pe care le dorim, dar nu sunt posibile acum \u0219i \u00een anul urm\u0103tor. A\u0219a c\u0103 am scris-o \u0219i vom merge s\u0103-i \u00eentreb\u0103m pe cei principali.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/19094729074fd28c5b9f236833a9a266.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Iar ceea ce putem face sunt cererea simpl\u0103 \u0219i cererea extins\u0103.<\/p>\n<p><\/p>\n<p>Care este specificitatea fiec\u0103rui abordare? <\/p>\n<p><\/p>\n<p>Cererea simpl\u0103 este bine de folosit pentru execu\u021bii ocazionale. O dat\u0103 ce a fost executat\u0103, o uit\u0103m. \u0218i problema este c\u0103 nu suport\u0103 formatul binar de date, adic\u0103 pentru anumite sisteme de \u00eenalt\u0103 performan\u021b\u0103 nu este adecvat.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/062c0e45cefd91ece331a306a7651bde.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Interogarea extins\u0103 \u2013 v\u0103 ajut\u0103 s\u0103 economisi\u021bi timp \u00een procesul de parsare. Acesta este ceea ce am realizat \u0219i am \u00eenceput s\u0103 folosim. Ne-a ajutat enorm. Acolo nu exist\u0103 doar economii \u00een parsare. Exist\u0103 economii \u0219i \u00een transferul de date. Transmiterea datelor \u00een format binar este mult mai eficient\u0103. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/6df83a2f3736568668cf9d74de5d9758.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>S\u0103 trecem la practic\u0103. Iat\u0103 cum arat\u0103 o aplica\u021bie tipic\u0103. Poate fi Java etc. <\/p>\n<p><\/p>\n<p>Am creat un statement. Am executat comanda. Am creat un close. Unde este gre\u0219eala aici? Care este problema? Nu exist\u0103 probleme. A\u0219a este scris \u00een toate c\u0103r\u021bile. A\u0219a trebuie s\u0103 scriem. Dac\u0103 dori\u021bi o performan\u021b\u0103 maxim\u0103, scrie\u021bi a\u0219a. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/6923293946dec45b3fd508235728ab95.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dar practica a ar\u0103tat c\u0103 aceasta nu func\u021bioneaz\u0103. De ce? Pentru c\u0103 avem metoda \u201eclose\u201d. \u0218i c\u00e2nd facem a\u0219a, din punctul de vedere al bazei de date, este ca \u0219i cum un fum\u0103tor ar lucra cu baza de date. Am spus \u201ePARSE EXECUTE DEALLOCATE\u201d.<\/p>\n<p><\/p>\n<p>De ce aceste crea\u021bii \u0219i desc\u0103rc\u0103ri inutile de statements? Nimeni nu are nevoie de ele. Dar de obicei, \u00een PreparedStatement a\u0219a se \u00eent\u00e2mpl\u0103, c\u00e2nd le \u00eenchidem, acestea \u00eenchid tot \u00een baza de date. Nu aceasta este ceea ce ne dorim. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5a357a209e414024c437d042f600f251.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Vrem s\u0103 lucr\u0103m cu baza de date ca oamenii s\u0103n\u0103to\u0219i. O dat\u0103 am luat \u0219i preg\u0103tit statementul nostru, apoi \u00eel execut\u0103m de multe ori. De fapt, multe ori \u2013 este o dat\u0103 pe parcursul \u00eentregii vie\u021bi a aplica\u021biei c\u00e2nd l-am parcurs. \u0218i folosim acela\u0219i id de statement pe diferite REST-uri. Aceasta este scopul nostru. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ac4aed702a624ad9f9addc4362daf760.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cum putem realiza acest lucru? <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/0f101d890d1a2af8e99f8a3a8a06fd5a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Foarte simplu \u2013 nu trebuie s\u0103 \u00eenchidem statements. Scriem a\u0219a: \u201eprepare\u201d \u201eexecute\u201d. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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 \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/724646bfc55b5e2b611b1584ca8ba6aa.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dac\u0103 lans\u0103m a\u0219a ceva, este evident c\u0103 undeva se va dep\u0103\u0219i capacitatea. Dac\u0103 nu este clar, putem m\u0103sura. S\u0103 lu\u0103m \u0219i s\u0103 scriem un benchmark \u00een care s\u0103 fie o metod\u0103 at\u00e2t de simpl\u0103. Cre\u0103m un statement. \u00cel rul\u0103m pe o anumit\u0103 versiune a driverului \u0219i constat\u0103m c\u0103 se pr\u0103bu\u0219e\u0219te destul de repede, pierz\u00e2nd toat\u0103 memoria care a fost consumat\u0103. <\/p>\n<p><\/p>\n<p>Este clar c\u0103 astfel de erori sunt u\u0219or de corectat. Nu voi vorbi despre ele. Dar voi spune c\u0103 \u00een noua versiune func\u021bioneaz\u0103 mult mai repede. Este o metod\u0103 inutil\u0103, dar cu toate acestea. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b232a6205e708c70f52be0024bbe9980.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cum s\u0103 lucr\u0103m corect? Ce trebuie s\u0103 facem pentru aceasta?<\/p>\n<p><\/p>\n<p>\u00cen realitate, aplica\u021biile \u00eenchid \u00eentotdeauna statements. \u00cen toate c\u0103r\u021bile se spune s\u0103 le \u00eenchide\u021bi, altfel memoria va sc\u0103pa. <\/p>\n<p><\/p>\n<p>\u0218i PostgreSQL nu poate s\u0103 cacheze interog\u0103rile. Fiecare sesiune trebuie s\u0103 \u00ee\u0219i creeze singur\u0103 acest cache. <\/p>\n<p><\/p>\n<p>\u0218i nu vrem s\u0103 pierdem timp pe parsare. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/85b9574938aa6e6bf59c956e3728bdc3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i, ca de obicei, avem dou\u0103 op\u021biuni. <\/p>\n<p><\/p>\n<p>Prima variant\u0103 \u2013 lu\u0103m \u0219i spunem c\u0103 s\u0103 \u00eempachet\u0103m totul \u00een PgSQL. Acolo exist\u0103 cache. Totul este cache-uit. Va fi minunat. Am analizat a\u0219a ceva. Avem 100500 de cereri. Nu func\u021bioneaz\u0103. Nu suntem de acord s\u0103 transform\u0103m cererile manual \u00een proceduri. Nu, nu. <\/p>\n<p><\/p>\n<p>Avem o a doua variant\u0103 \u2013 s\u0103 lu\u0103m \u0219i s\u0103 facem noi. Deschidem sursele, \u00eencepem s\u0103 modific\u0103m. Continu\u0103m s\u0103 modific\u0103m. A rezultat c\u0103 nu este at\u00e2t de complicat de realizat. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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>A ap\u0103rut asta \u00een august 2015. Acum exist\u0103 deja o versiune mai modern\u0103. \u0218i totul este grozav. Func\u021bioneaz\u0103 at\u00e2t de bine \u00eenc\u00e2t nu mai facem nicio modificare \u00een aplica\u021bie. \u0218i chiar am \u00eencetat s\u0103 ne g\u00e2ndim la PgSQL, adic\u0103 ne-a fost suficient pentru a reduce practic cheltuielile la zero. <\/p>\n<p><\/p>\n<p>Prin urmare, declara\u021biile preg\u0103tite la nivel de server se activeaz\u0103 la a cincea execu\u021bie pentru a nu consuma memorie \u00een baza de date pentru fiecare cerere unic\u0103. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/8ee95e71f92187941989ad0c18ece0c0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Se poate \u00eentreba \u2013 unde sunt cifrele? Ce ob\u021bine\u021bi? \u0218i aici nu voi oferi cifre, deoarece fiecare cerere are propriile sale cifre.<\/p>\n<p><\/p>\n<p>Am avut cereri de genul c\u0103 cheltuiam cam 20 de milisecunde pe analiz\u0103 la cererile OLTP. Acolo erau 0,5 milisecunde pentru execu\u021bie, 20 de milisecunde pentru analiz\u0103. Cererea \u2013 10 KiB de text, 170 de r\u00e2nduri \u00een plan. Aceasta este o cerere OLTP. Solicit\u0103 1, 5, 10 r\u00e2nduri, uneori mai multe. <\/p>\n<p><\/p>\n<p>Dar nu ne-am dorit deloc s\u0103 cheltuim 20 de milisecunde. Am redus totul la 0. Totul este grozav. <\/p>\n<p><\/p>\n<p>Ce pute\u021bi s\u0103 deduce\u021bi de aici? Dac\u0103 ave\u021bi Java, lua\u021bi versiunea modern\u0103 a driverului \u0219i v\u0103 bucura\u021bi. <\/p>\n<p><\/p>\n<p>Dac\u0103 ave\u021bi un alt limbaj, g\u00e2ndi\u021bi-v\u0103 \u2013 poate c\u0103 ar trebui \u0219i voi? Deoarece din perspectiva limbajului final, de exemplu, dac\u0103 ave\u021bi PL 8 sau LibPQ, nu este evident c\u0103 pierde\u021bi timp nu pentru execu\u021bie, ci pentru analiz\u0103, iar asta merit\u0103 verificat\u0103. Cum? Totul este gratuit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/fda6b1c1b126cb85c970e42335e1dc24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cu excep\u021bia faptului c\u0103 exist\u0103 erori, anumite particularit\u0103\u021bi. \u0218i despre ele vom vorbi acum. O mare parte va fi despre arheologia industrial\u0103, despre ce am descoperit, la ce ne-am ciocnit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9a3b383f83c5e227cd02952c5825b64f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dac\u0103 cererea se genereaz\u0103 dinamic. A\u0219a ceva se \u00eent\u00e2mpl\u0103. Cineva concateneaz\u0103 \u0219irurile, rezult\u00e2nd o cerere SQL.<\/p>\n<p><\/p>\n<p>De ce este aceasta proast\u0103? Este proast\u0103 pentru c\u0103, \u00een final, ob\u021binem de fiecare dat\u0103 un \u0219ir diferit.<\/p>\n<p><\/p>\n<p>\u0218i aceast\u0103 linie divers\u0103 trebuie s\u0103 recalculeze din nou hashCode. Aceasta este cu adev\u0103rat o sarcin\u0103 CPU \u2013 g\u0103sirea unui text lung al interog\u0103rii \u00eentr-un hash existent nu este at\u00e2t de simpl\u0103. Ceea ce \u00eenseamn\u0103 c\u0103 regula este simpl\u0103 \u2013 nu genera\u021bi interog\u0103ri. Stoca\u021bi-le \u00eentr-o singur\u0103 variabil\u0103. \u0218i bucura\u021bi-v\u0103.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/bcd4f729204cecdb572a98f357bcc33f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Urm\u0103toarea problem\u0103. Tipurile de date sunt importante. Exist\u0103 ORM-uri care spun c\u0103 nu conteaz\u0103 ce NULL exist\u0103, s\u0103 fie unul oarecare. Dac\u0103 este Int, spunem setInt. Iar dac\u0103 este NULL, atunci s\u0103 fie \u00eentotdeauna VARCHAR. \u0218i, \u00een fond, ce conteaz\u0103 ce NULL? Baza de date \u00een\u021belege de la sine totul. \u0218i acest scenariu nu func\u021bioneaz\u0103. <\/p>\n<p><\/p>\n<p>\u00cen practic\u0103, baza de date nu \u00eei pas\u0103 deloc. <strong>Dac\u0103 prima dat\u0103 a\u021bi spus c\u0103 este un num\u0103r, iar a doua dat\u0103 a\u021bi spus c\u0103 este VARCHAR, atunci nu se pot reutiliza afirma\u021biile preg\u0103tite de server. \u0218i \u00een acest caz, trebuie s\u0103 recrea\u021bi din nou afirma\u021bia.<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/552d9c2e25cf9abbdfef12c98d027740.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dac\u0103 efectua\u021bi aceea\u0219i interogare, ave\u021bi grij\u0103 s\u0103 nu v\u0103 confunda\u021bi tipurile de date \u00een coloan\u0103. Trebuie s\u0103 fi\u021bi aten\u021bi la NULL. Aceasta este o eroare frecvent\u0103 pe care am avut-o dup\u0103 ce am \u00eenceput s\u0103 folosim PreparedStatements.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5d73c8f5dfa281c3bda962fc3e5edd84.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Bine, am activat. Poate c\u0103 am luat un driver. \u0218i performan\u021ba a sc\u0103zut. Totul a devenit r\u0103u. <\/p>\n<p><\/p>\n<p>Cum poate fi a\u0219a? Este un bug sau o caracteristic\u0103? Din p\u0103cate, nu am reu\u0219it s\u0103 \u00een\u021belegem \u2013 este un bug sau o caracteristic\u0103. Dar exist\u0103 un scenariu destul de simplu pentru a reproduce aceast\u0103 problem\u0103. Ne-a surprins complet. \u0218i const\u0103 \u00een selectarea literalmente dintr-un singur tabel. Desigur, am avut mai multe astfel de interog\u0103ri. \u00cen general, acestea includeau dou\u0103-trei tabele, dar exist\u0103 un astfel de scenariu de reproducere. Lua\u021bi baza dumneavoastr\u0103 de date din orice versiune \u0219i reproduce\u021bi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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>Ideea este c\u0103 avem dou\u0103 coloane, fiecare indexat\u0103. \u00centr-o coloan\u0103, la valoarea NULL, sunt un milion de r\u00e2nduri. Iar \u00een cealalt\u0103 coloan\u0103 sunt doar 20 de r\u00e2nduri. C\u00e2nd execut\u0103m f\u0103r\u0103 variabile corelate, atunci totul func\u021bioneaz\u0103 bine. <\/p>\n<p><\/p>\n<p>Dac\u0103 \u00eencepem s\u0103 execut\u0103m cu variabile corelate, adic\u0103 execut\u0103m semnul \u201e?\u201d sau \u201e$1\u201d pentru interogarea noastr\u0103, atunci ce ob\u021binem \u00een cele din urm\u0103?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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>Prima execu\u021bie \u2013 a\u0219a cum trebuie. A doua \u2013 pu\u021bin mai repede. Unele lucruri s-au pus \u00een cache. A treia, a patra, a cincea. Apoi, bam \u2013 \u0219i a\u0219a a fost. \u0218i ceea ce este cel mai r\u0103u, se \u00eent\u00e2mpl\u0103 la a \u0219asea execu\u021bie. Cine \u0219tia c\u0103 trebuie s\u0103 facem exact \u0219ase execu\u021bii pentru a \u00een\u021belege care este cu adev\u0103rat planul de execu\u021bie?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/893c955bc1e2e15a6986690e516f72a9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cine este de vin\u0103? Ce s-a \u00eent\u00e2mplat? Baza de date con\u021bine o optimizare. \u0218i este, \u00eentr-un fel, optimizat\u0103 pentru un caz generic. A\u0219adar, \u00eencep\u00e2nd de la un anumit punct, trece pe un plan generic, care, din p\u0103cate, poate fi diferit. Poate fi la fel, dar poate fi \u0219i diferit. \u0218i exist\u0103 un anumit prag care duce la un asemenea comportament. <\/p>\n<p><\/p>\n<p>Ce se poate face \u00een leg\u0103tur\u0103 cu asta? Aici, desigur, este mai complicat s\u0103 facem vreo presupunere. Exist\u0103 o solu\u021bie simpl\u0103 pe care o folosim. Este +0, OFFSET 0. Cu siguran\u021b\u0103, cunoa\u0219te\u021bi astfel de solu\u021bii. Pur \u0219i simplu ad\u0103ug\u0103m \u201e+0\u201d \u00een interogare \u0219i totul este bine. Voi ar\u0103ta mai t\u00e2rziu. <\/p>\n<p><\/p>\n<p>\u0218i exist\u0103 o alt\u0103 op\u021biune \u2013 s\u0103 ne uit\u0103m mai atent la planuri. Dezvoltatorul trebuie s\u0103 nu scrie doar interogarea, ci \u0219i s\u0103 spun\u0103 \u201eexplain analyze\u201d de 6 ori. Dac\u0103 o face de 5 ori, nu va fi suficient. <\/p>\n<p><\/p>\n<p>\u0218i exist\u0103 o a treia op\u021biune \u2013 s\u0103 scriem un mesaj pe pgsql-hackers. Am scris, dar nu este clar \u00eenc\u0103 \u2013 este un bug sau o caracteristic\u0103.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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>P\u00e2n\u0103 c\u00e2nd ne g\u00e2ndim \u2013 este un bug sau o caracteristic\u0103, s\u0103 repar\u0103m. S\u0103 lu\u0103m interogarea noastr\u0103 \u0219i s\u0103 ad\u0103ug\u0103m \u201e+0\u201d. Totul este bine. Dou\u0103 caractere \u0219i nici m\u0103car nu trebuie s\u0103 ne g\u00e2ndim cum \u0219i ce. Foarte simplu. Pur \u0219i simplu am interzis bazei de date s\u0103 foloseasc\u0103 indexul pe aceast\u0103 coloan\u0103. Nu avem index pe coloana \u201e+0\u201d \u0219i totul, baza de date nu folose\u0219te indexul, totul este bine. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/5261d80d084786a15b9764f6367014cd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Iat\u0103 regula celor 6 \u201eexplain-uri\u201d. Acum, \u00een versiunile curente, trebuie s\u0103 o faci de 6 ori, dac\u0103 ai variabile legate. Dac\u0103 nu ai variabile legate, atunci facem a\u0219a. \u0218i, \u00een cele din urm\u0103, anume aceast\u0103 interogare e\u0219ueaz\u0103. Nu este complicat.<\/p>\n<p><\/p>\n<p>Poate c\u0103 pare c\u0103 nu se poate? Aici este un bug, acolo este un bug. De fapt, bug-uri sunt peste tot. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/674dc7e7b4624baa5c79eee9e1e89db0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Hai s\u0103 ne uit\u0103m \u0219i mai departe. De exemplu, avem dou\u0103 scheme. Schema A cu tabela Y \u0219i schema B cu tabela Y. Interogarea \u2013 selecta\u021bi date din tabel. Ce avem \u00een acest caz? Vom avea o eroare. Tot ce am men\u021bionat anterior va ap\u0103rea. Regula este astfel \u2013 bug-uri sunt peste tot, vom avea tot ce am men\u021bionat anterior.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/dd6f544bb9e930c39e36523c9afe6003.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Acum \u00eentrebarea: \u201eDe ce?\u201d. Poate c\u0103 exist\u0103 documenta\u021bie care spune c\u0103, dac\u0103 avem o schem\u0103, exist\u0103 variabila \u201esearch_path\u201d care indic\u0103 unde trebuie c\u0103utat\u0103 tabela. Poate c\u0103 variabila exist\u0103.<\/p>\n<p><\/p>\n<p>Care este problema? Problema este c\u0103 server-prepared statements nu suspecteaz\u0103 c\u0103 search_path ar putea fi modificat de cineva. Aceast\u0103 valoare r\u0103m\u00e2ne, \u00eentr-un fel, constant\u0103 pentru baza de date. \u0218i unele p\u0103r\u021bi pot s\u0103 nu preia noile valori. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/f738301606d84d72bfa7cc5b568c0df8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Desigur, depinde de versiunea pe care o testa\u021bi. Depinde de c\u00e2t de mult difer\u0103 tabelele dumneavoastr\u0103. Iar versiunea 9.1 va executa pur \u0219i simplu vechile interog\u0103ri. Versiunile noi pot detecta problemele \u0219i pot spune c\u0103 ave\u021bi o eroare.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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 + declara\u021bii preg\u0103tite de server =<br \/>\nplanul cached nu trebuie s\u0103 schimbe tipul de rezultat<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Cum se repar\u0103 asta? Exist\u0103 o re\u021bet\u0103 simpl\u0103 \u2013 nu face\u021bi a\u0219a. Nu trebuie s\u0103 schimba\u021bi search_path \u00een timpul func\u021bion\u0103rii aplica\u021biei. Dac\u0103 schimba\u021bi, mai bine s\u0103 crea\u021bi o nou\u0103 conexiune.<\/p>\n<p><\/p>\n<p>Putem discuta, adic\u0103 s\u0103 deschidem, s\u0103 discut\u0103m, s\u0103 complet\u0103m. Poate c\u0103 vom convinge dezvoltatorii bazei de date c\u0103 \u00een cazul \u00een care cineva schimb\u0103 o valoare, baza de date ar trebui s\u0103 spun\u0103 clientului: \u201eUita\u021bi, s-a actualizat o valoare. Poate ar trebui s\u0103 reseta\u021bi declara\u021biile, s\u0103 le recrea\u021bi?\u201d. Acum baza de date se comport\u0103 t\u0103cut \u0219i nu informeaz\u0103 \u00een niciun fel despre schimb\u0103rile intervenite \u00een interiorul declara\u021biilor. <\/p>\n<p><\/p>\n<p>\u0218i \u00eenapoi subliniez \u2013 asta nu este tipic pentru Java. Vom vedea acela\u0219i lucru \u00een PL\/pgSQL unul la unul. Dar acolo va fi reprodus.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a4e7b201d0c0aa4a0092d2b6ff152df7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Hai s\u0103 \u00eencerc\u0103m \u00eenc\u0103 o dat\u0103 s\u0103 select\u0103m datele. Select\u0103m, select\u0103m. Avem un tabel cu un milion de r\u00e2nduri. Fiecare r\u00e2nd are un kilobyte. Aproape un gigabyte de date. \u0218i avem memorie de lucru \u00een ma\u0219ina Java de 128 megabytes. <\/p>\n<p><\/p>\n<p>Noi, a\u0219a cum este recomandat \u00een toate c\u0103r\u021bile, folosim procesarea pe flux. Adic\u0103 deschidem resultSet \u0219i citim din el datele pu\u021bin c\u00e2te pu\u021bin. Va func\u021biona asta? Nu va c\u0103dea din cauza memoriei? Va citi pu\u021bin c\u00e2te pu\u021bin? Hai s\u0103 ne punem \u00eencrederea \u00een baz\u0103, \u00een Postgres s\u0103 ne punem \u00eencrederea. Nu ne \u00eencredem. Vom c\u0103dea \u00een OutOfMemory? Cine a c\u0103zut \u00een OutOfMemory? \u0218i cine a reu\u0219it s\u0103 repare dup\u0103 asta? Cineva a reu\u0219it s\u0103 repare. <\/p>\n<p><\/p>\n<p>Dac\u0103 ave\u021bi un milion de r\u00e2nduri, nu pute\u021bi s\u0103 selecta\u021bi pur \u0219i simplu. Trebuie neap\u0103rat OFFSET \/ LIMIT. Cine este pentru aceast\u0103 op\u021biune? \u0218i cine este pentru op\u021biunea c\u0103 trebuie s\u0103 ne juc\u0103m cu autoCommit? <\/p>\n<p><\/p>\n<p>Aici, ca de obicei, cea mai nea\u0219teptat\u0103 op\u021biune se dovede\u0219te a fi corect\u0103. \u0218i dac\u0103 \u00eent\u00e2mpl\u0103tor opri\u021bi autoCommit, va ajuta. De ce? \u0218tiin\u021bei nu este cunoscut. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2e5d9d2a8450a9069155de86a8ec387a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dar \u00een mod implicit toate clientele care se conecteaz\u0103 la baza de date Postgres selecteaz\u0103 datele \u00een \u00eentregime. PgJDBC \u00een acest sens nu face excep\u021bie, selecteaz\u0103 toate r\u00e2ndurile.<\/p>\n<p><\/p>\n<p>Exist\u0103 o varia\u021bie pe tema FetchSize, adic\u0103 pute\u021bi la nivelul unui anumit statement s\u0103 spune\u021bi c\u0103 aici, v\u0103 rug\u0103m, alege\u021bi datele c\u00e2te 10, 50. Dar asta nu func\u021bioneaz\u0103 p\u00e2n\u0103 nu opri\u021bi autoCommit. Opri\u021bi autoCommit \u2013 \u00eencepe s\u0103 func\u021bioneze. <\/p>\n<p><\/p>\n<p>Dar a merge pe cod \u0219i a seta setFetchSize peste tot este incomod. De aceea, am creat o astfel de setare care va specifica valoarea implicit\u0103 pentru \u00eentreaga conexiune.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c9ac8e483dc6bfdbbc300972ee1ab1e0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Iat\u0103, am spus asta. Am configurat parametrul. \u0218i ce am ob\u021binut? Dac\u0103 alegem un num\u0103r mic, de exemplu, alegem c\u00e2te 10 r\u00e2nduri, avem costuri de overhead destul de mari. A\u0219a c\u0103 ar trebui s\u0103 set\u0103m aceast\u0103 valoare la aproximativ o sut\u0103. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/073e7a4af63688396eefe332ba8261d6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00cen ideal, bine\u00een\u021beles, ar trebui s\u0103 \u00eenv\u0103\u021b\u0103m s\u0103 limit\u0103m \u0219i \u00een bi\u021bi, dar re\u021beta este aceasta: set\u0103m defaultRowFetchSize mai mare de o sut\u0103 \u0219i ne bucur\u0103m. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e3078faf2078fcaec10d6d99872a9990.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Hai s\u0103 trecem la inserarea datelor. Inserarea este mai simpl\u0103, exist\u0103 diferite variante. De exemplu, INSERT, VALUES. Este o variant\u0103 bun\u0103. Putem spune \u201eINSERT SELECT\u201d. \u00cen practic\u0103, este acela\u0219i lucru. Nu exist\u0103 nicio diferen\u021b\u0103 \u00een performan\u021b\u0103. <\/p>\n<p><\/p>\n<p>C\u0103r\u021bile spun c\u0103 trebuie s\u0103 execut\u0103m Batch statement, c\u0103 putem executa comenzi mai complexe cu mai multe paranteze. \u0218i \u00een Postgres exist\u0103 o func\u021bie minunat\u0103 \u2013 putem face COPY, adic\u0103 putem face asta mai repede. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4d281c9515bdf8de7fbf1e9922d192d0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dac\u0103 m\u0103sur\u0103m, putem descoperi din nou c\u00e2teva lucruri interesante. Cum dorim s\u0103 func\u021bioneze acest lucru? Vrem s\u0103 nu facem parsing \u0219i s\u0103 nu execut\u0103m comenzi suplimentare. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7b2e4343c51dab3ddb6919bf0f117c4d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00cen practic\u0103, TCP nu ne permite s\u0103 facem asta. Dac\u0103 clientul este ocupat cu trimiterea unei cereri, baza de date, \u00een \u00eencerc\u0103rile de a ne trimite r\u0103spunsuri, nu cite\u0219te cererile. \u00cen cele din urm\u0103, clientul a\u0219teapt\u0103 baza de date s\u0103 citeasc\u0103 cererea, iar baza de date a\u0219teapt\u0103 clientul s\u0103 citeasc\u0103 r\u0103spunsul. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/17a062c30ddd83cb890775ec1722044f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i de aceea clientul este nevoit s\u0103 trimit\u0103 periodic un pachet de sincronizare. Interac\u021biuni de re\u021bea inutile, pierderi de timp suplimentare.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ae7a4f32e2949c1d0550da051937de02.jpg\" style=\"display:block;margin: 0 auto;\" \/>\u0218i cu c\u00e2t ad\u0103ug\u0103m mai multe, cu at\u00e2t devine mai r\u0103u. Driverul este destul de pesimist \u0219i le adaug\u0103 destul de des, aproximativ o dat\u0103 la 200 de r\u00e2nduri, \u00een func\u021bie de dimensiunea r\u00e2ndurilor etc. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. 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>Se \u00eent\u00e2mpl\u0103 uneori s\u0103 corectezi un singur r\u00e2nd \u0219i totul se accelereaz\u0103 de zece ori. Asta se \u00eent\u00e2mpl\u0103. De ce? Ca de obicei, o constant\u0103 a fost folosit\u0103 deja undeva. \u0218i valoarea \u201e128\u201d \u00eensemna \u2013 nu folosi batching.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7b6bcd95591b36441037b4c4631d2ad0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"http:\/\/openjdk.java.net\/projects\/code-tools\/jmh\/\">Java microbenchmark harness<\/a><\/noindex><\/p>\n<p><\/p>\n<p>E bine c\u0103 asta nu a ajuns \u00een versiunea oficial\u0103. Am descoperit \u00eenainte de a \u00eencepe s\u0103 lans\u0103m versiunea. Toate valorile pe care le men\u021bionez se bazeaz\u0103 pe versiunile moderne. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/28c82d9e11f6bdaf3cf62b49fc074d68.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Hai s\u0103 m\u0103sur\u0103m. M\u0103sur\u0103m InsertBatch simplu. M\u0103sur\u0103m InsertBatch multipl\u0103, adic\u0103 acela\u0219i lucru, dar cu multe valori. O mi\u0219care ingenioas\u0103. Nu toat\u0103 lumea \u0219tie s\u0103 fac\u0103 asta, dar este o mi\u0219care simpl\u0103, mult mai simpl\u0103 dec\u00e2t COPY.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/72f7cf6c3b9d9175d3e5a6d5410fe209.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Se poate face COPY.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2b0d8bbcc50f1293c9a8a1eb8eb70d2e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i se poate face acest lucru pe structuri. Declara\u021bi User default type, transmite\u021bi un array \u0219i insera\u021bi direct \u00een tabel. <\/p>\n<p><\/p>\n<p>Dac\u0103 deschide\u021bi linkul: pgjdbc\/ubenchmsrk\/InsertBatch.java, acest cod se afl\u0103 pe GitHub. Pute\u021bi vedea exact ce interog\u0103ri sunt generate acolo. Nu este esen\u021bial.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/28dede211fe401286685ca62081453de.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Am \u00eenceput. \u0218i primul lucru pe care l-am realizat este c\u0103 a nu folosi batch - este pur \u0219i simplu imposibil. Toate op\u021biunile de batching sunt egale cu zero, adic\u0103 timpul de execu\u021bie este practic egal cu zero comparativ cu execu\u021bia unic\u0103. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4ea68f712bbdeadd35f5baa4ddaeff33.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Inser\u0103m date. Este o tabel\u0103 destul de simpl\u0103. Trei coloane. \u0218i ce vedem aici? Vedem c\u0103 toate aceste trei variante sunt aproximativ comparabile. \u0218i COPY, desigur, este mai bun.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2686f1d7eea347a839ac23aef2a27e53.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Este atunci c\u00e2nd inser\u0103m \u00een buc\u0103\u021bi. Atunci c\u00e2nd am spus c\u0103 o valoare VALUES, dou\u0103 valori VALUES, trei valori VALUES sau am specificat 10 separate prin virgul\u0103. Asta este acum pe orizontal\u0103. 1, 2, 4, 128. Se observ\u0103 c\u0103 Batch Insert, care este reprezentat cu albastru, \u00eei devine astfel mult mai u\u0219or. Adic\u0103, atunci c\u00e2nd insera\u021bi c\u00e2te unul sau chiar c\u00e2nd insera\u021bi c\u00e2te patru, se \u00eembun\u0103t\u0103\u021be\u0219te de dou\u0103 ori, doar din faptul c\u0103 am introdus pu\u021bin mai mult \u00een VALUES. Mai pu\u021bine opera\u021biuni EXECUTE.<\/p>\n<p><\/p>\n<p>Utilizarea COPY pentru volume mici este extrem de nepromi\u021b\u0103toare. La primele dou\u0103 nici m\u0103car nu le-am desenat. Ele se duc \u00een cer, adic\u0103 aceste cifre verzi pentru COPY.<\/p>\n<p><\/p>\n<p>COPY trebuie utilizat atunci c\u00e2nd volumul de date este de cel pu\u021bin mai mult de o sut\u0103 de r\u00e2nduri. Cheltuielile administrative pentru deschiderea acestei conexiuni sunt mari. \u0218i, sincer, nu am investigat \u00een aceast\u0103 direc\u021bie. Am optimizat batch-ul, pe COPY nu. <\/p>\n<p><\/p>\n<p>Ce facem mai departe? Am m\u0103surat. \u00cen\u021belegem c\u0103 trebuie s\u0103 folosim fie structuri, fie un batch ingenios care s\u0103 combine mai multe valori. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun. Vladimir Sitnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e02fa2574e2d1b678382db15064610d3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ce trebuie s\u0103 re\u021binem din raportul de ast\u0103zi?<\/p>\n<p><\/p>\n<ul>\n<li>PreparedStatement \u2013 este totul pentru noi. Ofer\u0103 foarte mult pentru performan\u021b\u0103. Aduce o mare cantitate de probleme. <\/li>\n<li>\u0218i trebuie s\u0103 facem EXPLAIN ANALYZE de 6 ori.<\/li>\n<li>\u0218i trebuie s\u0103 dilu\u0103m OFFSET 0, \u0219i s\u0103 facem trucuri precum +0 pentru a corecta procentul r\u0103mas din interog\u0103rile noastre problematice.<\/li>\n<\/ul>\n<p>Sursa: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/499794\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440\u0430 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432\u0430 &quot;PostgreSQL \u0438 JDBC \u0432\u044b\u0436\u0438\u043c\u0430\u0435\u043c \u0432\u0441\u0435 \u0441\u043e\u043a\u0438&quot; \u0414\u043e\u0431\u0440\u044b\u0439 \u0434\u0435\u043d\u044c! \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0412\u043b\u0430\u0434\u0438\u043c\u0438\u0440 \u0421\u0438\u0442\u043d\u0438\u043a\u043e\u0432. \u042f \u0440\u0430\u0431\u043e\u0442\u0430\u044e 10 \u043b\u0435\u0442 \u0432 \u043a\u043e\u043c\u043f\u0430\u043d\u0438\u0438 NetCracker. \u0418 \u0432 \u043e\u0441\u043d\u043e\u0432\u043d\u043e\u043c \u044f \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0441\u044c \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e. \u0412\u0441\u0435, \u0447\u0442\u043e \u0441\u0432\u044f\u0437\u0430\u043d\u043e \u0441 Java, \u0432\u0441\u0435, \u0447\u0442\u043e \u0441\u0432\u044f\u0437\u0430\u043d\u043e \u0441 SQL \u2013 \u044d\u0442\u043e \u0442\u043e, \u0447\u0442\u043e \u044f \u043b\u044e\u0431\u043b\u044e. \u0418 \u0441\u0435\u0433\u043e\u0434\u043d\u044f \u044f \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":79894,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-79893","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2.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\/ro\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"ro_RO\" \/>\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\/ro\/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 \u0219i JDBC, extragem tot din ele. Vladimir Sitnikov | ProHoster","description":"V\u0103 propun s\u0103 cunoa\u0219te\u021bi transcrierea raportului din \u00eenceputul anului 2016 al lui Vladimir Sitnikov \"PostgreSQL \u0219i JDBC: Extragem tot ce este mai bun\"","canonical_url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/postgresql-i-jdbc-vyzhimaem-vse-soki-vladimir-sitnikov","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"ro_RO","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\/ro\/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\/ro\/wp-json\/wp\/v2\/posts\/79893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/comments?post=79893"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/79893\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media\/79894"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media?parent=79893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/categories?post=79893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/tags?post=79893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}