Postgres: bloat, pg_repack மற்றும் deferred constraints

அட்டவணைகள் மற்றும் குறியீடுகளில் வீக்கத்தின் விளைவு பரவலாக அறியப்படுகிறது மற்றும் போஸ்ட்கிரெஸில் மட்டுமல்ல. VACUUM FULL அல்லது CLUSTER போன்றவற்றைக் கையாள்வதற்கான வழிகள் உள்ளன, ஆனால் அவை செயல்பாட்டின் போது அட்டவணைகளைப் பூட்டுகின்றன, எனவே எப்போதும் பயன்படுத்த முடியாது.

வீக்கம் எவ்வாறு ஏற்படுகிறது, அதை எவ்வாறு எதிர்த்துப் போராடலாம், ஒத்திவைக்கப்பட்ட தடைகள் மற்றும் அவை pg_repack நீட்டிப்பைப் பயன்படுத்துவதில் ஏற்படும் சிக்கல்கள் பற்றிய ஒரு சிறிய கோட்பாடு கட்டுரையில் இருக்கும்.

என்ற அடிப்படையில் இக்கட்டுரை எழுதப்பட்டுள்ளது என் பேச்சு PgConf.Russia 2020 இல்.

வீக்கம் ஏன் ஏற்படுகிறது?

Postgres பல பதிப்பு மாதிரியை அடிப்படையாகக் கொண்டது (எம்விசிசி) அதன் சாராம்சம் என்னவென்றால், அட்டவணையில் உள்ள ஒவ்வொரு வரிசையும் பல பதிப்புகளைக் கொண்டிருக்கலாம், அதே நேரத்தில் பரிவர்த்தனைகள் இந்த பதிப்புகளில் ஒன்றுக்கு மேல் பார்க்க முடியாது, ஆனால் ஒரே மாதிரியாக இருக்க வேண்டிய அவசியமில்லை. இது பல பரிவர்த்தனைகளை ஒரே நேரத்தில் வேலை செய்ய அனுமதிக்கிறது மற்றும் ஒன்றுக்கொன்று எந்த தாக்கத்தையும் ஏற்படுத்தாது.

வெளிப்படையாக, இந்த பதிப்புகள் அனைத்தும் சேமிக்கப்பட வேண்டும். Postgres பக்கம் பக்கமாக நினைவகத்துடன் வேலை செய்கிறது மற்றும் ஒரு பக்கம் என்பது வட்டில் இருந்து படிக்கக்கூடிய அல்லது எழுதப்பட்ட தரவின் குறைந்தபட்ச அளவு. இது எப்படி நடக்கிறது என்பதைப் புரிந்துகொள்ள ஒரு சிறிய உதாரணத்தைப் பார்ப்போம்.

எங்களிடம் ஒரு அட்டவணை உள்ளது, அதில் பல பதிவுகளைச் சேர்த்துள்ளோம் என்று வைத்துக்கொள்வோம். அட்டவணை சேமிக்கப்பட்ட கோப்பின் முதல் பக்கத்தில் புதிய தரவு தோன்றியது. இவை வரிசைகளின் நேரடி பதிப்புகளாகும், அவை ஒரு உறுதிப்பாட்டிற்குப் பிறகு மற்ற பரிவர்த்தனைகளுக்குக் கிடைக்கும் (எளிமைக்காக, தனிமைப்படுத்தப்பட்ட நிலை ரீட் கமிட்டட் என்று நாங்கள் கருதுவோம்).

பின்னர் உள்ளீடுகளில் ஒன்றைப் புதுப்பித்தோம், இதன் மூலம் பழைய பதிப்பை இனி பொருந்தாது எனக் குறித்தோம்.

படிப்படியாக, வரிசை பதிப்புகளைப் புதுப்பித்தல் மற்றும் நீக்குதல், நாங்கள் ஒரு பக்கத்துடன் முடித்தோம், அதில் கிட்டத்தட்ட பாதி தரவு "குப்பை". இந்தத் தரவு எந்தப் பரிவர்த்தனைக்கும் தெரிவதில்லை.

Postgres ஒரு பொறிமுறையைக் கொண்டுள்ளது வெற்றிட, இது வழக்கற்றுப் போன பதிப்புகளை சுத்தம் செய்து புதிய தரவுகளுக்கு இடமளிக்கிறது. ஆனால் இது போதுமான அளவு ஆக்ரோஷமாக உள்ளமைக்கப்படவில்லை அல்லது பிற அட்டவணையில் வேலை செய்வதில் பிஸியாக இருந்தால், "குப்பை தரவு" இருக்கும், மேலும் புதிய தரவுகளுக்கு கூடுதல் பக்கங்களைப் பயன்படுத்த வேண்டும்.

எனவே எங்கள் எடுத்துக்காட்டில், ஒரு கட்டத்தில் அட்டவணை நான்கு பக்கங்களைக் கொண்டிருக்கும், ஆனால் அதில் பாதி மட்டுமே நேரடி தரவுகளைக் கொண்டிருக்கும். இதன் விளைவாக, அட்டவணையை அணுகும்போது, ​​தேவையானதை விட அதிகமான தரவைப் படிப்போம்.

VACUUM இப்போது அனைத்து பொருத்தமற்ற வரிசை பதிப்புகளையும் நீக்கினாலும், நிலைமை பெரிய அளவில் மேம்படாது. புதிய வரிசைகளுக்கான பக்கங்கள் அல்லது முழுப் பக்கங்களிலும் எங்களிடம் இலவச இடம் இருக்கும், ஆனால் தேவையானதை விட அதிகமான தரவைப் படிப்போம்.
மூலம், கோப்பின் முடிவில் முற்றிலும் வெற்றுப் பக்கம் (எங்கள் எடுத்துக்காட்டில் இரண்டாவது) இருந்தால், VACUUM ஆல் அதை ஒழுங்கமைக்க முடியும். ஆனால் இப்போது அவள் நடுவில் இருப்பதால் அவளை எதுவும் செய்ய முடியாது.

