Postgres 16 क्वेरी प्लानर की नई खूबियां

 (citusdata.com)
1 पॉइंट द्वारा GN⁺ 2024-02-11 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • PostgreSQL 16 ने क्वेरी प्लानर/optimizer में 10 सुधार जोड़े हैं, जिससे DISTINCT, aggregate, join, window function और partition table queries के लिए execution plan चुनने के विकल्प बढ़ गए हैं
  • SELECT DISTINCT, ORDER BY/DISTINCT aggregate, और Merge Join के बाद की processing में partially sorted input का अधिक सक्रिय उपयोग किया जा सकता है, जिससे full sort की तुलना में कम memory में result बनाया जा सकता है
  • UNION ALL के अंदर Memoize, Right Anti Join, और FULL/RIGHT joins के लिए parallel hash join support का फोकस repeated lookups और बड़े hash table बनाने की लागत कम करना है
  • window functions में अनावश्यक RANGE processing और अंत तक चलने वाले WindowAgg को कम किया गया है, और कुछ functions में condition के अनुसार early stop भी संभव हो गया है
  • सभी सुधार default रूप से enabled हैं, इसलिए PostgreSQL 16 upgrade से पहले और बाद में वास्तविक workload के EXPLAIN और execution time की तुलना करना उपयोगी हो सकता है

PostgreSQL 16 प्लानर सुधारों का दायरा

  • PostgreSQL 16 ने query planner में कई सुधार पेश किए हैं, जिससे कई SQL queries पुराने PostgreSQL versions की तुलना में तेज़ चल सकती हैं
  • PG16 release notes में शामिल planner improvements को यहां अधिक विस्तार से समझाया गया है, साथ में PG15 और PG16 के EXPLAIN output की तुलना और reproducible test examples भी दिए गए हैं
  • यहां planner वही component है जिसे दूसरे relational databases में आमतौर पर optimizer कहा जाता है

Sort और DISTINCT optimization

  • SELECT DISTINCT में Incremental Sort का उपयोग

    • Incremental Sort पहली बार PostgreSQL 13 में जोड़ा गया था, और जब result पहले से leading columns के आधार पर sorted हो, तब बाकी columns को ही sort करके cost कम करता है
    • PostgreSQL 16 planner अब SELECT DISTINCT queries में भी Incremental Sort पर विचार करता है
    • उदाहरण के लिए, यदि a column पर btree index है और a, b क्रम चाहिए, तो index से a के आधार पर sorted result लेने के बाद a का value बदलने पर केवल b को sort किया जा सकता है
    • PostgreSQL के quicksort में एक बड़े group को sort करने की तुलना में कई छोटे groups को sort करना अधिक efficient हो सकता है
    • उदाहरण query में PG15 ने HashAggregate और sequential scan का उपयोग किया, जबकि PG16 ने distinct_test_a_idx index और Incremental Sort चुना
    • PG16 output में Presorted Key: a का मतलब है कि a के आधार पर पहले से sorted input का उपयोग किया गया
    • PG15 की hash method ने लगभग 30MB disk spill किया, जबकि PG16 के Incremental Sort की maximum memory 26KB थी
    • execution time PG15 के 414.226ms से घटकर PG16 में 263.167ms हो गया

  • ORDER BY या DISTINCT वाले aggregates का optimization

    • PostgreSQL 15 और उससे पहले, ORDER BY या DISTINCT clause वाले aggregate functions हमेशा Aggregate node के अंदर sort करते थे
    • PostgreSQL 16 planner ऐसा execution plan बना सकता है जो Aggregate node को सही order में rows दे, और executor input पहले से sorted हो तो internal sort को छोड़ देता है
    • COUNT(DISTINCT b) उदाहरण में PG15 और PG16 दोनों ने GroupAggregate और Index Only Scan का उपयोग किया, लेकिन PG15 output में temp read=4540 written=4560 दिखता है
    • यह temporary file I/O इस बात का परिणाम है कि PG15 का implicit sort disk पर spill हो गया
    • PG16 output में ऐसा temp I/O नहीं है, और execution time PG15 के 302.693ms से घटकर PG16 में 115.534ms हो गया, यानी 2 गुना से भी अधिक तेज़

