DBA: സമന്വയങ്ങളും ഇറക്കുമതികളും സമർത്ഥമായി സംഘടിപ്പിക്കുക

വലിയ ഡാറ്റാ സെറ്റുകളുടെ സങ്കീർണ്ണമായ പ്രോസസ്സിംഗിനായി (വ്യത്യസ്ത ETL പ്രക്രിയകൾ: ഇറക്കുമതികൾ, പരിവർത്തനങ്ങൾ, ഒരു ബാഹ്യ ഉറവിടവുമായി സമന്വയിപ്പിക്കൽ) പലപ്പോഴും ആവശ്യമുണ്ട് താൽക്കാലികമായി "ഓർക്കുക" ഉടൻ തന്നെ വേഗത്തിൽ പ്രോസസ്സ് ചെയ്യുക വലിയ എന്തെങ്കിലും.

ഇത്തരത്തിലുള്ള ഒരു സാധാരണ ജോലി സാധാരണയായി ഇതുപോലെയാണ്: "ഇവിടെ തന്നെ അക്കൗണ്ടിംഗ് വകുപ്പ് ക്ലയൻ്റ് ബാങ്കിൽ നിന്ന് ഇറക്കി അവസാനം ലഭിച്ച പേയ്‌മെൻ്റുകൾ, നിങ്ങൾ അവ വെബ്‌സൈറ്റിലേക്ക് വേഗത്തിൽ അപ്‌ലോഡ് ചെയ്യുകയും നിങ്ങളുടെ അക്കൗണ്ടുകളിലേക്ക് ലിങ്ക് ചെയ്യുകയും വേണം.

എന്നാൽ ഈ “എന്തോ” വിൻ്റെ അളവ് നൂറുകണക്കിന് മെഗാബൈറ്റുകളിൽ അളക്കാൻ തുടങ്ങുമ്പോൾ, സേവനം 24x7 ഡാറ്റാബേസിൽ പ്രവർത്തിക്കുന്നത് തുടരണം, നിങ്ങളുടെ ജീവിതത്തെ നശിപ്പിക്കുന്ന നിരവധി പാർശ്വഫലങ്ങൾ ഉണ്ടാകുന്നു.

PostgreSQL-ൽ (അതിൽ മാത്രമല്ല) അവ കൈകാര്യം ചെയ്യാൻ, നിങ്ങൾക്ക് ചില ഒപ്റ്റിമൈസേഷനുകൾ ഉപയോഗിക്കാം, അത് വേഗത്തിലും കുറഞ്ഞ വിഭവ ഉപഭോഗത്തിലും എല്ലാം പ്രോസസ്സ് ചെയ്യാൻ നിങ്ങളെ അനുവദിക്കും.

1. എവിടെ കയറ്റി അയയ്ക്കണം?

ആദ്യം, നമുക്ക് “പ്രോസസ്സ്” ചെയ്യേണ്ട ഡാറ്റ എവിടെ അപ്‌ലോഡ് ചെയ്യാമെന്ന് തീരുമാനിക്കാം.

1.1 താത്കാലിക പട്ടികകൾ (ടെമ്പററി ടേബിൾ)

തത്വത്തിൽ, PostgreSQL-ന് താൽക്കാലിക ടേബിളുകൾ മറ്റേതിന് തുല്യമാണ്. അതിനാൽ, അന്ധവിശ്വാസങ്ങൾ ഇഷ്ടപ്പെടുന്നു "അവിടെയുള്ളതെല്ലാം മെമ്മറിയിൽ മാത്രം സംഭരിച്ചിരിക്കുന്നു, അത് അവസാനിക്കും". എന്നാൽ നിരവധി കാര്യമായ വ്യത്യാസങ്ങളും ഉണ്ട്.

ഡാറ്റാബേസിലേക്കുള്ള ഓരോ കണക്ഷനും നിങ്ങളുടെ സ്വന്തം "നെയിംസ്പേസ്"

രണ്ട് കണക്ഷനുകൾ ഒരേ സമയം ബന്ധിപ്പിക്കാൻ ശ്രമിക്കുകയാണെങ്കിൽ CREATE TABLE x, അപ്പോൾ ആർക്കെങ്കിലും തീർച്ചയായും ലഭിക്കും അനന്യമായ തെറ്റ് ഡാറ്റാബേസ് വസ്തുക്കൾ.

എന്നാൽ ഇരുവരും എക്സിക്യൂട്ട് ചെയ്യാൻ ശ്രമിച്ചാൽ CREATE TEMPORARY TABLE x, അപ്പോൾ രണ്ടുപേരും അത് സാധാരണ രീതിയിൽ ചെയ്യും, എല്ലാവർക്കും ലഭിക്കും നിങ്ങളുടെ പകർപ്പ് പട്ടികകൾ. അവർക്കിടയിൽ പൊതുവായി ഒന്നുമില്ല.

