PostgreSQL: index, EXPLAIN, MVCC, izolasyon ve kilitleme

Bu bölümde

Bu bölümde PostgreSQL'in bir sorguyu nasıl çalıştırdığını iki eksende öğreneceksin: veriyi nasıl bulduğu (B-tree, composite/covering/partial/expression index, EXPLAIN) ve aynı veriye eşzamanlı erişimi nasıl yönettiği (MVCC, izolasyon seviyeleri, satır kilitleri, deadlock). Mülakatta bu konu "bu sorgu için hangi index", "index var ama neden kullanılmıyor", "puanlar neden kayboluyor", "PostgreSQL'in varsayılan izolasyonu ne ve ne garanti eder" gibi kod okuma ve soru-cevap formatında gelir. Bankacılık geçmişin burada avantajdır: lost update, write skew ve deadlock'u production'da görmüş biri gibi anlatabilmelisin. Sayısal örnekler PostgreSQL 16 ve 17 üzerinde ölçüldü; mutlak süreler makineye göre değişir, oranlar önemlidir.

Konu anlatımı

B-tree index nasıl çalışır

PostgreSQL'de tablo verisi heap denen, sırasız 8 KB'lık sayfalarda durur. Her satır sürümünün fiziksel adresi bir TID'dir (ctid: sayfa numarası, sayfa içi sıra). Index ayrı bir yapıdır ve tablo satırlarını değil, anahtar → TID çiftlerini tutar.

B-tree (varsayılan index tipi) dengeli bir ağaçtır:

• Kök ve iç sayfalar sadece yön bulmak içindir: "bu anahtar aralığı için şu alt sayfaya git".
• Yaprak sayfalar anahtarları sıralı tutar ve her anahtarın yanında heap'teki satırın TID'i vardır. Yaprak sayfalar komşularına bağlıdır; aralık taramasında ağaçta tekrar yukarı çıkmadan yandaki sayfaya geçilir.
• Bir sayfaya yüzlerce anahtar sığdığı için ağaç çok sığdır: milyonlarca, hatta milyarlarca satırda bile kökten yaprağa genelde 3-4 seviye vardır. Bir eşitlik araması bu yüzden birkaç sayfa okumasıdır.

Bir Index Scan şunu yapar: kökten yaprağa iner, koşulu sağlayan ilk anahtarı bulur, yaprak üzerinde sıralı ilerler ve her TID için heap'e gidip satırı okur. Heap'e gitmek rastgele I/O'dur ve maliyetin büyük kısmı buradadır. Index sıralı olduğu için ORDER BY ile aynı sırada okunursa ayrı bir Sort adımına gerek kalmaz; B-tree iki yönde de taranabildiği için tek kolonlu bir ASC index ORDER BY ... DESC'e de hizmet eder.

B-tree'nin desteklediği operatörler: =, <, <=, >, >=, BETWEEN, IN, IS NULL/IS NOT NULL ve uygun collation'da ön ek LIKE 'abc%'. Sıralı bir yapıda "ortasında 'abc' geçen" bir değerin başlangıç noktası olmadığı için LIKE '%abc%' B-tree ile aranamaz.

Önemli bir PostgreSQL ayrıntısı: MySQL InnoDB'de ikincil index'ler primary key'i taşır, PostgreSQL'de taşımaz. Her index doğrudan heap'teki TID'i gösterir. Bu yüzden "index'te PK de var nasılsa" varsayımı PostgreSQL'de yanlıştır.

Composite index, sol-öncelik kuralı ve kolon sırası

(a, b, c) index'i satırları önce a'ya, a eşitse b'ye, o da eşitse c'ye göre sıralar; telefon rehberinin "soyad, ad" sıralaması gibi. Bundan iki kural çıkar:

Sol-öncelik (leftmost prefix): Index, baştaki kolonlardan başlayan koşullar için verimlidir. (a, b, c) index'i a, (a, b) ve (a, b, c) koşullarına hizmet eder. Sadece b koşulu varsa aranacak bir başlangıç noktası yoktur: b = 5 olan satırlar her a değerinin altında dağınıktır. PostgreSQL 17 ve öncesinde planner yine de bu index'i kullanabilir ama tüm index'i tarar; planda koşul Index Cond olarak görünse bile tarama aralığını daraltmaz. PostgreSQL 18 (Eylül 2025) B-tree için skip scan getirdi: baştaki kolonda = koşulu yoksa, o kolonun her farklı değeri için ağaca ayrı ayrı inip atlayarak tarayabilir. Bu, baştaki kolonun az sayıda farklı değeri varsa (ör. region, status) işe yarar; on binlerce farklı değeri olan bir seller_id için yine verimsizdir.

Kolon sırası: eşitlik önce, aralık sonra. Bir aralık koşulundan sonra gelen kolonlar aralığı daraltmaz, sadece filtre olur. Sıralama kolonu da eşitliklerin hemen arkasına konmalıdır ki satırlar zaten sıralı gelsin. Sık kullanılan kural adıyla: Equality → Sort → Range (ESR).

-- Sorgu
SELECT id, status, created_at FROM orders
 WHERE customer_id = $1 AND created_at >= now() - interval '90 days'
 ORDER BY created_at DESC LIMIT 20;

-- İyi: eşitlik (customer_id), sonra sıralama+aralık (created_at)
CREATE INDEX ON orders (customer_id, created_at DESC);

-- Kötü: aralık başta; son 90 günün TÜM müşterilerinin satırları taranır
CREATE INDEX ON orders (created_at DESC, customer_id);

1 milyon satırlık bir tabloda bu sorgunun index'siz hali paralel Seq Scan + Sort ile ~70 ms sürdü ve 14.000'den fazla buffer okudu; (customer_id, created_at DESC) index'iyle Index Scan, Sort yok, 8 buffer ve 0,07 ms.

IN neden saf eşitlik değildir: status IN ('DELIVERED', 'RETURNED') index'te iki ayrı alt aralıktır. (customer_id, status, created_at) index'inde her statünün satırları kendi içinde created_at sıralıdır ama ikisinin birleşimi sıralı değildir. PostgreSQL 17'de bu index'le ORDER BY created_at DESC LIMIT 5 sorgusu planda ayrı bir Sort node'u üretti. Yani IN kolonu sıralama kolonunun önüne konursa ORDER BY + LIMIT optimizasyonu kaybolur.

Seçicilik de karara girer: Eşitlik kolonlarından hangisinin önce geleceği çoğu zaman önemsizdir (hepsi eşitlikse aralık aynıdır), ama tek başına da kullanılacak olanı başa koymak index'i başka sorgulara da açar.

Covering index, Index Only Scan ve visibility map

Sorgunun ihtiyaç duyduğu tüm kolonlar index'teyse heap'e gitmeye gerek yoktur: Index Only Scan. PostgreSQL 11+ bunun için INCLUDE sunar: bu kolonlar sadece yaprakta taşınır, sıralamaya ve ağacın iç sayfalarına katılmaz.

CREATE INDEX idx_orders_customer_created
    ON orders (customer_id, created_at DESC) INCLUDE (status, id);

Ama bir şart daha var: PostgreSQL'de görünürlük bilgisi (satır sürümünün hangi transaction'a görünür olduğu) heap'tedir, index'te değil. Index Only Scan, satırın heap'te durduğu sayfanın visibility map'te "tümü görünür" (all-visible) işaretli olup olmadığına bakar. İşaretliyse heap'e gitmez; değilse gider ve buna Heap Fetches denir. Bu işareti VACUUM koyar, sayfadaki herhangi bir değişiklik kaldırır.

Ölçülen davranış:

-- VACUUM sonrası
Index Only Scan using idx_orders_customer_created on orders
  Index Cond: (customer_id = 1)
  Heap Fetches: 0

-- Aynı satırlara UPDATE sonrası, VACUUM'dan önce
Index Only Scan using idx_orders_customer_created on orders
  Index Cond: (customer_id = 1)
  Heap Fetches: 5

Sonuç: Çok yazılan tablolarda Index Only Scan'in faydası autovacuum'un ne kadar sık çalıştığına bağlıdır. Planda Index Only Scan görüp Heap Fetches sayısına bakmamak sık yapılan bir okuma hatasıdır.

