2026 में PostgreSQL क्वेरी प्रदर्शन को कैसे अनुकूलित करें: इंडेक्स, व्याख्या और ट्यूनिंग

अधिकांश PostgreSQL प्रदर्शन समस्याएं कुछ कारणों से आती हैं: एक अनुपलब्ध सूचकांक, एक सूचकांक जिसे योजनाकार उपयोग नहीं कर सकता है, एक क्वेरी जो आवश्यकता से कहीं अधिक पंक्तियों को खींचती है, या आँकड़े जो अब डेटा को प्रतिबिंबित नहीं करते हैं। यह मार्गदर्शिका बताती है कि आपके पास जो है उसे कैसे ढूंढें और उसे कैसे ठीक करें, इस क्रम में कि समस्याओं का सबसे तेजी से पता लगाया जाए।

चरण 1: धीमी क्वेरीज़ ढूंढें

अनुमान मत लगाओ. सक्षम करेंpg_stat_statements, जो प्रत्येक क्वेरी आकार के लिए निष्पादन आँकड़े रिकॉर्ड करता है।

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
-- then restart, and in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Highest total time — usually where the real wins are
SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

के अनुसार क्रमबद्ध करें कुल मतलब के बजाय समय. 20 एमएस लेने वाली एक क्वेरी, लेकिन एक घंटे में 100,000 बार चलाने की लागत दिन में दो बार 2 सेकंड लेने वाली क्वेरी से कहीं अधिक है, और इसे ठीक करना आमतौर पर आसान होता है।धीमी क्वेरी भी लॉग करें ताकि आप यह जान सकें कि उत्पादन में क्या होता है लेकिन परीक्षण में नहीं।

चरण 2: व्याख्या विश्लेषण ठीक से पढ़ें

-- postgresql.conf
log_min_duration_statement = 500   -- log anything over 500ms
log_lock_waits = on
log_temp_files = 0                 -- log every temp file (indicates spilling)

हमेशा

का उपयोग करें . सादाEXPLAIN (ANALYZE, BUFFERS) योजनाकार का अनुमान दिखाता है; EXPLAIN वास्तव में क्वेरी चलाता है और वास्तविकता की रिपोर्ट करता है, और दोनों के बीच का अंतर अक्सर बग होता है।ANALYZEअंतरतम नोड से बाहर की ओर आउटपुट पढ़ें, और चार चीजों की तलाश करें।

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= now() - interval '7 days'
  AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

अनुमानित बनाम वास्तविक पंक्तियाँ.

इसका मतलब है कि योजनाकार बुरी तरह से गलत था, और उसके द्वारा डाउनस्ट्रीम में लिया गया हर निर्णय संदिग्ध है। आमतौर पर बासी आँकड़े। rows=10 ... actual rows=48000बड़ी मेजों पर अनुक्रमिक स्कैन।

छोटी मेजों पर जुर्माना और बड़ी मेजों पर चयनात्मक फिल्टर के साथ लाल झंडा।फ़िल्टर द्वारा पंक्तियाँ हटाई गईं.

एक उच्च संख्या का मतलब है कि डेटाबेस ने जितनी पंक्तियाँ लौटाई हैं उससे कहीं अधिक पढ़ी है – क्लासिक लापता-सूचकांक हस्ताक्षर।बाहरी मर्ज या डिस्क-आधारित सॉर्ट।

सॉर्ट डिस्क पर फैल गया क्योंकि इसके लिए बहुत छोटा था.work_memचरण 3: सही तरीके से अनुक्रमणित करें

-- Refresh statistics when estimates are wrong
ANALYZE orders;

-- Collect more detail on a column with skewed distribution
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

समग्र सूचकांक में कॉलम क्रम बहुत मायने रखता है।

नियम: पहले समानता कॉलम, फिर श्रेणी कॉलम, फिर आपके द्वारा क्रमबद्ध कॉलम। के साथ सबसे पहले, सूचकांक मेल खाने वाली पंक्तियों तक सीमित हो जाता है और फिर उन्हें

