Postgres मेमोरी सेटिंग्स का अधिकतम लाभ कैसे उठाएँ

• Postgres डेटाबेस बड़ी मात्रा में RAM का उपयोग करता है। result set बनाते समय index matching, table से संबंधित rows लाना, tuple merge/filter/aggregate/sort जैसे चरण होते हैं, और इन सभी चरणों में मेमोरी पर निर्भरता होती है
• Postgres की मेमोरी उपयोगिता को optimize करने के लिए उपलब्ध RAM का अधिकतम उपयोग करना ज़रूरी है, साथ ही अलग-अलग प्रकार के memory allocation को कुशलता से संतुलित करना और OS को अत्यधिक मेमोरी उपयोग के कारण process terminate करने से रोकना भी आवश्यक है

Sharing is Caring

• Postgres से जुड़ी RAM का सबसे बड़ा हिस्सा shared_buffers कहलाता है, जो सबसे अधिक बार एक्सेस की जाने वाली tables और indexes की rows को दर्शाता है। इसे उपयोग आवृत्ति के आधार पर score देने वाली heuristic support करती है
  • shared_buffers Postgres start होने पर allocate होने वाला fixed value है, इसलिए यह अप्रत्याशित मेमोरी समस्याओं में योगदान नहीं देता
  • default value 128MB है
  • लेकिन OS इसे पहले से allocate की गई मेमोरी के रूप में न माने, ऐसा हो सकता है, इसलिए instance की कुल RAM के बराबर बहुत ऊँचा value देना जोखिम भरा हो सकता है
• production system में shared_buffers के लिए सबसे सामान्य recommendation उपलब्ध RAM का 25% है। यह hardware के अनुसार tune किया गया एक अच्छा शुरुआती बिंदु है
  • benchmark नतीजे दिखाते हैं कि 25% वाला सुझाव आम तौर पर पर्याप्त होता है, लेकिन यह डेटाबेस के उपयोग के तरीके पर निर्भर कर सकता है
  • उदाहरण के लिए reporting system में complex ad-hoc query के कारण cache hit rate कम हो सकता है, और ऐसे में थोड़ा कम setting पर थोड़ा बेहतर performance मिल सकता है
• pg_buffercache extension का उपयोग करके यह ठीक-ठीक देखा जा सकता है कि shared buffer में कौन-सी tables और indexes allocated हैं। buffer के used pages की संख्या देखकर shared_buffers value को adjust किया जा सकता है
  • अगर buffer cache 100% उपयोग नहीं हो रहा है, तो setting बहुत अधिक हो सकती है, इसलिए instance size या shared_buffers value कम की जा सकती है
  • अगर उपयोग 100% है और कई tables का केवल कुछ हिस्सा ही cache में है, तो diminishing returns आने तक क्रमशः अधिक value देना फायदेमंद हो सकता है
• Postgres 16 का नया pg_stat_io view भी shared_buffers tuning में मदद कर सकता है। इससे hit rate और client backend read/write देखा जा सकता है
  • अगर read-to-write ratio 1 के करीब है, तो यह संकेत हो सकता है कि Postgres एक ही pages को shared_buffers में लगातार घुमा रहा है। ऐसी thrashing कम करने के लिए shared_buffers बढ़ाना उपयोगी हो सकता है
• अगर यह system RAM के 50% से ऊपर जाने लगे, तो instance size बढ़ाने पर विचार करना चाहिए, क्योंकि Postgres को user session और उनसे जुड़ी queries के लिए भी मेमोरी चाहिए

Working Memory

• Postgres जिस दूसरी बड़ी मेमोरी का उपयोग वास्तविक काम करने के लिए करता है, वह working memory है, जिसे work_mem parameter नियंत्रित करता है
  • इसकी default value 4MB है, और query execution speed बढ़ाने के लिए user अक्सर सबसे पहले इसी value को बदलते हैं
  • लेकिन यदि OS "out of memory" संदेश के कारण Postgres को बंद कर रहा है, तो work_mem बढ़ाने का मन हो सकता है, जबकि इससे समस्या और खराब होगी। इससे Postgres द्वारा उपयोग की जाने वाली RAM और बढ़ेगी और ऐसे shutdown की संभावना अधिक होगी
