İçeriğe geç
Muhammet Şafak
en
Cevaplandı:

Sadece 'pending' satırları taranan bir kuyruk tablosunda partial index kullanmalı mıyım?


Soru

Postgres'te bir `jobs` kuyruk tablom var. İçinde milyonlarca `completed` satır birikiyor ama ben yalnızca birkaç bin `pending` satırı sorguluyorum: worker'lar `WHERE status = 'pending' ORDER BY created_at` ile sıradaki işi çekiyor. `status` üzerine normal bir index mi, yoksa `WHERE status = 'pending'` koşullu bir partial index mi kullanmalıyım? Partial index'i kullanırken planner'ın onu gerçekten seçmesi için nelere dikkat etmeliyim?

Cevap

Kısa cevap

Evet, partial index tam da bu senaryonun ders kitabı örneğidir.

Bağlam

WHERE status = 'pending' koşullu bir index yalnızca birkaç bin satırı tutar; küçük olur, belleğe sığar, taraması hızlı ve güncellemesi ucuzdur — milyonlarca ölü satıra hiç dokunmadan geçer. Index’in hangi kolonları, hangi sırayla içermesi gerektiğini composite index kolon sırası kaydında ayrıca ele almıştım; buradaki mesele o index’i ayrıca daraltmak.

Neden

  1. Partial index yalnızca eşleşen satırları indeksler. CREATE INDEX ... WHERE status='pending' ile index milyonlarca değil birkaç bin girdi tutar. Bu, onu neredeyse tamamen cache’te tutar ve her insert/update’te güncellenmesi gereken ağacı küçültür.

  2. Status üzerine düz index’ten kesinlikle daha iyidir. Düşük kardinaliteli status kolonuna düz bir index’in büyük kısmı işe yaramaz done değerleridir; planner çoğu zaman onu es geçip seq scan yapar. Bu erişim deseni için partial index net üstündür.

  3. Aynı mantık her çarpık predicate için geçerlidir. Ders yalnızca kuyruklara özgü değil: WHERE deleted_at IS NULL (soft-delete), WHERE processed = false gibi tablonun küçük bir azınlığını hedefleyen her sorguda partial index aynı kazancı verir. “Az sayıda satırı, çok sayıda ölü satırın arasından” çektiğiniz her yerde aklınıza gelsin.

Ne yapmalı

  1. ORDER BY kolonunu index’e koyun. Worker’lar tipik olarak WHERE status='pending' ORDER BY created_at FOR UPDATE SKIP LOCKED yapar. Sıralama anahtarını index’e dahil edin: (created_at) WHERE status='pending'. Böylece hem index sırasında tarama alırsınız hem de SKIP LOCKED ile kilitli satırları atlayıp sıradaki işi anında çekersiniz. SQL şöyle:

    CREATE INDEX idx_jobs_pending
        ON jobs (created_at)
        WHERE status = 'pending';
    
    -- worker'ın çekiş sorgusu:
    SELECT id, payload
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1;
  2. Predicate’i sorguda literal geçin. Sorgunuzun WHERE’i, index predicate’inin (status='pending') mantıksal olarak kapsadığı bir koşul olmalı. status = $1 gibi parametreyle sorarsanız planner index’i seçemeyebilir; koşulu literal 'pending' olarak yazın ki eşleşme kanıtlanabilsin.

  3. Churn ve bloat’a dikkat. Bir satır pending’den done’a döndüğünde partial index’ten düşer — bu iyi. Ama yoğun update trafiği bloat üretebilir; autovacuum’un sağlıklı çalışması önemli. İyi haber: index küçük olduğu için vacuum’u da ucuzdur.

Sonuç: Ben olsam çekiş predicate’i üzerine, ORDER BY kolonunu da içerecek şekilde bir partial index kurar ve onu FOR UPDATE SKIP LOCKED ile eşlerdim. Tek dikkat noktası: sorgularınızın 'pending'’i literal geçmesi ki planner index’i gerçekten seçsin. EXPLAIN (ANALYZE, BUFFERS) ile index scan aldığınızı bir kez doğrulayın; sonra milyonlarca completed satırın birikmesine rahatça aldırmazsınız.

Sahada karşılaşılan ve bana sorulan sorulardan derlendi.

İlgili ölçüm

Partial index kuyruk tablosunu kırk bir kat küçültüyor — planner onu seçtiği sürece

Milyonlarca ölü satır biriken bir Postgres kuyruk tablosunda partial index ne kazandırıyor, ve planner onu ne zaman seçmiyor?

Bulgu

10 milyon ölü satırda partial index saniyede 11.537 iş çıkarıyor, index'siz tablo 7. Composite index'le hız farkı küçük (%6,9) ama boyut farkı değil: 7,6 MB'a karşı 310,4 MB, ve partial tabloyla birlikte büyümüyor çünkü yalnız 5.000 canlı satırı indeksliyor. Asıl bulgu bunların hiçbiri: planner hazırlanmış bir deyimde generic plana geçtiği anda partial index tamamen devre dışı kalıyor — 11.752 tps 7'ye, 0,68 ms 1,1 saniyeye düşüyor. Bin altı yüz otuz beş kat. Aynı koşulda composite index etkilenmiyor.

88 gün önce ölçüldü

Yüksek güven

Yorumlar

Yorum yapmak için GitHub hesabınızla giriş yapmanız yeterli. Yorumlar GitHub Discussions üzerinde saklanır.

Diğer Sorular

Tüm sorular

Sitede Ara

Yazı, proje ve sayfalarda arama yapmak için yazmaya başlayın.

Esc ile kapat Pagefind ile güçlendirildi