-- For: WHERE status = 'pending' AND created_at >= ... ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

द्वारा पहले से ही ऑर्डर किए गए को पढ़ता है , इसलिए सॉर्ट पूरी तरह से गायब हो जाता है। क्रम को उलट दें और सूचकांक बहुत कम उपयोगी हो जाता है।statusआंशिक सूचकांकcreated_at जब आप केवल किसी उपसमूह के बारे में पूछते हैं तो ये नाटकीय रूप से छोटे हो जाते हैं।

सूचकांकों को कवर करना तालिका को पूरी तरह से टालते हुए, क्वेरी का उत्तर अकेले सूचकांक से दिया जाए।

-- If 98% of orders are completed and you only query pending ones
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at DESC)
  WHERE status = 'pending';

हमेशा लाइव सिस्टम परके साथ इंडेक्स बनाएं , जो लिखने को रोकता नहीं है।

CREATE INDEX CONCURRENTLY idx_orders_lookup
  ON orders (user_id, created_at DESC)
  INCLUDE (total, status);
-- EXPLAIN then shows "Index Only Scan"

चरण 4: किसी सूचकांक को नज़रअंदाज़ क्यों किया जा रहा हैCONCURRENTLYएक सामान्य निराशा – सूचकांक मौजूद है और योजनाकार इसका उपयोग नहीं करेगा। सामान्य कारण:

कॉलम पर एक फ़ंक्शन लागू किया जाता है.

यह सूचकांक को अनुपयोगी बना देता है।

बेमेल टाइप। एक

-- ❌ Index on email cannot be used
WHERE lower(email) = 'user@example.com';

-- ✅ Index the expression instead
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users (lower(email));

की तुलना करना एक स्ट्रिंग पर कॉलम एक कास्ट को बाध्य करता है जो इंडेक्स को हरा देता है। अपने क्वेरी पैरामीटर में प्रकारों का मिलान करें.LIKE में अग्रणी वाइल्डकार्ड।bigint बी-ट्री इंडेक्स का उपयोग नहीं कर सकते। उसके लिए ट्रिग्राम इंडेक्स का उपयोग करें।

क्वेरी चयनात्मक नहीं है. LIKE '%term' यदि फ़िल्टर तालिका के बड़े हिस्से से मेल खाता है, तो अनुक्रमिक स्कैन वास्तव में तेज़ होता है और योजनाकार सही होता है। प्रत्येक “अप्रयुक्त सूचकांक” एक बग नहीं है।

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin (name gin_trgm_ops);
-- now ILIKE '%widget%' can use an index

चरण 5: एन+1 क्वेरी ठीक करेंडेटाबेस लोड का सबसे आम एप्लिकेशन-स्तरीय कारण, और आमतौर पर धीमी-क्वेरी लॉग में अदृश्य होता है क्योंकि प्रत्येक व्यक्तिगत क्वेरी तेज़ होती है।

ORM में, इसका अर्थ है उत्सुक लोडिंग –

सक्रिय रिकॉर्ड में,

-- 1 query for orders, then one per order for the user: 101 round trips
SELECT * FROM orders LIMIT 100;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;   -- ... and so on

-- ✅ One query
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT 100;

SQLAlchemy में,includes प्रिज्मा में. विकास में प्रति अनुरोध प्रश्नों की गिनती करके पैटर्न का पता लगाएं; सूची के आकार में अचानक उछाल हस्ताक्षर है।selectinloadचरण 6: पेजिनेशन दैट स्केल्सinclude आपका पेज जितना गहरा होगा, यह उतना ही धीमा होता जाएगा, क्योंकि डेटाबेस को प्रत्येक छोड़ी गई पंक्ति को उत्पन्न और त्यागना होगा।