• बहुत से लोग "working memory" को query के दौरान होने वाले सभी कार्यों के लिए एक single allocation मानते हैं, लेकिन वास्तव में यह उससे अधिक हो सकता है
  • हर stage (node) को work_mem का अलग instance मिलता है। उदाहरण के लिए यदि work_mem की default value 4MB है, तो 4 nodes वाली query अधिकतम 16MB RAM उपयोग कर सकती है
  • यदि busy server पर ऐसी 100 queries एक साथ चलें, तो केवल result calculate करने में ही 1.6GB तक RAM लग सकती है। और अधिक complex queries में execution के लिए आवश्यक nodes की संख्या के अनुसार और अधिक RAM लग सकती है
• EXPLAIN command से query execution plan देखने पर Postgres query को कैसे चलाता है और output बनाने के लिए किन nodes की ज़रूरत है, यह दिखता है
  • pg_stat_statements extension के साथ उपयोग करने पर सबसे सक्रिय queries अलग की जा सकती हैं और work_mem से होने वाले कुल memory usage का अनुमान लगाया जा सकता है
• अगर work_mem बहुत कम set है, तो जो rows या intermediate results RAM में fit नहीं होते वे disk पर spill हो जाते हैं, जिससे performance बहुत धीमी हो जाती है
  • pg_stat_database view देखकर disk पर लिखी गई सभी temporary files का cumulative size और count देखा जा सकता है, और यदि average size उचित लगे तो work_mem को उतना बढ़ाया जा सकता है
• प्रति session उपलब्ध RAM का मोटा अनुमान लगाने के लिए यह formula उपयोग करें: (कुल RAM का 80% - shared_buffers) / (max_connections)
  • उदाहरण के लिए 16GB RAM, 4GB shared buffer, और 100 max connections होने पर प्रति session लगभग 88MB उपलब्ध होंगे
  • इस value को query plan nodes की औसत संख्या से divide करके work_mem के लिए एक अच्छा setting निकाला जा सकता है

Ongoing Maintenance

• Postgres RAM usage का अंतिम tunable हिस्सा maintenance से संबंधित है, जो working memory जैसा ही है और maintenance_work_mem नाम के parameter से नियंत्रित होता है
  • इसकी default value 64MB है, और यह VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY जैसे कार्यों के लिए समर्पित RAM की मात्रा तय करता है
• चूँकि यह प्रति session एक task तक सीमित होता है और एक साथ बहुत अधिक maintenance tasks चलने की संभावना कम होती है, इसलिए अधिक value देना आमतौर पर सुरक्षित माना जाता है
  • ये maintenance tasks काफी मेमोरी उपयोग कर सकते हैं, और अगर पूरी तरह RAM में चल पाएँ तो बहुत तेज़ पूरे हो सकते हैं, इसलिए इसे 1GB या 2GB तक set करना बहुत सामान्य है
• एक महत्वपूर्ण सावधानी Postgres की autovacuum प्रक्रिया है, जो dead tuples को बाद में reuse के लिए mark करती है
  • autovacuum background jobs को autovacuum_max_workers सीमा तक शुरू करता है, और हर job maintenance_work_mem का पूरा instance उपयोग कर सकती है
  • जिन servers में काफी spare RAM है, वहाँ 1GB maintenance working memory आम तौर पर सुरक्षित है, लेकिन RAM कम हो तो अधिक सावधानी चाहिए
  • autovacuum workers को सीमित करने के लिए autovacuum_work_mem parameter अलग से उपलब्ध है
  • Postgres autovacuum worker 1GB से अधिक उपयोग नहीं कर सकते, इसलिए autovacuum_work_mem को इससे ऊपर configure करने का कोई लाभ नहीं है

Session Pooling

• मेमोरी खपत कम करने का सबसे आसान तरीका संभावित allocation पर logical limit लगाना है
  • Postgres अभी process-based engine है, इसलिए हर user session को thread नहीं बल्कि physical process मिलता है
  • इसलिए हर connection के साथ कुछ RAM overhead जुड़ता है और context switching भी बढ़ती है
  • इसी कारण सामान्य recommendation यह है कि max_connections को उपलब्ध CPU threads की संख्या के 4 गुना से अधिक न रखा जाए। इससे active sessions को CPUs के बीच switch करने में लगने वाला समय कम होता है और कुल RAM उपयोग भी स्वाभाविक रूप से सीमित रहता है