அத்தகைய வெற்று அல்லது மிகவும் அரிதான பக்கங்களின் எண்ணிக்கை பெரிதாகும்போது, ​​இது ப்ளோட் என்று அழைக்கப்படுகிறது, அது செயல்திறனை பாதிக்கத் தொடங்குகிறது.

மேலே விவரிக்கப்பட்ட அனைத்தும் அட்டவணையில் வீக்கம் ஏற்படுவதற்கான இயக்கவியல் ஆகும். குறியீடுகளில் இது அதே வழியில் நடக்கும்.

எனக்கு வீக்கம் உள்ளதா?

உங்களுக்கு வீக்கம் உள்ளதா என்பதை தீர்மானிக்க பல வழிகள் உள்ளன. அட்டவணையில் உள்ள வரிசைகளின் எண்ணிக்கை, "நேரடி" வரிசைகளின் எண்ணிக்கை போன்றவற்றைப் பற்றிய தோராயமான தகவல்களைக் கொண்டிருக்கும் உள் போஸ்ட்கிரெஸ் புள்ளிவிவரங்களைப் பயன்படுத்துவதே முதல் யோசனை. இணையத்தில் ஆயத்த ஸ்கிரிப்ட்களின் பல மாறுபாடுகளை நீங்கள் காணலாம். நாங்கள் அடிப்படையாக எடுத்துக் கொண்டோம் ஸ்கிரிப்ட் PostgreSQL நிபுணர்களிடமிருந்து, இது டோஸ்ட் மற்றும் ப்ளோட் பிட்ரீ குறியீடுகளுடன் ப்ளோட் டேபிள்களை மதிப்பிட முடியும். எங்கள் அனுபவத்தில், அதன் பிழை 10-20% ஆகும்.

மற்றொரு வழி நீட்டிப்பைப் பயன்படுத்துவது pgstattuple, இது பக்கங்களுக்குள் பார்க்கவும், மதிப்பிடப்பட்ட மற்றும் சரியான ப்ளோட் மதிப்பைப் பெறவும் உங்களை அனுமதிக்கிறது. ஆனால் இரண்டாவது வழக்கில், நீங்கள் முழு அட்டவணையையும் ஸ்கேன் செய்ய வேண்டும்.

20% வரை ஒரு சிறிய ப்ளோட் மதிப்பை ஏற்கத்தக்கதாக நாங்கள் கருதுகிறோம். இது நிரப்பு காரணியின் அனலாக் என்று கருதலாம் அட்டவணைகள் и குறியீடுகள். 50% மற்றும் அதற்கு மேல், செயல்திறன் சிக்கல்கள் தொடங்கலாம்.

வீக்கத்தை எதிர்த்துப் போராடுவதற்கான வழிகள்

போஸ்ட்கிரெஸ் அவுட் ஆஃப் தி பாக்ஸைச் சமாளிக்க பல வழிகளைக் கொண்டுள்ளது, ஆனால் அவை எப்போதும் அனைவருக்கும் பொருந்தாது.

வீக்கம் ஏற்படாதவாறு AUTOVACUUM ஐ உள்ளமைக்கவும். அல்லது இன்னும் துல்லியமாக, அதை நீங்கள் ஏற்றுக்கொள்ளக்கூடிய அளவில் வைத்திருக்க வேண்டும். இது "கேப்டனின்" ஆலோசனை போல் தெரிகிறது, ஆனால் உண்மையில் இதை அடைவது எளிதல்ல. எடுத்துக்காட்டாக, தரவுத் திட்டத்தில் வழக்கமான மாற்றங்களுடன் நீங்கள் செயலில் வளர்ச்சியைக் கொண்டிருக்கிறீர்கள் அல்லது சில வகையான தரவு இடம்பெயர்வு நடைபெறுகிறது. இதன் விளைவாக, உங்கள் சுமை சுயவிவரம் அடிக்கடி மாறலாம் மற்றும் பொதுவாக அட்டவணைக்கு அட்டவணை மாறுபடும். இதன் பொருள் நீங்கள் தொடர்ந்து சிறிது முன்னோக்கி வேலை செய்ய வேண்டும் மற்றும் ஒவ்வொரு அட்டவணையின் சுயவிவரத்தை மாற்றுவதற்கு AUTOVACUUM ஐ சரிசெய்ய வேண்டும். ஆனால் வெளிப்படையாக இதைச் செய்வது எளிதானது அல்ல.

AUTOVACUUM ஆனது அட்டவணைகளைத் தொடர முடியாததற்கு மற்றொரு பொதுவான காரணம், அந்த பரிவர்த்தனைகளுக்குக் கிடைக்கும் தரவைச் சுத்தம் செய்வதைத் தடுக்கும் நீண்ட கால பரிவர்த்தனைகள் இருப்பதால். இங்குள்ள பரிந்துரையும் வெளிப்படையானது - "தொங்கும்" பரிவர்த்தனைகளை அகற்றி, செயலில் உள்ள பரிவர்த்தனைகளின் நேரத்தைக் குறைக்கவும். ஆனால் உங்கள் பயன்பாட்டில் உள்ள சுமை OLAP மற்றும் OLTP இன் கலப்பினமாக இருந்தால், நீங்கள் ஒரே நேரத்தில் பல புதுப்பிப்புகள் மற்றும் குறுகிய வினவல்கள் மற்றும் நீண்ட கால செயல்பாடுகள் - எடுத்துக்காட்டாக, ஒரு அறிக்கையை உருவாக்குதல். அத்தகைய சூழ்நிலையில், வெவ்வேறு தளங்களில் சுமைகளை பரப்புவது பற்றி சிந்திக்க வேண்டியது அவசியம், அவை ஒவ்வொன்றையும் இன்னும் நன்றாகச் சரிசெய்ய அனுமதிக்கும்.

மற்றொரு எடுத்துக்காட்டு - சுயவிவரம் ஒரே மாதிரியாக இருந்தாலும், தரவுத்தளம் மிக அதிக சுமையின் கீழ் இருந்தாலும், மிகவும் ஆக்ரோஷமான AUTOVACUUM கூட சமாளிக்க முடியாது, மேலும் வீக்கம் ஏற்படும். அளவிடுதல் (செங்குத்து அல்லது கிடைமட்ட) மட்டுமே தீர்வு.