വിച്ഛേദിക്കുമ്പോൾ "സ്വയം നശിപ്പിക്കുക"

കണക്ഷൻ അടയ്‌ക്കുമ്പോൾ, എല്ലാ താൽക്കാലിക ടേബിളുകളും സ്വയമേവ ഇല്ലാതാക്കപ്പെടും DROP TABLE x അല്ലാതെ ഒരു കാര്യവുമില്ല...

നിങ്ങൾ പ്രവർത്തിക്കുകയാണെങ്കിൽ ഇടപാട് മോഡിൽ pgbouncer, ഈ കണക്ഷൻ ഇപ്പോഴും സജീവമാണെന്ന് ഡാറ്റാബേസ് വിശ്വസിക്കുന്നത് തുടരുന്നു, അതിൽ ഈ താൽക്കാലിക പട്ടിക ഇപ്പോഴും നിലവിലുണ്ട്.

അതിനാൽ, മറ്റൊരു കണക്ഷനിൽ നിന്ന് pgbouncer-ലേക്ക് ഇത് വീണ്ടും സൃഷ്ടിക്കാൻ ശ്രമിക്കുന്നത് ഒരു പിശകിന് കാരണമാകും. എന്നാൽ ഇത് ഉപയോഗിക്കുന്നതിലൂടെ മറികടക്കാൻ കഴിയും CREATE TEMPORARY TABLE IF NOT EXISTS x.

ശരിയാണ്, എന്തായാലും ഇത് ചെയ്യാതിരിക്കുന്നതാണ് നല്ലത്, കാരണം "മുൻ ഉടമയിൽ" നിന്ന് ശേഷിക്കുന്ന ഡാറ്റ നിങ്ങൾക്ക് "പെട്ടെന്ന്" കണ്ടെത്താനാകും. പകരം, മാനുവൽ വായിച്ച് ഒരു പട്ടിക സൃഷ്ടിക്കുമ്പോൾ അത് ചേർക്കുന്നത് സാധ്യമാണെന്ന് കാണുന്നത് വളരെ നല്ലതാണ് ON COMMIT DROP - അതായത്, ഇടപാട് പൂർത്തിയാകുമ്പോൾ, പട്ടിക സ്വയമേവ ഇല്ലാതാക്കപ്പെടും.

നോൺ-റെപ്ലിക്കേഷൻ

അവ ഒരു പ്രത്യേക കണക്ഷനിൽ മാത്രമുള്ളതിനാൽ, താൽക്കാലിക പട്ടികകൾ ആവർത്തിക്കില്ല. പക്ഷേ ഇത് ഡാറ്റയുടെ ഇരട്ട റെക്കോർഡിംഗിൻ്റെ ആവശ്യകത ഇല്ലാതാക്കുന്നു കൂമ്പാരം + വാൾ, അതിനാൽ അതിലേക്ക് തിരുകുക/അപ്‌ഡേറ്റ് ചെയ്യുക/ഇല്ലാതാക്കുക വളരെ വേഗതയുള്ളതാണ്.

എന്നാൽ ഒരു താൽക്കാലിക ടേബിൾ ഇപ്പോഴും "ഏതാണ്ട് സാധാരണ" പട്ടികയായതിനാൽ, അത് ഒരു പകർപ്പിൽ സൃഷ്ടിക്കാൻ കഴിയില്ല. കുറഞ്ഞത് ഇപ്പോഴെങ്കിലും, അനുബന്ധ പാച്ച് വളരെക്കാലമായി പ്രചരിക്കുന്നുണ്ടെങ്കിലും.

1.2 ലോഗ് ചെയ്യാത്ത മേശ

എന്നാൽ എന്തുചെയ്യണം, ഉദാഹരണത്തിന്, നിങ്ങൾക്ക് ഒരു ഇടപാടിനുള്ളിൽ നടപ്പിലാക്കാൻ കഴിയാത്ത ഏതെങ്കിലും തരത്തിലുള്ള ബുദ്ധിമുട്ടുള്ള ETL പ്രോസസ്സ് ഉണ്ടെങ്കിൽ, നിങ്ങൾക്ക് ഇപ്പോഴും ഉണ്ട് ഇടപാട് മോഡിൽ pgbouncer? ..

അല്ലെങ്കിൽ ഡാറ്റ ഫ്ലോ വളരെ വലുതാണ് ഒരു കണക്ഷനിൽ മതിയായ ബാൻഡ്‌വിഡ്ത്ത് ഇല്ല ഒരു ഡാറ്റാബേസിൽ നിന്ന് (വായിക്കുക, ഓരോ സിപിയുവും ഒരു പ്രോസസ്സ്)?..