Covering index'in bedeli: index büyür (her INCLUDE kolonu her yaprak girdisinde var), daha az sayfa cache'e sığar ve INCLUDE'daki kolon güncellendiğinde index de güncellenir. Sadece sıcak ve dar sorgular için kullanılır.

Partial index ve expression index

Partial index, sadece WHERE koşulunu sağlayan satırları içerir. Flash sale projesindeki outbox tablosu klasik örnektir:

CREATE INDEX idx_outbox_unpublished
    ON outbox_event (created_at)
    WHERE published_at IS NULL;

Tablo yüz milyonlarca gönderilmiş event içerse bile index sadece bekleyenleri tutar; küçük kalır, hep cache'tedir. Planner bu index'i ancak sorgunun koşulu index koşulunu mantıksal olarak içeriyorsa kullanır: WHERE published_at IS NULL ORDER BY created_at kullanır, WHERE published_at IS NOT NULL kullanmaz. Prepared statement'ta koşul parametreyle verilirse (WHERE status = $1) planner genel planda bunu kanıtlayamayabilir; partial index'in koşulu sorguda sabit olarak yazılmalıdır. Diğer tipik kullanımlar: nadir durumlar (WHERE status = 'CANCELLED'), soft delete'li tablolarda WHERE deleted_at IS NULL ile unique constraint.

Expression index, kolonun kendisi yerine bir ifadenin sonucunu index'ler:

CREATE UNIQUE INDEX ux_customers_email_lower ON customers (lower(email));

SELECT * FROM customers WHERE lower(email) = lower($1);
-- Index Scan using ux_customers_email_lower ... Index Cond: (lower(email) = ...)

Sorgudaki ifade index'teki ifadeyle birebir aynı olmalıdır. İfade IMMUTABLE fonksiyonlardan oluşmalıdır (aynı girdiye her zaman aynı çıktı). timestamptz için created_at::date oturumun saat dilimine bağlıdır, bu yüzden index oluşturma functions in index expression must be marked IMMUTABLE hatası verir; ((created_at AT TIME ZONE 'Europe/Istanbul')::date) ise sabit dilimle immutable'dır. Çoğu durumda daha iyi çözüm ifadeyi index'lemek değil, sorguyu aralığa çevirmektir (aşağıda).

GIN, BRIN, Hash, GiST: ne zaman

1 milyon satırlık, 111 MB'lık bir orders tablosunda ölçülen boyutlar: created_at B-tree index'i 21 MB, aynı kolonun BRIN index'i 24 kB, note kolonu üzerindeki trigram GIN index'i 120 MB (tablodan büyük). BRIN'in küçüklüğünün bedeli kesinsizliktir: "bu blok aralığında olabilir" der, eşleşen blokları okuyup her satırı yeniden kontrol eder; ancak veri fiziksel olarak created_at sırasıyla yazıldıysa (append-only) iyi çalışır. GIN'in bedeli yazmadır: bir metin onlarca trigram üretir ve her biri index'e girer.

LIKE '%telefon%' için trigram GIN index'i Seq Scan'i Bitmap Index Scan'e çevirir:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON products USING gin (name gin_trgm_ops);

Ama ürün araması gibi gerçek bir arama ihtiyacında (Türkçe kök bulma, yazım hatası toleransı, alaka sıralaması, facet) doğru araç bir arama motorudur (bkz. Bölüm 11).

Index neden kullanılmaz

Planner her alternatifin maliyetini tahmin eder ve en ucuzunu seçer. Index'in kullanılmaması iki sebepten olur: ya index bu koşula kullanılamaz, ya da kullanılabilir ama planner daha pahalı bulur. 1 milyon satırlık tabloda doğrulanan durumlar:

1. Kolonu fonksiyonla sarmak. WHERE date(created_at) = '2026-03-01' paralel Seq Scan yaptı; aynı gün created_at >= '2026-03-01' AND created_at < '2026-03-02' olarak yazılınca Index Scan oldu. Index created_at değerleri üzerinde sıralıdır, date(created_at) üzerinde değil. Kural: kolonu çıplak bırak, işlemi sabit tarafa taşı (sargable koşul). price * 1.2 > 100 yerine price > 100 / 1.2.
2. Tip uyuşmazlığı. int bir kolonu WHERE id = 5.0 ile aramak planda Filter: ((id)::numeric = 5.0) üretti: kolon numeric'e çevrildi ve index devre dışı kaldı. 5::bigint ile karşılaştırma ise index'i kullandı (int ve bigint aynı operatör ailesinde). Java'da BigDecimal parametreyi integer kolona bağlamak, ya da varchar kolonu sayıyla karşılaştırmak bu tür sessiz planlar üretir.
3. Düşük seçicilik. Satırların %97'si status <> 'CANCELLED' koşulunu sağlarken planner Seq Scan seçti ve haklıydı: neredeyse her satırı index üzerinden rastgele okumak, tabloyu sırayla okumaktan pahalıdır. Aynı tabloda status = 'CANCELLED' (%3) Bitmap Heap Scan, status = 'DELIVERED' (%88) Seq Scan oldu. Geniş tarih aralığı (7 ay, tablonun %73'ü) da Seq Scan'e döndü.
4. Baştaki joker: LIKE '%x%' B-tree ile aranamaz; trigram GIN ya da arama motoru.
5. Ön ek LIKE ve collation. LIKE 'abc%' B-tree ile ancak veritabanı collation'ı C ise ya da index text_pattern_ops ile kurulduysa çalışır. tr_TR.UTF-8 gibi bir collation'da düz index ön ek araması için kullanılamaz.
6. Composite index'in baştaki kolonu sorguda yok. (seller_id, created_at) index'i varken WHERE created_at > ... sorgusu bu index'ten verimli faydalanamaz (PostgreSQL 18 skip scan istisnası yukarıda).
7. Eski istatistikler. Planner satır sayısını pg_statistic'teki histogram ve en sık değerlerden tahmin eder. Toplu yüklemeden sonra ANALYZE çalışmadıysa tahminler yanlıştır. Belirti: EXPLAIN ANALYZE'da rows= (tahmin) ile actual rows= arasında büyük fark. Çözüm ANALYZE tablo, korele kolonlar için CREATE STATISTICS, çarpık dağılımda ALTER TABLE ... ALTER COLUMN ... SET STATISTICS.
8. Küçük tablo. Birkaç sayfalık bir tabloyu sırayla okumak, index'e inip heap'e gitmekten ucuzdur. Test ortamında "index kullanılmıyor" diye panik yapmadan önce tablo boyutuna bak.
9. Generic plan. Prepared statement'larda PostgreSQL ilk beş çalıştırmayı parametre değerine özel (custom) planla yapar, sonra parametreden bağımsız genel (generic) bir plan daha ucuz görünürse ona geçer. Çarpık dağılımlı bir kolonda (bir satıcı siparişlerin %40'ı, diğerleri binde bir) generic plan bazı değerler için kötü olabilir. pgJDBC varsayılan olarak (prepareThreshold=5) aynı ifadenin beşinci çalıştırmasında server-side prepared statement'a geçer. Teşhis: plan_cache_mode ayarı ve auto_explain.
10. Maliyet parametreleri. random_page_cost varsayılanı 4'tür (sıralı okumanın 4 katı); SSD'lerde genelde 1.1-2 aralığına düşürülür, aksi halde planner index taramasını olduğundan pahalı sanar.

Teşhiste SET enable_seqscan = off ile index'i zorlamak sadece "planner bu index'i kullanabilir mi" sorusunu cevaplar; production'da kalıcı çözüm değildir.

EXPLAIN ve EXPLAIN ANALYZE okuma

EXPLAIN tahmini planı gösterir, sorguyu çalıştırmaz. EXPLAIN (ANALYZE, BUFFERS) sorguyu gerçekten çalıştırır ve gerçek süreleri, satır sayılarını, okunan sayfaları ekler. DML'de ANALYZE gerçekten yazar; BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; ile sarılır.

