Yüksek Trafikli Uygulamalar İçin Veritabanı Optimizasyonu

“Daha büyük bir veritabanına ihtiyacımız var” tartışmalarının çoğu, aslında “beş sorguyu düzeltmemiz gerekiyor” tartışmalarıdır. Donanım, sorun olmaya başlayana kadar ucuzdur; yavaş bir uygulamaya RDS instance büyütmek, bir sorgu planı okumaktan kaçınmanın en pahalı yoludur.

Bu, bir IWWOMI müşterisi bize veritabanının yandığını söylediğinde uyguladığımız oyun planı. Çoğu üretim sistemi PostgreSQL üzerinde çalıştığı için onu varsayıyoruz, ancak ilkeler MySQL, SQL Server veya SQL konuşan başka her şeye doğrudan uyarlanabilir.

80/20 kuralı aslında 95/5’e daha yakın

Herhangi bir üretim DB’sinden yavaş sorgu loglarını çekin ve her seferinde aynı şekli göreceksiniz: bir avuç sorgu, veritabanı zamanının ezici çoğunluğundan sorumlu. 80/20 değil, 95/5. Saniyede 200 kez çalışan tek bir kötü sorgu, iyi yazılmış yüzlerce sorgunun maliyetini gölgede bırakır.

Bu önemli, çünkü çabanızı nereye harcayacağınızı değiştirir. Şemayı yeniden yazmayın. Farklı bir veritabanı motoruna geçmeyin. Toplam süreye göre en yavaş on sorguyu bulun ve onları düzeltin.

PostgreSQL’de pg_stat_statements eklentisi size tam olarak bunu verir:

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

mean_exec_time yerine total_exec_time’a göre sıralayın. Günde bir kez çalışan ve 2 saniye süren bir sorgu, dakikada on bin kez çalışan ve 5ms süren bir sorgudan daha az önemlidir.

En pahalı beş sorgunuzun adını ezberden söyleyemiyorsanız, optimizasyon yapmıyorsunuz — tahmin ediyorsunuz.

İndeksleme: ne zaman eklemeli, ne zaman uzak durmalı

İndeksler bedava gibi görünür. Değildir. Her indeks, veritabanının indekslenmiş sütunlara dokunan her INSERT, UPDATE ve DELETE’te güncellemesi gereken paralel bir veri yapısıdır. Buna write amplification denir ve “sorgulanabilecek her şeye indeks ekleyeyim” içgüdünüzün eninde sonunda yazma kapasitenizi öldürmesinin sebebidir.

10M satırlık bir tabloda eksik bir indeks, bir sorguyu 5ms’den 4 saniyeye çıkarabilir. Yazma-yoğun bir tabloda aynı indeks, bir transaction’ı 2ms’den 20ms’ye çıkarabilir. Her iki sayı da önemlidir.

Uyguladığımız kurallar:

• WHERE, JOIN ON ve ORDER BY’da kullanılan sütunları indeksleyin. Bir sütun bunların hiçbirinde geçmiyorsa, indekse ihtiyacı yoktur.
• Composite indekslerde en seçici sütunla başlayın. (tenant_id, created_at) üzerindeki bir indeks, her sorgu sadece created_at ile filtreliyorsa işe yaramaz.
• Çoğu satır ilgisizse partial index kullanın. orders tablonuzun %99’u status = 'completed' ise ve siz sadece status = 'pending' sorguluyorsanız, partial index 100x daha küçük ve hızlıdır:

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

• Kullanılmayan indeksleri silin. pg_stat_user_indexes, idx_scan sayısı sıfır olan indeksleri size söyler. Onlar saf maliyettir.

B-tree indekslerinin nasıl çalıştığına dair daha derin bir kaynak için use-the-index-luke.com hâlâ internetteki en iyi referans.

N+1 problemi ve ORM’inizin neden yalan söylediği

N+1 sorgu problemi, code review’larda en sık karşılaştığımız performans hatasıdır. Masum görünür:

posts = Post.objects.all()
for post in posts:
    print(post.author.name)

Postlar için bir sorgu, sonra yazarlar için N sorgu. 500 post ile veritabanına 501 round trip yaptınız. Round trip başına 2ms’den, toplamda 5ms sürmesi gereken sorgulardan bir saniyelik yanıt süresi çıkardınız.

ORM’ler bunu gizler çünkü post.author’ı bir property erişimi gibi gösterirler. Değildir — bir network çağrısıdır. Her ORM’in çözümü vardır: Django’da select_related / prefetch_related, Rails’te includes, Sequelize’da eager loading, EF Core’da Include. Kendinizinkini öğrenin ve kullanın.

Düzeltme:

posts = Post.objects.select_related('author').all()

Tek sorgu. Bitti.

EXPLAIN ANALYZE’ı ağlamadan okumak

