Paginarea devine tot mai lentă la pagini mari (LIMIT ... OFFSET 500000)
Cu `LIMIT 20 OFFSET 500000`, baza citește și aruncă 500.000 de rânduri pentru a returna 20, deci fiecare pagină e mai lentă decât precedenta, iar roboții care parcurg toate paginile încarcă baza. Folosește paginare cu cursor (keyset): `WHERE id > :ultimul_id ORDER BY id LIMIT 20`, pe o coloană indexată.
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
OFFSET pare ieftin, dar baza nu poate „sări” direct la rândul 500.000: trebuie să-l găsească, parcurgând în ordine toate rândurile de dinainte, chiar dacă folosește un index. Pagina 1 durează 1 ms, pagina 25.000 poate dura secunde. Problema rămâne invizibilă în teste (puține date, primele pagini) și apare în producție, mai ales din cauza crawler-elor, a exporturilor și a API-urilor parcurse integral de integrări.
Paginarea cu cursor (keyset / seek) reține ultima valoare văzută (id, sau perechea (data, id) pentru ordonări după dată) și cere rândurile de după ea. Cu un index pe coloanele de ordonare, fiecare pagină costă la fel, indiferent cât de departe e. Bonus: nu sare și nu repetă rânduri când se inserează date noi între cereri.
Compromisul: nu mai poți sări direct la „pagina 742”; pentru interfețe, „Încarcă mai multe” sau scroll infinit se potrivesc natural.
De ce apare: cauzele frecvente
1OFFSET pentru export complet
SELECT * FROM comenzi ORDER BY id LIMIT 1000 OFFSET :offset;SELECT * FROM comenzi WHERE id > :ultimul_id ORDER BY id LIMIT 1000;Fiecare lot pornește direct de la ultimul id, prin index.
2Ordonare după dată, fără cursor stabil
SELECT * FROM postari ORDER BY creat_la DESC LIMIT 20 OFFSET 400;SELECT * FROM postari
WHERE (creat_la, id) < (:creat_la, :id)
ORDER BY creat_la DESC, id DESC LIMIT 20;
-- index pe (creat_la DESC, id DESC)Perechea (dată, id) e unică și ordonată, deci cursorul e stabil.
Repară eroarea, direct în pagină
Răspunde la cele trei întrebări despre paginarea care încetinește la pagini mari.
Indiciu
Ce face baza cu rândurile dinaintea OFFSET-ului?
Reparația
- q1: Baza citește și aruncă primele 500.000 de rânduri.
- q2:
WHERE id > :ultimul_id ORDER BY id LIMIT 20. - q3: Adevărat.
De ce merge
OFFSET-ul obligă baza să parcurgă toate rândurile de dinainte. Paginarea cu cursor sare direct prin index și e stabilă la inserări.
Alte forme ale mesajului
Slow query: LIMIT 20 OFFSET 500000Rows Removed by Filter / rows examined: 500020
Cum o eviți pe viitor
- Folosește paginare cu cursor pentru liste mari și API-uri.
- Limitează adâncimea maximă a paginării cu OFFSET.
- Indexează coloanele de ordonare.
- Evită
COUNT(*)exact pe tabele uriașe la fiecare pagină.
Întrebări frecvente
Pot păstra numerotarea paginilor?
Pentru primele câteva zeci de pagini, OFFSET e acceptabil. Pentru adâncimi mari, limitează-l sau trece la cursor.
Ce trimit clientului ca cursor?
Valorile ultimului rând (de exemplu id-ul sau perechea dată-id), adesea codificate într-un șir opac, ca next_cursor.
Erori înrudite
Slow query: Seq Scan pe un tabel mare (index lipsă) Interogarea citește tot tabelul rând cu rând (`Seq Scan` în PostgreSQL, `type: ALL` în MySQL) pentru că nu există un index pe coloana din `WHERE`. Pe tabele mari, fiecare citire durează și încarcă baza. Rulează `EXPLAIN ANALYZE`, creează indexul potrivit și verifică din nou planul. N+1 queries: o pagină face sute de interogări la baza de date Codul citește o listă (1 interogare), apoi, pentru fiecare element, încă o interogare pentru datele asociate (N interogări), de obicei din lazy loading-ul unui ORM. La 100 de elemente, pagina face 101 interogări și supraîncarcă baza. Încarcă datele asociate într-o singură interogare (JOIN, fetch join, `IN (...)`) sau în loturi. ERROR: canceling statement due to statement timeout PostgreSQL a oprit interogarea pentru că a rulat mai mult decât `statement_timeout`. Timeout-ul și-a făcut treaba: a protejat baza de o interogare prea lungă. Optimizează interogarea (index, mai puține date), mut-o pe o replică sau precalculează rezultatul; nu dezactiva timeout-ul global. Învață System Design de la zero, cu exerciții la fiecare lecție
Curs System Design: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 10 lecții sunt gratuite.