അല്ലെങ്കിൽ ചില പ്രവർത്തനങ്ങൾ നടക്കുന്നു അസമന്വിതമായി വ്യത്യസ്ത ബന്ധങ്ങളിൽ?..

ഇവിടെ ഒരു ഓപ്ഷൻ മാത്രമേയുള്ളൂ - താൽക്കാലികമല്ലാത്ത ഒരു പട്ടിക താൽക്കാലികമായി സൃഷ്ടിക്കുക. പൺ, അതെ. അതാണ്:

• ആരുമായും വിഭജിക്കാതിരിക്കാൻ പരമാവധി ക്രമരഹിതമായ പേരുകളുള്ള "എൻ്റെ സ്വന്തം" പട്ടികകൾ സൃഷ്ടിച്ചു
• എക്സ്ട്രാക്റ്റുചെയ്യുക: ഒരു ബാഹ്യ ഉറവിടത്തിൽ നിന്നുള്ള ഡാറ്റ ഉപയോഗിച്ച് അവ നിറച്ചു
• പരിവർത്തനം ചെയ്യുക: പരിവർത്തനം ചെയ്തു, കീ ലിങ്കിംഗ് ഫീൽഡുകൾ പൂരിപ്പിച്ചു
• ഭാരം: ടാർഗെറ്റ് ടേബിളുകളിലേക്ക് തയ്യാറായ ഡാറ്റ പകർന്നു
• "എൻ്റെ" പട്ടികകൾ ഇല്ലാതാക്കി

ഇപ്പോൾ - തൈലത്തിൽ ഒരു ഈച്ച. സത്യത്തിൽ, PostgreSQL-ലെ എല്ലാ എഴുത്തുകളും രണ്ടുതവണ സംഭവിക്കുന്നു - ആദ്യം WAL ൽ, തുടർന്ന് പട്ടിക/ഇൻഡക്സ് ബോഡികളിലേക്ക്. എസിഐഡിയെ പിന്തുണയ്ക്കുന്നതിനും ഡാറ്റാ ദൃശ്യപരത ശരിയാക്കുന്നതിനുമാണ് ഇതെല്ലാം ചെയ്യുന്നത് COMMIT'നട്ടി ആൻഡ് ROLLBACK'ശൂന്യമായ ഇടപാടുകൾ.

എന്നാൽ ഞങ്ങൾക്ക് ഇത് ആവശ്യമില്ല! ഞങ്ങൾക്ക് മുഴുവൻ പ്രക്രിയയും ഉണ്ട് ഒന്നുകിൽ അത് പൂർണ്ണമായും വിജയിച്ചു അല്ലെങ്കിൽ അത് വിജയിച്ചില്ല.. എത്ര ഇൻ്റർമീഡിയറ്റ് ഇടപാടുകൾ ഉണ്ടായിരിക്കുമെന്നത് പ്രശ്നമല്ല - “മധ്യത്തിൽ നിന്ന് പ്രക്രിയ തുടരുന്നതിൽ” ഞങ്ങൾക്ക് താൽപ്പര്യമില്ല, പ്രത്യേകിച്ചും അത് എവിടെയാണെന്ന് വ്യക്തമല്ലാത്തപ്പോൾ.

ഇത് ചെയ്യുന്നതിന്, PostgreSQL ഡവലപ്പർമാർ, പതിപ്പ് 9.1-ൽ ഇത്തരമൊരു കാര്യം അവതരിപ്പിച്ചു UNLOGGED പട്ടികകൾ:

ഈ സൂചന ഉപയോഗിച്ച്, പട്ടിക അൺലോഗ് ചെയ്തതായി സൃഷ്ടിച്ചിരിക്കുന്നു. അൺലോഗ് ചെയ്യാത്ത ടേബിളുകളിലേക്ക് എഴുതിയ ഡാറ്റ, റൈറ്റ്-എഹെഡ് ലോഗിലൂടെ കടന്നുപോകുന്നില്ല (അധ്യായം 29 കാണുക), ഇത് അത്തരം ടേബിളുകൾക്ക് കാരണമാകുന്നു. പതിവിലും വളരെ വേഗത്തിൽ പ്രവർത്തിക്കുക. എന്നിരുന്നാലും, അവർ പരാജയത്തിൽ നിന്ന് മുക്തരല്ല; സെർവർ തകരാർ അല്ലെങ്കിൽ അടിയന്തര ഷട്ട്ഡൗൺ സംഭവിക്കുകയാണെങ്കിൽ, ഒരു അൺലോഗ് ചെയ്ത പട്ടിക യാന്ത്രികമായി വെട്ടിച്ചുരുക്കി. കൂടാതെ, അൺലോഗ് ചെയ്ത പട്ടികയിലെ ഉള്ളടക്കങ്ങൾ പകർത്തിയിട്ടില്ല അടിമ സെർവറുകളിലേക്ക്. ലോഗ് ചെയ്യാത്ത പട്ടികയിൽ സൃഷ്‌ടിച്ച എല്ലാ സൂചികകളും സ്വയമേവ അൺലോഗ് ചെയ്യപ്പെടും.