Limit  (cost=0.42..23.85 rows=5 width=25) (actual time=0.038..0.049 rows=5 loops=1)
  Buffers: shared hit=5 read=3
  ->  Index Scan using o2_cust_created on orders2  (cost=0.42..28.53 rows=6 width=25)
                                                   (actual time=0.037..0.047 rows=5 loops=1)
        Index Cond: (customer_id = 4242)

• cost=0.42..23.85: Birimsiz tahmini maliyet; ilk satırı üretme maliyeti ve tamamı. Mutlak değeri değil, alternatifler arası karşılaştırmayı anlatır.
• rows=6: Planner'ın tahmini satır sayısı. actual rows=5 loops=1: gerçek. loops birden fazlaysa (Nested Loop'un iç tarafı) gerçek toplam rows × loops'tur.
• width: Ortalama satır genişliği (byte).
• Buffers: shared hit / read: Cache'ten ve diskten (ya da OS cache'ten) okunan sayfa sayısı. Performans karşılaştırmasında süreden daha kararlı bir ölçüdür.
• Rows Removed by Filter: Index'ten ya da taramadan gelip sonradan elenen satırlar. Büyükse index koşulu yeterince seçici değildir.
• Plan ağacı içten dışa okunur: en içteki node önce veri üretir.

Tarama node'ları:

Join node'ları (aynı 1 milyon satırlık veride gözlenen planlar):

• Nested Loop: Dış taraftaki her satır için iç tarafta arama. Dış taraf küçük ve iç tarafta index varsa en iyisi: tek müşteri ve siparişleri (WHERE c.id = 77) Nested Loop + index ile geldi. Dış taraf büyükse felakettir.
• Hash Join: Küçük tarafın hash tablosu belleğe kurulur, büyük taraf bir kez taranır. Büyük, sırasız iki kümenin eşitlik join'i: tüm müşteri ve siparişleri birleştirip gruplayan rapor Hash Join oldu. Hash tablosu work_mem'e sığmazsa batch'lere bölünüp diske taşar.
• Merge Join: İki taraf join anahtarına göre sıralıysa (ya da sıralanabiliyorsa) fermuar gibi birleştirir. İki tarafta da uygun index ve ORDER BY id LIMIT 100 olan sorgu Merge Join + iki Index Scan seçti.

Okuma alışkanlığı: önce en pahalı node'u bul (actual time), sonra tahmin ile gerçek satır sayısını karşılaştır. 10 kat ve üzeri sapma, planner'ın yanlış join tipi veya sırası seçmesinin en yaygın nedenidir.

Index'in yazma maliyeti ve CREATE INDEX CONCURRENTLY

Index okuma hızlandırır ama bedava değildir:

• Her INSERT her index'e, her UPDATE (aşağıdaki HOT istisnası hariç) her index'e yeni bir girdi ekler; her biri ayrıca WAL üretir. DELETE index'lere anında dokunmaz (sadece heap'teki tuple'ın xmax'ını doldurur), ama geride kalan ölü index girdilerini VACUUM temizlemek zorundadır.
• Index disk ve shared buffer tüketir; sıcak veri cache'ten itilebilir.
• Ölçüm: 200.000 satırı index'siz tabloya eklemek 0,4 sn, beş index'li (dördü B-tree, biri trigram GIN) kopyasına eklemek 10,8 sn sürdü. GIN aşırı bir örnek ama yön açıktır.

HOT (Heap-Only Tuple) update: UPDATE index'lenmiş hiçbir kolonu değiştirmiyorsa (PostgreSQL 16+'da sadece BRIN index'li kolonların değişmesi HOT'u engellemez) ve aynı heap sayfasında yer varsa, PostgreSQL yeni sürümü aynı sayfaya yazar ve index'leri hiç güncellemez. Bu yüzden sık güncellenen bir kolonu (last_seen_at, view_count) "belki lazım olur" diye index'lemek, o tablodaki tüm UPDATE'leri pahalı hale getirir. fillfactor (ör. 90) sayfalarda HOT için boşluk bırakır. pg_stat_user_tables.n_tup_hot_upd HOT oranını gösterir.

Kullanılmayan index'ler: pg_stat_user_indexes.idx_scan = 0 olan index'ler (istatistik sıfırlanmasından beri hiç kullanılmamış) aday listesidir; replica'larda da kullanılmadığından emin olduktan sonra silinir.

CREATE INDEX CONCURRENTLY: Normal CREATE INDEX tabloya yazmaları engelleyen bir kilit alır (okumalar devam eder). Büyük bir tabloda bu dakikalarca sipariş alınamaması demektir. CONCURRENTLY:

• Yazmaları engellemez; tabloyu iki kez tarar ve arada başlamış transaction'ların bitmesini bekler, bu yüzden daha yavaştır.
• Transaction bloğu içinde çalışmaz (CREATE INDEX CONCURRENTLY cannot run inside a transaction block). Flyway/Liquibase migration'larında bu migration'ın transaction dışı çalışacak şekilde işaretlenmesi gerekir.
• Başarısız olursa (ör. UNIQUE index'te mükerrer veri, deadlock) geride INVALID bir index bırakır: sorgularda kullanılmaz ama yazmalarda güncellenmeye devam eder. DROP INDEX CONCURRENTLY ile silinip tekrar denenir ya da PostgreSQL 12+'te REINDEX INDEX CONCURRENTLY.
• Uzun süren bir transaction (unutulmuş bir idle in transaction oturum) CONCURRENTLY'yi bekletir.

ACID

• Atomicity: Transaction'daki değişikliklerin ya hepsi ya hiçbiri kalıcı olur. İade kaydı yazıldı ama stok artırılamadıysa iade kaydı da geri alınır.
• Consistency: Transaction, veritabanını bir geçerli durumdan diğerine taşır: constraint'ler (PK, FK, UNIQUE, CHECK) ve uygulamanın invariant'ları korunur. Constraint'leri veritabanı garanti eder; iş kuralının doğruluğu uygulamanın sorumluluğudur.
• Isolation: Eşzamanlı transaction'ların birbirinin ara durumlarını ne ölçüde görebileceği. Seviyesi seçilebilir (aşağıda).
• Durability: Commit edilen veri, hemen ardından sunucu çökse bile kaybolmaz. PostgreSQL bunu WAL (write-ahead log) ile sağlar: değişiklik önce sıralı bir log'a yazılır; varsayılan synchronous_commit = on ayarında commit, WAL kaydı diske flush edildikten sonra döner. Veri sayfaları daha sonra (checkpoint'te) yazılır; çökmeden sonra WAL tekrar oynatılarak kurtarılır. synchronous_commit = off yapılırsa commit WAL flush'ını beklemeden döner; çökmede son birkaç yüz milisaniyelik commit'ler kaybolabilir (veritabanı tutarlı kalır ama kayıp olur). Log veya metrik tablosu için kabul edilebilir, para hareketi için değil.

MVCC: xmin, xmax, VACUUM ve bloat

PostgreSQL eşzamanlılığı MVCC (Multi-Version Concurrency Control) ile yönetir: bir satır güncellendiğinde yerinde değiştirilmez, yeni bir sürüm (tuple) yazılır. Her tuple'ın başlığında:

• xmin: Bu sürümü oluşturan transaction'ın id'si.
• xmax: Bu sürümü silen ya da güncelleyen transaction'ın id'si (canlı sürümde 0).

Doğrudan görebilirsin:

SELECT xmin, xmax, ctid, product_id FROM stock WHERE product_id = 1;
--  737 | 0 | (0,1)  | 1
UPDATE stock SET quantity = quantity WHERE product_id = 1;   -- değer aynı olsa bile
SELECT xmin, xmax, ctid, product_id FROM stock WHERE product_id = 1;
--  738 | 0 | (0,17) | 1     -- yeni xmin, yeni fiziksel konum

Değeri değiştirmeyen UPDATE bile yeni bir tuple yazdı. Eski tuple'ın xmax'ı 738 oldu ve "ölü" sayılır.

