PostgreSQL फुल-टेक्स्ट सर्च: सही तरीके से करें तो तेज़ है (धीमा होने की गलतफ़हमी दूर)

• PostgreSQL के built-in Full-Text Search(FTS) को अक्सर धीमा माना जाता है, लेकिन सही optimization करने पर यह बहुत तेज़ चलता है
• Neon के ब्लॉग में Rust-आधारित pg_search extension और built-in FTS की तुलना करके दावा किया गया कि बाद वाला धीमा है
• लेकिन यह तुलना संभवतः ऐसी स्थिति में की गई थी जहाँ PostgreSQL FTS के लिए ज़रूरी बेसिक optimization steps शामिल नहीं थे
• यह लेख संख्याओं के साथ दिखाता है कि सिर्फ साधारण optimization लागू करके भी built-in FTS में 50x performance improvement मिल सकता है

बेंचमार्क सेटअप का अवलोकन

• 1 करोड़ log entries वाली table पर टेस्ट किया गया

  CREATE TABLE benchmark_logs (  
      id SERIAL PRIMARY KEY,  
      message TEXT,  
      country VARCHAR(255),  
      severity INTEGER,  
      timestamp TIMESTAMP,  
      metadata JSONB  
  );  
  

• समस्या वाली query संरचना:

  SELECT country, COUNT(*)  
  FROM benchmark_logs  
  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')  
  GROUP BY country  
  ORDER BY country;  
  

  • query के अंदर to_tsvector() चलाया जा रहा है → बहुत inefficent
  • GIN index होने पर भी उसका सही उपयोग नहीं होता

टेस्ट वातावरण (डिफ़ॉल्ट सेटिंग की पुनरावृत्ति)

• EC2 i7ie.xlarge instance, लोकल NVMe SSD का उपयोग
• 4 vCPUs, PostgreSQL 16(Docker) का उपयोग
• मुख्य PostgreSQL सेटिंग्स:

  -c shared_buffers=8GB  
  -c maintenance_work_mem=8GB  
  -c max_parallel_workers=4  
  -c max_worker_processes=4  
  

• parallel processing सीमा: max_parallel_workers_per_gather = 2 (Neon ने 8 का उपयोग किया)

performance गिरने का कारण 1: real-time tsvector calculation

• query के अंदर to_tsvector() चलाने पर:
• हर बार text parsing, morphological analysis आदि करना पड़ता है
• index का बिल्कुल उपयोग नहीं हो पाता

• समाधान: tsvector column पहले से बनाकर index करें

  • 1. tsvector column जोड़ें

  ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;  
  

  • 2. डेटा भरें

    UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);  
    

  • 3. index बनाएँ (fastupdate निष्क्रिय)

    CREATE INDEX idx_gin_logs_message_tsvector  
    ON benchmark_logs USING GIN (message_tsvector)  
    WITH (fastupdate = off);  
    

  • 4. query बदलें

    SELECT country, COUNT(*)  
    FROM benchmark_logs  
    WHERE message_tsvector @@ to_tsquery('english', 'research')  
    GROUP BY country  
    ORDER BY country;  
    

performance गिरने का कारण 2: GIN index में fastupdate=on सेटिंग

• fastupdate=on write performance के लिए अच्छा है, लेकिन search performance पर बुरा असर डालता है
• read-only या search-केंद्रित dataset के लिए fastupdate=off ज़रूरी है
• index छोटा और तेज़ हो जाता है, और pending list process करने की ज़रूरत नहीं रहती

• optimized GIN index बनाने का तरीका

  CREATE INDEX idx_gin_logs_message_tsvector  
  ON benchmark_logs USING GIN (message_tsvector)  
  WITH (fastupdate = off);  
  

performance improvement: 50x से अधिक सुधार

• optimization से पहले: लगभग 41.3 सेकंड (41,301 ms)
• optimization के बाद: लगभग 0.88 सेकंड (877 ms)
• लगभग 50x performance improvement दिखा
• कम parallel processing वाले वातावरण में भी यह performance हासिल की जा सकती है

ts_rank performance वास्तव में धीमी हो सकती है

• ts_rank या ts_rank_cd सभी results का मूल्यांकन करके sort करते हैं, इसलिए ये अपेक्षाकृत धीमे हो सकते हैं
• खासकर जब results बहुत अधिक हों, तब CPU/IO पर बड़ा लोड पड़ता है

उन्नत ranking फीचर: VectorChord-BM25 extension

• जहाँ ranking accuracy और speed महत्वपूर्ण हों, वहाँ dedicated extension का उपयोग अधिक प्रभावी हो सकता है
• VectorChord-BM25 PostgreSQL के लिए एक extension है, जो BM25 algorithm आधारित ranking evaluation देता है
• ऐसी रिपोर्ट भी हैं कि यह Elasticsearch से 3x तेज़ है

VectorChord-BM25 के फायदे

• BM25 algorithm: TF-IDF से अधिक उन्नत search ranking algorithm
• dedicated index format: Block WeakAnd आदि के साथ high-speed search optimization
• bm25vector type उपलब्ध: tokenized representation को store करने के लिए
• search accuracy और speed दोनों में सुधार

