PostgreSQL Query Optimization: Slow से Sub-Millisecond तक | Akousa

6 नवंबर 202522 मिनट पढ़ने का समय

PostgreSQL Query Optimization: Slow से Sub-Millisecond तक

Production databases पर मेरी PostgreSQL optimization techniques। EXPLAIN ANALYZE, index strategies, query rewrites, और वो exact changes जिन्होंने query times seconds से microseconds में काट दिए।

पिछले महीने मुझे रात 2 बजे Slack message आया: "Dashboard timeout हो रहा है।" मैंने production box में SSH किया, pg_stat_activity खोला, और एक single query मिली जो 12 results return करने के लिए 14 million rows scan कर रही थी। Fix एक partial index था जिसे बनाने में 30 seconds लगे। Query 4.2 seconds से 0.3 milliseconds हो गई।

PostgreSQL performance की बात ही यही है। Problems लगभग कभी exotic नहीं होतीं। Missed indexes, bad statistics, queries जो 10,000 rows वाली table पर सही थीं लेकिन अब 10 million हैं। Fixes usually simple हैं — एक बार जब पता हो कहां देखना है।

यह post production databases चलाने से PostgreSQL query optimization के बारे में मैंने जो कुछ सीखा है वो सब है। बिना practice के कोई theory नहीं। यहां हर technique ने real systems पर मेरा real time बचाया है।

Debugging Mindset: Guess मत करो, Measure करो

Slow queries के साथ developers की सबसे बड़ी गलती guessing है। "शायद हमें उस column पर index चाहिए।" "शायद JOIN slow है।" "शायद हमें और RAM add करनी चाहिए।"

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

sql

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- अच्छा: equality पहले, फिर range, फिर sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- बुरा: equality से पहले range
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

sql

-- हम बार-बार run करते हैं:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

sql

-- केवल 2% orders 'pending' हैं, लेकिन हम उन्हें लगातार query करते हैं
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

sql

-- एक और common pattern: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- सिर्फ active records पर unique constraint
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

sql

-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- अब यह fast है:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

sql

-- IP range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Temporal ranges (scheduling conflicts)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Overlapping bookings ढूंढो:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

sql

-- बड़े text columns पर equality-only lookups के लिए अच्छे
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- यह fast है:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- यह hash index इस्तेमाल नहीं कर सकता:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

sql

-- Unused indexes ढूंढो
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- primary key और unique constraints exclude
  )
ORDER BY pg_relation_size(indexrelid) DESC;

sql

-- Duplicate indexes ढूंढो (same columns, same order)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

python

# Python / SQLAlchemy — classic trap
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # यह हर ORDER के लिए separate query trigger करता है
    print(f"Customer: {order.customer.name}")

sql

-- Query 1: सभी pending orders लाओ
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Order 1 का customer लाओ
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Order 2 का customer लाओ
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Order 500 का customer लाओ
SELECT * FROM customers WHERE id = 600;

sql

-- 0ms से ज़्यादा लेने वाली सभी queries log करो (यानी, सभी queries)
SET log_min_duration_statement = 0;
 
-- Production के लिए बेहतर: सिर्फ slow queries log करो
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms threshold
SELECT pg_reload_conf();

sql

-- Slow queries के लिए auto_explain enable करो
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

python

# SQLAlchemy — joinedload same query में customers fetch करता है
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

sql

-- Approach 1: Single JOIN (one-to-many में duplicate data produce कर सकता है)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Approach 2: दो queries (अगर orders में बहुत columns हैं तो कम data transfer)
SELECT * FROM orders WHERE status = 'pending';
-- application order_ids collect करती है = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

sql

-- WHERE में subquery — अक्सर ठीक, कभी-कभी terrible
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — usually सबसे अच्छी choice
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — PG 11 और below में materialized, PG 12+ में optimized
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

sql

-- EXISTS — पहले match पर रुक जाता है, existence check के लिए great
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN subquery के साथ — पहले पूरा result set बनाता है
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — अगर returns में per order multiple rows हैं तो duplicates produce कर सकता है
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

sql

-- बुरा: सभी 25 columns fetch करता है जिसमें kilobytes data वाला TEXT column शामिल
SELECT * FROM products WHERE category = 'electronics';
 
-- अच्छा: सिर्फ वही fetch करो जो चाहिए
SELECT id, name, price FROM products WHERE category = 'electronics';

sql

-- Slow: correlated subquery हर row के लिए एक बार run होती है
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Fast: window functions single pass में compute करती हैं
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

sql

-- Slow: correlated subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Fast: DISTINCT ON (PostgreSQL-specific, बहुत useful)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- यह भी fast: ROW_NUMBER window function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

sql

-- Page 1: fast
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Page 500: slow — PostgreSQL को 9,980 rows scan और discard करनी पड़ती हैं
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Page 5000: बहुत slow — 20 return करने के लिए 99,980 rows scan
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

