{"id":74953,"date":"2020-03-22T08:42:22","date_gmt":"2020-03-22T05:42:22","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy"},"modified":"2020-03-22T08:42:22","modified_gmt":"2020-03-22T05:42:22","slug":"dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","title":{"rendered":"DBA: spr\u00e1vn\u011b organizujeme synchronizace a importy","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>P\u0159i slo\u017eit\u00e9m zpracov\u00e1n\u00ed velk\u00fdch datov\u00fdch sad (r\u016fzn\u00fdch <noindex><a rel=\"nofollow\" href=\"https:\/\/ru.wikipedia.org\/wiki\/ETL\">ETL proces\u016f<\/a><\/noindex>: importy, konverze a synchronizace s extern\u00edm zdrojem) \u010dasto nast\u00e1v\u00e1 pot\u0159eba <b>do\u010dasn\u011b \"zapamatovat\" a ihned rychle zpracovat<\/b> n\u011bco objemn\u00e9ho.<\/p>\n<p>Typick\u00e1 \u00faloha podobn\u00e9ho druhu obvykle zn\u00ed takto: <i>\"Tady <noindex><a rel=\"nofollow\" href=\"https:\/\/sbis.ru\/accounting\">\u00fa\u010detn\u00ed odd\u011blen\u00ed vyexportovalo z klientsk\u00e9ho bankovn\u00ed syst\u00e9mu<\/a><\/noindex> posledn\u00ed p\u0159ijat\u00e9 platby, mus\u00edme je rychle nahr\u00e1t na web a p\u0159i\u0159adit k \u00fa\u010dt\u016fm\"<\/i><\/p>\n<p>Kdy\u017e objem tohoto \u201en\u011b\u010deho\u201c za\u010dne b\u00fdt m\u011b\u0159en v des\u00edtk\u00e1ch megabajt\u016f a slu\u017eba mus\u00ed nad\u00e1le pracovat s datab\u00e1z\u00ed v re\u017eimu 24\/7, vznik\u00e1 mnoho vedlej\u0161\u00edch efekt\u016f, kter\u00e9 v\u00e1m znep\u0159\u00edjemn\u00ed \u017eivot.<br \/>\n<img decoding=\"async\" alt=\"DBA: spr\u00e1vn\u011b organizujeme synchronizace a importy\" src=\"\/wp-content\/uploads\/2020\/03\/f74afb2cd6f5f8de26a0932166933c95.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nAbychom se s nimi vypo\u0159\u00e1dali v PostgreSQL (a nejen v n\u011bm), m\u016f\u017eeme vyu\u017e\u00edt n\u011bkter\u00e9 mo\u017enosti optimalizace, kter\u00e9 umo\u017en\u00ed zpracovat v\u0161e rychleji a s men\u0161\u00edmi n\u00e1roky na zdroje.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>1. Kam nahr\u00e1vat?<\/h2>\n<p>\nNejprve si ujasn\u011bme, kam m\u016f\u017eeme nahr\u00e1t data, kter\u00e1 chceme \u201ezpracovat\u201c.<\/p>\n<h3>1.1. Do\u010dasn\u00e9 tabulky (TEMPORARY TABLE)<\/h3>\n<p>\nV z\u00e1sad\u011b jsou do\u010dasn\u00e9 tabulky v PostgreSQL stejn\u00e9 jako jak\u00e9koli jin\u00e9. Proto jsou nep\u0159esn\u00e9 pov\u011bry jako <i><b>\"tam se v\u0161echno ukl\u00e1d\u00e1 jen do pam\u011bti, a ta m\u016f\u017ee doj\u00edt\"<\/b><\/i>. Ale existuje i n\u011bkolik z\u00e1sadn\u00edch rozd\u00edl\u016f.<\/p>\n<h4>Sv\u016fj \u201enamespace\u201c pro ka\u017ed\u00e9 p\u0159ipojen\u00ed k DB<\/h4>\n<p>\nPokud se dva spojen\u00ed pokus\u00ed sou\u010dasn\u011b prov\u00e9st <code>CREATE TABLE x<\/code>, pak n\u011bkdo ur\u010dit\u011b obdr\u017e\u00ed <b>chybovou zpr\u00e1vu o nejedine\u010dnosti<\/b> objekt\u016f DB.<\/p>\n<p>Pokud v\u0161ak oba provedou p\u0159\u00edkaz <code>VYTVO\u0158IT <b>DO\u010cASNOU<\/b> TABULKU x<\/code>, pak to oba \u00fasp\u011b\u0161n\u011b ud\u011blaj\u00ed a ka\u017ed\u00fd obdr\u017e\u00ed <b>sv\u016fj vlastn\u00ed exempl\u00e1\u0159<\/b> tabulky. A mezi nimi nebude nic spole\u010dn\u00e9ho.<\/p>\n<h4>\"Sebevra\u017eda\" p\u0159i odpojen\u00ed<\/h4>\n<p>\nP\u0159i uzav\u0159en\u00ed p\u0159ipojen\u00ed se v\u0161echny do\u010dasn\u00e9 tabulky automaticky odstran\u00ed, tak\u017ee nem\u00e1 smysl prov\u00e1d\u011bt <code>DROP TABLE x<\/code> , krom\u011b\u2026<\/p>\n<p>Pokud pracujete p\u0159es <b>pgbouncer v re\u017eimu transakce<\/b>, datab\u00e1ze st\u00e1le pova\u017euje toto spojen\u00ed za aktivn\u00ed, a v n\u011bm tato do\u010dasn\u00e1 tabulka st\u00e1le existuje.<\/p>\n<p>Pokud se tedy pokus\u00edte vytvo\u0159it ji znovu ji\u017e z jin\u00e9ho p\u0159ipojen\u00ed k pgbouncer, dojde k chyb\u011b. Ale lze to obej\u00edt pomoc\u00ed <code>VYTVO\u0158TE DO\u010cASNOU TABULKU <b>POKUD NEEXISTUJE<\/b> x<\/code>.<\/p>\n<p>Je pravda, \u017ee je lep\u0161\u00ed to tak ned\u011blat, proto\u017ee pak m\u016f\u017eete \"n\u00e1hodn\u011b\" objevit data, kter\u00e1 zanechal \"p\u0159edchoz\u00ed majitel\". M\u00edsto toho je mnohem lep\u0161\u00ed si p\u0159e\u010d\u00edst manu\u00e1l a zjistit, \u017ee p\u0159i vytv\u00e1\u0159en\u00ed tabulky m\u00e1te mo\u017enost p\u0159idat <code>PO P\u0158IHL\u00c1\u0160EN\u00cd <b>DROP<\/b><\/code> \u2014 to znamen\u00e1, \u017ee po dokon\u010den\u00ed transakce bude tabulka automaticky odstran\u011bna.<\/p>\n<h4>Nereplikace<\/h4>\n<p>\nZ d\u016fvodu p\u0159\u00edslu\u0161nosti pouze k ur\u010dit\u00e9mu spojen\u00ed, do\u010dasn\u00e9 tabulky se nereplikuj\u00ed. Nicm\u00e9n\u011b <b>to zbavuje nutnosti duplicitn\u00edho z\u00e1pisu dat<\/b> v heap + WAL, tak\u017ee INSERT\/UPDATE\/DELETE do n\u00ed je v\u00fdrazn\u011b rychlej\u0161\u00ed.<\/p>\n<p>Ale proto\u017ee do\u010dasn\u00e1 tabulka je p\u0159ece jen \"t\u00e9m\u011b\u0159 b\u011b\u017en\u00e1\" tabulka, tak ji nelze vytvo\u0159it ani na replikaci. Minim\u00e1ln\u011b zat\u00edm, a\u010dkoli p\u0159\u00edslu\u0161n\u00fd patch u\u017e d\u00e1vno existuje.<\/p>\n<h3>1.2. Nje\u017eurn\u00e1lovan\u00e9 tabulky (UNLOGGED TABLE)<\/h3>\n<p>\nAle co d\u011blat, pokud m\u00e1te n\u011bjak\u00fd obs\u00e1hl\u00fd ETL proces, kter\u00fd nelze realizovat v r\u00e1mci jedn\u00e9 transakce, a p\u0159itom m\u00e1te <b>pgbouncer v re\u017eimu transakce<\/b>?..<\/p>\n<p>Nebo je tok dat tak velk\u00fd, \u017ee <b>nedostate\u010dn\u00e1 propustnost jednoho spojen\u00ed<\/b> s DB (\u010dti, jednoho procesu na CPU)?..<\/p>\n<p>Nebo \u010d\u00e1st operac\u00ed prob\u00edh\u00e1 <b>asynchronn\u011b<\/b> v r\u016fzn\u00fdch p\u0159ipojen\u00edch?..<\/p>\n<p>Tady je jen jedna mo\u017enost \u2014 <b>do\u010dasn\u011b vytvo\u0159it ne-do\u010dasnou tabulku<\/b>. Hra se slovy, \u017ee? To znamen\u00e1:<\/p>\n<ul>\n<li>vytvo\u0159il \"sv\u00e9\" tabulky s maxim\u00e1ln\u011b n\u00e1hodn\u00fdmi n\u00e1zvy, abych se s nik\u00fdm nesetkal<\/li>\n<li><b>Extract<\/b>: nahr\u00e1l do nich data z extern\u00edho zdroje<\/li>\n<li><b>Transform<\/b>: transformoval, vyplnil kl\u00ed\u010dov\u00e9 vazebn\u00e9 pole<\/li>\n<li><b>Na\u010dten\u00ed<\/b>: p\u0159elil hotov\u00e1 data do c\u00edlov\u00fdch tabulek<\/li>\n<li>smazal \"sv\u00e9\" tabulky<\/li>\n<\/ul>\n<p>\nA te\u010f \u2014 l\u017e\u00edce dehtu. V podstat\u011b, <b>ka\u017ed\u00fd z\u00e1pis v PostgreSQL prob\u00edh\u00e1 dvakr\u00e1t<\/b> \u2014 <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/postgrespro\/blog\/461523\/\">nejd\u0159\u00edve do WAL<\/a><\/noindex>, pot\u00e9 ji\u017e do t\u011bl tabulek\/index\u016f. To v\u0161e bylo ud\u011bl\u00e1no pro podporu ACID a spr\u00e1vn\u00e9 viditelnosti dat mezi <code>COMMIT<\/code>\u2018vlo\u017een\u00ed a <code>ROLLBACK<\/code>\u2018vlo\u017een\u00ed transakc\u00ed.<\/p>\n<p>Ale my to nepot\u0159ebujeme! N\u00e1\u0161 cel\u00fd proces <b>bu\u010f \u00fasp\u011b\u0161n\u011b prob\u011bhl, nebo ne.<\/b>. Nen\u00ed d\u016fle\u017eit\u00e9, kolik v n\u011bm bude mezit\u00edmn\u00edch transakc\u00ed \u2014 nezaj\u00edm\u00e1 n\u00e1s \"pokra\u010dovat v procesu ze st\u0159edu\", obzvl\u00e1\u0161\u0165 kdy\u017e nen\u00ed jasn\u00e9, kde byl.<\/p>\n<p>K tomu v\u00fdvoj\u00e1\u0159i PostgreSQL u\u017e v verzi 9.1 zavedli n\u011bco, jako <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/sql-createtable#SQL-CREATETABLE-UNLOGGED\">nejeurn\u00e1lovan\u00e9 (UNLOGGED) tabulky.<\/a><\/noindex>:<\/p>\n<blockquote><p>S t\u00edmto ozna\u010den\u00edm se tabulka vytv\u00e1\u0159\u00ed jako ne\u017eurn\u00e1lov\u00e1. Data zapisovan\u00e1 do ne\u017eurn\u00e1lovan\u00fdch tabulek neproch\u00e1zej\u00ed skrze p\u0159edz\u00e1znamov\u00fd den\u00edk (viz Kapitola 29), v d\u016fsledku \u010deho\u017e takov\u00e9 tabulky <b>pracuj\u00ed mnohem rychleji ne\u017e obvykl\u00e9.<\/b>Nicm\u00e9n\u011b, nejsou chr\u00e1n\u011bny p\u0159ed selh\u00e1n\u00edm; p\u0159i selh\u00e1n\u00ed nebo havarijn\u00edm vypnut\u00ed serveru se ne\u017eurn\u00e1lovan\u00e1 tabulka <b>automaticky zru\u0161\u00ed.<\/b>Krom\u011b toho obsah ne\u017eurn\u00e1lovan\u00e9 tabulky <b>se nereplikuje.<\/b> na \u0159\u00edzen\u00e9 servery. Jak\u00e9koli indexy vytvo\u0159en\u00e9 pro nezapisovan\u00e9 tabulky se automaticky st\u00e1vaj\u00ed nezapisovan\u00fdmi.<\/p><\/blockquote>\n<p>Zkr\u00e1tka, <b>bude to v\u00fdrazn\u011b rychlej\u0161\u00ed<\/b>, ale pokud server DB \u201espadne\u201c \u2014 bude to nep\u0159\u00edjemn\u00e9. Ale jak \u010dasto se to st\u00e1v\u00e1 a dok\u00e1\u017ee v\u00e1\u0161 ETL proces to spr\u00e1vn\u011b zpracovat \u201eod p\u016flky\u201c po \u201eobnoven\u00ed\u201c DB?..<\/p>\n<p>Pokud ne, a v\u00e1\u0161 p\u0159\u00edpad v\u00fd\u0161e je podobn\u00fd \u2014 pou\u017eijte <code>UNLOGGED<\/code>, ale nikdy <b>nezap\u00ednejte tento atribut u skute\u010dn\u00fdch tabulek<\/b>, jejich\u017e data jsou pro v\u00e1s cenn\u00e1.<\/p>\n<h3>1.3. ON COMMIT { DELETE ROWS | DROP }<\/h3>\n<p>\nTato konstrukce umo\u017e\u0148uje p\u0159i vytv\u00e1\u0159en\u00ed tabulky ur\u010dit automatick\u00e9 chov\u00e1n\u00ed p\u0159i dokon\u010den\u00ed transakce.<\/p>\n<p>O <code>PO P\u0158IHL\u00c1\u0160EN\u00cd <b>DROP<\/b><\/code> u\u017e jsem zm\u00ednil v\u00fd\u0161e, generuje to <code>DROP TABLE<\/code>, ale situace s <code>PO P\u0158IHL\u00c1\u0160EN\u00cd <b>SMAZAT \u0158\u00c1DKY<\/b><\/code> je zaj\u00edmav\u011bj\u0161\u00ed \u2014 zde se generuje <code>TRUNCATE TABLE<\/code>.<\/p>\n<p>Jeliko\u017e cel\u00e1 infrastruktura ukl\u00e1d\u00e1n\u00ed meta popisu do do\u010dasn\u00e9 tabulky je stejn\u00e1 jako u b\u011b\u017en\u00e9, \u010din\u00ed <b>neust\u00e1l\u00e9 vytv\u00e1\u0159en\u00ed a odstra\u0148ov\u00e1n\u00ed do\u010dasn\u00fdch tabulek v\u00fdrazn\u00e9 \u201enabobtn\u00e1n\u00ed\u201c syst\u00e9mov\u00fdch tabulek<\/b> pg_class, pg_attribute, pg_attrdef, pg_depend,\u2026<\/p>\n<p>Nyn\u00ed si p\u0159edstavte, \u017ee m\u00e1te worker na p\u0159\u00edm\u00e9m spojen\u00ed s DB, kter\u00fd ka\u017edou sekundu otev\u00edr\u00e1 novou transakci, vytv\u00e1\u0159\u00ed, napln\u00ed, zpracov\u00e1v\u00e1 a odstra\u0148uje do\u010dasnou tabulku\u2026 Odpad v syst\u00e9mov\u00fdch tabulk\u00e1ch se hromad\u00ed, co\u017e zp\u016fsobuje zbyte\u010dn\u00e9 zpomalen\u00ed p\u0159i ka\u017ed\u00e9 operaci.<\/p>\n<p>Celkov\u011b to tak ned\u011blejte! V tomto p\u0159\u00edpad\u011b je mnohem efektivn\u011bj\u0161\u00ed <code>VYTV\u00c1\u0158EN\u00cd DO\u010cASN\u00c9 TABULKY x ... ON COMMIT DELETE ROWS<\/code> vyt\u00e1hnout mimo cyklus transakc\u00ed \u2014 pak na za\u010d\u00e1tku ka\u017ed\u00e9 nov\u00e9 transakce tabulky ji\u017e <b>existovat budou<\/b> (\u0161et\u0159\u00edme v\u00fdzvu <code>VYTVO\u0158IT<\/code>), ale <b>bude pr\u00e1zdn\u00e1<\/b>, d\u00edky <code>TRUNCATE<\/code> (jeho v\u00fdzvu jsme tak\u00e9 u\u0161et\u0159ili) p\u0159i dokon\u010den\u00ed p\u0159edchoz\u00ed transakce.<\/p>\n<h3>1.4. JAKO\u2026 ZAHRNUJ\u00cdC &#8230;<\/h3>\n<p>\nZm\u00ednil jsem na za\u010d\u00e1tku, \u017ee jedn\u00edm z typick\u00fdch p\u0159\u00edpad\u016f pou\u017eit\u00ed do\u010dasn\u00fdch tabulek jsou r\u016fzn\u00e9 importy \u2014 a v\u00fdvoj\u00e1\u0159 unaven\u011b kop\u00edruje seznam pol\u00ed c\u00edlov\u00e9 tabulky do deklarace sv\u00e9 do\u010dasn\u00e9\u2026<\/p>\n<p>Ale lenost je hybatelem pokroku! Proto <b>vytvo\u0159it novou tabulku \"podle vzoru\"<\/b> je mnohem jednodu\u0161\u0161\u00ed:<\/p>\n<pre><code class=\"sql\">VYTVO\u0158EN\u00cd DO\u010cASN\u00c9 TABULKY import_table(\n  LIKE target_table\n);<\/code><\/pre>\n<p>\nJeliko\u017e lze do t\u00e9to tabulky generovat pom\u011brn\u011b velk\u00e9 mno\u017estv\u00ed dat, hled\u00e1n\u00ed v n\u00ed nebude zrovna rychl\u00e9. Ale proti tomu existuje tradi\u010dn\u00ed \u0159e\u0161en\u00ed \u2014 indexy! A ano, <b>i do\u010dasn\u00e1 tabulka m\u016f\u017ee m\u00edt indexy<\/b>.<\/p>\n<p>Proto\u017ee se \u010dasto pot\u0159ebn\u00e9 indexy shoduj\u00ed s indexy c\u00edlov\u00e9 tabulky, lze jednodu\u0161e napsat <code>JAKO target_table <b>V\u010cETN\u011a INDEX\u016e<\/b><\/code>.<\/p>\n<p>Pokud pot\u0159ebujete tak\u00e9 <code>DEFAULT<\/code>- hodnoty (nap\u0159\u00edklad k vypln\u011bn\u00ed hodnot prim\u00e1rn\u00edho kl\u00ed\u010de) m\u016f\u017eete vyu\u017e\u00edt <code>JAKO target_table <b>ZAPNOUT MEZIN\u00c1RODN\u00cd VARIANTY<\/b><\/code>. Nebo jednodu\u0161e \u2014 <code>JAKO target_table <b>V\u010cETN\u011a V\u0160EHO<\/b><\/code> \u2014 zkop\u00edruje v\u00fdchoz\u00ed hodnoty, indexy, omezen\u00ed,\u2026<\/p>\n<p>Ale tady u\u017e mus\u00edte pochopit, \u017ee pokud jste vytv\u00e1\u0159eli <b>importn\u00ed tabulku rovnou s indexy, budou se data nahr\u00e1vat d\u00e9le<\/b>, ne\u017e kdy\u017e je nejprve v\u0161echny nahrajete a pot\u00e9 naindexujete \u2014 pod\u00edvejte se jako p\u0159\u00edklad, jak to d\u011bl\u00e1 <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/app-pgdump\">pg_dump<\/a><\/noindex>.<\/p>\n<p>Obecn\u011b, <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/sql-createtable\">RTFM<\/a><\/noindex>!<\/p>\n<h2>2. Jak ps\u00e1t?<\/h2>\n<p>\n\u0158eknu jednodu\u0161e \u2014 pou\u017e\u00edvejte <code><noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/sql-copy\">COPY<\/a><\/noindex><\/code>- proud m\u00edsto \u201eparti\u00ed\u201c <code>INSERT<\/code>, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.citusdata.com\/blog\/2017\/11\/08\/faster-bulk-loading-in-postgresql-with-copy\/\">zrychlen\u00ed n\u011bkolikan\u00e1sobn\u011b<\/a><\/noindex>. M\u016f\u017eete dokonce p\u0159\u00edmo z p\u0159edem p\u0159ipraven\u00e9ho souboru.<\/p>\n<h2>3. Jak zpracov\u00e1vat?<\/h2>\n<p>\nTak\u017ee, na\u0161e vstupn\u00ed data vypadaj\u00ed p\u0159ibli\u017en\u011b takto:<\/p>\n<ul>\n<li>m\u00e1te v datab\u00e1zi tabulku s klientsk\u00fdmi daty na <b>1M z\u00e1znam\u016f<\/b><\/li>\n<li>ka\u017ed\u00fd den v\u00e1m klient pos\u00edl\u00e1 nov\u00fd <b>\u00fapln\u00fd \u201eobraz\u201c<\/b><\/li>\n<li>podle zku\u0161enost\u00ed v\u00edte, \u017ee od p\u0159\u00edpadu k p\u0159\u00edpadu <b>se m\u011bn\u00ed maxim\u00e1ln\u011b 10K z\u00e1znam\u016f<\/b><\/li>\n<\/ul>\n<p>\nKlasick\u00fdm p\u0159\u00edkladem takov\u00e9 situace je <noindex><a rel=\"nofollow\" href=\"https:\/\/www.gnivc.ru\/technical_support\/classifiers_reference\/kladr\/\">datab\u00e1ze KLAADR<\/a><\/noindex> \u2014 je sice hodn\u011b adres, ale v ka\u017ed\u00e9m t\u00fddenn\u00edm v\u00fdstupu zm\u011bn (p\u0159ejmenov\u00e1n\u00ed obc\u00ed, slu\u010dov\u00e1n\u00ed ulic, objevov\u00e1n\u00ed nov\u00fdch dom\u016f) je jich velmi m\u00e1lo i v celost\u00e1tn\u00edm m\u011b\u0159\u00edtku.<\/p>\n<h3>3.1. Algoritmus \u00fapln\u00e9 synchronizace<\/h3>\n<p>\nPro zjednodu\u0161en\u00ed p\u0159edpokl\u00e1dejme, \u017ee nemus\u00edte data restrukturovat \u2014 sta\u010d\u00ed tabulku p\u0159iv\u00e9st do pot\u0159ebn\u00e9ho stavu, tedy:<\/p>\n<ul>\n<li><b>odstranit<\/b> v\u0161echno, co u\u017e neexistuje<\/li>\n<li><b>aktualizovat<\/b> v\u0161echno, co u\u017e bylo a je t\u0159eba aktualizovat<\/li>\n<li><b>vlo\u017eit<\/b> v\u0161echno, co je\u0161t\u011b nebylo<\/li>\n<\/ul>\n<p>\nPro\u010d by se m\u011bly operace prov\u00e1d\u011bt pr\u00e1v\u011b v tomto po\u0159ad\u00ed? Proto\u017ee tak se velikost tabulky zv\u00fd\u0161\u00ed minim\u00e1ln\u011b (<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/491366\/\">nezapome\u0148te na MVCC!<\/a><\/noindex>).<\/p>\n<h4>DELETE FROM dst<\/h4>\n<p>\nNe, samoz\u0159ejm\u011b je mo\u017en\u00e9 se obej\u00edt s pouh\u00fdmi dv\u011bma operacemi:<\/p>\n<ul>\n<li><b>odstranit<\/b> (<code>DELETE<\/code>) \u00fapln\u011b v\u0161echno<\/li>\n<li><b>vlo\u017eit<\/b> v\u0161e z nov\u00e9ho obrazu<\/li>\n<\/ul>\n<p>\nAle s pomoc\u00ed MVCC, <b>se velikost tabulky zv\u00fd\u0161\u00ed p\u0159esn\u011b dvakr\u00e1t<\/b>! Z\u00edskat +1M obraz\u016f z\u00e1znam\u016f v tabulce z aktualizace 10K \u2014 to je dost zbyte\u010dn\u00e9\u2026<\/p>\n<h4>TRUNCATE dst<\/h4>\n<p>\nZku\u0161en\u011bj\u0161\u00ed v\u00fdvoj\u00e1\u0159 v\u00ed, \u017ee celou tabulku lze relativn\u011b levn\u011b vymazat:<\/p>\n<ul>\n<li><b>vymazat<\/b> (<code>TRUNCATE<\/code>) celou tabulku<\/li>\n<li><b>vlo\u017eit<\/b> v\u0161e z nov\u00e9ho obrazu<\/li>\n<\/ul>\n<p>\nMetoda je efektivn\u00ed, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/481866\/\">n\u011bkdy je zcela pou\u017eiteln\u00e1<\/a><\/noindex>, ale je tu probl\u00e9m... Nahr\u00e1vat 1M z\u00e1znam\u016f bude trvat p\u0159\u00ed-\u00ed-\u00edli\u0161 dlouho, a proto si nem\u016f\u017eeme dovolit nechat tabulku pr\u00e1zdnou po celou tuto dobu (jak by se stalo bez obalen\u00ed do jedin\u00e9 transakce).<\/p>\n<p>To znamen\u00e1:<\/p>\n<ul>\n<li>za\u010d\u00edn\u00e1me <b>dlouhou transakci<\/b><\/li>\n<li><code>TRUNCATE<\/code> aplikuje <b>AccessExclusive<\/b>-z\u00e1mku<\/li>\n<li>dlouho prov\u00e1d\u00edme vlo\u017een\u00ed, zat\u00edmco v\u0161ichni ostatn\u00ed v tuto dobu <b>v\u016fbec nemohou <code>nebude prov\u00e1d\u011bn p\u0159i nepravdivosti prvn\u00ed podm\u00ednky. Opravy pomoc\u00ed<\/code><\/b><\/li>\n<\/ul>\n<p>\nN\u011bco nevypad\u00e1 dob\u0159e\u2026<\/p>\n<h4>ALTER TABLE\u2026 P\u0158EJMENOVAT\u2026 \/ DROP TABLE &#8230;<\/h4>\n<p>\nJednou z mo\u017enost\u00ed je nahr\u00e1t v\u0161e do nov\u00e9 tabulky a pot\u00e9 ji jednodu\u0161e p\u0159ejmenovat za starou. Existuje n\u011bkolik nep\u0159\u00edjemn\u00fdch drobnost\u00ed:<\/p>\n<ul>\n<li>tak\u00e9 <b>AccessExclusive<\/b>, i kdy\u017e to trv\u00e1 v\u00fdrazn\u011b m\u00e9n\u011b \u010dasu<\/li>\n<li>v\u0161echny pl\u00e1ny dotaz\u016f\/statistiky t\u00e9to tabulky se resetuj\u00ed, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/479656\/\">mus\u00edme spustit ANALYZE<\/a><\/noindex><\/li>\n<li><b>v\u0161echny extern\u00ed kl\u00ed\u010de se poru\u0161uj\u00ed<\/b> (FK) na tabulku<\/li>\n<\/ul>\n<p>\nExistoval WIP patch od Simona Riggse, kter\u00fd navrhoval prov\u00e9st <code>ALTER<\/code>-operaci pro v\u00fdm\u011bnu t\u011bla tabulky na \u00farovni soubor\u016f, ani\u017e by se dotklo statistik a FK, ale nez\u00edskal pot\u0159ebn\u00fd po\u010det hlas\u016f.<\/p>\n<h4>DELETE, UPDATE, INSERT<\/h4>\n<p>\nTak\u017ee se zastavujeme na neblokuj\u00edc\u00ed variant\u011b ze t\u0159\u00ed operac\u00ed. T\u00e9m\u011b\u0159 t\u0159\u00ed\u2026 Jak to ud\u011blat co nejefektivn\u011bji?<\/p>\n<pre><code class=\"sql\">-- v\u0161e prov\u00e1d\u00edme v r\u00e1mci transakce, aby nikdo nevid\u011bl \"mezi\" stavy\nBEGIN;\n\n-- vytv\u00e1\u0159\u00edme do\u010dasnou tabulku s importovan\u00fdmi daty\nCREATE TEMPORARY TABLE tmp(\n  LIKE dst INCLUDING INDEXES -- podle vzoru, v\u010detn\u011b index\u016f\n) ON COMMIT DROP; -- mimo transakci ji nepot\u0159ebujeme\n\n-- rychle vkl\u00e1d\u00e1me nov\u00fd obraz pomoc\u00ed COPY\nCOPY tmp FROM STDIN;\n-- ...\n-- .\n\n-- odstra\u0148ujeme chyb\u011bj\u00edc\u00ed\nDELETE FROM\n  dst D\nUSING\n  dst X\nLEFT JOIN\n  tmp Y\n    USING(pk1, pk2) -- pole prim\u00e1rn\u00edho kl\u00ed\u010de\nWHERE\n  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND\n  Y IS NOT DISTINCT FROM NULL; -- \"anti join\"\n\n-- aktualizujeme zb\u00fdvaj\u00edc\u00ed\nUPDATE\n  dst D\nSET\n  (f1, f2, f3) = (T.f1, T.f2, T.f3)\nFROM\n  tmp T\nWHERE\n  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND\n  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- nen\u00ed pot\u0159eba aktualizovat shoduj\u00edc\u00ed se\n\n-- vkl\u00e1d\u00e1me chyb\u011bj\u00edc\u00ed\nINSERT INTO\n  dst\nSELECT\n  T.*\nFROM\n  tmp T\nLEFT JOIN\n  dst D\n    USING(pk1, pk2)\nWHERE\n  D IS NOT DISTINCT FROM NULL;\n\nCOMMIT;\n<\/code><\/pre>\n<p><\/p>\n<h3>3.2. Postprocessing importu<\/h3>\n<p>\nVe stejn\u00e9m KLADe je t\u0159eba v\u0161echny zm\u011bn\u011bn\u00e9 z\u00e1znamy d\u00e1le zpracovat \u2014 normalizovat, vy\u010dlenit kl\u00ed\u010dov\u00e1 slova, p\u0159ev\u00e9st do po\u017eadovan\u00fdch struktur. Ale jak zjistit \u2014 <b>co p\u0159esn\u011b se m\u011bnilo<\/b>, ani\u017e bychom t\u00edm zkomplikovali synchroniza\u010dn\u00ed k\u00f3d, ide\u00e1ln\u011b ho v\u016fbec nezasahovat?<\/p>\n<p>Pokud m\u00e1 v\u00e1\u0161 proces v okam\u017eiku synchronizace pouze z\u00e1pisov\u00e1 opr\u00e1vn\u011bn\u00ed, m\u016f\u017eeme vyu\u017e\u00edt spou\u0161t\u011b\u010d, kter\u00fd shrom\u00e1\u017ed\u00ed v\u0161echny zm\u011bny:<\/p>\n<pre><code class=\"sql\">-- c\u00edlov\u00e9 tabulky\nCREATE TABLE kladr(...);\nCREATE TABLE kladr_house(...);\n\n-- tabulky s histori\u00ed zm\u011bn\nCREATE TABLE kladr$log(\n  ro kladr, -- zde le\u017e\u00ed cel\u00e9 obrazy z\u00e1znam\u016f star\u00fd\/nov\u00fd\n  rn kladr\n);\n\nCREATE TABLE kladr_house$log(\n  ro kladr_house,\n  rn kladr_house\n);\n\n-- obecn\u00e1 funkce logov\u00e1n\u00ed zm\u011bn\nCREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$\nDECLARE\n  dst varchar = TG_TABLE_NAME || '$log';\n  stmt text = '';\nBEGIN\n  -- kontrolujeme pot\u0159ebu logov\u00e1n\u00ed p\u0159i aktualizaci z\u00e1znamu\n  IF TG_OP = 'UPDATE' THEN\n    IF NEW IS NOT DISTINCT FROM OLD THEN\n      RETURN NEW;\n    END IF;\n  END IF;\n  -- vytv\u00e1\u0159\u00edme z\u00e1znam logu\n  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';\n  CASE TG_OP\n    WHEN 'INSERT' THEN\n      EXECUTE stmt || 'NULL,$1)' USING NEW;\n    WHEN 'UPDATE' THEN\n      EXECUTE stmt || '$1,$2)' USING OLD, NEW;\n    WHEN 'DELETE' THEN\n      EXECUTE stmt || '$1,NULL)' USING OLD;\n  END CASE;\n  RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n<\/code><\/pre>\n<p>\nNyn\u00ed m\u016f\u017eeme p\u0159ed za\u010d\u00e1tkem synchronizace spustit (nebo aktivovat p\u0159es <code>ALTER TABLE ... ENABLE TRIGGER ...<\/code>):<\/p>\n<pre><code class=\"sql\">CREATE TRIGGER log\n  AFTER INSERT OR UPDATE OR DELETE\n  ON kladr\n    FOR EACH ROW\n      EXECUTE PROCEDURE diff$log();\n\nCREATE TRIGGER log\n  AFTER INSERT OR UPDATE OR DELETE\n  ON kladr_house\n    FOR EACH ROW\n      EXECUTE PROCEDURE diff$log();\n<\/code><\/pre>\n<p>\nA pot\u00e9 m\u016f\u017eeme v klid\u011b z tabulek logu extrahovat v\u0161echny pot\u0159ebn\u00e9 zm\u011bny a poslat je dal\u0161\u00edm zpracovatel\u016fm.<\/p>\n<h3>3.3. Import souvisej\u00edc\u00edch sad<\/h3>\n<p>\nV\u00fd\u0161e jsme prob\u00edrali p\u0159\u00edpady, kdy se struktury dat zdroje a p\u0159\u00edjemce shoduj\u00ed. Co v\u0161ak d\u011blat, pokud export z extern\u00edho syst\u00e9mu m\u00e1 form\u00e1t odli\u0161n\u00fd od struktury, kterou uchov\u00e1v\u00e1me v na\u0161\u00ed datab\u00e1zi?<\/p>\n<p>P\u0159edstavme si p\u0159\u00edklad uchov\u00e1v\u00e1n\u00ed klient\u016f a jejich faktur, klasick\u00fd p\u0159\u00edpad \u201emnoho-k-jednomu\u201c:<\/p>\n<pre><code class=\"sql\">CREATE TABLE client(\n  client_id\n    serial\n      PRIMARY KEY\n, inn\n    varchar\n      UNIQUE\n, name\n    varchar\n);\n\nCREATE TABLE invoice(\n  invoice_id\n    serial\n      PRIMARY KEY\n, client_id\n    integer\n      REFERENCES client(client_id)\n, number\n    varchar\n, dt\n    date\n, sum\n    numeric(32,2)\n);<\/code><\/pre>\n<p>\nA zde p\u0159ich\u00e1z\u00ed export z extern\u00edho zdroje v podob\u011b \u201ev\u0161echno v jednom\u201c:<\/p>\n<pre><code class=\"sql\">CREATE TEMPORARY TABLE invoice_import(\n  client_inn\n    varchar\n, client_name\n    varchar\n, invoice_number\n    varchar\n, invoice_dt\n    date\n, invoice_sum\n    numeric(32,2)\n);<\/code><\/pre>\n<p>\nJe z\u0159ejm\u00e9, \u017ee \u00fadaje o klientech se v tomto p\u0159\u00edpad\u011b mohou duplikovat, a hlavn\u00ed z\u00e1znam je \u201efaktura\u201c:<\/p>\n<pre><code class=\"plaintext\">0123456789;V\u00e1clav;A-01;2020-03-16;1000.00\n9876543210;Petr;A-02;2020-03-16;666.00\n0123456789;V\u00e1clav;B-03;2020-03-16;9999.00\n<\/code><\/pre>\n<p>\nPro model jednodu\u0161e vlo\u017e\u00edme na\u0161e testovac\u00ed \u00fadaje, ale pamatujeme \u2014 <code>COPY<\/code> efektivn\u011bji!<\/p>\n<pre><code class=\"sql\">INSERT INTO invoice_import\nVALUES\n  ('0123456789', 'V\u00e1clav', 'A-01', '2020-03-16', 1000.00)\n, ('9876543210', 'Petr', 'A-02', '2020-03-16', 666.00)\n, ('0123456789', 'V\u00e1clav', 'B-03', '2020-03-16', 9999.00);<\/code><\/pre>\n<p>\nNejprve vymez\u00edme ty \u201e\u0159ezy\u201c, na kter\u00e9 na\u0161e \u201efaktury\u201c odkazuj\u00ed. V na\u0161em p\u0159\u00edpad\u011b faktury odkazuj\u00ed na klienty:<\/p>\n<pre><code class=\"sql\">CREATE TEMPORARY TABLE client_import AS\nSELECT DISTINCT ON(client_inn)\n-- lze jednodu\u0161e SELECT DISTINCT, pokud jsou data zjevn\u011b konzistentn\u00ed\n  client_inn inn\n, client_name \"name\"\nFROM\n  invoice_import;<\/code><\/pre>\n<p>\nAbychom spr\u00e1vn\u011b spojili \u00fa\u010dty s ID klient\u016f, mus\u00edme tyto identifik\u00e1tory nejprve zjistit nebo vygenerovat. P\u0159id\u00e1me pro n\u011b pole:<\/p>\n<pre><code class=\"sql\">ALTER TABLE invoice_import ADD COLUMN client_id integer;\nALTER TABLE client_import ADD COLUMN client_id integer;<\/code><\/pre>\n<p>\nVyu\u017eijeme v\u00fd\u0161e popsan\u00fd zp\u016fsob synchronizace tabulek s malou \u00fapravou \u2014 nebudeme nic aktualizovat a mazat v c\u00edlov\u00e9 tabulce, proto\u017ee import klient\u016f je u n\u00e1s \u201eappend-only\u201c:<\/p>\n<pre><code class=\"sql\">-- p\u0159i\u0159azujeme do tabulky importu ID ji\u017e existuj\u00edc\u00edch z\u00e1znam\u016f\nUPDATE\n  client_import T\nSET\n  client_id = D.client_id\nFROM\n  client D\nWHERE\n  T.inn = D.inn; -- unik\u00e1tn\u00ed kl\u00ed\u010d\n\n-- vkl\u00e1d\u00e1me chyb\u011bj\u00edc\u00ed z\u00e1znamy a p\u0159i\u0159azujeme jejich ID\nWITH ins AS (\n  INSERT INTO client(\n    inn\n  , name\n  )\n  SELECT\n    inn\n  , name\n  FROM\n    client_import\n  WHERE\n    client_id IS NULL -- pokud ID nebylo p\u0159i\u0159azeno\n  RETURNING *\n)\nUPDATE\n  client_import T\nSET\n  client_id = D.client_id\nFROM\n  ins D\nWHERE\n  T.inn = D.inn; -- unik\u00e1tn\u00ed kl\u00ed\u010d\n\n-- p\u0159i\u0159azujeme ID klient\u016f u z\u00e1znam\u016f \u00fa\u010dt\u016f\nUPDATE\n  invoice_import T\nSET\n  client_id = D.client_id\nFROM\n  client_import D\nWHERE\n  T.client_inn = D.inn; -- aplika\u010dn\u00ed kl\u00ed\u010d\n<\/code><\/pre>\n<p>\nVlastn\u011b v\u0161e \u2014 v <code>invoice_import<\/code> nyn\u00ed m\u00e1me vypln\u011bn\u00e9 pole vazby <code>client_id<\/code>, se kter\u00fdm vlo\u017e\u00edme \u00fa\u010det.<br \/>\n<br \/>Zdroj: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/492464\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0438 \u0441\u043b\u043e\u0436\u043d\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0440\u0430\u0437\u043d\u044b\u0435 ETL-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b: \u0438\u043c\u043f\u043e\u0440\u0442\u044b, \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0430\u0446\u0438\u0438 \u0438 \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0441 \u0432\u043d\u0435\u0448\u043d\u0438\u043c \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u043e\u043c) \u0447\u0430\u0441\u0442\u043e \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0435\u0442 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c \u0432\u0440\u0435\u043c\u0435\u043d\u043d\u043e \u00ab\u0437\u0430\u043f\u043e\u043c\u043d\u0438\u0442\u044c\u00bb, \u0438 \u0441\u0440\u0430\u0437\u0443 \u0431\u044b\u0441\u0442\u0440\u043e \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u0447\u0442\u043e-\u0442\u043e \u043e\u0431\u044a\u0435\u043c\u043d\u043e\u0435. \u0422\u0438\u043f\u043e\u0432\u0430\u044f \u0437\u0430\u0434\u0430\u0447\u0430 \u043f\u043e\u0434\u043e\u0431\u043d\u043e\u0433\u043e \u0440\u043e\u0434\u0430 \u0437\u0432\u0443\u0447\u0438\u0442 \u043e\u0431\u044b\u0447\u043d\u043e \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u0442\u0430\u043a: \u00ab\u0412\u043e\u0442 \u0442\u0443\u0442 \u0431\u0443\u0445\u0433\u0430\u043b\u0442\u0435\u0440\u0438\u044f \u0432\u044b\u0433\u0440\u0443\u0437\u0438\u043b\u0430 \u0438\u0437 \u043a\u043b\u0438\u0435\u043d\u0442-\u0431\u0430\u043d\u043a\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0435 \u043f\u043e\u0441\u0442\u0443\u043f\u0438\u0432\u0448\u0438\u0435 \u043e\u043f\u043b\u0430\u0442\u044b, \u043d\u0430\u0434\u043e \u0438\u0445 \u0431\u044b\u0441\u0442\u0440\u0435\u043d\u044c\u043a\u043e \u0432\u043a\u0430\u0447\u0430\u0442\u044c \u043d\u0430 \u0441\u0430\u0439\u0442 \u0438 \u043f\u0440\u0438\u0432\u044f\u0437\u0430\u0442\u044c \u043a \u0441\u0447\u0435\u0442\u0430\u043c\u00bb \u041d\u043e \u043a\u043e\u0433\u0434\u0430 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":74954,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-74953","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.3 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0440\u0438 \u0441\u043b\u043e\u0436\u043d\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0440\u0430\u0437\u043d\u044b\u0435 ETL-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b: \u0438\u043c\u043f\u043e\u0440\u0442\u044b, \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0430\u0446\u0438\u0438 \u0438 \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0441 \u0432\u043d\u0435\u0448\u043d\u0438\u043c \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u043e\u043c) \u0447\u0430\u0441\u0442\u043e.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47DBA: \u0433\u0440\u0430\u043c\u043e\u0442\u043d\u043e \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u043e\u0432\u044b\u0432\u0430\u0435\u043c \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0438 \u0438\u043c\u043f\u043e\u0440\u0442\u044b | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u0438 \u0441\u043b\u043e\u0436\u043d\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0440\u0430\u0437\u043d\u044b\u0435 ETL-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b: \u0438\u043c\u043f\u043e\u0440\u0442\u044b, \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0430\u0446\u0438\u0438 \u0438 \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0441 \u0432\u043d\u0435\u0448\u043d\u0438\u043c \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u043e\u043c) \u0447\u0430\u0441\u0442\u043e.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy\" \/>\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-03-22T05:42:22+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-03-22T05:42:22+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\udd47DBA: spr\u00e1vn\u011b organizujeme synchronizace a importy | ProHoster","description":"P\u0159i slo\u017eit\u00e9m zpracov\u00e1n\u00ed velk\u00fdch sad dat (r\u016fzn\u00e9 ETL procesy: importy, konverze a synchronizace s extern\u00edm zdrojem) \u010dasto.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47DBA: \u0433\u0440\u0430\u043c\u043e\u0442\u043d\u043e \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u043e\u0432\u044b\u0432\u0430\u0435\u043c \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0438 \u0438\u043c\u043f\u043e\u0440\u0442\u044b | ProHoster","og:description":"\u041f\u0440\u0438 \u0441\u043b\u043e\u0436\u043d\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0440\u0430\u0437\u043d\u044b\u0435 ETL-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b: \u0438\u043c\u043f\u043e\u0440\u0442\u044b, \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0430\u0446\u0438\u0438 \u0438 \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0441 \u0432\u043d\u0435\u0448\u043d\u0438\u043c \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u043e\u043c) \u0447\u0430\u0441\u0442\u043e.","og:url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","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-03-22T05:42:22+00:00","article:modified_time":"2020-03-22T05:42:22+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"74953","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 18:04:26","updated":"2022-09-30 13:25:20","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/74953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=74953"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/74953\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/74954"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=74953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=74953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=74953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}