निष्कर्ष: PostgreSQL का built-in FTS भी काफ़ी तेज़ है

• tsvector column और सही GIN index(fastupdate=off) का उपयोग करने पर built-in FTS से भी बहुत तेज़ search संभव है
• performance comparison optimized baseline पर किया जाना चाहिए
• अगर advanced ranking फीचर चाहिए, तो VectorChord-BM25 जैसे extension tools पर विचार किया जा सकता है
• मुख्य संदेश: tool धीमा नहीं है, समस्या settings में हो सकती है

[stadia]

इसी की बदौलत मैंने query tuning की।

[pcj9024]

Hacker News की राय तो डरावनी है... "एक करोड़? मज़ाक कर रहे हो?"

[GN⁺]

Hacker News राय

• pg_search के मेंटेनर के रूप में, Postgres docs के अनुसार Neon/ParadeDB लेख और यहाँ इस्तेमाल की गई रणनीति दोनों वैध विकल्पों के रूप में पेश किए गए हैं

  • Postgres FTS की समस्या किसी एक query को optimize करना नहीं, बल्कि तरह-तरह की वास्तविक queries पर Elastic-स्तर की performance देना है
  • pg_search इसी दूसरी समस्या को हल करने के लिए डिज़ाइन किया गया है, और benchmark भी इसे दर्शाते हैं
  • Neon/ParadeDB benchmark में कुल 12 queries शामिल हैं, जो वास्तविक उपयोग के मामलों के लिए अवास्तविक है
  • pg_search अलग-अलग "Elastic style" queries और Postgres types पर सिर्फ़ simple index definition के साथ काम करता है

• tsvector को real time में calculate करना एक बड़ी गलती है

  • जब मैंने Postgres FTS को एक personal project में implement किया, तो मैंने docs पढ़े और guidelines का पालन किया
  • docs साफ़ तौर पर बताते हैं कि एक basic non-optimized case कैसे बनाया जाए और फिर उसे optimize कैसे किया जाए
  • जिसने यह गलती की, उसने या तो docs नहीं पढ़े, या Postgres FTS को ग़लत तरीके से पेश करने का इरादा था

• मैं यह समझ नहीं पाता कि हर चीज़ को Postgres में डालने की प्रवृत्ति क्यों है

• Postgres-native full text search implementations ज़्यादा देखने को मिल रहे हैं, यह देखकर खुशी है

  • वैकल्पिक solutions (lucene/tantivy) immutable segments के हिसाब से डिज़ाइन किए गए हैं, इसलिए Postgres heap table के साथ जोड़ने पर वे और ख़राब समाधान बन सकते हैं

• execution plan न होने से यह समझना मुश्किल है कि क्या हो रहा है

  • अगर query index का उपयोग करती है, तो real-time tsvector recheck सिर्फ़ matched items पर लागू होता है, और benchmark query में LIMIT 10 है, इसलिए recheck कम होते हैं
  • query condition में 2 gin indexes पर शर्तें हैं, इसलिए लगता है planner पहले सभी matched items को recheck कर रहा है

• कुछ साल पहले मैं native FTS का उपयोग करना चाहता था, लेकिन असफल रहा

  • ऐसी table में जहाँ प्रति सेकंड हज़ारों inserts होते थे, full updates धीमे हो गए और transaction timeout होने लगे
  • मैंने indexes जोड़े, लेकिन दूसरा index पूरा होते ही सिस्टम में timeout आने लगे
  • मुझे indexes फिर हटाने पड़े, और मुझे वास्तविक FTS performance test करने का मौका ही नहीं मिला

• मैंने pg_search और vchord_bm25 extension RPM/DEB को package किया है

  • जो लोग ख़ुद benchmark करना चाहते हैं, उनके लिए मैं link दे रहा हूँ

• मैंने कई teams को सीधे Elasticsearch या Meilisearch पर जाते देखा है

  • अगर सही तरीके से इस्तेमाल किया जाए, तो native PG FTS से काफ़ी performance मिल सकती है
  • सोचता हूँ कि क्या SQLite + FTS5 + Wasm का उपयोग करके browser में इसी तरह की performance मिल सकती है

• 1 करोड़ records एक toy dataset है

  • पूरे Wikipedia या 2022 से पहले के Reddit comments जैसे बड़े text datasets benchmark के लिए ज़्यादा उपयुक्त हैं

• 2008 के आसपास मैंने पहली बार pg full text का उपयोग किया था

  • Postgres full text search की समस्या यह नहीं कि यह बहुत धीमा है, बल्कि यह कि यह पर्याप्त flexible नहीं है
  • simple search जोड़ने के लिए यह अच्छा है, लेकिन search को tune करने के लिए यह काफ़ी नहीं है
  • Solr और Elasticsearch आपको complex indexing और search processing सेट करने देते हैं
  • Postgres इन features को अपना सकता है, लेकिन अभी यह कुछ भी उपलब्ध नहीं कराता
  • Postgres whitespace के आधार पर split करता है, और आप manually stopwords और stemming का उपयोग कर सकते हैं
  • field weighting के आधार on search scoring करना संभव नहीं है
  • विकल्पों की तुलना में यह एक toy system है