Repeated lookups और join plan सुधार

  • UNION ALL के अंदर Memoize लागू करना

    • Memoize plan node पहली बार PostgreSQL 14 में आया था, और parameterized Nested Loop तथा inner input के बीच cache layer की तरह काम करता है
    • PostgreSQL 16 planner अब उन मामलों में भी Memoize पर विचार करता है जहां parameterized Nested Loop के inner side में UNION ALL query हो
    • उदाहरण में PG15 ने Append को 10 लाख बार चलाया, जबकि PG16 ने Append के ऊपर Memoize रखा
    • PG16 के Memoize ने Hits: 999990, Misses: 10, Memory Usage: 2kB दर्ज किया
    • Append की execution count PG15 की 10 लाख बार से घटकर PG16 में 10 बार रह गई
    • execution time PG15 के 1926.151ms से घटकर PG16 में 282.120ms हो गया, यानी लगभग 6 गुना तेज़

  • Right Anti Join support

    • INNER JOIN के Hash Join में आमतौर पर छोटे table पर hash table बनाना बेहतर होता है
    • छोटा hash table बनाना कम काम मांगता है, CPU cache के लिए अधिक friendly होता है, और main memory से data का इंतज़ार करते समय CPU stall की संभावना भी कम करता है
    • PostgreSQL 16 से पहले Anti Join में NOT EXISTS में संदर्भित table हमेशा join के inner side पर रखा जाता था, इसलिए कभी-कभी बड़े table पर hash table बनाना पड़ता था
    • PostgreSQL 16 Right Anti Join को support करता है, जिससे दोनों tables में से छोटे वाले को hash किया जा सकता है
    • उदाहरण में PG15 ने 10 लाख rows वाले large table को hash किया और memory usage 6446KB था, जबकि PG16 ने 100 rows वाले small table को hash किया और केवल 12KB इस्तेमाल किया
    • execution time PG15 के 139.023ms से घटकर PG16 में 77.076ms हो गया, यानी लगभग आधा

  • FULL/RIGHT joins के लिए parallel hash join

    • PostgreSQL 11 ने Parallel Hash Join पेश किया था, जिसमें कई parallel workers एक single hash table बनाने में भाग लेते हैं
    • PostgreSQL 16 का Parallel Hash Join अब FULL और RIGHT join types को support करता है
    • अब FULL OUTER JOIN और Right Join plans भी parallel execution कर सकते हैं
    • FULL JOIN उदाहरण में PG15 ने single Hash Full Join का उपयोग किया, जबकि PG16 ने Parallel Hash Full Join और Gather का उपयोग किया
    • PG16 output में Workers Planned: 1, Workers Launched: 1 दिखता है
    • execution time PG15 के 220.677ms से घटकर PG16 में 129.769ms हो गया, यानी स्पष्ट रूप से तेज़

