Dlaczego różne wartości LIMIT zwracają różne wyniki w PostgreSQL (i jak to naprawić)

Zdarzyło Ci się kiedyś, że to samo zapytanie do PostgreSQL uruchomione z LIMIT 5 i z LIMIT 20 zwracało zupełnie inne pierwsze 5 wyników? Jeśli tak, to trafiłeś na jeden z najbardziej mylących aspektów paginacji w SQL — taki, który potrafi doprowadzić dewelopera do szału.

Dokładnie to spotkało nas na produkcji, w naszym search API, i niewiele brakowało, a kosztowałoby nas kilku dużych klientów. Poniżej opisuję prawdziwy problem i to, jak go rozwiązaliśmy.

Problem, który prawie położył nasze search API

Użytkownicy zgłaszali, że podpowiedzi wyszukiwania (pierwsze 5 wyników) pokazują inne produkty niż to samo wyszukiwanie na głównej stronie wyników (pierwsze 20 wyników). To samo zapytanie z inną wartością LIMIT zwracało inne pierwsze 5 produktów.

Oto problematyczne zapytanie, które siało ten cały zamęt:

SELECT
    fp.id,
    fp.code,
    product.grossPrice,
    fp.avg_similarity,
    fp.categoryPriority
FROM FinalProductsPool fp
JOIN Product ON product.id = fp.id
ORDER BY
    -- Promoted products first
    CASE WHEN fp.code = ANY($1) THEN 1 ELSE 0 END DESC,
    -- Then by relevance
    fp.avg_similarity DESC,
    fp.avg_similarity_without_worst DESC,
    fp.categoryPriority DESC,
    fp.plcRank DESC
LIMIT 5; -- vs LIMIT 20 = different results!

Dlaczego ten koszmar w ogóle się dzieje

Trzej winowajcy całego chaosu:

1. Niejednoznaczne rozstrzyganie remisów

Kiedy wiele wierszy ma identyczne wartości we wszystkich kolumnach z ORDER BY, PostgreSQL nie gwarantuje, które z nich pójdą pierwsze. Zwraca je w takiej kolejności, jaka akurat jest najwydajniejsza.

Nasze produkty często miały:

Ten sam similarity score (0.75)
Ten sam priorytet kategorii (100)
Ten sam PLC rank (0.5)

Efekt? PostgreSQL losowo tasował "remisujące" produkty.

2. Różne plany zapytań

Różne wartości LIMIT uruchamiają różne strategie wykonania:

LIMIT 5: używa quick-sortu zoptymalizowanego pod małe zbiory wyników
LIMIT 20: może sięgnąć po heap-sort, który przetwarza dane w inny sposób

3. Precyzja liczb zmiennoprzecinkowych

Wyliczenia podobieństwa produkują wartości, które wyglądają identycznie, ale takie nie są:

-- These look equal but cause random ordering
0.7500000001
0.7500000000
0.7499999999

Jednolinijkowa poprawka, która uratowała nasze API

Rozwiązanie jest proste: zawsze dodawaj unikalne, deterministyczne pole jako ostatnie kryterium sortowania.

ORDER BY
    CASE WHEN fp.code = ANY($1) THEN 1 ELSE 0 END DESC,
    fp.avg_similarity DESC,
    fp.avg_similarity_without_worst DESC,
    fp.categoryPriority DESC,
    fp.plcRank DESC,
    COALESCE(NULLIF(product.grossPrice, '')::NUMERIC, 0) DESC,
    fp.id ASC  -- 🎯 The magic line!

Dodanie fp.id ASC gwarantuje, że:

Identyczne produkty zawsze są uporządkowane po ID
Wyniki są w pełni deterministyczne
Pierwsze 5 produktów jest takie samo niezależnie od LIMIT

Jak to wygląda w prawdziwym projekcie

Tak naprawiliśmy to w naszym stacku Node.js/Prisma:

const searchResults = await prisma.$queryRaw`
  WITH RankedProducts AS (
    SELECT 
      p.id,
      p.code,
      p.grossPrice,
      similarity_score(p.name, ${searchQuery}) as relevance
    FROM products p
    WHERE p.active = true
  )
  SELECT * FROM RankedProducts
  ORDER BY
    relevance DESC,
    COALESCE(NULLIF(grossPrice, '')::NUMERIC, 0) DESC,
    id ASC  -- Deterministic tie-breaker
  OFFSET ${skip}
  LIMIT ${limit}
`

Kluczowe szczegóły implementacji

Obsłuż poprawnie wartości NULL

ORDER BY
    COALESCE(similarity_score, 0) DESC,
    COALESCE(category_priority, 0) DESC,
    COALESCE(NULLIF(price, '')::NUMERIC, 0) DESC,
    id ASC -- Never NULL in primary keys

Dobierz właściwy tie-breaker

Twój tie-breaker powinien być:

Unikalny: klucz główny albo inny unikalny identyfikator
Stabilny: nie zmienia się między zapytaniami
Zaindeksowany: ze względu na wydajność (klucze główne mają indeks z automatu)

Weź pod uwagę logikę biznesową

Czasem chcesz rozstrzygać remisy w konkretny sposób:

ORDER BY
    relevance DESC,
    price DESC,        -- Higher prices first when tied
    created_at DESC,   -- Newer products for tied prices
    id ASC            -- Final deterministic breaker

Wpływ na wydajność: minimalny koszt, ogromna korzyść

Dodanie deterministycznego sortowania praktycznie nie kosztuje wydajności:

Klucze główne zawsze mają indeks
Tie-breaker działa tylko na wierszach o identycznych wartościach sortowania
Nowoczesne bazy danych radzą sobie z tym wydajnie

Testowanie poprawki

Sprawdź, czy rozwiązanie faktycznie działa:

-- Test 1: Small limit
SELECT id FROM your_table ORDER BY score DESC, id ASC LIMIT 5;

-- Test 2: Larger limit
SELECT id FROM your_table ORDER BY score DESC, id ASC LIMIT 20;

-- First 5 IDs should be identical! ✅

Najważniejsze wnioski

Zawsze dodawaj deterministyczne tie-breakery do paginowanych zapytań
Używaj unikalnych, stabilnych kolumn (klucze główne sprawdzają się idealnie)
Obsługuj wartości NULL jawnie w kolumnach sortowania
Testuj z różnymi wartościami LIMIT, żeby mieć pewność co do spójności
Uwzględniaj logikę biznesową przy wyborze kryteriów rozstrzygania remisów

Kiedy ta poprawka jest Ci potrzebna

Czerwone lampki, które wołają o to rozwiązanie:

Wyniki paginacji zmieniają się losowo
To samo zapytanie z różnymi wartościami LIMIT daje różne pierwsze wyniki
Użytkownicy narzekają na "skaczące" wyniki wyszukiwania
Sklepy e-commerce z niespójną kolejnością produktów
Dowolne złożone sortowanie, w którym mogą wystąpić remisy

Następnym razem, gdy zobaczysz niespójną paginację, pamiętaj: dodaj deterministyczny tie-breaker, a Twoje problemy same się posortują.

Ta poprawka uratowała nasze API przed wściekłymi klientami i dała nam przewidywalną paginację w tysiącach wyszukiwań produktów dziennie. Czasem proste problemy naprawdę mają proste rozwiązania.