நீங்கள் AUTOVACUUM ஐ அமைத்துள்ள சூழ்நிலையில் என்ன செய்வது, ஆனால் வீக்கம் தொடர்ந்து வளரும்.

அணி வெற்றிட முழு அட்டவணைகள் மற்றும் குறியீடுகளின் உள்ளடக்கங்களை மீண்டும் உருவாக்குகிறது மற்றும் அவற்றில் தொடர்புடைய தரவை மட்டுமே விட்டுச்செல்கிறது. வீக்கத்தை அகற்ற, இது சரியாக வேலை செய்கிறது, ஆனால் அதன் செயல்பாட்டின் போது மேசையில் ஒரு பிரத்யேக பூட்டு பிடிக்கப்படுகிறது (AccessExclusiveLock), இது இந்த அட்டவணையில் வினவல்களை இயக்க அனுமதிக்காது, தேர்ந்தெடுக்கிறது. உங்கள் சேவையை அல்லது அதன் ஒரு பகுதியை சிறிது நேரம் நிறுத்தினால் (தரவுத்தளத்தின் அளவு மற்றும் உங்கள் வன்பொருளைப் பொறுத்து பத்து நிமிடங்கள் முதல் பல மணிநேரம் வரை), இந்த விருப்பம் சிறந்தது. துரதிர்ஷ்டவசமாக, திட்டமிடப்பட்ட பராமரிப்பின் போது VACUUM FULL ஐ இயக்க எங்களுக்கு நேரம் இல்லை, எனவே இந்த முறை எங்களுக்கு ஏற்றது அல்ல.

அணி க்ளஸ்டர் அட்டவணைகளின் உள்ளடக்கங்களை VACUUM FULL போலவே மீண்டும் உருவாக்குகிறது, ஆனால் ஒரு குறியீட்டைக் குறிப்பிட உங்களை அனுமதிக்கிறது, அதன்படி தரவுகள் வட்டில் வரிசைப்படுத்தப்படும் (ஆனால் எதிர்காலத்தில் புதிய வரிசைகளுக்கு ஆர்டர் உத்தரவாதம் இல்லை). சில சூழ்நிலைகளில், பல வினவல்களுக்கு இது ஒரு சிறந்த தேர்வுமுறையாகும் - குறியீட்டின் மூலம் பல பதிவுகளைப் படிக்கும். கட்டளையின் தீமை VACUUM FULL போன்றது - இது செயல்பாட்டின் போது அட்டவணையை பூட்டுகிறது.

அணி REINDEX முந்தைய இரண்டைப் போலவே, ஆனால் ஒரு குறிப்பிட்ட குறியீடு அல்லது அட்டவணையின் அனைத்து குறியீடுகளையும் மீண்டும் உருவாக்குகிறது. பூட்டுகள் சற்று பலவீனமாக உள்ளன: டேபிளில் உள்ள ஷேர்லாக் (மாற்றங்களைத் தடுக்கிறது, ஆனால் தேர்ந்தெடுக்க அனுமதிக்கிறது) மற்றும் மறுகட்டமைக்கப்பட்ட குறியீட்டில் உள்ள AccessExclusiveLock (இந்த குறியீட்டைப் பயன்படுத்தி வினவல்களைத் தடுக்கிறது). இருப்பினும், போஸ்ட்கிரெஸின் 12 வது பதிப்பில் ஒரு அளவுரு தோன்றியது ஒரே நேரத்தில், இது ஒரே நேரத்தில் சேர்த்தல், மாற்றம் அல்லது பதிவுகளை நீக்குவதைத் தடுக்காமல் குறியீட்டை மீண்டும் உருவாக்க உங்களை அனுமதிக்கிறது.

Postgres இன் முந்தைய பதிப்புகளில், REINDEX ஒரே நேரத்தில் பயன்படுத்துவது போன்ற முடிவை நீங்கள் அடையலாம் ஒரே நேரத்தில் குறியீட்டை உருவாக்கவும். கடுமையான பூட்டுதல் இல்லாமல் ஒரு குறியீட்டை உருவாக்க இது உங்களை அனுமதிக்கிறது (ShareUpdateExclusiveLock, இது இணையான வினவல்களில் தலையிடாது), பின்னர் பழைய குறியீட்டை புதியதாக மாற்றி பழைய குறியீட்டை நீக்கவும். இது உங்கள் பயன்பாட்டில் குறுக்கிடாமல் குறியீட்டு வீக்கத்தை அகற்ற அனுமதிக்கிறது. குறியீடுகளை மீண்டும் கட்டமைக்கும்போது வட்டு துணை அமைப்பில் கூடுதல் சுமை இருக்கும் என்பதைக் கருத்தில் கொள்வது அவசியம்.

எனவே, குறியீடுகளுக்கு “பறக்கும்போது” வீக்கத்தை அகற்ற வழிகள் இருந்தால், அட்டவணைகளுக்கு எதுவும் இல்லை. இங்குதான் பல்வேறு வெளிப்புற நீட்டிப்புகள் செயல்படுகின்றன: pg_repack (முன்பு pg_reorg), pgcompact, pgcompactable மற்றும் பலர். இந்த கட்டுரையில், நான் அவற்றை ஒப்பிட மாட்டேன், மேலும் pg_repack பற்றி மட்டுமே பேசுவேன், சில மாற்றங்களுக்குப் பிறகு, நாங்கள் நம்மைப் பயன்படுத்துகிறோம்.

pg_repack எப்படி வேலை செய்கிறது