ചുരുക്കത്തിൽ, അത് വളരെ വേഗത്തിലായിരിക്കും, എന്നാൽ ഡാറ്റാബേസ് സെർവർ "വീഴുകയാണെങ്കിൽ", അത് അരോചകമായിരിക്കും. എന്നാൽ ഇത് എത്ര തവണ സംഭവിക്കുന്നു, ഡാറ്റാബേസ് "പുനരുജ്ജീവിപ്പിച്ചതിന്" ശേഷം "മധ്യത്തിൽ നിന്ന്" ഇത് എങ്ങനെ ശരിയാക്കാമെന്ന് നിങ്ങളുടെ ETL പ്രോസസ്സിന് അറിയാമോ?

ഇല്ലെങ്കിൽ, മുകളിലുള്ള കേസ് നിങ്ങളുടേതിന് സമാനമാണെങ്കിൽ, ഉപയോഗിക്കുക UNLOGGEDപക്ഷേ ഒരിക്കലും യഥാർത്ഥ പട്ടികകളിൽ ഈ ആട്രിബ്യൂട്ട് പ്രവർത്തനക്ഷമമാക്കരുത്, അതിൽ നിന്നുള്ള ഡാറ്റ നിങ്ങൾക്ക് പ്രിയപ്പെട്ടതാണ്.

1.3 കമ്മിറ്റിൽ { വരികൾ ഇല്ലാതാക്കുക | ഡ്രോപ്പ്}

ഒരു പട്ടിക സൃഷ്ടിക്കുമ്പോൾ ഒരു ഇടപാട് പൂർത്തിയാകുമ്പോൾ യാന്ത്രിക സ്വഭാവം വ്യക്തമാക്കാൻ ഈ നിർമ്മാണം നിങ്ങളെ അനുവദിക്കുന്നു.

ഓൺ ON COMMIT DROP ഞാൻ ഇതിനകം മുകളിൽ എഴുതി, അത് സൃഷ്ടിക്കുന്നു DROP TABLE, എന്നാൽ കൂടെ ON COMMIT DELETE ROWS സാഹചര്യം കൂടുതൽ രസകരമാണ് - അത് ഇവിടെ സൃഷ്ടിക്കപ്പെടുന്നു TRUNCATE TABLE.

ഒരു താൽക്കാലിക പട്ടികയുടെ മെറ്റാ-വിവരണം സംഭരിക്കുന്നതിനുള്ള മുഴുവൻ ഇൻഫ്രാസ്ട്രക്ചറും ഒരു സാധാരണ ടേബിളിന് തുല്യമായതിനാൽ, തുടർന്ന് താൽക്കാലിക ടേബിളുകൾ നിരന്തരം സൃഷ്ടിക്കുന്നതും ഇല്ലാതാക്കുന്നതും സിസ്റ്റം ടേബിളുകളുടെ ഗുരുതരമായ "വീക്കം" ലേക്ക് നയിക്കുന്നു pg_class, pg_attribute, pg_attrdef, pg_depend,...

ഇപ്പോൾ നിങ്ങൾക്ക് ഡാറ്റാബേസിലേക്ക് നേരിട്ടുള്ള കണക്ഷനിൽ ഒരു തൊഴിലാളി ഉണ്ടെന്ന് സങ്കൽപ്പിക്കുക, അത് ഓരോ സെക്കൻഡിലും ഒരു പുതിയ ഇടപാട് തുറക്കുകയും ഒരു താൽക്കാലിക ടേബിൾ സൃഷ്ടിക്കുകയും പൂരിപ്പിക്കുകയും പ്രോസസ്സ് ചെയ്യുകയും ഇല്ലാതാക്കുകയും ചെയ്യുന്നു... സിസ്റ്റം ടേബിളുകളിൽ അധികമായി മാലിന്യം കുമിഞ്ഞുകൂടും. ഇത് ഓരോ പ്രവർത്തനത്തിനും അധിക ബ്രേക്കുകൾ ഉണ്ടാക്കും.

പൊതുവേ, ഇത് ചെയ്യരുത്! ഈ സാഹചര്യത്തിൽ, ഇത് കൂടുതൽ ഫലപ്രദമാണ് CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ഇടപാട് സൈക്കിളിൽ നിന്ന് പുറത്തെടുക്കുക - തുടർന്ന് ഓരോ പുതിയ ഇടപാടിൻ്റെയും ആരംഭത്തോടെ പട്ടികകൾ ഇതിനകം തന്നെ നിലനിൽക്കും (ഒരു കോൾ സംരക്ഷിക്കുക CREATE), പക്ഷേ ശൂന്യമായിരിക്കും, നന്ദി TRUNCATE മുമ്പത്തെ ഇടപാട് പൂർത്തിയാക്കുമ്പോൾ (അതിൻ്റെ കോളും ഞങ്ങൾ സംരക്ഷിച്ചു).

