PostgreSQL એન્ટિપેટર્ન: રજિસ્ટ્રી નેવિગેટ કરવું

આજે SQL માં કોઈ જટિલ કેસ અને અત્યાધુનિક અલ્ગોરિધમ્સ હશે નહીં. કેપ્ટન ઓબ્વિયસના સ્તરે, બધું ખૂબ જ સરળ હશે - ચાલો તે કરીએ ઇવેન્ટ રજિસ્ટ્રી જોઈ રહ્યા છીએ સમય દ્વારા ક્રમાંકિત.

એટલે કે, ડેટાબેઝમાં એક સાઇન છે events, અને તેણી પાસે એક ક્ષેત્ર છે ts - બરાબર તે સમયે કે જે સમયે અમે આ રેકોર્ડ્સને વ્યવસ્થિત રીતે પ્રદર્શિત કરવા માંગીએ છીએ:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

તે સ્પષ્ટ છે કે અમારી પાસે ત્યાં એક ડઝન રેકોર્ડ્સ હશે નહીં, તેથી અમને કેટલાક સ્વરૂપોની જરૂર પડશે પૃષ્ઠ નેવિગેશન.

#0 "હું મારી માતાનો પોગ્રોમિસ્ટ છું"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

તે લગભગ મજાક નથી - તે દુર્લભ છે, પરંતુ જંગલીમાં જોવા મળે છે. કેટલીકવાર, ORM સાથે કામ કર્યા પછી, SQL સાથે "ડાયરેક્ટ" કાર્ય પર સ્વિચ કરવું મુશ્કેલ બની શકે છે.

પરંતુ ચાલો વધુ સામાન્ય અને ઓછી સ્પષ્ટ સમસ્યાઓ તરફ આગળ વધીએ.

#1. ઓફસેટ

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

26 નંબર ક્યાંથી આવ્યો? આ એક સ્ક્રીન ભરવા માટેની એન્ટ્રીઓની અંદાજિત સંખ્યા છે. વધુ સ્પષ્ટ રીતે, 25 પ્રદર્શિત રેકોર્ડ્સ, વત્તા 1, સંકેત આપે છે કે નમૂનામાં ઓછામાં ઓછું કંઈક બીજું છે અને તે આગળ વધવાનો અર્થપૂર્ણ છે.

અલબત્ત, આ મૂલ્ય વિનંતીના મુખ્ય ભાગમાં "સીવેલું" હોઈ શકતું નથી, પરંતુ પરિમાણમાંથી પસાર થઈ શકે છે. પરંતુ આ કિસ્સામાં, PostgreSQL શેડ્યૂલર એ જ્ઞાન પર આધાર રાખી શકશે નહીં કે પ્રમાણમાં ઓછા રેકોર્ડ્સ હોવા જોઈએ - અને સરળતાથી બિનઅસરકારક યોજના પસંદ કરશે.

અને જ્યારે એપ્લીકેશન ઈન્ટરફેસમાં હોય ત્યારે, રજિસ્ટ્રીને જોવાનું વિઝ્યુઅલ "પૃષ્ઠો" વચ્ચે સ્વિચિંગ તરીકે અમલમાં મૂકવામાં આવે છે, લાંબા સમય સુધી કોઈ પણ શંકાસ્પદ વસ્તુની નોંધ લેતું નથી. બરાબર એ ક્ષણ સુધી કે જ્યારે, સગવડતા માટેના સંઘર્ષમાં, UI/UX એ ઇન્ટરફેસને "અંતહીન સ્ક્રોલ" પર રીમેક કરવાનું નક્કી કરે છે - એટલે કે, બધી રજિસ્ટ્રી એન્ટ્રીઓ એક જ સૂચિમાં દોરવામાં આવે છે જેને વપરાશકર્તા ઉપર અને નીચે સ્ક્રોલ કરી શકે છે.

અને તેથી, આગામી પરીક્ષણ દરમિયાન, તમે પકડાઈ ગયા છો રેકોર્ડનું ડુપ્લિકેશન રજિસ્ટ્રીમાં. શા માટે, કારણ કે કોષ્ટકમાં સામાન્ય અનુક્રમણિકા છે (ts), તમારી ક્વેરી જેના પર આધાર રાખે છે?

બરાબર કારણ કે તમે તે ધ્યાનમાં લીધું નથી ts અનન્ય કી નથી આ કોષ્ટકમાં. ખરેખર, અને તેના મૂલ્યો અનન્ય નથી, વાસ્તવિક પરિસ્થિતિઓમાં કોઈપણ "સમય" ની જેમ - તેથી, સમાન કી મૂલ્યને સૉર્ટ કરવાના માળખામાં અલગ અંતિમ ક્રમને કારણે બે સંલગ્ન ક્વેરીઝમાં સમાન રેકોર્ડ સરળતાથી પૃષ્ઠથી પૃષ્ઠ પર "જમ્પ" થાય છે.

વાસ્તવમાં, અહીં એક બીજી સમસ્યા પણ છુપાયેલી છે, જે ધ્યાનમાં લેવી વધુ મુશ્કેલ છે - કેટલીક એન્ટ્રીઓ બતાવવામાં આવશે નહીં બધા પર! છેવટે, "ડુપ્લિકેટ" રેકોર્ડ્સ કોઈ બીજાનું સ્થાન લે છે. સુંદર ચિત્રો સાથે વિગતવાર સમજૂતી મળી શકે છે અહીં વાંચો.