எங்களிடம் முற்றிலும் சாதாரண அட்டவணை உள்ளது என்று வைத்துக்கொள்வோம் - குறியீடுகள், கட்டுப்பாடுகள் மற்றும், துரதிர்ஷ்டவசமாக, வீக்கம். pg_repack இன் முதல் படி, அது இயங்கும் போது அனைத்து மாற்றங்களைப் பற்றிய தரவையும் சேமிக்க ஒரு பதிவு அட்டவணையை உருவாக்குவதாகும். தூண்டுதல் இந்த மாற்றங்களை ஒவ்வொரு செருகல், புதுப்பித்தல் மற்றும் நீக்குதல் ஆகியவற்றைப் பிரதிபலிக்கும். பின்னர் ஒரு அட்டவணை உருவாக்கப்பட்டது, கட்டமைப்பில் அசல் ஒன்றைப் போன்றது, ஆனால் குறியீடுகள் மற்றும் கட்டுப்பாடுகள் இல்லாமல், தரவைச் செருகும் செயல்முறையை மெதுவாக்காது.

அடுத்து, pg_repack ஆனது பழைய டேபிளில் இருந்து புதிய டேபிளுக்கு தரவை மாற்றுகிறது, அனைத்து பொருத்தமற்ற வரிசைகளையும் தானாகவே வடிகட்டுகிறது, பின்னர் புதிய அட்டவணைக்கான குறியீடுகளை உருவாக்குகிறது. இந்த அனைத்து செயல்பாடுகளின் செயல்பாட்டின் போது, ​​பதிவு அட்டவணையில் மாற்றங்கள் குவிகின்றன.

அடுத்த படி மாற்றங்களை புதிய அட்டவணைக்கு மாற்ற வேண்டும். இடம்பெயர்வு பல மறு செய்கைகளில் செய்யப்படுகிறது, மேலும் பதிவு அட்டவணையில் 20 க்கும் குறைவான உள்ளீடுகள் இருக்கும் போது, ​​pg_repack ஒரு வலுவான பூட்டைப் பெறுகிறது, சமீபத்திய தரவை நகர்த்துகிறது மற்றும் Postgres கணினி அட்டவணையில் பழைய அட்டவணையை புதியதாக மாற்றுகிறது. நீங்கள் அட்டவணையுடன் வேலை செய்ய முடியாத ஒரே மற்றும் மிகக் குறுகிய நேரம் இதுதான். இதற்குப் பிறகு, பழைய அட்டவணை மற்றும் பதிவுகள் கொண்ட அட்டவணை நீக்கப்பட்டு, கோப்பு முறைமையில் இடம் விடுவிக்கப்படுகிறது. செயல்முறை முடிந்தது.

கோட்பாட்டில் எல்லாம் நன்றாக இருக்கிறது, ஆனால் நடைமுறையில் என்ன நடக்கிறது? நாங்கள் pg_repack ஐ சுமை இல்லாமல் மற்றும் சுமையின் கீழ் சோதித்தோம், மேலும் முன்கூட்டியே நிறுத்தப்பட்டால் அதன் செயல்பாட்டைச் சரிபார்த்தோம் (வேறுவிதமாகக் கூறினால், Ctrl+C ஐப் பயன்படுத்தி). அனைத்து சோதனைகளும் நேர்மறையானவை.

நாங்கள் உணவுக் கடைக்குச் சென்றோம் - பின்னர் எல்லாம் நாங்கள் எதிர்பார்த்தபடி நடக்கவில்லை.

முதல் பான்கேக் விற்பனைக்கு உள்ளது

முதல் கிளஸ்டரில், ஒரு தனித்துவமான தடையை மீறுவது குறித்த பிழையைப் பெற்றோம்:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

இந்த வரம்பு தானாக உருவாக்கப்பட்ட பெயர் index_16508 - இது pg_repack ஆல் உருவாக்கப்பட்டது. அதன் கலவையில் சேர்க்கப்பட்டுள்ள பண்புகளின் அடிப்படையில், அதனுடன் தொடர்புடைய "எங்கள்" தடையை நாங்கள் தீர்மானித்தோம். பிரச்சனை என்னவென்றால், இது முற்றிலும் சாதாரண வரம்பு அல்ல, ஆனால் ஒத்திவைக்கப்பட்ட ஒன்று (ஒத்திவைக்கப்பட்ட கட்டுப்பாடு), அதாவது. அதன் சரிபார்ப்பு sql கட்டளையை விட பின்னர் செய்யப்படுகிறது, இது எதிர்பாராத விளைவுகளுக்கு வழிவகுக்கிறது.

ஒத்திவைக்கப்பட்ட கட்டுப்பாடுகள்: அவை ஏன் தேவை மற்றும் அவை எவ்வாறு செயல்படுகின்றன

ஒத்திவைக்கப்பட்ட கட்டுப்பாடுகள் பற்றிய ஒரு சிறிய கோட்பாடு.
ஒரு எளிய உதாரணத்தைக் கருத்தில் கொள்வோம்: எங்களிடம் இரண்டு பண்புகளைக் கொண்ட கார்களின் அட்டவணை-குறிப்பு புத்தகம் உள்ளது - கோப்பகத்தில் காரின் பெயர் மற்றும் வரிசை.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

முதல் மற்றும் இரண்டாவது கார்களை மாற்ற வேண்டும் என்று வைத்துக்கொள்வோம். முதல் மதிப்பை இரண்டாவதாகவும், இரண்டாவது முதல் மதிப்பாகவும் புதுப்பிப்பதே நேரடியான தீர்வு:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

ஆனால் இந்த குறியீட்டை இயக்கும்போது, ​​அட்டவணையில் உள்ள மதிப்புகளின் வரிசை தனித்துவமானது என்பதால், கட்டுப்பாடு மீறலை எதிர்பார்க்கிறோம்:

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

நான் அதை எப்படி வித்தியாசமாக செய்ய முடியும்? விருப்பம் ஒன்று: அட்டவணையில் இல்லை என்று உத்தரவாதம் அளிக்கப்பட்ட ஆர்டருக்கு கூடுதல் மதிப்பு மாற்றத்தைச் சேர்க்கவும், எடுத்துக்காட்டாக “-1”. நிரலாக்கத்தில், இது "இரண்டு மாறிகளின் மதிப்புகளை மூன்றில் ஒரு வழியாக பரிமாற்றம்" என்று அழைக்கப்படுகிறது. இந்த முறையின் ஒரே குறைபாடு கூடுதல் புதுப்பிப்பு ஆகும்.