1.4 പോലെ...ഉൾപ്പെടെ...

താൽകാലിക ടേബിളുകൾക്കുള്ള സാധാരണ ഉപയോഗ കേസുകളിൽ ഒന്ന് വിവിധ തരത്തിലുള്ള ഇറക്കുമതികളാണെന്ന് ഞാൻ തുടക്കത്തിൽ സൂചിപ്പിച്ചു - കൂടാതെ ഡെവലപ്പർ ക്ഷീണിതനായി ടാർഗെറ്റ് ടേബിളിൻ്റെ ഫീൽഡുകളുടെ ലിസ്റ്റ് തൻ്റെ താൽക്കാലിക പ്രഖ്യാപനത്തിലേക്ക് പകർത്തി ഒട്ടിക്കുന്നു...

എന്നാൽ അലസതയാണ് പുരോഗതിയുടെ എഞ്ചിൻ! അതുകൊണ്ടാണ് "സാമ്പിൾ അടിസ്ഥാനമാക്കി" ഒരു പുതിയ പട്ടിക സൃഷ്ടിക്കുക ഇത് വളരെ ലളിതമായിരിക്കാം:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

നിങ്ങൾക്ക് ഈ പട്ടികയിലേക്ക് ധാരാളം ഡാറ്റ സൃഷ്ടിക്കാൻ കഴിയുന്നതിനാൽ, അതിലൂടെ തിരയുന്നത് ഒരിക്കലും വേഗത്തിലാകില്ല. എന്നാൽ ഇതിന് ഒരു പരമ്പരാഗത പരിഹാരമുണ്ട് - സൂചികകൾ! ഒപ്പം, അതെ, ഒരു താൽക്കാലിക പട്ടികയ്ക്കും സൂചികകൾ ഉണ്ടായിരിക്കാം.

പലപ്പോഴും, ആവശ്യമായ സൂചികകൾ ടാർഗെറ്റ് പട്ടികയുടെ സൂചികകളുമായി പൊരുത്തപ്പെടുന്നതിനാൽ, നിങ്ങൾക്ക് ലളിതമായി എഴുതാം LIKE target_table INCLUDING INDEXES.

നിങ്ങൾക്കും ആവശ്യമെങ്കിൽ DEFAULT-മൂല്യങ്ങൾ (ഉദാഹരണത്തിന്, പ്രാഥമിക കീ മൂല്യങ്ങൾ പൂരിപ്പിക്കുന്നതിന്), നിങ്ങൾക്ക് ഉപയോഗിക്കാം LIKE target_table INCLUDING DEFAULTS. അല്ലെങ്കിൽ ലളിതമായി - LIKE target_table INCLUDING ALL — പകർപ്പുകൾ ഡിഫോൾട്ടുകൾ, സൂചികകൾ, നിയന്ത്രണങ്ങൾ,...

എന്നാൽ നിങ്ങൾ സൃഷ്ടിച്ചതാണെങ്കിൽ ഇവിടെ നിങ്ങൾ മനസ്സിലാക്കേണ്ടതുണ്ട് സൂചികകൾ ഉപയോഗിച്ച് ഉടൻ പട്ടിക ഇറക്കുമതി ചെയ്യുക, തുടർന്ന് ഡാറ്റ ലോഡുചെയ്യാൻ കൂടുതൽ സമയമെടുക്കുംനിങ്ങൾ ആദ്യം എല്ലാം പൂരിപ്പിക്കുകയും പിന്നീട് സൂചികകൾ ചുരുട്ടുകയും ചെയ്യുന്നതിനേക്കാൾ - ഇത് എങ്ങനെ ചെയ്യുന്നുവെന്ന് ഒരു ഉദാഹരണമായി നോക്കുക pg_dump.

ചുരുക്കത്തിൽ, ര്ത്ഫ്മ്!

2. എങ്ങനെ എഴുതാം?

ഞാൻ പറയട്ടെ - അത് ഉപയോഗിക്കുക COPY"പാക്ക്" എന്നതിന് പകരം ഒഴുകുക INSERT, ചില സമയങ്ങളിൽ ത്വരണം. മുൻകൂട്ടി ജനറേറ്റുചെയ്ത ഫയലിൽ നിന്ന് പോലും നിങ്ങൾക്ക് നേരിട്ട് കഴിയും.

3. എങ്ങനെ പ്രോസസ്സ് ചെയ്യാം?