Window function optimization

  • अनावश्यक RANGE processing छोड़ना

    • row_number(), rank(), dense_rank(), percent_rank(), cume_dist(), ntile() जैसे window functions में, अगर window clause में ROWS option नहीं है, तो PostgreSQL default RANGE option का उपयोग करता है
    • RANGE option same sort value वाली peer rows खोजने के लिए पिछली rows को देखता है, और ORDER BY के हिसाब से same value बहुत होने पर इसकी cost बढ़ सकती है
    • ऊपर के functions का behavior ROWS या RANGE देने पर नहीं बदलता, लेकिन PostgreSQL 16 से पहले executor इस फर्क को समझ नहीं पाता था और हर case में peer row checks करने पड़ते थे
    • PostgreSQL 16 planner अब जानता है कि कौन-से window functions ROWS/RANGE options से प्रभावित होते हैं, और executor को ऐसी जानकारी देता है कि अनावश्यक processing छोड़ी जा सके
    • row_number() <= 10 उदाहरण में PG15 ने index से 50,410 rows पढ़ने के बाद रुकना सीखा, जबकि PG16 ने केवल 11 rows पढ़ीं
    • PG16 इस तथ्य का उपयोग करता है कि row_number 11 पर पहुंचने के बाद <= 10 condition को पूरा करने वाली और rows नहीं होंगी
    • execution time PG15 के 29.775ms से घटकर PG16 में 0.058ms हो गया, यानी 500 गुना से अधिक तेज़

  • Monotonically increasing window functions के लिए early stop का विस्तार

    • PostgreSQL 15 ने WHERE clause की ऐसी conditions के लिए WindowAgg को जल्दी रोकना संभव बनाया था, जिनमें किसी खास window function के लिए एक बार false होने के बाद वह फिर true नहीं हो सकती
    • PostgreSQL 16 ने इस optimization को ntile(), cume_dist(), percent_rank() तक बढ़ा दिया है
    • PostgreSQL 15 में यह केवल row_number(), rank(), dense_rank(), count(), count(*) पर लागू था
    • percent_rank() <= 0.01 उदाहरण में PG15 ने condition को subquery के Filter के रूप में process किया, और WindowAgg ने पूरे 50,000 rows process किए
    • PG16 ने इसी condition को Run Condition के रूप में इस्तेमाल किया, जिससे WindowAgg जल्दी रुक गया
    • execution time PG15 के 84.358ms से घटकर PG16 में 19.454ms हो गया, यानी 4 गुना से अधिक तेज़

Partition table और obvious DISTINCT handling

  • Partition table पर LEFT JOIN हटाना

    • PostgreSQL लंबे समय से ऐसे LEFT JOIN हटा सकता था जो query में आवश्यक नहीं हों और row duplication की संभावना भी न रखते हों
    • PostgreSQL 16 से पहले partition tables पर LEFT JOIN removal supported नहीं था
    • क्योंकि यह साबित करने के लिए ज़रूरी आधार उपलब्ध नहीं था कि inner rows outer rows को duplicate नहीं कर सकतीं
    • PostgreSQL 16 planner अब partition tables पर भी LEFT JOIN removal optimization लागू करता है
    • यह optimization views में खास तौर पर उपयोगी हो सकता है
      • क्योंकि views में columns बहुत हो सकते हैं, लेकिन actual query में हर column हमेशा नहीं पढ़ा जाता
    • उदाहरण में PG15 plan में part_tab के साथ join शामिल है, जबकि PG16 plan केवल normal_table पर sequential scan करता है

  • जब result एक ही value पर निश्चित हो तो DISTINCT को Limit से handle करना

    • PostgreSQL planner अगर यह पहचान ले कि सभी rows का value समान होगा, तो duplicate removal के लिए plan node को छोड़ सकता है
    • PostgreSQL 16 इस बात का उपयोग करता है कि अगर DISTINCT target columns सभी WHERE clause की equality conditions से fixed हों, तो result में केवल वही value होगी, इसलिए इसे LIMIT 1 से handle किया जा सकता है
    • उदाहरण query SELECT DISTINCT a,b,c FROM abc WHERE a = 5 AND b = 5 AND c = 5 में हर DISTINCT column एक ही value तक सीमित है
    • PG15 ने पूरा result पढ़कर Unique operator से उसे 1 row तक घटाया
    • PG16 ने Limit और sequential scan का उपयोग कर केवल 1 row return की
    • execution time PG15 के 30.381ms से घटकर PG16 में 0.025ms हो गया, यानी 1200 गुना से अधिक तेज़