सहित चूंकि टाईब्रेकर ऑर्डर को कुल बनाता है, जो टाइमस्टैम्प के टकराने पर पंक्तियों को छोड़े जाने या दोहराए जाने से रोकता है।

OFFSETचरण 7: बदलने लायक कॉन्फ़िगरेशन

-- ❌ Reads and throws away 100,000 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- ✅ Keyset pagination — constant time at any depth
SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)   -- values from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

डिफ़ॉल्ट रूढ़िवादी हैं और बहुत मामूली हार्डवेयर मानते हैं।idसेटिंग

मार्गदर्शन

सिस्टम RAM का लगभग 25%

random_page_costभारी लेखन ट्रैफ़िक वाली तालिकाओं के लिए, वैश्विक स्तर के बजाय विशेष रूप से उस तालिका पर ऑटोवैक्यूम को अधिक आक्रामक बनाएं।

-- Test the effect on a single session before changing it globally
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

भारी लेखन ट्रैफ़िक वाली तालिकाओं के लिए, वैश्विक स्तर के बजाय विशेष रूप से उस तालिका पर ऑटोवैक्यूम को अधिक आक्रामक बनाएं।

भारी लेखन ट्रैफ़िक वाली तालिकाओं के लिए, वैश्विक स्तर के बजाय विशेष रूप से उस तालिका पर ऑटोवैक्यूम को अधिक आक्रामक बनाएं।

-- Which tables are bloated and when were they last vacuumed?
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Rebuild a bloated table without a long exclusive lock
VACUUM (ANALYZE, VERBOSE) orders;

-- For severe bloat, rebuild indexes concurrently
REINDEX INDEX CONCURRENTLY idx_orders_status_created;

भारी लेखन ट्रैफ़िक वाली तालिकाओं के लिए, वैश्विक स्तर के बजाय विशेष रूप से उस तालिका पर ऑटोवैक्यूम को अधिक आक्रामक बनाएं।

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

अप्रयुक्त और डुप्लिकेट इंडेक्स ढूँढना

प्रत्येक सूचकांक लिखने को धीमा करता है और स्थान की खपत करता है। समय-समय पर उनका ऑडिट करें.

-- Indexes that have never been scanned
SELECT
  schemaname, relname AS table_name, indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

कार्य करने से पहले अपटाइम की जांच करें – पिछले सप्ताह पुनरारंभ होने के बाद से अप्रयुक्त सूचकांक मासिक रिपोर्ट के लिए आवश्यक हो सकता है।

निष्कर्ष

PostgreSQL को अनुकूलित करना एक अनुक्रम है, अनुमान नहीं: के साथ महंगी क्वेरी खोजें कुल समय के अनुसार क्रमबद्ध, पढ़ेंpg_stat_statements फ़िल्टर द्वारा हटाए गए अनुमान-बनाम-वास्तविक अंतराल और पंक्तियों के लिए, पहले समानता कॉलम और उसके बाद श्रेणी कॉलम के साथ समग्र अनुक्रमणिका जोड़ें, उत्सुक लोडिंग के साथ एन + 1 पैटर्न को खत्म करें, गहरे ऑफसेट को कीसेट पेजिनेशन के साथ बदलें, और सेट करेंEXPLAIN (ANALYZE, BUFFERS) SSDs के लिए उपयुक्त।random_page_cost फिर आँकड़ों को अद्यतन और ऑटोवैक्यूम ट्यून्ड रखें, क्योंकि पुराने आँकड़ों वाला एक अच्छी तरह से अनुक्रमित डेटाबेस अभी भी खराब योजनाएँ उत्पन्न करता है। फिर आँकड़ों को अद्यतन और ऑटोवैक्यूम ट्यून्ड रखें, क्योंकि पुराने आँकड़ों वाला एक अच्छी तरह से अनुक्रमित डेटाबेस अभी भी खराब योजनाएँ उत्पन्न करता है।