• यदि सभी sessions query चला रहे हों और हर node work_mem के एक allocation का प्रतिनिधित्व करे, तो theoretical maximum working memory usage connections * nodes * work_mem होगा
  • query complexity कम करना हमेशा संभव नहीं होता, लेकिन connections की संख्या आम तौर पर कम की जा सकती है
  • अगर application हमेशा कुछ विस्तृत संख्या में sessions खोलकर रखती है या कई अलग-अलग microservices Postgres पर निर्भर हैं, तो यह उतना आसान नहीं हो सकता
• work_mem * max_connections * 5 formula यह मानकर एक मोटा अनुमान देता है कि यदि सभी connections active हों तो basic queries संभालने के लिए Postgres instance user sessions को अधिकतम कितनी RAM allocate कर सकता है
  • यदि server में इसके लिए पर्याप्त RAM नहीं है, तो किसी एक factor को कम करना या RAM बढ़ाना चाहिए
  • प्रति query औसतन 5 nodes का अनुमान आपके application पर लागू न भी हो सकता है, इसलिए query execution plans को बेहतर समझने के बाद इसे आवश्यकतानुसार adjust करना चाहिए
• अगला कदम PgBouncer जैसे connection pooler को जोड़ना है
  • यह client connections को database से अलग करता है और महँगे Postgres sessions को clients के बीच reuse करता है
  • सही configuration के साथ सैकड़ों clients बिना application को प्रभावित किए दर्जनों Postgres connections साझा कर सकते हैं
  • देखा गया है कि PgBouncer इस तरीके से 1000 से अधिक connections को 40-50 तक multiplex कर सकता है, जिससे process overhead के कारण होने वाली कुल memory consumption काफी कम हो जाती है

Reducing Bloat

• memory usage को track करने के सबसे कठिन पहलुओं में से एक शायद table bloat है
  • Postgres अपने storage system में data को दर्शाने के लिए multi-version concurrency control (MVCC) का उपयोग करता है
  • यानी जब भी table row modify होती है, Postgres table में कहीं row की एक और copy बनाता है और उसे नए version number से mark करता है
  • Postgres की VACUUM प्रक्रिया पुराने row versions को "unused" space के रूप में mark करती है ताकि वहाँ नए row versions रखे जा सकें
• Postgres में एक autovacuum background process होता है जो लगातार ऐसे reusable allocations खोजता रहता है ताकि tables अनंत रूप से बढ़ती न जाएँ
  • लेकिन कभी-कभी, खासकर बड़े systems में, इसकी default configuration पर्याप्त नहीं होती और यह maintenance पीछे रह सकती है
  • परिणामस्वरूप tables में live rows से अधिक dead rows जमा हो सकती हैं और tables पुराने data से "bloated" हो सकती हैं
• यदि table बहुत अधिक bloated हो, तो shared buffers पर उसके प्रभाव पर विचार करना चाहिए
  • यदि हर page में केवल एक live row और कई dead rows हों, और किसी query को 10 rows चाहिए हों, तो 10 pages shared buffer में लाने पड़ेंगे, जिससे बहुत-सी मेमोरी व्यर्थ जाएगी जो अन्य उपयोग के काम आ सकती थी
  • यदि इन rows की demand अधिक है, तो उनका usage उन्हें shared buffer में बनाए रखेगा, जिससे cache efficiency काफी कम हो जाएगी
• इंटरनेट पर table bloat का अनुमान लगाने वाली कई queries मिल जाती हैं, लेकिन table pages वास्तव में कैसे दिखते हैं इसे ठोस रूप से देखने का एकमात्र तरीका pgstattuple extension का उपयोग है
• यदि free_percent 30% से अधिक है, तो autovacuum को अधिक आक्रामक रूप से tune करना पड़ सकता है। यदि यह 30% से काफी अधिक है, तो bloat को पूरी तरह हटाना बेहतर हो सकता है
  • इसके लिए वर्तमान में एकमात्र supported तरीका VACUUM FULL command है, जो मूलतः table को rebuild करता है। यह सभी live rows को नई location पर फिर से रखता है और पहले की bloated copy को हटाता है
  • यह प्रक्रिया अपने दौरान exclusive access lock लेती है, इसलिए लगभग हर स्थिति में कुछ downtime की आवश्यकता होती है