Her transaction (ya da READ COMMITTED'da her ifade) bir snapshot alır: "şu an commit olmuş transaction'lar şunlar". Bir tuple, xmin'i snapshot'a göre commit olmuşsa ve xmax'ı boşsa ya da henüz commit olmamışsa görünürdür. Sonuç: okuyucular yazıcıları, yazıcılar okuyucuları bloklamaz. Bir rapor sorgusu 10 dakika sürse bile o sırada siparişler yazılmaya devam eder; rapor kendi snapshot'ındaki tutarlı görüntüyü okur. Kilitlenme sadece aynı satırı yazmak isteyen iki transaction arasında olur.

Bedeli ölü tuple'lardır. Onları VACUUM temizler: hiçbir aktif snapshot'ın göremeyeceği ölü tuple'ların yerini yeniden kullanılabilir işaretler, visibility map'i günceller. autovacuum bunu arka planda yapar; varsayılan eşik tablonun %20'si (autovacuum_vacuum_scale_factor = 0.2) artı 50 satır kadar ölü tuple birikmesidir. Çok büyük, sık güncellenen tablolarda %20 geç kalır; tablo bazında düşürülür.

Bloat ölü tuple'ların temizlenemediği ya da temizlenen alanın geri kullanılamadığı durumdur. En yaygın sebebi uzun süren transaction'lardır: açık bir transaction'ın snapshot'ı eski tuple'ları görebilir durumda olduğu için VACUUM o noktadan sonraki hiçbir ölü tuple'ı silemez. Klasik production senaryosu: bir servis transaction açıp dış bir HTTP çağrısında takılır ya da bir geliştirici BEGIN yazıp oturumu açık bırakır; saatler içinde sık güncellenen tablolar şişer, index'ler büyür, sorgular yavaşlar. İzleme: pg_stat_activity (state = 'idle in transaction', xact_start), pg_stat_user_tables.n_dead_tup, last_autovacuum. Koruma: idle_in_transaction_session_timeout, statement_timeout. Normal VACUUM tabloyu küçültmez, alanı yeniden kullanılabilir yapar; diske geri vermek için VACUUM FULL (tabloyu tamamen kilitler) ya da pg_repack gibi araçlar gerekir.

Bir diğer VACUUM görevi transaction ID wraparound'u önlemektir: transaction id'leri 32 bittir ve döner. VACUUM eski tuple'ları "dondurur" (freeze). Uzun süre vacuum edilemeyen bir tablo, PostgreSQL'i veri kaybını önlemek için yeni transaction kabul etmemeye kadar götürebilir. Bu yüzden autovacuum'u kapatmak hiçbir zaman bir performans çözümü değildir.

İzolasyon seviyeleri ve PostgreSQL'deki gerçek davranış

SQL standardı dört seviye ve önlenmesi gereken olguları tanımlar. PostgreSQL resmi dokümantasyonundaki tablo:

PostgreSQL dört seviyeyi de kabul eder ama içeride üç farklı davranış uygular:

READ UNCOMMITTED = READ COMMITTED. BEGIN ISOLATION LEVEL READ UNCOMMITTED hata vermez ama READ COMMITTED gibi çalışır. MVCC'de commit edilmemiş sürüm zaten hiçbir snapshot'a görünmez. Doğrulama: bir oturum değeri 999'a güncelleyip commit etmeden beklerken READ UNCOMMITTED'daki ikinci oturum eski değeri okudu.

READ COMMITTED (varsayılan): Her ifade başladığı anda yeni bir snapshot alır. Aynı transaction'daki iki SELECT arasında başka bir transaction commit ederse ikinci SELECT yeni değeri görür (non-repeatable read; doğrulandı: aynı transaction'da 100, sonra 200 okundu). Satır yazarken özel bir kural vardır: UPDATE/DELETE/SELECT FOR UPDATE, başka bir transaction'ın kilitlediği satıra gelirse bekler; o commit olunca satırın en güncel sürümünü alır ve WHERE koşulunu onun üzerinde yeniden değerlendirir. Koşullu stok UPDATE'inin güvenli olmasının nedeni budur (bkz. Bölüm 9).

REPEATABLE READ: Transaction'ın ilk ifadesinde (BEGIN'de değil) tek bir snapshot alınır ve transaction boyunca kullanılır. Teknik adı snapshot isolation'dır. Snapshot sabit olduğu için standardın izin verdiği phantom read de PostgreSQL'de oluşmaz. Yazma kuralı farklıdır: snapshot'tan sonra başka bir transaction'ın güncellediği bir satırı güncellemeye çalışırsan PostgreSQL beklemez-yeniden-değerlendirmez, hata verir:

ERROR:  could not serialize access due to concurrent update

SERIALIZABLE: PostgreSQL 9.1'den beri SSI (Serializable Snapshot Isolation) ile uygulanır. Snapshot isolation'a ek olarak transaction'ların okuduklarını predicate lock (SIReadLock) olarak işaretler; bu kilitler kimseyi bloklamaz, sadece transaction'lar arası okuma-yazma bağımlılıklarını takip eder. Bağımlılıklar "hiçbir seri sıralamayla açıklanamayacak" bir döngü oluşturursa transaction'lardan birini düşürür:

ERROR:  could not serialize access due to read/write dependencies among transactions
HINT:  The transaction might succeed if retried.

İki seviyedeki serialization hataları SQLSTATE 40001 ile döner. Bu bir bug değil, protokolün parçasıdır: REPEATABLE READ ve SERIALIZABLE kullanan uygulama retry mantığı yazmak zorundadır. Retry tüm transaction'ı baştan çalıştırmalıdır (yeni snapshot ile), sadece son ifadeyi değil. SSI'nin maliyetleri: predicate lock takibi için ek bellek ve CPU, ve "yanlış pozitif" iptaller (gerçekte anomali oluşmayacak bazı durumlarda da düşürebilir); Seq Scan yapan sorgular tüm tabloya predicate lock alır ve iptal oranını artırır. Dokümantasyonun önerileri: mümkünse transaction'ı READ ONLY işaretle, transaction'ları kısa tut, bağlantı sayısını havuzla sınırla.

Anomaliler

• Dirty read: Commit edilmemiş veriyi okumak. PostgreSQL'de hiçbir seviyede olmaz.
• Non-repeatable read: Aynı satırı iki kez okuyup farklı değer görmek. READ COMMITTED'da olur.
• Phantom read: Aynı koşulla tekrar sorgulayınca yeni satırların gelmesi ("son 1 saatteki siparişleri say" iki kez farklı sayı döner). READ COMMITTED'da olur, PostgreSQL REPEATABLE READ'de olmaz.
• Lost update: İki transaction aynı değeri okur, ikisi de kendi hesabını üzerine yazar, birinin güncellemesi kaybolur. Okuma-hesaplama-yazma deseninin klasik hatası.
• Write skew: İki transaction ortak bir kuralı kontrol eder ve farklı satırları günceller; her biri tek başına kurala uyar, birlikte kuralı bozar. Klasik örnek: "en az bir doktor nöbette olmalı". İkisi de nöbetteki doktor sayısını 2 okur, biri Alice'i, diğeri Bob'u nöbetten çıkarır. Farklı satırlara yazdıkları için satır kilidi çakışmaz ve REPEATABLE READ'de ikisi de commit olur, nöbette kimse kalmaz. Aynı senaryo SERIALIZABLE'da çalıştırıldığında ikinci transaction commit sırasında could not serialize access due to read/write dependencies hatasıyla düştü ve Bob nöbette kaldı. Bankacılık karşılığı: "iki hesabın toplam bakiyesi sıfırın altına düşmemeli" kuralını kontrol edip farklı hesaplardan eşzamanlı para çekmek. Write skew sadece SERIALIZABLE ile ya da kuralın dayandığı satırları açıkça kilitleyerek (SELECT ... FOR UPDATE ile ilgili tüm satırları ya da ortak bir "üst" satırı) önlenir.

Lost update çözümleri

Senaryo (q10): sadakat puanı. findById ile oku, Java'da points + 10 hesapla, commit'te UPDATE. İki eşzamanlı istek ikisi de 100 okur ve ikisi de 110 yazar. Bu PostgreSQL 16 üzerinde birebir tekrarlandı: iki "+10" işleminden sonra bakiye 110 kaldı.

1) Atomik UPDATE (ilk tercih):

UPDATE wallet SET points = points + :p WHERE user_id = :id;

