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
-
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. -
Status üzerine düz index’ten kesinlikle daha iyidir. Düşük kardinaliteli
statuskolonuna düz bir index’in büyük kısmı işe yaramazdonedeğerleridir; planner çoğu zaman onu es geçip seq scan yapar. Bu erişim deseni için partial index net üstündür. -
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 = falsegibi 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ı
-
ORDER BY kolonunu index’e koyun. Worker’lar tipik olarak
WHERE status='pending' ORDER BY created_at FOR UPDATE SKIP LOCKEDyapar. 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 deSKIP LOCKEDile 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; -
Predicate’i sorguda literal geçin. Sorgunuzun
WHERE’i, index predicate’inin (status='pending') mantıksal olarak kapsadığı bir koşul olmalı.status = $1gibi parametreyle sorarsanız planner index’i seçemeyebilir; koşulu literal'pending'olarak yazın ki eşleşme kanıtlanabilsin. -
Churn ve bloat’a dikkat. Bir satır
pending’dendone’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ü
Yorumlar
Yorum yapmak için GitHub hesabınızla giriş yapmanız yeterli. Yorumlar GitHub Discussions üzerinde saklanır.