কিভাবে 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;

অনুসারে সাজানমোট সময় বরং মানে. একটি ক্যোয়ারী যা 20ms সময় নেয় কিন্তু ঘন্টায় 100,000 বার চালানোর জন্য দিনে দুবার 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)

ধাপ 2: সঠিকভাবে ব্যাখ্যা বিশ্লেষণ পড়ুন

সর্বদা ব্যবহার করুন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 এটির জন্য খুব ছোট ছিল।

-- 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;

ধাপ 3: সঠিক পথের সূচী

একটি যৌগিক সূচকে কলামের ক্রম অত্যন্ত গুরুত্বপূর্ণ। নিয়ম: প্রথমে সমতা কলাম, তারপর ব্যাপ্তি কলাম, তারপর কলাম আপনি সাজান।

-- 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"

সর্বদা লাইভ সিস্টেমে সূচী তৈরি করুনCONCURRENTLY, যা লেখা ব্লক করে না।

ধাপ 4: কেন একটি সূচক উপেক্ষা করা হচ্ছে

একটি সাধারণ হতাশা – সূচকটি বিদ্যমান এবং পরিকল্পনাকারী এটি ব্যবহার করবেন না। স্বাভাবিক কারণ:

কলামে একটি ফাংশন প্রয়োগ করা হয়। এটি সূচকটিকে অব্যবহারযোগ্য করে তোলে।

-- ❌ 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));

টাইপ অমিল। তুলনা করা হচ্ছেbigint একটি স্ট্রিং থেকে কলাম একটি কাস্টকে বাধ্য করে যা সূচককে পরাজিত করে। আপনার ক্যোয়ারী প্যারামিটারের প্রকারের সাথে মিল করুন।

LIKE-এ অগ্রণী ওয়াইল্ডকার্ড। 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: N+1 প্রশ্ন ঠিক করুন

ডাটাবেস লোডের সবচেয়ে সাধারণ অ্যাপ্লিকেশান-স্তরের কারণ, এবং সাধারণত ধীর-ক্যোয়ারী লগগুলিতে অদৃশ্য থাকে কারণ প্রতিটি পৃথক ক্যোয়ারী দ্রুত হয়।

-- 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;

একটি ORM-এ, এর অর্থ হল উৎসুক লোড হচ্ছে —includes সক্রিয় রেকর্ডে,selectinload SQLAlchemy,include প্রিজমায়। ডেভেলপমেন্টে অনুরোধ প্রতি প্রশ্ন গণনা করে প্যাটার্ন সনাক্ত করুন; তালিকার আকার সহ একটি আকস্মিক লাফ হল স্বাক্ষর।

ধাপ 6: পেজিনেশন যা দাঁড়িপাল্লা

OFFSET আপনার পৃষ্ঠার গভীরতা ধীর হয়ে যায়, কারণ ডাটাবেসকে অবশ্যই প্রতিটি বাদ দেওয়া সারি তৈরি এবং বাতিল করতে হবে।

-- ❌ 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 একটি টাইব্রেকার হিসাবে অর্ডারিং টোটাল করে, যা টাইমস্ট্যাম্পের সংঘর্ষের সময় সারিগুলি এড়িয়ে যাওয়া বা পুনরাবৃত্তি হওয়া প্রতিরোধ করে।

ধাপ 7: কনফিগারেশন যোগ্য পরিবর্তন

ডিফল্টগুলি রক্ষণশীল এবং খুব শালীন হার্ডওয়্যার ধরে নেয়।

random_page_cost সবচেয়ে প্রভাবশালী এবং কম পরিচিত। 4.0 এর ডিফল্ট অনুমান করে যে র্যান্ডম রিডগুলি ক্রমিকগুলির তুলনায় চারগুণ বেশি ব্যয়বহুল, যা যান্ত্রিক ড্রাইভের জন্য সত্য ছিল। এসএসডি-তে এটি পরিকল্পনাকারীকে সূচীগুলিকে ব্যবহার করা উচিত এড়াতে বাধ্য করে।

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

ধাপ 8: ব্লোট এবং ভ্যাকুয়াম

পোস্টগ্রেএসকিউএল পুরানো সারি সংস্করণগুলিকে ভ্যাকুয়াম পুনরায় দাবি না করা পর্যন্ত রাখে। ভারী আপডেট বা মুছে ফেলার ফলে ট্র্যাফিক ফুলে যায়, যা স্ক্যানকে প্রয়োজনের চেয়ে বেশি পৃষ্ঠা পড়তে বাধ্য করে।

-- 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 মোট সময় অনুসারে সাজানো, পড়ুনEXPLAIN (ANALYZE, BUFFERS) ফিল্টার দ্বারা মুছে ফেলা অনুমান-বনাম-বাস্তব ফাঁক এবং সারিগুলির জন্য, সমতা কলামগুলির সাথে প্রথমে এবং রেঞ্জ কলামগুলির সাথে যৌগিক সূচী যোগ করুন, আগ্রহী লোডিং সহ N+1 প্যাটার্নগুলি মুছে ফেলুন, কীসেট পৃষ্ঠাঙ্কন দিয়ে গভীর অফসেট প্রতিস্থাপন করুন, এবং সেট করুনrandom_page_cost SSD-এর জন্য যথাযথভাবে। তারপরে পরিসংখ্যানগুলিকে বর্তমান এবং অটোভ্যাকুয়াম টিউন রাখুন, কারণ বাসি পরিসংখ্যান সহ একটি ভাল-সূচীযুক্ত ডাটাবেস এখনও খারাপ পরিকল্পনা তৈরি করে।