• इसका एक विकल्प Tembo द्वारा supported pg_repack extension है
  • यह command-line tool exclusive lock के बिना पूरी तरह online तरीके से bloat हटाने के लिए table को reorganize कर सकता है
  • यह tool Postgres core के बाहर मौजूद है और table व index storage को modify करता है, इसलिए इसे अक्सर advanced usage माना जाता है
  • उपयोग से पहले non-production environment में पर्याप्त testing की सलाह दी जाती है
• आप column order को reorganize करके प्रति page rows की संख्या अधिकतम करने वाला column tetris भी कर सकते हैं
  • यह शायद optimization का काफ़ी चरम स्तर है, लेकिन जिन environments में इस तरह table rebuild करने की स्वतंत्रता हो, वहाँ यह एक व्यावहारिक strategy हो सकती है

The Balancing Act

• इन सभी parameters और resources को सही तरह configure करना कला भी है और विज्ञान भी
  • हमने देखा कि shared buffers के वास्तविक उपयोग को कैसे मापा जाए और कैसे पता किया जाए कि working memory बहुत कम है या नहीं
  • लेकिन जैसा अक्सर होता है, यदि उपलब्ध hardware या budget सीमित हो तो क्या किया जाए? यहीं "कला" की ज़रूरत पड़ती है
• कम मेमोरी की स्थिति में, अधिक work_mem के लिए जगह बनाने हेतु shared_buffers थोड़ा कम करना पड़ सकता है। या दोनों कम करने पड़ सकते हैं
  • यदि application को बहुत अधिक sessions चाहिए, तो एक साथ चलने वाले sessions द्वारा व्यापक RAM allocation जमा होने से रोकने के लिए work_mem कम करना या connection pool जोड़ना अधिक समझदारी हो सकती है
  • यदि आपने पहले यह मानकर maintenance_work_mem बढ़ा दिया था कि हर चीज़ के लिए पर्याप्त RAM है, तो उसे कम करना अधिक उचित हो सकता है। विचार करने के लिए बहुत कुछ है
• low-memory instances में ऊपर दी गई recommendations भी पर्याप्त न हों, ऐसा हो सकता है। ऐसी स्थिति में memory usage को अधिकतम करने और resource exhaustion से बचने के लिए निम्न कार्य क्रम अपनाना बेहतर है:
  1. connection pooler जोड़ें और max_connections कम करें। अधिकतम resource consumption घटाने का यह सबसे तेज़ और आसान तरीका है
  2. pg_stat_statements में रिपोर्ट हुई सबसे अधिक बार चलने वाली queries पर EXPLAIN चलाकर औसत नहीं, बल्कि query के अधिकतम nodes की संख्या पता करें। फिर work_mem को (कुल RAM का 80% - shared_buffers) / (max_connections * अधिकतम plan node count) से कम या बराबर रखें
  3. maintenance_work_mem और autovacuum_work_mem को वापस default 64MB पर ले आएँ। यदि maintenance tasks बहुत धीमे हों और अधिक RAM उपलब्ध हो सके, तो 8MB increments में बढ़ाने पर विचार करें
  4. pg_buffercache extension का उपयोग करके shared_buffers में रखी tables की मात्रा देखें। हर table और index की सावधानी से जाँच करें और देखें कि data archiving, query बदलावों से information usage कम करना आदि करके इसे घटाने का कोई तरीका है या नहीं। इसमें active bloated tables द्वारा उपयोग किए जा रहे pages को compact करने के लिए VACUUM FULL या pg_repack शामिल हो सकते हैं
  5. यदि pg_buffercache दिखाता है कि shared_buffers पूरी तरह भर चुका है और active pages हटाए बिना इसे और कम नहीं किया जा सकता, तो usagecount column का उपयोग करके सबसे सक्रिय pages को प्राथमिकता दें। इस column की value 1-5 होती है, इसलिए 3-5 बार उपयोग हुए pages पर ध्यान देकर performance पर बड़ा असर डाले बिना shared_buffers कम किया जा सकता है
  6. अंत में अधिक शक्तिशाली hardware provision करें। यदि database को मौजूदा workload के लिए वास्तव में अधिक RAM चाहिए और ऊपर दिए parameters कम करने से system performance पर बहुत अधिक नकारात्मक असर पड़ता है, तो आमतौर पर upgrade करना ही अधिक व्यावहारिक होता है