விருப்பம் இரண்டு: முழு எண்களுக்குப் பதிலாக ஆர்டர் மதிப்புக்கான மிதக்கும் புள்ளி தரவு வகையைப் பயன்படுத்த அட்டவணையை மறுவடிவமைக்கவும். பின்னர், 1 இலிருந்து மதிப்பைப் புதுப்பிக்கும்போது, ​​எடுத்துக்காட்டாக, 2.5 க்கு, முதல் நுழைவு தானாகவே இரண்டாவது மற்றும் மூன்றாவது இடையே "நிற்க". இந்த தீர்வு வேலை செய்கிறது, ஆனால் இரண்டு வரம்புகள் உள்ளன. முதலில், இடைமுகத்தில் மதிப்பு எங்காவது பயன்படுத்தப்பட்டால் அது உங்களுக்கு வேலை செய்யாது. இரண்டாவதாக, தரவு வகையின் துல்லியத்தைப் பொறுத்து, எல்லா பதிவுகளின் மதிப்புகளையும் மீண்டும் கணக்கிடுவதற்கு முன், உங்களிடம் குறைந்த எண்ணிக்கையிலான செருகல்கள் இருக்கும்.

விருப்பத்தேர்வு மூன்று: தடையை ஒத்திவைக்க வேண்டும்.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

எங்கள் ஆரம்ப கோரிக்கையின் தர்க்கம், உறுதியளிக்கும் நேரத்தில் அனைத்து மதிப்புகளும் தனித்துவமாக இருப்பதை உறுதி செய்வதால், அது வெற்றி பெறும்.

மேலே விவாதிக்கப்பட்ட உதாரணம், நிச்சயமாக, மிகவும் செயற்கையானது, ஆனால் அது யோசனையை வெளிப்படுத்துகிறது. எங்கள் பயன்பாட்டில், போர்டில் உள்ள பகிரப்பட்ட விட்ஜெட் பொருள்களுடன் பயனர்கள் ஒரே நேரத்தில் பணிபுரியும் போது முரண்பாடுகளைத் தீர்ப்பதற்குப் பொறுப்பான தர்க்கத்தைச் செயல்படுத்த, ஒத்திவைக்கப்பட்ட கட்டுப்பாடுகளைப் பயன்படுத்துகிறோம். இத்தகைய கட்டுப்பாடுகளைப் பயன்படுத்தி, பயன்பாட்டுக் குறியீட்டை கொஞ்சம் எளிமையாக்க முடியும்.

பொதுவாக, தடையின் வகையைப் பொறுத்து, போஸ்ட்கிரெஸ் அவற்றைச் சரிபார்ப்பதற்கு மூன்று நிலைகளைக் கொண்டுள்ளது: வரிசை, பரிவர்த்தனை மற்றும் வெளிப்பாடு நிலைகள்.

ஆதாரம்: பிச்சைக்காரர்கள்

CHECK மற்றும் NOT NULL ஆகியவை எப்போதும் வரிசை மட்டத்தில் மற்ற கட்டுப்பாடுகளுக்கு சரிபார்க்கப்படுகின்றன, அட்டவணையில் இருந்து பார்க்க முடியும், வெவ்வேறு விருப்பங்கள் உள்ளன. நீங்கள் மேலும் படிக்கலாம் இங்கே.

சுருக்கமாக சுருக்கமாக, பல சூழ்நிலைகளில் ஒத்திவைக்கப்பட்ட கட்டுப்பாடுகள் அதிக படிக்கக்கூடிய குறியீட்டையும் குறைவான கட்டளைகளையும் வழங்குகின்றன. இருப்பினும், பிழைத்திருத்த செயல்முறையை சிக்கலாக்குவதன் மூலம் இதற்கு நீங்கள் பணம் செலுத்த வேண்டும், ஏனெனில் பிழை ஏற்படும் தருணமும் அதைப் பற்றி நீங்கள் கண்டுபிடிக்கும் தருணமும் சரியான நேரத்தில் பிரிக்கப்படுகின்றன. மற்றொரு சாத்தியமான சிக்கல் என்னவென்றால், கோரிக்கை ஒத்திவைக்கப்பட்ட தடையை உள்ளடக்கியிருந்தால், திட்டமிடுபவர் எப்போதும் உகந்த திட்டத்தை உருவாக்க முடியாது.

pg_repack இன் முன்னேற்றம்

ஒத்திவைக்கப்பட்ட கட்டுப்பாடுகள் என்ன என்பதை நாங்கள் விவரித்துள்ளோம், ஆனால் அவை எங்கள் பிரச்சனையுடன் எவ்வாறு தொடர்புபடுகின்றன? நாம் முன்பு பெற்ற பிழையை நினைவில் கொள்வோம்:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

பதிவு அட்டவணையில் இருந்து புதிய அட்டவணைக்கு தரவு நகலெடுக்கப்படும் போது இது நிகழ்கிறது. இது விசித்திரமாக தெரிகிறது, ஏனெனில் ... பதிவு அட்டவணையில் உள்ள தரவு மூல அட்டவணையில் உள்ள தரவுகளுடன் இணைக்கப்பட்டுள்ளது. அசல் அட்டவணையின் கட்டுப்பாடுகளை அவர்கள் திருப்திப்படுத்தினால், புதியதில் அதே கட்டுப்பாடுகளை எப்படி மீற முடியும்?

அது மாறிவிடும், சிக்கலின் வேர் pg_repack இன் முந்தைய கட்டத்தில் உள்ளது, இது குறியீடுகளை மட்டுமே உருவாக்குகிறது, ஆனால் கட்டுப்பாடுகள் அல்ல: பழைய அட்டவணையில் ஒரு தனித்துவமான கட்டுப்பாடு இருந்தது, புதியது அதற்கு பதிலாக ஒரு தனித்துவமான குறியீட்டை உருவாக்கியது.

