POSTGRESQL INDEX STRATEJİSİ VE SORGU OPTİMİZASYONU

EXPLAIN ANALYZE
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY post_count DESC
LIMIT 10;

Limit  (cost=1543.21..1543.24 rows=10) (actual time=23.451..23.453 ms)
  -> Sort  (cost=1543.21..1556.87) (actual time=23.449..23.450 ms)
      -> HashAggregate  (cost=1283.45..1366.87) (...)
          -> Hash Left Join  (cost=125.00..1200.12) (actual time=5.2..18.3 ms)
              Hash Cond: (p.user_id = u.id)
              -> Seq Scan on posts p  (cost=0..823.45 rows=30000) ← PROBLEM!
              -> Hash  (cost=100.00..100.00)
                  -> Index Scan on users u  (cost=0.29..100.00)
                      Index Cond: (created_at > '2025-01-01')
Planning Time: 1.234 ms
Execution Time: 23.892 ms

-- Tekil index
CREATE INDEX idx_posts_user_id ON posts(user_id);
 
-- Composite index — sıra önemli
-- WHERE user_id = x AND status = 'PUBLISHED' sorgusuna uygun
CREATE INDEX idx_posts_user_status ON posts(user_id, status);
 
-- user_id tek başına da bu index'i kullanır (öncü sütun)
-- status tek başına KULLANAMAZ
 
-- Descending — ORDER BY published_at DESC için
CREATE INDEX idx_posts_published_desc ON posts(published_at DESC NULLS LAST);

-- Sadece PUBLISHED statuslü postları index'le
-- Tablonun %20'si published ise → %80 daha küçük index
CREATE INDEX idx_posts_published ON posts(published_at)
WHERE status = 'PUBLISHED';
 
-- Soft delete pattern — deleted_at NULL olmayanları atla
CREATE INDEX idx_users_active ON users(email)
WHERE deleted_at IS NULL;
 
-- Bu sorgu partial index kullanır:
SELECT * FROM posts
WHERE status = 'PUBLISHED' AND published_at > NOW() - INTERVAL '7 days';

-- Full-text search
ALTER TABLE posts ADD COLUMN search_vector tsvector;
UPDATE posts SET search_vector = to_tsvector('turkish', title || ' ' || body);
 
CREATE INDEX idx_posts_fts ON posts USING GIN(search_vector);
 
-- Arama sorgusu
SELECT title, ts_rank(search_vector, query) AS rank
FROM posts, to_tsquery('turkish', 'python & veri:*') query
WHERE search_vector @@ query
ORDER BY rank DESC;
 
-- JSONB için GIN
CREATE INDEX idx_posts_meta ON posts USING GIN(meta);
 
-- meta->>'author' = 'Adem' gibi sorgular bu index'i kullanır
SELECT * FROM posts WHERE meta @> '{"author": "Adem"}';

-- Index + gerekli sütunları birlikte sakla — heap fetch yok
CREATE INDEX idx_posts_cover ON posts(user_id, status)
INCLUDE (title, published_at, slug);
 
-- Bu sorgu sadece index'i okur, heap'e gitmez (Index Only Scan)
SELECT title, published_at, slug
FROM posts
WHERE user_id = 42 AND status = 'PUBLISHED';

-- KÖTÜ: N+1 — her kullanıcı için ayrı sorgu
SELECT id FROM users WHERE active = true;
-- Sonra her user_id için: SELECT * FROM posts WHERE user_id = ?
 
-- İYİ: Tek sorgu, JOIN ile
SELECT
    u.id,
    u.name,
    json_agg(
        json_build_object('id', p.id, 'title', p.title)
        ORDER BY p.published_at DESC
    ) FILTER (WHERE p.id IS NOT NULL) AS posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id AND p.status = 'PUBLISHED'
WHERE u.active = true
GROUP BY u.id, u.name;

-- Tablo istatistiklerini güncelle (planner için)
ANALYZE posts;
 
-- Ölü tuple'ları temizle
VACUUM posts;
 
-- Index bloat kontrolü
SELECT
    relname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname LIKE 'idx_posts%'
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- Kullanılmayan index'leri bul
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS scan_count,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 10  -- nadiren kullanılan
ORDER BY pg_relation_size(indexrelid) DESC;

-- Eklentiyi etkinleştir (postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'
 
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- En yavaş 10 sorgu
SELECT
    substring(query, 1, 80) AS query_preview,
    calls,
    round(total_exec_time::numeric / calls, 2) AS avg_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 50
ORDER BY avg_ms DESC
LIMIT 10;

; pgbouncer.ini
[databases]
codeforge = host=127.0.0.1 port=5432 dbname=codeforge
 
[pgbouncer]
pool_mode = transaction  ; transaction bazlı havuz (en verimli)
max_client_conn = 1000
default_pool_size = 20
server_idle_timeout = 300

Olmadan: Her request → yeni PG bağlantısı → ~25ms overhead
PgBouncer ile: Bağlantı havuzundan al → <1ms overhead

-- Pahalı raporlama sorgusunu materialized view yap
CREATE MATERIALIZED VIEW mv_kullanici_istatistikleri AS
SELECT
    u.id,
    u.name,
    COUNT(p.id) AS post_count,
    SUM(p.view_count) AS total_views,
    MAX(p.published_at) AS last_post_at
FROM users u
LEFT JOIN posts p ON p.user_id = u.id AND p.status = 'PUBLISHED'
GROUP BY u.id, u.name
WITH DATA;
 
CREATE UNIQUE INDEX ON mv_kullanici_istatistikleri(id);
 
-- Periyodik yenile (cron veya trigger)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_kullanici_istatistikleri;
 
-- Hızlı okuma
SELECT * FROM mv_kullanici_istatistikleri ORDER BY total_views DESC LIMIT 20;