Offset Yerine İmleç Sayfalama: Keyset Sorgusu, Benzersiz Sıralama Anahtarı ve Sayfa Sınırında Kaybolan Satırlar

Liste uçlarının neredeyse hepsi LIMIT 20 OFFSET 40 ile başlar. Küçük tabloda sorunsuz çalışır, testte de sorunsuz çalışır. Sorun iki yerden gelir: tablo büyüdükçe derin sayfalar yavaşlar ve liste okunurken araya yeni kayıt girerse kullanıcı aynı satırı iki kez görür ya da bir satırı hiç görmez. Bu yazı offset sayfalamanın neden böyle davrandığını, yerine kullanılan imleç (keyset) sayfalamanın nasıl doğru kurulduğunu ve en çok nerede yanlış kurulduğunu anlatıyor.

Offset neden yavaşlar

OFFSET 100000 veritabanına "ilk yüz bin satırı atla" der ama atlamak bedava değildir. Veritabanı o satırları sıralı biçimde üretmek, saymak ve çöpe atmak zorundadır. İndeks olsa bile indeksin ilk yüz bin girdisi gezilir. Sonuç olarak sayfa numarası büyüdükçe sorgu süresi doğrusal artar. Kullanıcılar nadiren derin sayfaya gider ama arama motoru tarayıcıları, dışa aktarma betikleri ve "tüm kayıtları çek" diye yazılmış entegrasyonlar tam olarak bunu yapar. Bu yüzden derin sayfa yavaşlığı genellikle bir insanın değil bir otomasyonun yarattığı yük olarak görünür.

İkinci sorun tutarlılıktır. Kullanıcı ilk sayfayı aldıktan sonra listenin başına iki kayıt eklenirse ikinci sayfanın ilk iki satırı birinci sayfanın son iki satırıdır. Kayıt silinirse tersi olur, bir satır hiç görünmez. Bir arayüzde bu küçük bir rahatsızlıktır. Bir senkronizasyon işinde ise kayıp veri demektir.

İmleç sayfalama ne yapar

İmleç sayfalamada "kaçıncı sayfadayım" yerine "en son hangi satırı gördüm" bilgisi taşınır. Bir sonraki sayfa, o satırın sıralama anahtarından sonraki satırlardır. Veritabanı bu koşulu indekste doğrudan konumlanarak karşılar, öncesindeki satırları gezmez. Bu yüzden milyonuncu satırdan sonraki sayfa da ilk sayfa kadar hızlıdır, araya giren ya da silinen kayıtlar da sonraki sayfayı kaydırmaz.

Temel kural şudur: sıralama anahtarı benzersiz olmalıdır. Yalnızca created_at ile sıralarsanız aynı zaman damgasına sahip iki satırdan biri sayfa sınırında kaybolur. Çözüm, sıralamaya benzersiz bir sütunu, genellikle birincil anahtarı, ikinci anahtar olarak eklemektir.

PostgreSQL'de doğru kurulum

En yeni kayıtlar önce gelecek şekilde bir liste düşünelim. Önce sıralamayla birebir uyan bir indeks:

CREATE INDEX orders_created_id_idx
    ON orders (created_at DESC, id DESC);

İlk sayfa:

SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 21;

Sonraki sayfa, bir önceki sayfanın son satırının değerleriyle:

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 21;

(created_at, id) < ($1, $2) satır karşılaştırmasıdır: önce created_at karşılaştırılır, eşitse id. PostgreSQL bu ifadeyi çok sütunlu indekste aralık taraması olarak kullanabilir. Aynı anlamı created_at < $1 OR (created_at = $1 AND id < $2) biçiminde de yazabilirsiniz; mantıken aynıdır ama planlayıcı her zaman aynı verimli planı çıkarmaz. PostgreSQL'de satır karşılaştırmasını tercih edin, EXPLAIN ile indeks taraması gördüğünüzü doğrulayın.