sql

-- First page
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Next page: पिछले page की last id इस्तेमाल करो
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

sql

-- Single table analyze करो
ANALYZE orders;
 
-- पूरा database analyze करो
ANALYZE;
 
-- Column की current statistics देखो
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

sql

-- Autovacuum status check करो
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

sql

-- High-write tables के लिए ज़्यादा aggressive autovacuum
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- 1% dead tuples पर vacuum (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- 0.5% changes पर analyze (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- faster vacuum (newer PG में default 2ms)
);

sql

-- Table bloat estimate करो (simplified version)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool mode
pool_mode = transaction
 
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

javascript

const pool = new Pool({
  host: "127.0.0.1",
  port: 6432, // PgBouncer port, PostgreSQL port नहीं
  database: "mydb",
  max: 20, // PgBouncer default_pool_size से match करता है
  statement_timeout: 30000,
  // PgBouncer transaction mode के लिए prepared statements disable करो
  prepare: false,
});

sql

-- Total execution time से top 10 queries
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

sql

-- इस table पर कौन से indexes हैं?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

sql

-- Index concurrently बनाओ (table lock नहीं करता)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verify करो index इस्तेमाल हो रहा है
EXPLAIN (ANALYZE, BUFFERS) <slow query>;

sql

-- work_mem: sorts और hash joins के लिए memory (per operation, per connection नहीं)
-- Default 4MB है, जो complex queries के लिए बहुत कम है
SET work_mem = '64MB';  -- यह try करो और EXPLAIN ANALYZE फिर से run करो
 
-- effective_cache_size: planner को बताता है कितना disk cache expect करे
-- Total RAM का ~75% set करो
SHOW effective_cache_size;
 
-- random_page_cost: random I/O से sequential I/O का ratio
-- Default 4.0 है, SSDs के लिए 1.1-1.5 set करो
SHOW random_page_cost;

sql

-- Changes के बाद fresh data देखने के लिए pg_stat_statements reset करो
SELECT pg_stat_statements_reset();
 
-- एक घंटे/दिन बाद check करो
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

sql

-- 1. pg_stat_statements install करो
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Slow query logging enable करो (50ms मेरा threshold है)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-appropriate settings
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Anemic default से work_mem बढ़ाओ
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. effective_cache_size RAM का 75% set करो
-- 16GB server के लिए:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. shared_buffers RAM का 25% set करो
-- 16GB server के लिए:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Autovacuum responsiveness improve करो
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Changes apply करो
SELECT pg_reload_conf();
-- Note: shared_buffers restart चाहिए

sql

-- जिन tables को attention चाहिए
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Debugging Mindset: Guess मत करो, Measure करो

EXPLAIN — Blueprint

EXPLAIN ANALYZE — सच

EXPLAIN (ANALYZE, BUFFERS) — पूरी तस्वीर

EXPLAIN Options जो मैं Actually इस्तेमाल करता हूं

Query Plans को Detective की तरह पढ़ना

Seq Scan — Full Table Scan

Index Scan — Targeted Lookup

Index Only Scan — Holy Grail

Bitmap Scan — बीच का रास्ता

Bad Estimates पकड़ना

Index Strategy: सही Job के लिए सही Index

B-tree — Default Workhorse

INCLUDE के साथ Covering Indexes

Partial Indexes — सिर्फ वही Index करो जो Matter करता है

GIN Indexes — Full-Text Search और JSONB

GiST Indexes — Geometric और Range Types

Hash Indexes — Niche लेकिन Useful

जब Indexes नुकसान करते हैं

N+1 Query Problem

N+1 कैसा दिखता है

Logs में N+1 Detect करना

Fix: Eager Loading या JOINs

JOINs vs Multiple Queries

Common Query Rewrites

Subquery vs JOIN vs CTE

EXISTS vs IN vs JOIN

SELECT * से बचो

Window Functions vs Subqueries

Pagination सही तरीके से

Table Statistics और Vacuuming

ANALYZE: Statistics Update करो

Autovacuum Tuning

Table Bloat

Connection Pooling

Connection Pooler क्यों चाहिए

PgBouncer Configuration

Transaction vs Session Mode

Pool Sizing Formula

Practical Checklist: हर Slow Query के लिए Exact Steps

Step 1: Actual Query लो

Step 2: EXPLAIN (ANALYZE, BUFFERS) Run करो

Step 3: Table Statistics Check करो

Step 4: Existing Indexes Check करो

Step 5: Indexes बनाओ या Modify करो

Step 6: Query Rewrites Consider करो

Step 7: Server Settings Check करो

Step 8: Fix के बाद Monitor करो

Bonus: Quick Wins जो मैं हर नए Database पर Apply करता हूं

अंतिम विचार

संबंधित पोस्ट

How to Compress Images Without Losing Quality: The Complete 2026 Guide

2026 में Modern Authentication: JWT, Sessions, OAuth, और Passkeys