Merge Join के बाद Incremental Sort का विस्तारित उपयोग

  • PostgreSQL 16 से पहले planner जब Merge Join पर विचार करता था, तो वह join की sort order को तभी उपयोग करता था जब वह upper DISTINCT, GROUP BY, ORDER BY operation की requirement से बिल्कुल match करे
  • यह rule इस तथ्य को पर्याप्त रूप से नहीं दर्शाता था कि upper operation में Incremental Sort partially sorted input का उपयोग कर सकता है
  • PostgreSQL 16 ने Merge Join order पर विचार करने के rule को “exact match होना चाहिए” से बदलकर “कम-से-कम एक leading column सही तरीके से sorted होना चाहिए” कर दिया है
  • इस बदलाव से planner अब upper operations की ज़रूरत के हिसाब से Merge Join result पर Incremental Sort का अधिक बार उपयोग कर सकता है
    • Incremental Sort partially sorted input का उपयोग करके छोटे batches में sort करता है, इसलिए full sort की तुलना में memory usage और comparison count कम कर सकता है
  • उदाहरण में PG15 ने Merge Join के बाद full Sort का उपयोग किया, जबकि PG16 ने Incremental Sort का उपयोग किया
    • PG16 के Incremental Sort की maximum memory 26KB थी
    • execution time PG15 के 1010.738ms से घटकर PG16 में 915.589ms हुआ, यानी थोड़ा सुधार, लेकिन sorting में इस्तेमाल memory काफी कम हुई

लागू करने का तरीका और practical verification

  • PostgreSQL 16 के 10 planner improvements सभी default enabled हैं
  • हर optimization या तो सभी संभव मामलों में लागू होता है, या planner जब इसे उपयोगी मानता है तब selectively चुना जाता है
  • यदि आप PostgreSQL का पुराना version इस्तेमाल कर रहे हैं, तो PostgreSQL 16 पर अपना वास्तविक workload चलाकर देख सकते हैं कि कौन-सी queries तेज़ होती हैं
  • वास्तविक उपयोग से मिला feedback pgsql-general@postgresql.org mailing list पर साझा किया जा सकता है

संबंधित पढ़ाई

