Uygulamalarda sayfalama: OFFSET ve keyset

OFFSET ile sayfalama

En bilinen yöntem: sırala, ilk (sayfa − 1) × boyut satırı atla, sonraki boyut kadar satırı al.

-- 3. sayfa, sayfa başına 10 ürün
SELECT product_id, name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

Basittir ve “sayfa 7'ye git” yapılabilir. Ama iki sorunu var:

1. Derin sayfalar yavaşlar: OFFSET 100000, veritabanının 100.000 satırı okuyup atması demektir.
2. Kayma: Kullanıcı sayfalar arasında gezerken yeni satır eklenirse aynı kayıt iki sayfada görünür veya biri atlanır.

Keyset (seek) sayfalama

Önceki sayfanın son satırının sıralama anahtarını hatırla ve “ondan sonrakileri” iste. İndeks doğrudan o noktaya gider; sayfa ne kadar derin olursa olsun hız aynıdır. Sonsuz kaydırma (infinite scroll) ve API'ler için standart yöntemdir.

-- Önceki sayfa product_id = 20 ile bitti
SELECT product_id, name, price
FROM products
WHERE product_id > 20
ORDER BY product_id
LIMIT 10;

Tekil olmayan sıralama: eşitlik bozucu

Fiyata göre sıralarken aynı fiyatlı ürünler olabilir; yalnızca fiyatı hatırlamak satır kaybettirir. Sıralamaya tekil bir sütun (product_id) eklenir ve koşul iki sütunla yazılır:

-- Fiyata göre azalan; önceki sayfa (price = 1899, product_id = 25) ile bitti
SELECT product_id, name, price
FROM products
WHERE price < 1899
   OR (price = 1899 AND product_id < 25)
ORDER BY price DESC, product_id DESC
LIMIT 5;

Ölçelim: OFFSET ve keyset aynı sayfayı nasıl okur?

Aynı veriyle PostgreSQL 16.4 üzerinde üretilen gerçek planlar. Önce OFFSET:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT order_id, placed_at FROM lk_orders ORDER BY order_id LIMIT 10 OFFSET 5000;

Gerçek çıktı (PostgreSQL 16.4, PGlite)

Limit  (cost=197.88..198.27 rows=10 width=12) (actual rows=10 loops=1)
  Buffers: shared hit=74
  ->  Index Scan using lk_orders_pkey on lk_orders  (cost=0.28..283.95 rows=7178 width=12) (actual rows=5010 loops=1)
        Buffers: shared hit=74

İç düğümde actual rows=5010: 10 satır için 5.010 satır okundu. Keyset:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT order_id, placed_at FROM lk_orders WHERE order_id > 5000 ORDER BY order_id LIMIT 10;

Gerçek çıktı (PostgreSQL 16.4, PGlite)

Limit  (cost=0.28..0.72 rows=10 width=12) (actual rows=10 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using lk_orders_pkey on lk_orders  (cost=0.28..95.40 rows=2178 width=12) (actual rows=10 loops=1)
        Index Cond: (order_id > 5000)
        Buffers: shared hit=3

Okunan sayfa 74 yerine 3. PostgreSQL belgesi de OFFSET ile atlanan satırların sunucuda yine hesaplandığını, bu yüzden büyük OFFSET'in verimsiz olabileceğini yazar. Fark sayfa derinleştikçe büyür. Kendi tarayıcında SQLite planını da gör:

EXPLAIN QUERY PLAN SELECT order_id, placed_at FROM lk_orders ORDER BY placed_at DESC LIMIT 10 OFFSET 5000;
CREATE INDEX ix_zaman ON lk_orders (placed_at, order_id);
EXPLAIN QUERY PLAN SELECT order_id, placed_at FROM lk_orders
WHERE placed_at < '2024-11-15 12:00:00'
   OR (placed_at = '2024-11-15 12:00:00' AND order_id < 3000)
ORDER BY placed_at DESC, order_id DESC LIMIT 10;