தடையானது இயல்பானதாகவும், ஒத்திவைக்கப்படாமலும் இருந்தால், அதற்குப் பதிலாக உருவாக்கப்பட்ட தனித்துவமான குறியீட்டு இந்த தடைக்கு சமமானதாகும் என்பதை இங்கு கவனிக்க வேண்டியது அவசியம். தனித்த குறியீட்டை உருவாக்குவதன் மூலம் Postgres இல் உள்ள தனித்துவமான கட்டுப்பாடுகள் செயல்படுத்தப்படுகின்றன. ஆனால் ஒத்திவைக்கப்பட்ட தடையின் விஷயத்தில், நடத்தை ஒரே மாதிரியாக இருக்காது, ஏனெனில் குறியீட்டை ஒத்திவைக்க முடியாது மற்றும் sql கட்டளை செயல்படுத்தப்படும் நேரத்தில் எப்போதும் சரிபார்க்கப்படும்.

எனவே, சிக்கலின் சாராம்சம் காசோலையின் "தாமதத்தில்" உள்ளது: அசல் அட்டவணையில் அது உறுதியளிக்கும் நேரத்தில் நிகழ்கிறது, மற்றும் புதிய அட்டவணையில் sql கட்டளை செயல்படுத்தப்படும் நேரத்தில். இரண்டு நிகழ்வுகளிலும் ஒரே மாதிரியான காசோலைகள் செய்யப்படுவதை நாம் உறுதிசெய்ய வேண்டும் என்பதே இதன் பொருள்: ஒன்று எப்போதும் தாமதமாகவோ அல்லது எப்போதும் உடனடியாகவோ.

அப்படியானால் எங்களிடம் என்ன யோசனைகள் இருந்தன?

ஒத்திவைக்கப்பட்டதைப் போன்ற ஒரு குறியீட்டை உருவாக்கவும்

முதல் யோசனை இரண்டு காசோலைகளையும் உடனடி பயன்முறையில் செய்ய வேண்டும். இது பல தவறான நேர்மறை கட்டுப்பாடுகளை உருவாக்கலாம், ஆனால் அவற்றில் சில இருந்தால், இது பயனர்களின் வேலையை பாதிக்கக்கூடாது, ஏனெனில் இதுபோன்ற மோதல்கள் அவர்களுக்கு ஒரு சாதாரண சூழ்நிலை. எடுத்துக்காட்டாக, இரண்டு பயனர்கள் ஒரே நேரத்தில் ஒரே விட்ஜெட்டைத் திருத்தத் தொடங்கும் போது அவை நிகழ்கின்றன, மேலும் இரண்டாவது பயனரின் கிளையன்ட் முதல் பயனரால் திருத்துவதற்காக விட்ஜெட் ஏற்கனவே தடுக்கப்பட்டுள்ளது என்ற தகவலைப் பெற நேரம் இல்லை. அத்தகைய சூழ்நிலையில், சேவையகம் இரண்டாவது பயனரை மறுக்கிறது, மேலும் அதன் கிளையன்ட் மாற்றங்களைத் திருப்பி விட்ஜெட்டைத் தடுக்கிறது. சிறிது நேரம் கழித்து, முதல் பயனர் எடிட்டிங் முடிந்ததும், இரண்டாவது விட்ஜெட் தடுக்கப்படவில்லை என்ற தகவலைப் பெறுவார், மேலும் அவர்களின் செயலை மீண்டும் செய்ய முடியும்.

காசோலைகள் எப்போதும் ஒத்திவைக்கப்படாத பயன்முறையில் இருப்பதை உறுதிசெய்ய, அசல் ஒத்திவைக்கப்பட்ட தடைக்கு ஒத்த புதிய குறியீட்டை உருவாக்கினோம்:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

சோதனை சூழலில், எதிர்பார்த்த சில பிழைகளை மட்டுமே நாங்கள் பெற்றுள்ளோம். வெற்றி! தயாரிப்பில் மீண்டும் pg_repack ஐ இயக்கினோம், ஒரு மணிநேர வேலையில் முதல் கிளஸ்டரில் 5 பிழைகள் ஏற்பட்டன. இது ஏற்றுக்கொள்ளக்கூடிய முடிவு. இருப்பினும், ஏற்கனவே இரண்டாவது கிளஸ்டரில் பிழைகளின் எண்ணிக்கை கணிசமாக அதிகரித்துள்ளது மற்றும் pg_repack ஐ நிறுத்த வேண்டியிருந்தது.

அது ஏன் நடந்தது? ஒரே நேரத்தில் ஒரே விட்ஜெட்களில் எத்தனை பயனர்கள் வேலை செய்கிறார்கள் என்பதைப் பொறுத்து பிழை ஏற்படுவதற்கான சாத்தியக்கூறுகள் இருக்கும். வெளிப்படையாக, அந்த நேரத்தில் மற்றவர்களை விட முதல் கிளஸ்டரில் சேமிக்கப்பட்ட தரவுகளுடன் மிகக் குறைவான போட்டி மாற்றங்கள் இருந்தன, அதாவது. நாங்கள் "அதிர்ஷ்டசாலி".

யோசனை பலிக்கவில்லை. அந்த நேரத்தில், நாங்கள் வேறு இரண்டு தீர்வுகளைக் கண்டோம்: ஒத்திவைக்கப்பட்ட தடைகளை அகற்ற எங்கள் பயன்பாட்டுக் குறியீட்டை மீண்டும் எழுதவும் அல்லது அவற்றுடன் வேலை செய்ய pg_repack ஐ "கற்பிக்கவும்". இரண்டாவதாகத் தேர்ந்தெடுத்தோம்.

புதிய அட்டவணையில் உள்ள குறியீடுகளை அசல் அட்டவணையில் இருந்து ஒத்திவைக்கப்பட்ட கட்டுப்பாடுகளுடன் மாற்றவும்

திருத்தத்தின் நோக்கம் வெளிப்படையானது - அசல் அட்டவணையில் ஒத்திவைக்கப்பட்ட தடை இருந்தால், புதியதற்கு நீங்கள் அத்தகைய தடையை உருவாக்க வேண்டும், ஒரு குறியீட்டை அல்ல.

எங்கள் மாற்றங்களைச் சோதிக்க, நாங்கள் ஒரு எளிய தேர்வை எழுதினோம்:

• ஒத்திவைக்கப்பட்ட கட்டுப்பாடு மற்றும் ஒரு பதிவுடன் அட்டவணை;
• ஏற்கனவே உள்ள பதிவுடன் முரண்படும் சுழற்சியில் தரவைச் செருகவும்;
• புதுப்பிப்பைச் செய்யுங்கள் - தரவு இனி முரண்படாது;
• மாற்றங்களைச் செய்யுங்கள்.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

pg_repack இன் அசல் பதிப்பு எப்போதும் முதல் செருகலில் செயலிழந்தது, மாற்றியமைக்கப்பட்ட பதிப்பு பிழைகள் இல்லாமல் வேலை செய்தது. நன்று.

நாங்கள் உற்பத்திக்குச் சென்று, பதிவு அட்டவணையிலிருந்து தரவை புதியதாக நகலெடுக்கும் அதே கட்டத்தில் மீண்டும் ஒரு பிழையைப் பெறுகிறோம்:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

உன்னதமான சூழ்நிலை: எல்லாம் சோதனைச் சூழல்களில் வேலை செய்கிறது, ஆனால் உற்பத்தியில் இல்லையா?!

APPLY_COUNT மற்றும் இரண்டு தொகுதிகளின் சந்திப்பு

குறியீட்டை வரிக்கு வரியாக பகுப்பாய்வு செய்யத் தொடங்கினோம், மேலும் ஒரு முக்கியமான புள்ளியைக் கண்டறிந்தோம்: பதிவு அட்டவணையில் இருந்து புதிய ஒன்றுக்கு தரவு மாற்றப்படுகிறது, APPLY_COUNT மாறிலி தொகுப்பின் அளவைக் குறிக்கிறது:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

பிரச்சனை என்னவென்றால், அசல் பரிவர்த்தனையின் தரவு, பல செயல்பாடுகள் தடையை மீறக்கூடியவை, மாற்றப்படும்போது, ​​​​இரண்டு தொகுதிகளின் சந்திப்பில் முடிவடையும் - கட்டளைகளில் பாதி முதல் தொகுப்பிலும், மற்ற பாதி இரண்டாவது. இங்கே, உங்கள் அதிர்ஷ்டத்தைப் பொறுத்து: அணிகள் முதல் தொகுப்பில் எதையும் மீறவில்லை என்றால், எல்லாம் நன்றாக இருக்கிறது, ஆனால் அவர்கள் செய்தால், பிழை ஏற்படுகிறது.

APPLY_COUNT என்பது 1000 பதிவுகளுக்குச் சமம், இது எங்களின் சோதனைகள் ஏன் வெற்றியடைந்தன என்பதை விளக்குகிறது - அவை “தொகுதி சந்திப்பு” விஷயத்தை உள்ளடக்கவில்லை. நாங்கள் இரண்டு கட்டளைகளைப் பயன்படுத்தினோம் - செருகவும் மற்றும் புதுப்பிக்கவும், எனவே இரண்டு கட்டளைகளின் 500 பரிவர்த்தனைகள் எப்போதும் ஒரு தொகுப்பில் வைக்கப்படுகின்றன, மேலும் எங்களுக்கு எந்த பிரச்சனையும் ஏற்படவில்லை. இரண்டாவது புதுப்பிப்பைச் சேர்த்த பிறகு, எங்கள் திருத்தம் வேலை செய்வதை நிறுத்தியது:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

எனவே, ஒரு பரிவர்த்தனையில் மாற்றப்பட்ட அசல் அட்டவணையில் இருந்து தரவு, ஒரு பரிவர்த்தனைக்குள் புதிய அட்டவணையில் முடிவடைவதை உறுதி செய்வதே அடுத்த பணி.

தொகுப்பிலிருந்து மறுப்பு

மீண்டும் எங்களிடம் இரண்டு தீர்வுகள் இருந்தன. முதலில்: தொகுதிகளாகப் பிரிப்பதை முற்றிலும் கைவிட்டு, ஒரு பரிவர்த்தனையில் தரவை மாற்றுவோம். இந்த தீர்வின் நன்மை அதன் எளிமை - தேவையான குறியீடு மாற்றங்கள் குறைவாக இருந்தன (மூலம், பழைய பதிப்புகளில் pg_reorg சரியாக வேலை செய்தது). ஆனால் ஒரு சிக்கல் உள்ளது - நாங்கள் ஒரு நீண்ட கால பரிவர்த்தனையை உருவாக்குகிறோம், இது முன்பு கூறியது போல், ஒரு புதிய வீக்கத்தின் தோற்றத்திற்கு அச்சுறுத்தலாகும்.

இரண்டாவது தீர்வு மிகவும் சிக்கலானது, ஆனால் மிகவும் சரியானது: அட்டவணையில் தரவைச் சேர்த்த பரிவர்த்தனையின் அடையாளங்காட்டியுடன் பதிவு அட்டவணையில் ஒரு நெடுவரிசையை உருவாக்கவும். பின்னர், நாம் தரவை நகலெடுக்கும்போது, ​​இந்தப் பண்புக்கூறின் மூலம் அதைக் குழுவாக்கி, தொடர்புடைய மாற்றங்கள் ஒன்றாக மாற்றப்படுவதை உறுதிசெய்யலாம். பல பரிவர்த்தனைகளிலிருந்து (அல்லது ஒரு பெரியது) தொகுதி உருவாக்கப்படும் மற்றும் இந்த பரிவர்த்தனைகளில் எவ்வளவு தரவு மாற்றப்பட்டது என்பதைப் பொறுத்து அதன் அளவு மாறுபடும். வெவ்வேறு பரிவர்த்தனைகளின் தரவுகள் ஒரு சீரற்ற வரிசையில் பதிவு அட்டவணையில் நுழைவதால், முன்பு இருந்ததைப் போல, அதை வரிசையாகப் படிக்க முடியாது என்பதை கவனத்தில் கொள்ள வேண்டும். tx_id மூலம் வடிகட்டுதல் மூலம் ஒவ்வொரு கோரிக்கைக்கும் seqscan மிகவும் விலை உயர்ந்தது, ஒரு குறியீட்டு தேவை, ஆனால் அதை மேம்படுத்தும் மேல்நிலை காரணமாக இது முறையை மெதுவாக்கும். பொதுவாக, எப்போதும் போல, நீங்கள் ஏதாவது தியாகம் செய்ய வேண்டும்.