Hesap veritabanında, satırın güncel sürümü üzerinde yapılır. Satır kilidi yine transaction sonuna kadar tutulur, ama okuma ile yazma arasında uygulamaya gidip gelinmediği için kilit süresi en kısa haldedir. Hesap SQL ile ifade edilebiliyorsa her zaman en ucuz ve en doğru yoldur.

2) Pessimistic lock: SELECT ... FOR UPDATE:

public interface WalletRepository extends JpaRepository<Wallet, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)                  // PostgreSQL'de SELECT ... FOR UPDATE
    @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "2000"))
    @Query("select w from Wallet w where w.userId = :id")
    Optional<Wallet> findForUpdate(@Param("id") long id);
}

İkinci transaction SELECT'te bekler, ilki bitince güncel değeri okur. Okuduktan sonra karmaşık bir karar veriliyorsa (limit kontrolü, birden fazla kural) ve çekişme yüksekse uygundur. Bedeli: kilit transaction sonuna kadar tutulur, transaction içinde dış çağrı yapılırsa herkes bekler; birden fazla satır kilitleniyorsa deadlock riski vardır. NOWAIT (kilit alınamazsa hemen 55P03 hatası) ve lock_timeout ile bekleme sınırlanır. Dikkat: jakarta.persistence.lock.timeout ipucu PostgreSQL'de Hibernate 6.x'te (Spring Boot 3.x) sadece 0 (NOWAIT) ve SKIP LOCKED değerleri için uygulanır, 2000 gibi bir değer sessizce yok sayılır; Hibernate 7 (Spring Boot 4) bunu set local lock_timeout ile uygular. Hibernate 6'da süreyi rol, oturum ya da transaction seviyesinde lock_timeout ile ver.

3) Optimistic lock: @Version:

@Entity
class Wallet {
    @Id private Long userId;
    private int points;
    @Version private long version;
}

Hibernate UPDATE'e WHERE user_id = ? AND version = ? ekler ve version'ı artırır. Arada başka biri güncellediyse 0 satır etkilenir; Hibernate OptimisticLockException fırlatır, Spring bunu ObjectOptimisticLockingFailureException olarak sarar. Doğrulama: aynı version = 5 koşuluyla iki UPDATE'ten ilki UPDATE 1, ikincisi UPDATE 0 döndü. Kilit tutmaz; çekişme düşükse ve "okuma ile yazma arası uzun" akışlarda (kullanıcının formu düzenlemesi, HTTP'de ETag/If-Match) idealdir. Çekişme yüksekse retry fırtınası olur. Retry her denemede yeni bir transactionla tüm okuma-hesaplama-yazmayı tekrarlamalıdır:

@Service
class LoyaltyService {
    private final TransactionTemplate tx;
    private final WalletRepository wallets;

    LoyaltyService(TransactionTemplate tx, WalletRepository wallets) {
        this.tx = tx;
        this.wallets = wallets;
    }

    void addPoints(long userId, int points) {
        for (int attempt = 1; ; attempt++) {
            try {
                tx.executeWithoutResult(status -> {
                    Wallet w = wallets.findById(userId).orElseThrow();
                    w.addPoints(points);
                });
                return;
            } catch (ConcurrencyFailureException e) {   // optimistic ve 40001 hatalarının ortak üst tipi
                if (attempt == 3) throw e;
            }
        }
    }
}

Retry'ı @Transactional metodun içinde yapmak işe yaramaz: hata alan transaction rollback-only işaretlenir ve aynı snapshot/persistence context ile tekrar denemenin anlamı yoktur.

4) REPEATABLE READ: PostgreSQL'de lost update'i hata vererek önler: ikinci transaction could not serialize access due to concurrent update alır (doğrulandı: hata alan transaction düştü, bakiye doğru şekilde tek artışla 110 oldu). Retry şarttır.

@Transactional(readOnly = true) çözüm değildir: Hibernate'te flush'ı kapatır; değişiklik hiç yazılmaz. Bir ipucudur, kilit değildir.

Kuyruk için FOR UPDATE SKIP LOCKED: Birden fazla worker aynı tablodan iş alacaksa (outbox relay, e-posta kuyruğu):

SELECT * FROM outbox_event
 WHERE published_at IS NULL
 ORDER BY created_at
 LIMIT 100
   FOR UPDATE SKIP LOCKED;

FOR UPDATE seçilen satırları kilitler; SKIP LOCKED başka bir worker'ın kilitlediği satırları beklemeden atlar. Doğrulama: birinci oturum ilk 3 işi kilitliyken ikinci oturum aynı sorguyla 4, 5, 6'yı aldı. SKIP LOCKED olmasaydı ikinci worker bekler, FOR UPDATE olmasaydı iki worker aynı event'i iki kez gönderirdi. Bedeli: sıra garantisi zayıflar (worker'lar farklı hızda ilerler) ve kilitli satırlar geçici olarak görünmez. Aynı aggregate'in event sırası önemliyse tek aktif relay ya da CDC kullanılır (bkz. Bölüm 13).

Deadlock

İki transaction birbirinin tuttuğu kilidi beklerse deadlock oluşur. Tekrarlanan senaryo: T1 ürün 1'i, T2 ürün 2'yi güncelledi; sonra T1 ürün 2'yi, T2 ürün 1'i güncellemek istedi. PostgreSQL bir kilit beklemesi deadlock_timeout (varsayılan 1 sn) süresini aşınca bekleme grafiğinde döngü arar, bulursa transaction'lardan birini iptal eder:

ERROR:  deadlock detected
DETAIL:  Process 2838 waits for ShareLock on transaction 754; blocked by process 2840.
         Process 2840 waits for ShareLock on transaction 753; blocked by process 2838.

SQLSTATE 40P01. Diğer transaction devam eder. Önleme:

• Tutarlı kilit sırası: Sepetteki ürünlerin stokunu düşerken her zaman product_id artan sırada güncelle; para transferinde iki hesabı her zaman küçük id'den başlayarak kilitle. Döngü oluşamaz.
• Kısa transaction: Kilit tutma süresi kısaldıkça çakışma olasılığı düşer.
• Toplu kilitleme: SELECT ... FROM stock WHERE product_id = ANY(:ids) ORDER BY product_id FOR UPDATE ile hepsini tek seferde sıralı kilitle.
• Deadlock hatası da geçici bir hatadır; retry edilebilir. Sık görülüyorsa erişim sırasını düzelt, retry ile maskeleme.

İzleme: log_lock_waits = on (deadlock_timeout'u aşan beklemeleri loglar), pg_locks ile pg_stat_activity join'i, pg_blocking_pids(pid) fonksiyonu.

Advisory lock

Advisory lock, PostgreSQL'in bir tabloya bağlı olmayan, uygulamanın anlamlandırdığı bir sayıya (bigint) kilit vermesidir. Mutex gibi kullanılır:

SELECT pg_try_advisory_xact_lock(hashtext('daily-settlement-job'));  -- true / false

• pg_advisory_xact_lock transaction sonunda kendiliğinden bırakılır. pg_advisory_lock oturuma bağlıdır ve açıkça pg_advisory_unlock gerekir.
• try sürümleri beklemez, true/false döner.
• Kullanım: 10 pod'dan sadece birinin gece mutabakat job'ını çalıştırması (ShedLock benzeri), aynı müşteri için eşzamanlı işlemleri serileştirmek (pg_advisory_xact_lock(customer_id)), migration'ların tek seferde çalışması (Flyway bunu kullanır).
• Tuzaklar: Oturum seviyesi kilit, bağlantı havuza geri döndüğünde bırakılmazsa başka bir isteğin elinde kalır. PgBouncer'ın transaction pooling modunda oturum seviyesi kilitler (ve SET gibi oturum durumu) güvenilmezdir, çünkü ardışık transaction'lar farklı sunucu bağlantılarına gidebilir; orada sadece xact sürümü kullanılır. Kilit tek bir PostgreSQL primary'sinin içindedir; failover'da kaybolur.

Normalizasyon ve bilinçli denormalizasyon

Normalizasyonun amacı her bilginin tek bir yerde durması ve güncelleme anomalilerini önlemektir.

• 1NF: Her hücre atomik değer tutar; tekrarlayan grup yoktur. tags = 'kampanya,indirim' 1NF'i bozar: "indirim etiketli ürünler" sorgusu LIKE ile yapılır, index kullanılamaz, etiket yeniden adlandırılamaz. Çözüm ayrı bir product_tags tablosu. (PostgreSQL'de text[] veya jsonb + GIN bilinçli bir istisna olabilir.)
• 2NF: 1NF + bileşik anahtarlı tabloda anahtar olmayan her kolon anahtarın tamamına bağlıdır. order_items(order_id, product_id, product_name): product_name sadece product_id'ye bağlı, kısmi bağımlılık.
• 3NF: 2NF + anahtar olmayan bir kolon başka bir anahtar olmayan kolona bağlı değildir (geçişli bağımlılık yok). returns(id, customer_id, customer_email): email müşteriye bağlı, müşteri e-postasını değiştirince eski iadelerdeki kopyalar yanlış kalır.
• BCNF: Her fonksiyonel bağımlılığın sol tarafı bir aday anahtardır. 3NF'ten farkı, birden fazla örtüşen aday anahtar olan nadir durumlarda ortaya çıkar; pratikte "3NF'e kadar normalize ederim, BCNF ihlali nadirdir" demek yeterlidir.

