कैसे एक बिना इंडेक्स वाले कॉलम ने हमारे डेटाबेस को बर्बाद कर दिया
एक अकेले छूटे हुए इंडेक्स ने 8 ms की API को 8 सेकंड के दुस्वप्न में बदल दिया।
हमने एक बड़े रिलीज़ से पहले लोड टेस्ट किया। 100 रिकॉर्ड्स के साथ लोकल डेवलपमेंट ठीक से काम कर रहा था। फिर हमने प्रोडक्शन-साइज़ डेटा में 1.5 मिलियन rows पर 500 यूज़र्स का सिम्युलेशन किया।
परिणाम बहुत खराब थे:
- API रिस्पॉन्स टाइम 45 ms से बढ़कर 8,000 ms हो गया।
- डेटाबेस CPU 100% तक पहुँच गया।
- कनेक्शन पूल खत्म हो गए, जिससे टाइमआउट एरर आने लगे।
हमने स्लो-क्वेरी लॉग (slow-query log) चेक किया और हमें एक ऐसा एंडपॉइंट मिला जो user ID और status के आधार पर ऑर्डर हिस्ट्री फेच कर रहा था।
PostgreSQL पर EXPLAIN ANALYZE चलाने पर एक Sequential Scan दिखाई दिया। इंजन हर रिक्वेस्ट के लिए सभी 1.5 मिलियन rows पढ़ रहा था क्योंकि user_id पर कोई इंडेक्स नहीं था।
100 कॉन्करेंट (concurrent) रिक्वेस्ट के साथ, इसने एक साथ 150 मिलियन rows को स्कैन किया।
इसका समाधान करने में केवल पाँच मिनट लगे।
user_id पर एक साधारण इंडेक्स बनाने के बजाय, हमने (user_id, status, created_at DESC) पर एक कंपोजिट इंडेक्स (composite index) बनाया। इससे डेटाबेस को निम्नलिखित सुविधा मिली:
user_idद्वारा फ़िल्टर करना।statusद्वारा फ़िल्टर करना।- तुरंत नवीनतम rows वापस करना।
- अतिरिक्त सॉर्टिंग स्टेप्स को छोड़ना।
हमने CREATE INDEX CONCURRENTLY का उपयोग किया ताकि ऑपरेशन के दौरान टेबल अनलॉक रहे।
समाधान के बाद:
- क्वेरी टाइम 8,150 ms से घटकर 0.14 ms रह गया।
- API लेटेंसी 8 सेकंड से घटकर 12 ms हो गई।
- CPU यूसेज 100% से घटकर 8% से भी कम हो गया।
सीखे गए सबक:
- लोकल टेस्टिंग भ्रामक हो सकती है; 100 rows, मिलियन का प्रतिनिधित्व नहीं करतीं।
- अपनी foreign keys को इंडेक्स करें—ज़्यादातर ORMs इसे छोड़ देते हैं।
- और अधिक हार्डवेयर खरीदने से पहले
EXPLAIN ANALYZEचलाएँ। - उन क्वेरीज़ के आधार पर इंडेक्स डिज़ाइन करें जिन्हें आप वास्तव में चलाते हैं।
पहले सर्वर को स्केल न करें। क्वेरीज़ को स्केल करें।