எனவே, இது எளிமையானது என்பதால், முதல் விருப்பத்துடன் தொடங்க முடிவு செய்தோம். முதலில், ஒரு நீண்ட பரிவர்த்தனை உண்மையான பிரச்சனையாக இருக்குமா என்பதைப் புரிந்துகொள்வது அவசியம். ஒரு நீண்ட பரிவர்த்தனையில் பழைய டேபிளில் இருந்து புதிய டேபிளுக்கு முக்கிய தரவு பரிமாற்றம் நிகழும் என்பதால், கேள்வி "இந்தப் பரிவர்த்தனையை எவ்வளவு அதிகரிப்போம்?" முதல் பரிவர்த்தனையின் காலம் முக்கியமாக அட்டவணையின் அளவைப் பொறுத்தது. தரவு பரிமாற்றத்தின் போது அட்டவணையில் எத்தனை மாற்றங்கள் குவிகின்றன என்பதைப் பொறுத்து புதிய ஒன்றின் கால அளவு தங்கியுள்ளது, அதாவது. சுமையின் தீவிரத்தில். pg_repack ரன் குறைந்த சேவை சுமையின் போது நிகழ்ந்தது, மேலும் அட்டவணையின் அசல் அளவோடு ஒப்பிடும்போது மாற்றங்களின் அளவு அளவு குறைவாக இருந்தது. புதிய பரிவர்த்தனையின் நேரத்தை நாங்கள் புறக்கணிக்கலாம் என்று முடிவு செய்தோம் (ஒப்பிடுகையில், சராசரியாக இது 1 மணிநேரம் 2-3 நிமிடங்கள் ஆகும்).

சோதனைகள் நேர்மறையானவை. தயாரிப்பிலும் தொடங்கவும். தெளிவுக்காக, இயங்கிய பின் தரவுத்தளங்களில் ஒன்றின் அளவு கொண்ட படம் இங்கே:

இந்தத் தீர்வில் நாங்கள் முழுமையாக திருப்தி அடைந்ததால், இரண்டாவதாகச் செயல்படுத்த நாங்கள் முயற்சிக்கவில்லை, ஆனால் அதை நீட்டிப்பு உருவாக்குநர்களுடன் கலந்துரையாடுவதற்கான சாத்தியத்தை நாங்கள் பரிசீலித்து வருகிறோம். எங்கள் தற்போதைய திருத்தம், துரதிர்ஷ்டவசமாக, வெளியீட்டிற்கு இன்னும் தயாராக இல்லை, ஏனெனில் நாங்கள் தனித்துவமான ஒத்திவைக்கப்பட்ட கட்டுப்பாடுகளுடன் மட்டுமே சிக்கலைத் தீர்த்தோம், மேலும் ஒரு முழு அளவிலான இணைப்புக்கு மற்ற வகைகளுக்கு ஆதரவை வழங்குவது அவசியம். எதிர்காலத்தில் இதைச் செய்ய முடியும் என்று நம்புகிறோம்.

ஒருவேளை உங்களுக்கு ஒரு கேள்வி இருக்கலாம், நாங்கள் ஏன் இந்த கதையில் pg_repack இன் மாற்றத்துடன் ஈடுபட்டோம், எடுத்துக்காட்டாக, அதன் ஒப்புமைகளைப் பயன்படுத்தவில்லை? சில சமயங்களில் நாமும் இதைப் பற்றி யோசித்தோம், ஆனால் தாமதமான கட்டுப்பாடுகள் இல்லாமல் அட்டவணையில் இதைப் பயன்படுத்திய நேர்மறையான அனுபவம், சிக்கலின் சாராம்சத்தைப் புரிந்துகொண்டு அதைச் சரிசெய்ய எங்களைத் தூண்டியது. கூடுதலாக, பிற தீர்வுகளைப் பயன்படுத்துவதற்கும் சோதனைகளை நடத்துவதற்கு நேரம் தேவைப்படுகிறது, எனவே முதலில் அதில் உள்ள சிக்கலை சரிசெய்ய முயற்சிப்போம் என்று முடிவு செய்தோம், மேலும் இதை ஒரு நியாயமான நேரத்தில் செய்ய முடியாது என்பதை உணர்ந்தால், நாங்கள் ஒப்புமைகளைப் பார்க்கத் தொடங்குவோம். .

கண்டுபிடிப்புகள்

எங்கள் சொந்த அனுபவத்தின் அடிப்படையில் நாங்கள் என்ன பரிந்துரைக்க முடியும்:

1. உங்கள் வீக்கத்தைக் கண்காணிக்கவும். கண்காணிப்பு தரவின் அடிப்படையில், ஆட்டோவாகும் எவ்வளவு சிறப்பாக கட்டமைக்கப்பட்டுள்ளது என்பதை நீங்கள் புரிந்து கொள்ளலாம்.
2. வயிற்றை ஏற்றுக்கொள்ளக்கூடிய அளவில் வைத்திருக்க AUTOVACUUM ஐ சரிசெய்யவும்.
3. வீக்கம் இன்னும் அதிகமாக இருந்தால், பெட்டிக்கு வெளியே உள்ள கருவிகளைப் பயன்படுத்தி அதை நீங்கள் சமாளிக்க முடியாவிட்டால், வெளிப்புற நீட்டிப்புகளைப் பயன்படுத்த பயப்பட வேண்டாம். முக்கிய விஷயம் என்னவென்றால், எல்லாவற்றையும் நன்றாக சோதிக்க வேண்டும்.
4. உங்கள் தேவைகளுக்கு ஏற்ப வெளிப்புற தீர்வுகளை மாற்ற பயப்பட வேண்டாம் - சில நேரங்களில் இது உங்கள் சொந்த குறியீட்டை மாற்றுவதை விட மிகவும் பயனுள்ளதாகவும் எளிதாகவும் இருக்கும்.

ஆதாரம்: www.habr.com