Bilinçli denormalizasyon hata değil, gerekçeli bir tercihtir:

• Tarihsel gerçek (snapshot): order_items.unit_price ve sipariş anındaki ürün adı. Ürünün fiyatı değişince geçmiş siparişin tutarı değişmemelidir. Bu aslında denormalizasyon bile değildir; "sipariş anındaki fiyat" ayrı bir olgudur.
• Okuma modeli: Sipariş listesi ekranı için satıcı adı ve toplam tutar siparişte tutulur ya da ayrı bir read model'de (Elasticsearch, materialized view) birleştirilir.
• Sayaçlar: products.review_count her istekte count(*) yapmamak için.

Bedeli tutarlılığı yönetmektir: kopyanın kaynağıyla nasıl senkron tutulacağı (aynı transaction, trigger, event), ne kadar gecikmenin kabul edildiği ve tutarsızlık olduğunda neyin doğru kabul edileceği (kaynak tablo) açıkça yazılmalıdır.

Connection pool: HikariCP boyutlandırma

PostgreSQL her bağlantı için ayrı bir işletim sistemi süreci (process) açar. Bağlantı açmak pahalıdır (TCP, TLS, kimlik doğrulama, süreç oluşturma) ve her süreç bellek tüketir. Varsayılan max_connections 100'dür. Uygulama bu yüzden bağlantıları bir havuzda tutar; Spring Boot'un varsayılan havuzu HikariCP'dir ve varsayılan maximumPoolSize 10'dur.

Sezgiye aykırı gerçek: havuz büyüdükçe throughput artmaz, genelde düşer. Veritabanı sunucusunun aynı anda gerçekten çalıştırabileceği iş sayısı CPU çekirdeği ve disk sayısıyla sınırlıdır; fazlası context switch, kilit ve cache çekişmesi olarak geri döner. HikariCP'nin "About Pool Sizing" yazısı PostgreSQL projesinin önerdiği başlangıç formülünü aktarır:

bağlantı sayısı ≈ (çekirdek sayısı × 2) + effective_spindle_count

4 çekirdekli, tek diskli bir sunucu için yaklaşık 9-10. Bu bir başlangıç noktasıdır, ölçümle ayarlanır.

Uygulama tarafından düşünmenin yolu Little's Law'dur: gereken eşzamanlı bağlantı ≈ saniyedeki transaction × ortalama transaction süresi. Saniyede 2.000 transaction ve 5 ms ortalama süre için 2000 × 0,005 = 10 bağlantı yeter. Transaction süresi 50 ms'ye çıkarsa (içeride bir HTTP çağrısı yüzünden) aynı yük için 100 bağlantı gerekir. Yani havuz sorunlarının çoğu havuz boyutuyla değil, transaction süresiyle çözülür.

Kritik hesap toplam bağlantıdır: 20 pod × 10 bağlantı = 200, max_connections = 100 olan bir veritabanını aşar. Çok pod'lu mimaride havuzlar küçük tutulur ve gerekirse araya PgBouncer konur (transaction pooling ile binlerce istemci bağlantısını onlarca sunucu bağlantısına indirger).

Havuz tükenmesinin tipik sebepleri ve belirtisi (Connection is not available, request timed out after 30000ms, HikariCP'nin varsayılan connectionTimeout'u 30 sn):

• @Transactional içinde dış servis çağrısı: bağlantı yanıt beklerken tutulur.
• Havuz deadlock'u: Bir transaction bağlantı tutarken REQUIRES_NEW ile ikinci bir bağlantı ister. Havuz boyutu kadar eşzamanlı istek ilk bağlantıları almışsa hiçbiri ikincisini alamaz ve hepsi timeout'a kadar bekler. HikariCP wiki'sindeki formül bu durum için: havuz boyutu ≥ thread sayısı × (thread başına en fazla eşzamanlı bağlantı − 1) + 1.
• Spring Boot'ta varsayılan açık gelen Open Session in View: lazy yükleme için bağlantıya view render'ı boyunca ihtiyaç duyulabilir. spring.jpa.open-in-view=false önerilir (bkz. Bölüm 8).