അതിനാൽ, നമ്മുടെ ആമുഖം ഇതുപോലെയായിരിക്കട്ടെ:

• നിങ്ങളുടെ ഡാറ്റാബേസിൽ ക്ലയൻ്റ് ഡാറ്റ സംഭരിച്ചിരിക്കുന്ന ഒരു പട്ടിക നിങ്ങൾക്കുണ്ട് 1 മി റെക്കോർഡുകൾ
• എല്ലാ ദിവസവും ഒരു ക്ലയൻ്റ് നിങ്ങൾക്ക് പുതിയൊരെണ്ണം അയയ്ക്കുന്നു മുഴുവൻ "ചിത്രം"
• അനുഭവത്തിൽ നിന്ന് നിങ്ങൾക്ക് അത് കാലാകാലങ്ങളിൽ അറിയാം 10K-ൽ കൂടുതൽ റെക്കോർഡുകൾ മാറ്റില്ല

അത്തരമൊരു സാഹചര്യത്തിൻ്റെ ഒരു ക്ലാസിക് ഉദാഹരണം KLADR ബേസ് - മൊത്തത്തിൽ ധാരാളം വിലാസങ്ങൾ ഉണ്ട്, എന്നാൽ ഓരോ പ്രതിവാര അപ്‌ലോഡിലും ദേശീയ തലത്തിൽ പോലും വളരെ കുറച്ച് മാറ്റങ്ങളുണ്ട് (സെറ്റിൽമെൻ്റുകളുടെ പുനർനാമകരണം, തെരുവുകളുടെ സംയോജനം, പുതിയ വീടുകളുടെ രൂപം).

3.1 പൂർണ്ണ സമന്വയ അൽഗോരിതം

ലാളിത്യത്തിനായി, നിങ്ങൾക്ക് ഡാറ്റ പുനഃക്രമീകരിക്കേണ്ട ആവശ്യമില്ലെന്ന് പറയാം - ആവശ്യമുള്ള ഫോമിലേക്ക് പട്ടിക കൊണ്ടുവരിക, അതായത്:

• നീക്കം ചെയ്യുക ഇനി നിലവിലില്ലാത്ത എല്ലാം
• അപ്ഗ്രേഡ് നിലവിലുള്ളതും അപ്‌ഡേറ്റ് ചെയ്യേണ്ടതുമായ എല്ലാം
• തിരുകുക ഇതുവരെ സംഭവിക്കാത്തതെല്ലാം

എന്തുകൊണ്ടാണ് ഈ ക്രമത്തിൽ പ്രവർത്തനങ്ങൾ നടത്തേണ്ടത്? കാരണം ഇങ്ങനെയാണ് മേശയുടെ വലിപ്പം കുറഞ്ഞത് (MVCC ഓർക്കുക!).

dst-ൽ നിന്ന് ഇല്ലാതാക്കുക

ഇല്ല, തീർച്ചയായും നിങ്ങൾക്ക് രണ്ട് പ്രവർത്തനങ്ങളിലൂടെ നേടാനാകും:

• നീക്കം ചെയ്യുക (DELETE) പൊതുവെ എല്ലാം
• തിരുകുക എല്ലാം പുതിയ ചിത്രത്തിൽ നിന്ന്

എന്നാൽ അതേ സമയം, എംവിസിസിക്ക് നന്ദി, പട്ടികയുടെ വലിപ്പം കൃത്യമായി രണ്ടുതവണ വർദ്ധിക്കും! 1K അപ്‌ഡേറ്റ് കാരണം +10M റെക്കോർഡുകളുടെ ചിത്രങ്ങൾ പട്ടികയിൽ ലഭിക്കുന്നത് വളരെ ആവർത്തനമാണ്...

വെട്ടിച്ചുരുക്കുക dst

മുഴുവൻ ടാബ്‌ലെറ്റും വളരെ വിലകുറഞ്ഞ രീതിയിൽ വൃത്തിയാക്കാൻ കഴിയുമെന്ന് കൂടുതൽ പരിചയസമ്പന്നനായ ഒരു ഡെവലപ്പർക്ക് അറിയാം:

• മായ്ക്കാൻ (TRUNCATE) മുഴുവൻ പട്ടികയും
• തിരുകുക എല്ലാം പുതിയ ചിത്രത്തിൽ നിന്ന്

രീതി ഫലപ്രദമാണ്, ചിലപ്പോൾ തികച്ചും ബാധകമാണ്, പക്ഷേ ഒരു പ്രശ്‌നമുണ്ട്... ഞങ്ങൾ വളരെക്കാലം 1M റെക്കോർഡുകൾ ചേർക്കും, അതിനാൽ ഈ സമയമത്രയും ടേബിൾ ശൂന്യമായി വയ്ക്കാൻ ഞങ്ങൾക്ക് കഴിയില്ല (ഒറ്റ ഇടപാടിൽ പൊതിയാതെ സംഭവിക്കും).