ઇન્ડેક્સ વિસ્તરણ

એક ઘડાયેલું વિકાસકર્તા સમજે છે કે ઇન્ડેક્સ કીને અનન્ય બનાવવાની જરૂર છે, અને સૌથી સહેલો રસ્તો એ છે કે તેને દેખીતી રીતે અનન્ય ફીલ્ડ સાથે વિસ્તૃત કરવું, જેના માટે PK યોગ્ય છે:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

અને વિનંતી પરિવર્તિત થાય છે:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. "કર્સર્સ" પર સ્વિચ કરો

થોડા સમય પછી, DBA તમારી પાસે આવે છે અને તમારી વિનંતીઓથી "પ્રસન્ન" થાય છે તેઓ તેમના ઓફસેટ નિયમો સાથે સર્વરને નરકની જેમ લોડ કરે છે, અને સામાન્ય રીતે, આ પર સ્વિચ કરવાનો સમય છે બતાવેલ છેલ્લા મૂલ્યમાંથી નેવિગેશન. તમારી ક્વેરી ફરી પરિવર્તિત થાય છે:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

તે આવે ત્યાં સુધી તમે રાહતનો શ્વાસ લીધો...

#3. સફાઈ સૂચકાંકો

કારણ કે એક દિવસ તમારું DBA વાંચ્યું બિનઅસરકારક સૂચકાંકો શોધવા વિશેનો લેખ અને તે સમજાયું "નવીનતમ નથી" ટાઇમસ્ટેમ્પ સારી નથી. અને હું ફરીથી તમારી પાસે આવ્યો - હવે એ વિચાર સાથે કે તે ઇન્ડેક્સ હજી પણ પાછું ફેરવવું જોઈએ (ts DESC).

પરંતુ પૃષ્ઠો વચ્ચે "જમ્પિંગ" રેકોર્ડ્સની પ્રારંભિક સમસ્યાનું શું કરવું?.. અને બધું સરળ છે - તમારે રેકોર્ડ્સની અચોક્કસ સંખ્યા સાથે બ્લોક્સ પસંદ કરવાની જરૂર છે!

સામાન્ય રીતે, કોણ આપણને “ચોક્કસ 26” નહીં, પણ “26 કરતાં ઓછું નહીં” વાંચવાની મનાઈ કરે છે? ઉદાહરણ તરીકે, જેથી આગામી બ્લોકમાં હોય દેખીતી રીતે અલગ અર્થ સાથે રેકોર્ડ ts - પછી બ્લોક્સ વચ્ચે "જમ્પિંગ" રેકોર્ડ્સમાં કોઈ સમસ્યા નહીં હોય!

આ કેવી રીતે પ્રાપ્ત કરવું તે અહીં છે:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

અહિયાં શું થઇ રહ્યું છે?

1. અમે 25 રેકોર્ડ "ડાઉન" કરીએ છીએ અને "સીમા" મૂલ્ય મેળવીએ છીએ ts.
2. જો ત્યાં પહેલાથી કંઈ નથી, તો પછી NULL મૂલ્યને સાથે બદલો -infinity.
3. અમે પ્રાપ્ત મૂલ્ય વચ્ચેના મૂલ્યોના સમગ્ર સેગમેન્ટને બાદ કરીએ છીએ ts અને ઈન્ટરફેસમાંથી પસાર થયેલ $1 પેરામીટર (અગાઉનું "છેલ્લું" રેન્ડર કરેલ મૂલ્ય).
4. જો બ્લોક 26 કરતા ઓછા રેકોર્ડ સાથે પરત કરવામાં આવે છે, તો તે છેલ્લો છે.

અથવા સમાન ચિત્ર:

કારણ કે હવે અમારી પાસે છે નમૂનામાં કોઈ ચોક્કસ "શરૂઆત" નથી, તો પછી આ વિનંતિને વિરુદ્ધ દિશામાં "વિસ્તરણ" કરવાથી અને "સંદર્ભ બિંદુ" થી ડેટા બ્લોકના ગતિશીલ લોડિંગને બંને દિશામાં - નીચે અને ઉપર બંનેમાં અમલમાં કરવાથી અમને કંઈપણ અટકાવતું નથી.

નોંધ

1. હા, આ કિસ્સામાં આપણે ઇન્ડેક્સને બે વાર એક્સેસ કરીએ છીએ, પરંતુ બધું જ “કેવળ ઇન્ડેક્સ દ્વારા” છે. તેથી, સબક્વેરી માત્ર પરિણમશે માત્ર એક વધારાના અનુક્રમણિકા માટે સ્કેન કરો.
2. તે એકદમ સ્પષ્ટ છે કે આ તકનીકનો ઉપયોગ ત્યારે જ થઈ શકે છે જ્યારે તમારી પાસે મૂલ્યો હોય ts માત્ર તક દ્વારા પાર કરી શકે છે, અને તેમાંના ઘણા નથી. જો તમારો લાક્ષણિક કેસ "00:00:00.000 પર એક મિલિયન રેકોર્ડ્સ" છે, તો તમારે આ કરવું જોઈએ નહીં. મારો મતલબ છે કે તમારે આવા કિસ્સા બનવા દેવા જોઈએ નહીં. પરંતુ જો આવું થાય, તો વિસ્તૃત ઇન્ડેક્સ સાથે વિકલ્પનો ઉપયોગ કરો.

સોર્સ: www.habr.com