İzlenecek metrikler (Micrometer ile Actuator'da hazır): hikaricp.connections.active, hikaricp.connections.pending, hikaricp.connections.acquire (bağlantı alma süresi), hikaricp.connections.usage (bağlantı tutma süresi). pending > 0 sürekli ise ya havuz küçük ya da transaction'lar uzun; hangisi olduğunu usage söyler.

Quiz'deki sorular: çözüm ve nedenleri

j26: ACID, anomaliler ve normal formlar

Senaryo. İade modülünün veri modeli tasarlanıyor; hangi ifadeler doğru?

Doğru seçenekler: a, b, c, d, e.

• a (Atomicity: hep ya da hiç): Doğru. İade kaydı ve stok artışı aynı transaction'daysa biri başarısız olunca ikisi de geri alınır.
• b (Durability, WAL): Doğru. Bir nüans: commit'in WAL diske flush edildikten sonra dönmesi varsayılan synchronous_commit = on içindir. off yapılırsa commit flush beklemeden döner ve çökmede son commit'ler kaybolabilir; sunucu çökmesine karşı garanti tek sunucunun diskiyle sınırlıdır, disk kaybına karşı replikasyon gerekir.
• c (PostgreSQL'de READ UNCOMMITTED'da dirty read yok): Doğru. READ UNCOMMITTED READ COMMITTED gibi davranır; MVCC commit edilmemiş sürümü göstermez.
• d (Phantom read tanımı): Doğru. Non-repeatable read aynı satırın değerinin değişmesi, phantom ise koşulu sağlayan satır kümesinin değişmesidir.
• e (3NF ve geçişli bağımlılık): Doğru. returns tablosunda customer_email tutmak, email'in müşteriye, müşterinin iadeye bağlı olduğu geçişli bir bağımlılıktır.

Yanlış seçenekler:

• f (Denormalizasyon her durumda kötü): Yanlış. Sipariş anındaki fiyat gibi tarihsel veriler ve okuma modelleri bilinçli denormalizasyondur; bedeli kopyaların tutarlılığını yönetmektir.
• g (Index her zaman hızlandırır, yazmayı etkilemez): Yanlış. Her index her INSERT'te ve HOT olmayan her UPDATE'te güncellenir (DELETE'in bıraktığı ölü girdiler de VACUUM'a iş çıkarır); ölçümde 5 index'li tabloya ekleme 27 kat yavaşladı. Düşük seçicilikli sorgularda planner index'i zaten kullanmaz.

Ana kavram ve production karşılığı: ACID'deki C ile CAP'teki C farklı şeylerdir (bkz. Bölüm 11). İzolasyon seviyesini varsayılan bırakmak çoğu durumda doğrudur, ama bunun lost update ve write skew'e izin verdiğini bilip kritik yerlerde atomik UPDATE, kilit veya SERIALIZABLE ile kapatmak gerekir.

j27: Index var ama sorgu yine Seq Scan yapıyor

Senaryo. On milyonlarca satırlık tablolarda customers(email), orders(created_at), products(name), orders(status), orders(seller_id, created_at) index'leri var. Beş sorgu Seq Scan yapıyor.

(1) WHERE lower(email) = lower($1): Index email üzerinde, sorgu lower(email) arıyor; kolon fonksiyonla sarıldığı için index kullanılamaz. Çözüm: CREATE INDEX CONCURRENTLY ON customers (lower(email)) (giriş için UNIQUE olması da mükerrer hesap sorununu çözer), citext tipi ya da e-postayı kayıtta normalize edip küçük harfle saklamak ve sorguda düz email = $1 kullanmak.

(2) WHERE date(created_at) = '2026-10-01': Aynı sebep, sargable değil. Aralığa çevrilir: created_at >= '2026-10-01 00:00+03' AND created_at < '2026-10-02 00:00+03'. Ölçümde bu dönüşüm paralel Seq Scan'i Index Scan'e çevirdi. Saat dilimini açık yazmak önemlidir; timestamptz için "Türkiye'de 1 Ekim" UTC'de 30 Eylül 21:00'de başlar. date(created_at) üzerine expression index kurmak timestamptz için mümkün bile değildir (immutable değil).

(3) WHERE name LIKE '%telefon%': Baştaki joker ile B-tree'de başlangıç noktası yoktur. Çözümler: pg_trgm + GIN index (Seq Scan'i Bitmap Index Scan'e çevirdi; index tablodan büyük olabilir), PostgreSQL full-text search (tsvector + GIN), gerçek ürün araması için Elasticsearch.

(4) WHERE status <> 'CANCELLED' (%97 eşleşiyor): Planner haklı. Satırların neredeyse tamamı lazımsa index üzerinden rastgele okumak sıralı taramadan pahalıdır. Gerçek çözüm sorguyu daraltmaktır (tarih aralığı, sayfalama, LIMIT). Asıl ihtiyaç iptal edilenleri bulmaksa CREATE INDEX ... ON orders (created_at) WHERE status = 'CANCELLED' gibi küçük bir partial index.

(5) WHERE created_at > now() - interval '1 hour' AND seller_id IS NOT NULL: (seller_id, created_at) index'inde baştaki kolon için eşitlik yok; sol-öncelik kuralı gereği bu index created_at aralığını daraltamaz. Mevcut orders(created_at) index'i bu sorgu için doğru index'tir ve son 1 saat küçük bir aralıksa planner onu seçmelidir. Seçmiyorsa: istatistikler eski olabilir (ANALYZE orders), son 1 saatte toplu bir yükleme olmuş ve aralık gerçekten büyük olabilir ya da maliyet parametreleri yanlıştır. Quiz cevabına iki ek: PostgreSQL 18'in skip scan özelliği baştaki kolonda eşitlik olmayan composite index'leri kullanılabilir kılar, ama seller_id gibi binlerce farklı değeri olan bir kolonda her değer için ayrı arama yapacağı için yine created_at index'inden kötü olur. Ayrıca seller_id NOT NULL tanımlıysa IS NOT NULL koşulu gereksizdir; PostgreSQL 17 planner'ı NOT NULL kolonlardaki bu tür koşulları plandan siliyor (doğrulama sırasında planda görünmedi).

Her durumda: EXPLAIN (ANALYZE, BUFFERS) ile tahmini ve gerçek satır sayısını karşılaştır, değişikliği ölçerek doğrula, büyük tabloda index'i CONCURRENTLY oluştur.

q9: "Siparişlerim" sayfası için en iyi index hangisi?

Senaryo. 800 milyon satırlık orders, saniyede binlerce kez çalışan sorgu:

SELECT id, total, created_at FROM orders
 WHERE buyer_id = $1
   AND created_at >= now() - interval '90 days'
   AND status IN ('DELIVERED', 'RETURNED')      -- satırların ~%85'i
 ORDER BY created_at DESC
 LIMIT 20;

Doğru cevap: b) (buyer_id, created_at DESC) INCLUDE (status, total, id).

Neden: Eşitlik kolonu (buyer_id) başta; ardından hem sıralama hem aralık kolonu olan created_at DESC. Index alıcının satırlarına iner, son 90 günü zaten azalan sırada okur, Sort yoktur. status %85 seçicilikle zayıf bir filtre olduğu için sıralamayı bozmadan yaprakta (INCLUDE) kontrol edilir; 20 eşleşme bulununca tarama durur. total ve id de INCLUDE'da olduğu için Index Only Scan mümkündür; id'nin eklenmesi şarttır çünkü PostgreSQL ikincil index'leri primary key'i taşımaz. Benzer bir index ile PostgreSQL 17 planı Index Only Scan, Index Cond: (customer_id = ...) AND (created_at >= ...), Filter: (status = ANY (...)) oldu ve Sort node'u yoktu. Index Only Scan'in gerçek faydası visibility map'in güncel olmasına bağlıdır (Heap Fetches).

Yanlış seçenekler:

• a) (created_at DESC, buyer_id, status): Aralık kolonu başta; son 90 günün tüm alıcılarının satırları taranır, buyer_id sonradan filtrelenir.
• c) (buyer_id, status, created_at DESC): İkinci en iyi. status IN iki alt aralık üretir, birleşimleri created_at sırasında değildir; doğrulamada planda Sort göründü. status çok seçici olsaydı (%1) bu index öne geçebilirdi.
• d) Üç ayrı tek kolonlu index: Planner BitmapAnd ile birleştirebilir ama bitmap sırayı kaybeder, heap'e gidilir, Sort gerekir, LIMIT erken duramaz; ayrıca her yazma üç index'i günceller.

Daha iyi alternatif: Ekran her zaman bu iki statüyü gösteriyorsa partial index: CREATE INDEX CONCURRENTLY ON orders (buyer_id, created_at DESC) INCLUDE (total, id) WHERE status IN ('DELIVERED', 'RETURNED'). Daha küçük ve status filtresine hiç gerek kalmaz; bedeli sadece bu sorgu desenine hizmet etmesidir ve sorgudaki IN listesi index koşuluyla birebir uyumlu yazılmalıdır. 800 milyon satırda index'in kendisi onlarca GB olacağı için hangi sorguların gerçekten bu ekranı beslediği ölçülerek karar verilir; çok büyük tablolarda created_at üzerinden partitioning de masaya gelir.

q10: Puanlar kayboluyor (lost update)

Senaryo. @Transactional addPoints: findById (SELECT), Java'da points + 10, commit'te UPDATE. Başlangıç 100, iki eşzamanlı +10 sonrası bazen 110.

Doğru seçenekler: b, c, d, e.

• b) @Lock(PESSIMISTIC_WRITE) / SELECT ... FOR UPDATE: İkinci transaction SELECT'te bekler, ilki commit olunca 110'u okur ve 120 yazar.
• c) Atomik UPDATE wallet SET points = points + :p: En ucuz ve en doğru; hesap satırın güncel sürümünde yapılır.
• d) @Version + retry: İkinci commit WHERE version = ? koşulunu karşılamaz, 0 satır, OptimisticLockException; yeni transaction'da baştan denenir. Düşük çekişmede ideal.
• e) REPEATABLE READ: PostgreSQL ikinci transaction'ı could not serialize access due to concurrent update ile düşürür; retry şart. Doğrulamada tam bu hata alındı.

Yanlış seçenekler:

• a) "READ COMMITTED'da PostgreSQL okunan satırı zaten kilitler": Yanlış. Düz SELECT hiçbir satır kilidi almaz; MVCC'de okuma yazmayı bloklamaz. İki transaction da 100 okur. (Senaryo PostgreSQL 16'da tekrarlandığında sonuç 110 oldu.)
• f) @Transactional(readOnly = true): Yanlış. Hibernate flush'ı kapatır, değişiklik hiç yazılmaz; ayrıca bir kilit mekanizması değildir.

