PostgreSQL Optimizasyon Rehberi: İndeksleme, Sorgu Analizi ve Performans

-- B-Tree indeks: En yaygin kullanim
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite indeks: Birden fazla kolon
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

-- Partial indeks: Sadece belirli kosulu saglayan satirlar
CREATE INDEX idx_orders_active ON orders (status, created_at)
WHERE status = 'active';

-- Covering indeks: Index-only scan icin ek kolonlar
CREATE INDEX idx_orders_covering ON orders (customer_id)
INCLUDE (total_amount, status);

-- GIN indeks: JSONB alanlari icin
CREATE INDEX idx_products_metadata ON products USING gin (metadata jsonb_path_ops);

-- BRIN indeks: Zaman serisi verileri icin
CREATE INDEX idx_logs_created_at ON application_logs USING brin (created_at)
WITH (pages_per_range = 32);

-- Expression indeks: Fonksiyon bazli sorgular icin
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- Yavas sorgu ornegi
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
  AND o.status = 'completed'
  AND o.total_amount > 100
ORDER BY o.created_at DESC
LIMIT 50;

/*
Ornek cikti (optimize edilmemis):

Limit  (cost=15234.56..15234.69 rows=50 width=72) (actual time=1523.456..1523.489 rows=50 loops=1)
  Buffers: shared hit=2341 read=8923
  ->  Sort  (cost=15234.56..15298.23 rows=25468 width=72) (actual time=1523.454..1523.478 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Hash Join  (cost=1234.56..14567.89 rows=25468 width=72) (actual time=45.123..1498.234 rows=25468 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Seq Scan on orders o  (cost=0.00..12345.67 rows=25468 width=48) (actual time=0.015..1423.567 rows=25468 loops=1)
                    Filter: ((created_at >= '2025-01-01') AND (status = 'completed') AND (total_amount > 100))
                    Rows Removed by Filter: 974532
                    Buffers: shared hit=1234 read=8923
              ->  Hash  (cost=934.56..934.56 rows=24000 width=28) (actual time=44.567..44.567 rows=24000 loops=1)
                    ->  Seq Scan on customers c  (cost=0.00..934.56 rows=24000 width=28)
Planning Time: 0.234 ms
Execution Time: 1523.678 ms
*/

-- Indeks ekledikten sonra:
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC)
INCLUDE (total_amount, customer_id)
WHERE status = 'completed';

-- Ayni sorgunun optimize edilmis plani:
/*
Limit  (cost=234.56..234.69 rows=50 width=72) (actual time=2.345..2.378 rows=50 loops=1)
  Buffers: shared hit=156
  ->  Nested Loop  (cost=234.56..1234.89 rows=12734 width=72) (actual time=2.343..2.371 rows=50 loops=1)
        ->  Index Only Scan using idx_orders_status_date on orders o
              (cost=0.42..567.89 rows=12734 width=48) (actual time=0.034..0.156 rows=50 loops=1)
              Index Cond: ((status = 'completed') AND (created_at >= '2025-01-01'))
              Filter: (total_amount > 100)
              Heap Fetches: 0
        ->  Index Scan using customers_pkey on customers c
              (cost=0.29..0.31 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=50)
              Index Cond: (id = o.customer_id)
Planning Time: 0.312 ms
Execution Time: 2.456 ms
*/

-- Extension'i etkinlestir
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- En yavas 10 sorguyu listele (toplam sure bazinda)
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- En cok cagirilan sorgular
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Istatistikleri sifirla (periyodik olarak yapin)
SELECT pg_stat_statements_reset();

-- N+1 problemi: Her siparis icin ayri musteri sorgulama
-- Sorgu 1: SELECT * FROM orders WHERE status = 'active';
-- Sorgu 2..N+1: SELECT * FROM customers WHERE id = ? (her siparis icin)

-- Cozum: JOIN ile tek sorguda cozme
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'active';

-- Alternatif cozum: IN clause ile batch sorgulama
SELECT * FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id FROM orders WHERE status = 'active'
);

-- Range partitioning: Tarih bazli bolumendirme
CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Aylik partitionlar olustur
CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE orders_2025_03 PARTITION OF orders
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Varsayilan partition (tanimsiz araliklar icin)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Her partition icin indeks olustur (otomatik miras alinir)
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status, created_at DESC);

; pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Pool ayarlari
pool_mode = transaction
default_pool_size = 25
min_pool_size = 5
max_client_conn = 200
reserve_pool_size = 5

; Timeout ayarlari
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 30

-- Autovacuum ayarlarini tablo bazinda ozellestir
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.02
);

-- Tablo sismesini (bloat) kontrol et
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;