1 टिप्पणियां

 
GN⁺ 2024-02-11
Hacker News की राय

  • अगर PostgreSQL query planner execution के बीच में query को फिर से plan कर सके, तो वाकई अच्छा होगा
    असामान्य रूप से धीमी queries अक्सर इसलिए होती हैं क्योंकि planner को data distribution के लिए ज़रूरी जानकारी नहीं होती और वह cost का गलत अनुमान लगा देता है; execution time 1ms की बजाय 1 second हो जाए, यानी 1000 गुना फर्क, यह आसानी से हो सकता है
    table statistics 100% सटीक नहीं हो सकते, इसलिए query शुरू होने के बाद अगर progress उम्मीद से धीमी हो तो scanned pages की संख्या और matched tuples जैसी मौजूदा progress जानकारी planner में वापस डालकर नया plan बनाना अच्छा होगा
    हालांकि PostgreSQL results को अंत तक बनाकर भेजने के बजाय streaming से भेजता है, इसलिए बीच में plan बदलने के लिए client को पहले ही भेजे गए results को track करना पड़ेगा, जिससे infrastructure में बड़ा बदलाव चाहिए
    ऊपर से client query के बीच में दिशा पलटकर पिछले results को reverse order में फिर से request भी कर सकता है, जिससे complexity और बढ़ जाती है

    • blog लेखक और PostgreSQL committer के तौर पर, मुझे लगता है यह feature अच्छा रहेगा। हालांकि client को tuples भेजने की समस्या ऊपर कही गई बात से भी ज्यादा पेचीदा है
      क्योंकि इसकी गारंटी भी नहीं है कि नया plan वही tuples लौटाएगा। उदाहरण के लिए SELECT * FROM table LIMIT 10 में ORDER BY न हो तो कौन-से tuples आएंगे, यह non-deterministic है
      इसके बजाय X tuples को queue में जमा रखना, और queue भरने पर ही भेजना आसान हो सकता है। queue भर जाने के बाद मान लिया जाए कि re-planning के लिए बहुत देर हो चुकी है और current plan पर lock कर दिया जाए
      user X को adjust करके ज्यादा memory और first tuple latency स्वीकार कर सकता है, बदले में plan बदलने के लिए ज्यादा समय मिल जाएगा
    • एक और नजरिए से, ऐसी queries रखी जा सकती हैं जिनमें लंबा planning allowed हो। optimal plan चुनने में 1 second या कुछ seconds लगाने की अनुमति दी जाए, और इस दौरान और statistics collect किए जा सकें या query को थोड़ी देर run करके देखा जा सके
    • client query के बीच में दिशा पलटकर पिछले results को reverse order में फिर से पाने वाला feature कहां उपयोगी होता है, यह जानना चाहूंगा
    • अगर query sort order को पूरी तरह determine नहीं करती, तो क्या query plan result order को प्रभावित कर सकता है? अगर हां, तो सुझाया गया तरीका लगभग असंभव हो सकता है
      नई query सिर्फ पहले N results skip नहीं कर सकती; उसे पहले से भेजी गई हर row को dictionary से मिलाना होगा
    • यह paper और cited papers रुचिकर हो सकते हैं: https://arxiv.org/pdf/1902.08291

  • query visualization के लिए मैं यह tool इस्तेमाल करता हूं: https://explain.dalibo.com/
    https://www.pgexplain.dev/ भी है; पहले इसका output थोड़ा कम अच्छा था, लेकिन अब दोनों लगभग समान दिखते हैं

    • tool बहुत अच्छा है और मैं इसे इस्तेमाल करता हूं, लेकिन plan में जो हिस्सा खराब दिखता है उसे देखकर अपनी approach कैसे सुधारनी है, इतना गहरा understanding मेरे पास नहीं है
    • profile देखकर लगता है कि आप fintech CTO हैं; उस tool की “महत्वपूर्ण या संवेदनशील जानकारी न भेजने की सलाह दी जाती है” वाली guidance को आप कैसे handle करते हैं, यह जानना चाहूंगा
      क्या ऐसी स्थिति में मदद करने वाला कोई execution plan sanitization tool है?

  • query planner में सुधार हमेशा स्वागतयोग्य है, और database का बेहद महत्वपूर्ण हिस्सा है। बेशक आम तौर पर यह सबसे ज्यादा तब नजर आता है जब यह मेरी इच्छा के मुताबिक काम नहीं करता
    निजी तौर पर जो चीज काफी frustrating लगी, वह modern PostgreSQL का JIT है। कब इसे use करना है यह तय करने वाली heuristics बिल्कुल robust नहीं लगती
    मैंने इसे एक typical ORM-generated query में देखा: query खुद सरल थी, लेकिन joins के जरिए बहुत सारी tables खींच लाती थी। JIT के बिना यह कुछ milliseconds में खत्म हो जाती, लेकिन JIT 1–1.5 seconds extra लगाता और छोटे data पर भी इसे बहुत धीमा बना देता
    अब मुझे पता है कि JIT को बस off किया जा सकता है, लेकिन जिन users को अभी तक वजह समझ नहीं आई, उनके लिए यह PostgreSQL के बारे में impression बुरी तरह खराब कर सकता है। मुझे PostgreSQL पसंद है, लेकिन JIT को default on रखना बहुत risky लगता है

    • blog लेखक और PostgreSQL committer के तौर पर, मैं इस बात से काफी सहमत हूं कि JIT इस्तेमाल करना है या नहीं तय करने वाला code बेहतर होना चाहिए
      PG16 में यह सिर्फ plan की estimated total cost देखता है, compile किए जाने वाले expressions की संख्या को नहीं
      कुछ expressions compile करना तेज होता है, लेकिन अगर hundreds of partitions वाली partition table query की जाए और plan में वे सारे partitions आ जाएं, तो JIT compiler के लिए काम बहुत बढ़ जाता है
      मेरे पास एक colleague के साथ इसे सुधारने वाला code है, लेकिन इस समय पक्का नहीं है कि वह PG17 में जाएगा या नहीं
    • JIT में एक और अजीब लगने वाली बात यह है कि generated code cache नहीं होता। query execution में यह अक्सर सबसे महंगा हिस्सा होता है, फिर इसे cache क्यों नहीं करते, समझ नहीं आता
      PostgreSQL mailing list पर JIT से जुड़ी चर्चाएं खोजीं, लेकिन कोई संतोषजनक वजह नहीं मिली
      OLTP workloads में JIT बंद रखना ही सही है
    • मेरे हिसाब से JIT लगभग failure जैसा है। intention अच्छा था, लेकिन LLVM इसके लिए सही tool नहीं है। मैंने इसे globally off कर दिया है
      मैं ORM use नहीं करता, इसलिए वजह सिर्फ अजीब query patterns भी नहीं है
      इसके उलट query parallelization सच में उपयोगी हो सकती है, और सबसे बड़ी बात, यह शायद ही कभी नुकसान पहुंचाती है
    • हाल ही में production में JIT से जुड़ा एक अजीब bug मिला
      apt से कुछ packages update किए तो हर 5 मिनट में चलने वाली बड़ी query अचानक fail होने लगी। ठीक-ठीक कहें तो PostgreSQL query execution के बीच में बिना कोई log छोड़े connection को चुपचाप drop कर देता था
      manually EXPLAIN चलाकर जांचने पर दिखा कि query का वही variant टूट रहा था जो JIT use कर रहा था, और जो नहीं कर रहा था वह ठीक था। JIT बंद करते ही सब फिर normal हो गया
    • क्या आपने prepared statements इस्तेमाल करके compilation सिर्फ एक बार करने और compiled result को उस query के हर execution में reuse करने की कोशिश नहीं की?

  • यह जानने की उत्सुकता है कि ऐसे बदलाव असल queries में कितनी बार असर दिखाते हैं। खासकर “जहां संभव हो, DISTINCT लागू करने के लिए Unique के बजाय Limit का इस्तेमाल” वाला बदलाव ऐसा लगता है जैसे यह सिर्फ बेहद बेवकूफाना queries पर ही लागू होगा
    सोचता हूं कि क्या PostgreSQL developers के पास इसका आकलन करने के लिए कोई जानकारी का स्रोत है

    • काफी बार असर हो सकता है। DISTINCT अक्सर कम अनुभवी developers खराब query को ठीक करने की कोशिश में जोड़ देते हैं, और आम तौर पर performance सुधार शुरू करते समय सबसे पहले query को इस तरह फिर से लिखते हैं कि उसकी जरूरत ही न रहे
      अगर DISTINCT में सुधार से खराब queries के खिलाफ ज्यादा मजबूती मिलती है, तो काफी फायदा है। यह सारी समस्याएं नहीं सुलझाएगा, लेकिन कोई भी सुधार स्वागतयोग्य है
    • ब्लॉग लेखक और इस feature के लेखक के तौर पर, यह मामला सच में pgsql-hackers mailing list से आया था
      मैं मानता हूं कि इसके बार-बार लागू होने की संभावना कम है, लेकिन अच्छी बात यह थी कि इसे लागू किया जा सकता है या नहीं, यह detect करना उतना ही सरल था जितना यह check करना कि pointer NULL है या नहीं
      detection बहुत सरल है और ज्यादातर मामलों में लागू नहीं होगा, लेकिन जहां लागू हो सकता है वहां काफी performance improvement दे सकता है
    • समस्या यह है कि ORM को बहुत बेवकूफाना queries बनाने की आदत होती है, और developers SQL सीधे लिखकर उन्हें ठीक करने से यह कहकर बचते हैं कि वह किसी तरह “शुद्ध” नहीं है
      यह बहुत आम समस्या नहीं होगी, लेकिन कभी-कभी दिखे तो हैरानी नहीं होगी
    • पिछली नौकरी में legacy कारणों से users table में duplicate email addresses की अनुमति थी, लेकिन हम नए duplicates नहीं डालना चाहते थे, इसलिए नया user बनाने से पहले select distinct email from users where email = ? query चलाते थे
      मुझे नहीं लगता कि एक ही email वाली rows 100 से ज्यादा थीं। उनमें से ज्यादातर test users थे जिन्हें delete किया जा सकता था, लेकिन बात थोड़ी भटक गई

  • अच्छा होगा अगर PostgreSQL में app testing के लिए strict mode हो। यह ऐसा mode हो जो सिर्फ query को देखकर, statistics से स्वतंत्र रूप से, अगर index मौजूद हो तो asymptotically query को बेहतर बनाता हो और वह index न हो तो error return करे
    app upgrades के लिए वह index बनाने वाला CREATE INDICES FOR command भी हो तो अच्छा होगा, और interactive/development use के लिए automatic index creation mode भी होना चाहिए
    कुल मिलाकर system को इस तरह design किया जाना चाहिए कि asymptotically non-optimal execution कभी हो ही न

  • समझ नहीं आता कि hints क्यों implement नहीं करते

    • pg_hint_plan extension मौजूद है। hints का जोखिम यह है कि लिखे जाते समय वे सही हों, लेकिन table size या data skew बदलने पर उल्टा खराब हो सकते हैं
      मुझे याद है कि जब पहले hints पर चर्चा देखी थी, तो अगर तरीका ऐसा हो जो planner को बहुत कसकर न बांधे और underlying data changes के साथ adapt कर सके, तो सामान्य विरोध नहीं था
      उदाहरण के लिए, यह specify करने के बजाय कि कोई खास predicate 10 rows से match करता है, यह बताना कि दो columns के बीच correlation है
    • संबंधित चर्चा: Why PostgreSQL doesn't have query hints
      https://news.ycombinator.com/item?id=2179433 (60 comments, 2011)
      PostgreSQL wiki की आधिकारिक स्थिति https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion पर है
      उनका रुख है कि “हमें उन exact तरीकों वाले hints में रुचि नहीं है जो अन्य databases में आम तौर पर implement किए जाते हैं”
      मौजूदा hint systems की समस्याओं में application code की maintainability घटाना, upgrades में बाधा डालना, खराब DBA habits को बढ़ावा देना, और data size scale होने पर fit न होना शामिल हैं
      मैं उस रुख को दोष नहीं देना चाहता, लेकिन जब PostgreSQL बेवकूफाना plan चुनता है और उसे समझदारी भरा विकल्प चुनने के लिए राजी नहीं किया जा सकता, तो frustrate होना स्वाभाविक है

  • मिड-साइज़ कंपनियों के लिए Microsoft DBA रहे एक दोस्त ने कहा कि PostgreSQL से गंभीर काम नहीं किया जा सकता। उसने तो यह भी कहा कि PostgreSQL में query planner भी नहीं है, यह जानकर वह चौंक गया था
    मज़ाक को थोड़ी देर के लिए अलग रखें, तो मुझे जानना है कि क्या इस बड़े दावे में कुछ दम है कि MSSQL ऐसा scale संभाल सकता है जिसके लिए PostgreSQL उपयुक्त नहीं है। मेरी instinct कहती है कि यह बकवास है, लेकिन मैं DBA बिल्कुल नहीं हूँ

    • इस पहलू में कुछ बात है। अगर आपको ऐसी database चाहिए जो लगभग जो भी चाहिए उसे काफी अच्छी तरह संभाल ले, तो MSSQL और Oracle के कर पाने की संभावना बड़ी है
      इन्होंने पैसे और hardware, यानी और ज्यादा पैसे, झोंककर समस्या हल होने तक उसे push करने वाले तरीके से चीज़ें सुलझाई हैं। बेशक इसमें smart technology भी है, लेकिन मूल रूप से लंबे समय तक कहीं ज्यादा engineering लगी है
      ये PostgreSQL के reasonably कर सकने से ज्यादा बड़े पैमाने पर horizontally scale कर सकते हैं
      हालांकि PostgreSQL भी पकड़ बना रहा है, और MySQL/MariaDB के बारे में कह सकते हैं कि इस तरफ कहानी हमेशा ठीक-ठाक रही है। horizontal scaling के विकल्प लगातार बेहतर हो रहे हैं
      अब कम मशीनों पर multi-terabyte PostgreSQL cluster चलाकर बड़ा traffic संभालना, और “big data” को ज्यादा specialized databases में रखना भी आसान हो गया है। सब कुछ MSSQL/Oracle में ठूंस देने का पुराना तरीका थोड़ा outdated हो सकता है
    • मैंने MSSQL development काफी किया है, और PostgreSQL में कुछ थोड़ी हैरान करने वाली missing features हैं
      आपके दोस्त का मतलब शायद यह हो सकता है कि PostgreSQL में query plans को cache या pin करने का तरीका नहीं है। PostgreSQL manually prepared statements इस्तेमाल न करने पर हर statement को फिर से plan करता है, और वह भी सिर्फ connection-wise काम करता है
      MSSQL बहुत पहले से plans को cache और reuse करता आया है, इसलिए planner plan बनाने में ज्यादा समय लगा सकता है। इसमें hints भी हैं और plans को pin भी किया जा सकता है
      PostgreSQL में सच में hints की जरूरत है। optimizer बहुत अच्छा हो, फिर भी कभी-कभी मुझे बेहतर पता होता है, और मैं चाहता हूँ कि वह मेरी बात माने
      साथ ही PostgreSQL में असली clustered index नहीं है और सभी tables heap हैं। MSSQL में इसका अधिकतर बार खूब इस्तेमाल होता है, आम तौर पर primary key को clustered index set किया जाता है ताकि table खुद ही index बन जाए और key lookup में indirect reference न हो
      दिलचस्प बात है कि SQLite उल्टा है: tables में हमेशा clustered index होता है, चाहे आप बनाएं या नहीं, और MSSQL आपको heap और index-organized table में से चुनने देता है
    • PostgreSQL में query planner है। यह पूरा लेख उसी में सुधारों के बारे में है। इसलिए या तो communication गलत हुआ है, या आपके दोस्त को PostgreSQL के बारे में बिल्कुल जानकारी नहीं लगती
      बहुत बड़े PostgreSQL databases के भी अच्छी तरह चलने के उदाहरण हैं, इसलिए PostgreSQL निश्चित रूप से scale कर सकता है
      हालांकि SQL Server में कुछ features हैं जो PostgreSQL में नहीं हैं, और अगर वे महत्वपूर्ण हैं तो कुछ specific use cases के लिए वह बेहतर fit हो सकता है। आखिरकार ये अलग-अलग strengths और weaknesses वाली अलग databases हैं
    • मैंने OLTP और data warehousing दोनों में दोनों का इस्तेमाल किया है, और दोनों ठीक हैं
      पहले मैं लिखने वाला था कि अगर कंपनी में ऐसे vendor-provided applications न होते जिन्हें SQL Server चाहिए, तो मैं PostgreSQL migration recommend करता
      लेकिन फिर मुझे एहसास हुआ कि Microsoft जो reporting services, integration services, jobs, AD integration, service broker जैसी चीज़ें शामिल करके देता है, उन्हें replace करना कितना बड़ा काम होगा। notify/listen में message type नहीं होता
      analysis services अब इस्तेमाल नहीं करते, लेकिन जब पहले इस्तेमाल करते थे, तब उसे भी replace करना मुश्किल होता
      यही चीज़ें लोगों को बांधे रखती हैं। इन सबको replace करने में कितना समय लगेगा, इसका अंदाज़ा भी नहीं, और जो पहले से है उसे replace करने में 1 साल लगाना ROI के लिहाज से अच्छा नहीं है
    • AWS की Aurora यह काफी अच्छी तरह संभालती लगती है, और PostgreSQL तथा MySQL की drop-in replacement बनने का लक्ष्य रखती है

  • समझ नहीं आ रहा कि यह postgresql.org की बजाय citusdata पर क्यों publish हुआ। पता नहीं यह paid features के लिए है या open source addition है

    • क्योंकि लेखक Citus Data में काम करते हैं, और उन optimizations में से कुछ उन्होंने खुद भी लिखे हैं

  • IS NOT DISTINCT FROM queries को तेज़ करने के लिए index इस्तेमाल कर पाना कब तक संभव होगा ;)