അത് അർത്ഥമാക്കുന്നത്:

• ഞങ്ങൾ തുടങ്ങുകയാണ് ദീർഘകാല ഇടപാട്
• TRUNCATE ചുമത്തുന്നു എക്സ്ക്ലൂസീവ് ആക്സസ്- തടയുന്നു
• ഞങ്ങൾ ദീർഘനേരം ചേർക്കൽ ചെയ്യുന്നു, ഈ സമയത്ത് മറ്റെല്ലാവരും പോലും കഴിയില്ല SELECT

എന്തോ കുഴപ്പമില്ല...

പട്ടിക മാറ്റുക... പുനർനാമകരണം ചെയ്യുക... / ഡ്രോപ്പ് ടേബിൾ...

ഒരു പുതിയ പട്ടികയിൽ എല്ലാം പൂരിപ്പിക്കുക, തുടർന്ന് പഴയതിന് പകരം പേരുമാറ്റുക എന്നതാണ് ഒരു ബദൽ. കുറച്ച് മോശം ചെറിയ കാര്യങ്ങൾ:

• ഇപ്പോഴും എക്സ്ക്ലൂസീവ് ആക്സസ്, സമയം ഗണ്യമായി കുറവാണെങ്കിലും
• ഈ പട്ടികയ്‌ക്കായുള്ള എല്ലാ അന്വേഷണ പ്ലാനുകളും/ സ്ഥിതിവിവരക്കണക്കുകളും പുനഃസജ്ജീകരിച്ചിരിക്കുന്നു, വിശകലനം പ്രവർത്തിപ്പിക്കേണ്ടതുണ്ട്
• എല്ലാ വിദേശ കീകളും തകർന്നിരിക്കുന്നു (FK) മേശയിലേക്ക്

നിർമ്മിക്കാൻ നിർദ്ദേശിച്ച സൈമൺ റിഗ്‌സിൽ നിന്നുള്ള ഒരു WIP പാച്ച് ഉണ്ടായിരുന്നു ALTERസ്ഥിതിവിവരക്കണക്കുകളും എഫ്‌കെയും സ്പർശിക്കാതെ ഫയൽ തലത്തിൽ ടേബിൾ ബോഡി മാറ്റിസ്ഥാപിക്കുന്നതിനുള്ള ഒരു പ്രവർത്തനം, പക്ഷേ കോറം ശേഖരിച്ചില്ല.

ഇല്ലാതാക്കുക, അപ്ഡേറ്റ് ചെയ്യുക, ചേർക്കുക

അതിനാൽ, മൂന്ന് പ്രവർത്തനങ്ങളുടെ നോൺ-ബ്ലോക്കിംഗ് ഓപ്ഷനിൽ ഞങ്ങൾ സ്ഥിരതാമസമാക്കുന്നു. ഏതാണ്ട് മൂന്ന്... ഇത് എങ്ങനെ ഏറ്റവും ഫലപ്രദമായി ചെയ്യാം?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие

-- вставляем отсутствующие
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2 പോസ്റ്റ് പ്രോസസ്സിംഗ് ഇറക്കുമതി ചെയ്യുക

അതേ KLADR-ൽ, മാറ്റിയ എല്ലാ റെക്കോർഡുകളും പോസ്റ്റ്-പ്രോസസിംഗിലൂടെ അധികമായി റൺ ചെയ്യണം - നോർമലൈസ്ഡ്, കീവേഡുകൾ ഹൈലൈറ്റ് ചെയ്യുകയും ആവശ്യമായ ഘടനകളിലേക്ക് ചുരുക്കുകയും വേണം. പക്ഷെ നിനക്ക് എങ്ങനെ അറിയാം... കൃത്യമായി എന്താണ് മാറിയത്സിൻക്രൊണൈസേഷൻ കോഡ് സങ്കീർണ്ണമാക്കാതെ, അത് സ്പർശിക്കാതെ തന്നെ?