Sayfa boyutundan bir fazla satır istemek (LIMIT 21 ile 20'lik sayfa) "sonraki sayfa var mı" sorusunu ek sorgu yapmadan yanıtlar. 21. satır geldiyse sonraki sayfa vardır; o satırı cevaba koymazsınız.

İmleci istemciye nasıl verirsiniz

İmlecin içeriği son satırın sıralama değerleridir, ama istemciye bunları ham parametre olarak vermeyin. Değerleri bir yapıda toplayıp base64 ile kodlanmış opak bir dize olarak verin:

{
  "items": [ ... ],
  "next_cursor": "eyJjIjoiMjAyNi0wMS0xNVQxMDozMDowMC4xMjM0NTZaIiwiaSI6OTgxMn0"
}

Opak tutmanın iki gerekçesi var. Birincisi, sıralama anahtarını sonradan değiştirdiğinizde istemcileri kırmamak. İkincisi, istemcinin imleci elle üretip beklenmedik sorgular kurmasını zorlaştırmak. İmleç sorguya parametre olarak girdiği için SQL enjeksiyonu riski yoktur, ama yine de çözülen değerlerin tipini doğrulayın; bozuk imleç 500 değil 400 dönmelidir.

İmlecin içine sıralama yönünü ve etkin filtreleri de koyun. Kullanıcı bir filtreyle aldığı imleci başka bir filtreyle gönderirse sonuç anlamsızdır. İmleçteki filtre isteğinkiyle uyuşmuyorsa isteği reddedin.

Tuzaklar

Zaman damgası hassasiyeti. PostgreSQL timestamptz mikrosaniye tutar. İmleci üretirken değeri JavaScript Date nesnesinden geçirirseniz milisaniyeye yuvarlanır. Bir sonraki sorguda created_at < 10:30:00.123 koşulu, aslında 10:30:00.123456 olan son satırı yeniden getirir ya da aynı milisaniyedeki satırları atlar. Kullanıcı sayfa sınırında tekrar eden satırlar görür ve sebebi uzun süre bulunamaz. İmleçteki zaman damgasını veritabanından gelen metin biçimiyle, tam hassasiyetle taşıyın ya da sıralama için zaman damgası yerine artan bir tamsayı kimlik kullanın.

NULL değerli sıralama sütunu. Satır karşılaştırmasında bir taraf NULL ise sonuç NULL olur ve satır elenir. Sıralama sütunu NULL alabiliyorsa o satırlar ya hiç görünmez ya da yalnızca ilk sayfada görünür. Sıralama sütunlarını NOT NULL yapın; yapamıyorsanız sorguda COALESCE ile sabit bir değere çevirin ve indeksi de aynı ifade üzerine kurun.

Karışık yönlü sıralama. ORDER BY priority ASC, created_at DESC gibi bir sıralamada satır karşılaştırması işe yaramaz çünkü tek bir < iki farklı yönü ifade edemez. Burada açık OR biçimini yazmanız gerekir: priority > $1 OR (priority = $1 AND created_at < $2) OR .... Bu sorgu hızla karmaşıklaşır ve planlayıcıya zor gelir. Mümkünse ürün tarafında sıralama seçeneklerini tek yönlü kombinasyonlarla sınırlayın.

Değişen sıralama anahtarı. updated_at ile sıralanan bir listede, kullanıcı sayfalar arasında gezerken bir kayıt güncellenirse o kayıt listenin başına taşınır. Kullanıcı onu ya iki kez görür ya da hiç görmez. Bu imleç sayfalamanın kusuru değil, değişen bir anahtara göre sıralamanın doğasıdır. Senkronizasyon gibi kayıp kabul edilemeyen işlerde, değişiklik akışını ayrı bir artan sürüm sütunu üzerinden okuyun.

Toplam sayı ihtiyacı. "Toplam 48.213 sonuç, sayfa 7 / 2411" göstermek her istekte COUNT(*) demektir ve büyük tabloda bu, sayfanın kendisinden pahalıdır. İmleç sayfalamaya geçerken arayüzden kesin toplamı kaldırmayı da konuşun. Gerekirse yaklaşık değer verin; PostgreSQL'de filtresiz bir tablo için pg_class.reltuples iyi bir tahmindir.

Geri gitmek. Önceki sayfa için sıralamayı ve karşılaştırmayı ters çevirip ilk satırın değerleriyle sorgulayın, sonra gelen sonucu uygulamada ters çevirin. Bunu unutursanız önceki sayfa ters sırada görünür.

Ne zaman offset kalmalı

Her listeyi imlece çevirmek gerekmez. Birkaç bin satırı geçmeyen yönetim tabloları, kullanıcının gerçekten "sayfa 14'e git" dediği arayüzler ve sıralama sütununun kullanıcı tarafından serbestçe seçildiği tablolar offset ile daha iyi hizmet görür. İmleç sayfalama keyfi bir sayfaya atlamayı desteklemez; bunu isteyen bir ürün gereksinimi varsa imleç yanlış araçtır.

İmleç sayfalamayı şu yerlerde varsayılan yapın: dış API'lerin liste uçları, sonsuz kaydırmalı akışlar, dışa aktarma ve senkronizasyon işleri. Bu uçları bir kez offset ile yayınladığınızda sonradan değiştirmek istemcileri kıran bir sözleşme değişikliği olur, bu yüzden kararı ilk sürümde vermek en ucuz zamandır.