Ana kavram: Okuma-hesaplama-yazma deseni, okuma ile yazma arasında bir boşluk bıraktığı her yerde (Java'da, iki ayrı SQL'de, iki ayrı HTTP isteğinde) lost update üretir. Çözüm ya boşluğu kapatmak (atomik ifade), ya boşluğu kilitlemek (pessimistic), ya da boşlukta değişiklik olduysa tespit edip yeniden denemektir (optimistic, REPEATABLE READ). Bankacılıkta bakiye için genelde atomik UPDATE + CHECK (balance >= 0) ve hareket tablosu (ledger) kullanılır; bakiye, hareketlerin toplamıyla mutabakat edilebilir olmalıdır.

Üç seviyede cevap

Soru: "Tabloya index ekledik ama sorgu hâlâ yavaş ve EXPLAIN Seq Scan gösteriyor. Ne yaparsın?"

Soru: "PostgreSQL'in varsayılan izolasyon seviyesi nedir ve neyi garanti etmez?"

Soru: "Optimistic mi pessimistic lock mu? Nasıl karar verirsin?"

Sorulmadı ama sorulabilir

Tablo partitioning

PostgreSQL 10+ declarative partitioning ile büyük bir tabloyu (ör. orders) created_at aralıklarına göre aylık alt tablolara bölebilirsin. Sorguda partition anahtarı varsa planner ilgisiz partition'ları hiç okumaz (partition pruning). Asıl kazanç operasyoneldir: eski veriyi silmek DELETE yerine DETACH/DROP PARTITION olur (bloat yok, saniyeler sürer), VACUUM ve index'ler partition başına küçük kalır. Bedeli: partition anahtarını içermeyen sorgular tüm partition'lara gider, primary key ve unique constraint'ler partition anahtarını içermek zorundadır. Tek cümle: "Partitioning ilk olarak bakım ve veri yaşam döngüsü için kullanılır; sorgu hızı ancak sorgular partition anahtarını içeriyorsa gelir."

UUID primary key ve index yerelliği

Rastgele UUIDv4 primary key, her INSERT'i B-tree'nin rastgele bir yaprağına yazar: sıcak sayfa seti büyür, sayfa bölünmeleri artar, cache verimi düşer. Sıralı anahtarlar (bigint identity, zaman sıralı UUIDv7) yeni kayıtları index'in sağ ucuna ekler. PostgreSQL 18 uuidv7() fonksiyonunu çekirdeğe ekledi; öncesinde uygulamada üretilir. Tek cümle: "Dağıtık üretim için UUID gerekiyorsa zaman sıralı UUIDv7 seçerim, index yerelliği korunur."

Read replica ve replication lag

PostgreSQL streaming replication varsayılan olarak asenkrondur: primary commit eder, replica WAL'ı biraz sonra uygular. Replica'dan okuyan bir servis kullanıcının az önce yazdığını göremeyebilir (read-your-writes ihlali). Sipariş verdikten sonraki "siparişlerim" sayfası gibi kritik okumalar primary'ye yönlendirilir; Spring'de @Transactional(readOnly = true) ile routing datasource kurulabilir ama bu bir tutarlılık kararıdır, performans ayarı değil. Lag pg_stat_replication (replay_lag) ile izlenir. Senkron replikasyon (synchronous_standby_names) commit'i replica onayına bağlar: veri kaybı korunur ama her commit gecikir ve replica düşerse yazmalar bekler. Tek cümle: "Async replica'yı eventual consistency kaynağı olarak görür, kendi yazdığını okuması gereken akışları primary'ye yönlendiririm."

Zaman aşımları: statement_timeout, lock_timeout, idle_in_transaction_session_timeout

Production'da üç sigorta: statement_timeout tek bir sorgunun süresini, lock_timeout bir kilidi bekleme süresini, idle_in_transaction_session_timeout transaction açık bırakılmış boşta bir oturumun yaşamını sınırlar. Rol veya veritabanı seviyesinde (ALTER ROLE app SET statement_timeout = '5s') ayarlanır; migration rolü için ayrıca yüksek tutulur. Özellikle şema değişikliklerinde lock_timeout hayat kurtarır: ALTER TABLE aldığı kilit için uzun bir sorguyu beklerken arkasında tüm sorgular kuyruğa girer; kısa lock_timeout ile başarısız olup tekrar denemek, tüm trafiği dondurmaktan iyidir. Tek cümle: "Her uygulama rolüne statement ve lock timeout koyar, migration'larda kısa lock_timeout ile retry yaparım."

Sık yapılan hatalar

• Composite index'te aralık kolonunu başa koymak → aralıktan sonraki kolonlar daraltmaz, sadece filtreler → eşitlik önce, sonra sıralama/aralık.
• id'yi INCLUDE'a eklemeden Index Only Scan beklemek → PostgreSQL ikincil index'leri PK taşımaz → seçilen tüm kolonları index'e koy.
• Planda Index Only Scan görüp Heap Fetches'e bakmamak → visibility map güncel değilse heap'e yine gidilir → autovacuum ayarlarını ve Heap Fetches'i kontrol et.
• date(created_at) = ... ile gün sorgulamak → index kullanılamaz, saat dilimi belirsiz → açık saat dilimli yarı açık aralık.
• Düşük seçicilikli kolona index eklemek (is_active, %95 true) → planner kullanmaz, yazma maliyeti kalır → partial index ya da hiç.
• Her kolona "belki lazım olur" diye index → yazmalar yavaşlar, HOT update biter → sorgu desenine göre index, idx_scan = 0 olanları temizle.
• Büyük tabloda düz CREATE INDEX → yazmalar dakikalarca bloklanır → CONCURRENTLY, migration'ı transaction dışı çalıştır, INVALID index'i kontrol et.
• Foreign key kolonuna index koymamak → PostgreSQL otomatik açmaz; join'ler ve parent silme/güncelleme sırasında FK kontrolü yavaşlar → FK kolonlarını index'le.
• "READ COMMITTED'da SELECT satırı kilitler" sanmak → MVCC'de düz SELECT kilit almaz, lost update oluşur → atomik UPDATE, FOR UPDATE ya da @Version.
• REPEATABLE READ/SERIALIZABLE'ı retry'sız kullanmak → 40001 hataları kullanıcıya 500 olarak döner → transaction dışından sınırlı retry.
• Retry'ı @Transactional metodun içinde yapmak → transaction rollback-only olmuştur → her denemede yeni transaction.
• Birden fazla satırı rastgele sırada kilitlemek → deadlock → sabit sıra (id artan), toplu ORDER BY ... FOR UPDATE.
• Transaction içinde dış servis çağrısı → kilitler ve bağlantı uzun tutulur, pool tükenir, VACUUM geride kalır → dış çağrıyı transaction dışına al, gerekiyorsa outbox.
• Pool'u büyüterek timeout'ları çözmeye çalışmak → veritabanı çekişmesi artar, toplam bağlantı max_connections'ı aşar → bağlantı tutma süresini düşür, ölç, gerekiyorsa PgBouncer.
• Autovacuum'u "performans için" kapatmak → bloat, Index Only Scan kaybı, en kötüsü wraparound → tablo bazında ayarla, kapatma.

Hızlı tekrar

Kaynaklar

• PostgreSQL: Transaction Isolation: seviyelerin PostgreSQL'deki gerçek davranışı, hata mesajları ve SSI önerileri.
• PostgreSQL: Explicit Locking: satır ve tablo kilit modları, deadlock ve advisory lock.
• PostgreSQL: Multicolumn Indexes: composite index'in hangi koşullara nasıl hizmet ettiği.
• PostgreSQL: Index-Only Scans and Covering Indexes: INCLUDE ve visibility map ilişkisi.
• PostgreSQL: Using EXPLAIN: plan node'ları, maliyet ve gerçek değerleri okumanın resmi rehberi.
• PostgreSQL: Routine Vacuuming: VACUUM, visibility map ve wraparound.
• PostgreSQL 18 Released: skip scan, asenkron I/O ve uuidv7 duyurusu.
• HikariCP: About Pool Sizing: havuzun neden küçük olması gerektiği ve boyutlandırma formülleri.
• Use The Index, Luke: Concatenated Keys: composite index kolon sırasının sezgisel anlatımı.
• Martin Kleppmann: Designing Data-Intensive Applications, Bölüm 7 (Transactions): snapshot isolation, write skew ve serializability'nin en iyi kitap anlatımı.