EXPLAIN ANALYZE, veritabanı araç kutunuzdaki en önemli araçtır. Geliştiricilerin çoğu çalıştırır, bir duvar dolusu metin görür ve sekmeyi kapatır. Kapatmayın.

Şu sorguyu ele alın:

EXPLAIN ANALYZE
SELECT u.email, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.email;

Sağlıklı bir plan aşağıdan yukarıya okunur ve şöyle görünür:

HashAggregate  (cost=... rows=12000) (actual time=180.2..183.4 rows=11842 loops=1)
  ->  Hash Right Join  (actual time=20.1..150.3 rows=84000 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (actual time=0.01..40.2 rows=500000 loops=1)
        ->  Hash  (actual time=18.9..18.9 rows=12000 loops=1)
              ->  Index Scan using idx_users_created_at on users u
                    (actual time=0.03..15.2 rows=12000 loops=1)

Sırayla üç şeye bakın:

1. Büyük tabloda Seq Scan. Şu Seq Scan on orders 500K satır okuyor. Eğer siparişleri genelde kullanıcıya göre filtreliyorsanız, orders.user_id üzerine bir indeks ekleyin.
2. Tahmini satır vs gerçek satır. Planner bir adımın 100 satır döndüreceğini düşünüyorsa ve gerçekte 100.000 dönüyorsa, istatistikleriniz eskimiş demektir. ANALYZE çalıştırın.
3. En yavaş node. Süre, ağaçta yukarı doğru birikir. Bir node’un actual time’ı ile çocuklarının actual time’ı arasındaki farkın en büyük olduğu noktayı bulun. İş orada yapılıyor.

Bağlantı havuzlama: PgBouncer ya da çöküş

PostgreSQL bağlantıları ağırdır. Her bağlantı bir backend process’i fork eder ve birkaç megabayt bellek ayırır. 100 bağlantı için yapılandırılmış bir Postgres instance’ı, Node.js uygulama filonuz 800 bağlantı istemeye karar verdiği anda devrilir.

PgBouncer kullanın. Uygulamanızla Postgres arasına koyun. Minimal üretim konfigürasyonu:

[databases]
appdb = host=db.internal port=5432 dbname=appdb

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

pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600

server_tls_sslmode = require

En önemli iki ayar:

• pool_mode = transaction durumların %95’inde istediğiniz şey. Tek bir Postgres bağlantısının binlerce client’a hizmet vermesini sağlar; transaction’lar arasında bağlantıyı devreder. Yakalanma noktası: LISTEN/NOTIFY, prepared statement’lar (workaround olmadan) veya geçici tablolar gibi session düzeyi özellikleri kullanamazsınız.
• default_pool_size = 25, PgBouncer’ın her veritabanı+kullanıcı çifti için en fazla 25 bağlantı açacağı anlamına gelir. 2000 client bağlantısı 25 sunucu bağlantısını paylaşıyorsa, Postgres bağlantı bütçenizde 80x amplifikasyon elde ediyorsunuz.

Cloud-managed Postgres için RDS Proxy ve eşdeğer servisler hakkında notlar cloud migration rehberimizde var.

Caching: Redis, Memcached ve bilgisayar bilimindeki en zor problem

Bilgisayar biliminde iki zor şey vardır: isim koymak, cache invalidation ve off-by-one hataları. Birincisinde yardımcı olamayız.

Redis vs Memcached: çoğu durum için Redis’i seçin. Memcached saf key-value yükleri için daha basit ve biraz daha hızlıdır, ancak Redis size sorted set, pub/sub, Lua scripting, persistence ve replikasyon verir. Performans farkı eskisinden daha küçük, özellik farkı ise hâlâ büyük.

Cache invalidation stratejileri, ne kadar acı verdiklerine göre sıralanmış:

1. Sadece TTL. Bir TTL ayarlayın ve o pencere içinde eski verinin var olmasına izin verin. En basit. Eski verinin 30 saniye ila 5 dakika kabul edilebilir olduğu durumlarda işe yarar.
2. Write-through. Her yazımda cache’i ve veritabanını aynı transaction-benzeri akışta güncelleyin. Eskime yok, ama hata yüzeyini ikiye katlar.
3. Cache-aside, açık invalidation ile. Cache’ten oku, miss’te DB’ye düş. Yazımlarda cache anahtarını sil. Çoğu uygulama için varsayılan. Tuzak: değişen veriden türeyen her anahtarı invalidate etmelisiniz ve liste türündeki cache’ler unutmaya müsaittir.
4. Event-driven invalidation. DB’den (WAL, CDC veya uygulama event’leri yoluyla) değişiklik event’leri yayınlayın ve cache tüketicileri kendilerini invalidate etsin. Güçlü, ama artık bir dağıtık sistemin var.

Hesaplaması pahalı, yanlış olması ucuz olanı cache’leyin. Kullanıcı bakiyelerini cache’lemeyin. Anasayfa feed’ini cache’leyin.

Read replica ve sharding: ne zaman

Read replica’lar kolaydır. Bir tane provision edin, okuma-sadece sorguları oraya yönlendirin, bitti. İki tuzak:

• Replikasyon gecikmesi. Bir read replica her zaman primary’nin birkaç milisaniye (yüksek yükte saniye) gerisindedir. Bir kullanıcı yorum yazıp hemen sayfayı replica’dan okumak için yeniden yüklerse, yorum kayıptır. Ya post-write okumaları primary’ye yönlendirin ya da eventual consistency’yi kabul edin.
• Aslında yazma yükünü azaltmıyorsunuz. Replica’lar her yazımı tekrar oynatır. Okumayı ölçeklerler, yazımı değil.

Sharding zordur. Mecbur kalana kadar yapmayın. Mecbur olduğunuzun sinyalleri:

• Primary’nizin yazma IOPS’u doygun ve dikey ölçekleme bitti.
• Tek bir tablo o kadar büyük ki bakım operasyonları (VACUUM, indeks build’leri, yedekler) bakım pencerenizden uzun sürüyor.
• Zaten uygulama düzeyi multi-tenancy yapıyorsunuz ve tenant-keyed bir sharding şeması doğal.

Sharding bir operasyonel sorunu N operasyonel soruna dönüştürür. Benimsemeyi düşünüyorsanız, microservices mimarisi yazısındaki database-per-service deseninin daha az koordinasyon maliyetiyle aynı faydanın çoğunu sağlayıp sağlamadığını değerlendirin.

PostgreSQL’e özgü: VACUUM, partial index, JSONB

Üretimde ısıran birkaç PostgreSQL detayı:

VACUUM opsiyonel değildir. PostgreSQL MVCC kullanır, bu da update ve delete’lerin satırları silmek yerine ölü olarak işaretlediği anlamına gelir. VACUUM o alanı geri kazanır. autovacuum bunu arka planda halleder, ancak yüksek-yazımlı tablolarda varsayılanlar fazla muhafazakârdır. Tablo-bazlı ayarlayın:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

Bu, autovacuum’u varsayılan %20 yerine %5 ölü tuple’da tetikler. 100M satırlık bir tabloda bu, 5M ölü satırda vacuum yapmakla 20M’de yapmak arasındaki farktır.

JSONB indeksleme. JSONB harikadır, ta ki içinde indekssiz sorgu yapıp 50GB’lık bir tabloda sequential scan keşfedene kadar. GIN indeksleri JSONB containment sorgularını hızlandırır:

CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

-- Bu sorgu artık indeksi kullanır
SELECT * FROM events WHERE payload @> '{"type": "checkout"}';

Sadece containment (@>) gerektiğinde jsonb_path_ops kullanın; varsayılan GIN operator class’ından daha küçük ve daha hızlıdır.

Soft-delete için partial index. Tablolarınızda bir deleted_at sütunu varsa ve her sorgu WHERE deleted_at IS NULL ile filtreliyorsa, her indeks partial olmalıdır:

CREATE INDEX idx_users_email
ON users (email)
WHERE deleted_at IS NULL;

Daha küçük indeks, daha hızlı tarama ve sorgunuz predicate ile eşleştiğinde planner’ın otomatik olarak kullandığı bir indeks.

Monitoring optimizasyonun kendisidir

Ölçmediğinizi optimize edemezsiniz. Minimum:

• pg_stat_statements etkin ve haftalık sorgulanıyor.
• Slow query log eşiği geliştirme ortamında 100ms, üretimde 500ms.
• Bağlantı sayısı, replikasyon gecikmesi, cache hit ratio (hedef: %99’un üzerinde) ve transaction oranı için dashboardlar.
• Autovacuum gecikmesi, dakika başına deadlock ve uygulamanızın toleransını aşan replica gecikmesi için alarmlar.

Daha geniş observability stack’ini DevOps en iyi uygulamaları yazısında ele aldık — veritabanı monitoring oraya oturur.

Daha derin okuma için resmi PostgreSQL performans dokümantasyonu iyi ve kısa.

Performans denetimine mi ihtiyacınız var?

Uygulamanız yavaşsa ve sorunun veritabanı mı, uygulama kodu mu yoksa altyapı mı olduğunu söyleyemiyorsanız, IWWOMI’nin en sık yaptığı denetim tam olarak budur. Sorgu loguna, planlara, indekslere ve şemaya dalarız ve latency eğrisini gerçekten hareket ettirecek beş şeyi öncelik sırasına dizilmiş bir liste olarak getiririz.

İletişime geçin, bir bakalım.