സമന്വയ സമയത്ത് നിങ്ങളുടെ പ്രോസസ്സിന് മാത്രമേ എഴുത്ത് ആക്സസ് ഉള്ളൂ എങ്കിൽ, ഞങ്ങൾക്കായി എല്ലാ മാറ്റങ്ങളും ശേഖരിക്കുന്ന ഒരു ട്രിഗർ നിങ്ങൾക്ക് ഉപയോഗിക്കാം:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ഇപ്പോൾ നമുക്ക് സമന്വയം ആരംഭിക്കുന്നതിന് മുമ്പ് ട്രിഗറുകൾ പ്രയോഗിക്കാൻ കഴിയും (അല്ലെങ്കിൽ അവ വഴി പ്രവർത്തനക്ഷമമാക്കുക ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

തുടർന്ന് ലോഗ് ടേബിളിൽ നിന്ന് ആവശ്യമായ എല്ലാ മാറ്റങ്ങളും ഞങ്ങൾ ശാന്തമായി എക്‌സ്‌ട്രാക്റ്റ് ചെയ്യുകയും അധിക ഹാൻഡ്‌ലറുകളിലൂടെ പ്രവർത്തിപ്പിക്കുകയും ചെയ്യുന്നു.

3.3 ലിങ്ക്ഡ് സെറ്റുകൾ ഇറക്കുമതി ചെയ്യുന്നു

ഉറവിടത്തിൻ്റെയും ലക്ഷ്യസ്ഥാനത്തിൻ്റെയും ഡാറ്റാ ഘടനകൾ ഒരുപോലെ ആയിരിക്കുമ്പോൾ ഞങ്ങൾ മുകളിൽ പറഞ്ഞിരിക്കുന്ന കേസുകൾ പരിഗണിച്ചു. എന്നാൽ ഒരു ബാഹ്യ സിസ്റ്റത്തിൽ നിന്നുള്ള അപ്‌ലോഡിന് നമ്മുടെ ഡാറ്റാബേസിലെ സ്റ്റോറേജ് ഘടനയിൽ നിന്ന് വ്യത്യസ്തമായ ഒരു ഫോർമാറ്റ് ഉണ്ടെങ്കിൽ എന്തുചെയ്യും?

ക്ലയൻ്റുകളുടെയും അവരുടെ അക്കൗണ്ടുകളുടെയും സംഭരണം ഉദാഹരണമായി എടുക്കാം, ക്ലാസിക് "മനി-ടു-വൺ" ഓപ്ഷൻ:

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

എന്നാൽ ഒരു ബാഹ്യ ഉറവിടത്തിൽ നിന്നുള്ള ഡൗൺലോഡ് "എല്ലാം ഒരു" എന്ന രൂപത്തിൽ നമ്മിലേക്ക് വരുന്നു:

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

വ്യക്തമായും, ഈ പതിപ്പിൽ ഉപഭോക്തൃ ഡാറ്റ ഡ്യൂപ്ലിക്കേറ്റ് ചെയ്യാം, പ്രധാന റെക്കോർഡ് "അക്കൗണ്ട്" ആണ്:

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

മോഡലിനായി, ഞങ്ങൾ ഞങ്ങളുടെ ടെസ്റ്റ് ഡാറ്റ ചേർക്കും, പക്ഷേ ഓർക്കുക - COPY കൂടുതൽ കാര്യക്ഷമമായി!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

ആദ്യം, നമ്മുടെ "വസ്തുതകൾ" പരാമർശിക്കുന്ന "വെട്ടലുകൾ" ഹൈലൈറ്റ് ചെയ്യാം. ഞങ്ങളുടെ കാര്യത്തിൽ, ഇൻവോയ്‌സുകൾ ഉപഭോക്താക്കളെ പരാമർശിക്കുന്നു:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

ഉപഭോക്തൃ ഐഡികളുമായി അക്കൗണ്ടുകൾ ശരിയായി ബന്ധപ്പെടുത്തുന്നതിന്, ഞങ്ങൾ ആദ്യം ഈ ഐഡൻ്റിഫയറുകൾ കണ്ടെത്തുകയോ സൃഷ്ടിക്കുകയോ ചെയ്യേണ്ടതുണ്ട്. നമുക്ക് അവയ്ക്ക് കീഴിൽ ഫീൽഡുകൾ ചേർക്കാം:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

മുകളിൽ വിവരിച്ചിരിക്കുന്ന ടേബിൾ സിൻക്രൊണൈസേഷൻ രീതി ഒരു ചെറിയ ഭേദഗതിയോടെ നമുക്ക് ഉപയോഗിക്കാം - ഞങ്ങൾ ടാർഗെറ്റ് ടേബിളിൽ ഒന്നും അപ്ഡേറ്റ് ചെയ്യുകയോ ഇല്ലാതാക്കുകയോ ചെയ്യില്ല, കാരണം ഞങ്ങൾ ക്ലയൻ്റുകളെ "അനുയോജ്യമാക്കാൻ മാത്രം" ഇറക്കുമതി ചെയ്യുന്നു:

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

യഥാർത്ഥത്തിൽ, എല്ലാം അകത്തുണ്ട് invoice_import ഇപ്പോൾ ഞങ്ങൾ കോൺടാക്റ്റ് ഫീൽഡ് പൂരിപ്പിച്ചു client_id, ഞങ്ങൾ ഇൻവോയ്സ് ചേർക്കും.

അവലംബം: www.habr.com