🐅 🚾 🤜🏻 Antipattern PostgreSQL: Navigasi Registri 🤳 🙇🏿 🤛🏻

Hari ini tidak akan ada kasus rumit dan algoritma SQL canggih. Semuanya akan sangat sederhana, di tingkat Kapten Bukti - kami melakukan peninjauan register acara dengan mengurutkan berdasarkan waktu.

Artinya, ada piring di pangkalan events, dan bidangnya tstepat waktu yang sama dengan mana kami ingin menampilkan catatan ini secara tertib:

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

CREATE INDEX ON events(ts DESC);

Jelas bahwa kita tidak akan memiliki selusin entri di sana, jadi kita akan membutuhkan semacam navigasi halaman .

# 0 "Aku seorang pogrommist di ibuku"

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

Hampir tidak ada lelucon - jarang, tetapi ditemukan di alam liar. Kadang-kadang setelah bekerja dengan ORM bisa sulit untuk beralih ke pekerjaan "langsung" dengan SQL.

Tapi mari kita beralih ke masalah yang lebih umum dan kurang jelas.

# 1. MENGIMBANGI

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 -   , $1 -

26? . , 25 , 1, , - .

, «» , . PostgreSQL , , — .

Dan sementara di antarmuka aplikasi tampilan registri diimplementasikan sebagai peralihan antara "halaman" visual, tidak ada seorang pun untuk waktu yang lama memperhatikan sesuatu yang mencurigakan. Tepat sampai saat ketika, dalam perjuangan untuk kenyamanan, UI / UX tidak memutuskan untuk membuat ulang antarmuka menjadi "gulir tanpa akhir" - yaitu, semua entri registri digambar dalam satu daftar yang pengguna dapat putar ke atas dan ke bawah.

Dan sekarang, selama tes berikutnya, Anda terjebak entri duplikat di registri. Mengapa, karena tabel memiliki indeks normal (ts)yang menjadi dasar kueri Anda?

Tepat karena Anda tidak mempertimbangkan apa yang tsbukan kunci unik dalam tabel ini. Sebenarnya, maknanya tidak unik, seperti "waktu" dalam kondisi nyata - itulah sebabnya catatan yang sama di dua kueri tetangga dengan mudah "melompat" dari halaman ke halaman karena urutan akhir yang berbeda sebagai bagian dari pengurutan nilai kunci yang sama.

Bahkan, masalah kedua juga tersembunyi di sini, yang jauh lebih sulit untuk diperhatikan - beberapa entri tidak akan ditampilkan sama sekali! Lagi pula, catatan "duplikat" mengambil tempat seseorang. Penjelasan terperinci dengan gambar-gambar indah dapat ditemukan di sini .

Memperluas Indeks

Pengembang yang licik memahami bahwa Anda perlu membuat kunci indeks unik, dan cara termudah adalah mengembangkannya dengan bidang unik yang sengaja dibuat, yang cocok untuk PK:

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

Dan permintaan bermutasi:

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

# 2 Transisi ke "kursor"

Beberapa waktu kemudian, DBA mendatangi Anda dan “senang” bahwa permintaan Anda sedang membebani server dengan OFFSET yang ditarik kuda , dan secara umum, saatnya untuk beralih ke navigasi dari nilai terakhir yang ditunjukkan . Permintaan Anda bermutasi lagi:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) --      
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Anda menghela napas lega sebelum itu datang ...

# 3 Pembersihan indeks

Karena suatu hari DBA Anda membaca artikel tentang menemukan indeks yang tidak efisien dan menyadari bahwa cap waktu "terakhir" tidak baik . Dan dia mendatangi Anda lagi - sekarang dengan pemikiran bahwa indeks ini akan berubah menjadi (ts DESC).

Tapi apa yang harus dilakukan dengan masalah awal "melompat" catatan antara halaman? .. Dan semuanya sederhana - Anda harus memilih blok dengan jumlah catatan yang tidak terbatas!

Secara umum, siapa yang melarang kita membaca bukan “tepat 26”, tetapi “tidak kurang dari 26”? Misalnya, sehingga di blok berikutnya ada catatan dengan nilai yang jelas berbedats - maka tidak akan ada masalah dengan "melompat" di antara blok!

Inilah cara melakukannya:

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;

Apa yang terjadi disini?

Kami mundur 25 catatan dan mendapatkan nilai "batas" ts.
Jika tidak ada apa-apa di sana, ganti nilai NULL dengan -infinity.
Kurangi seluruh segmen nilai antara nilai yang diterima tsdan parameter $ 1 yang dilewatkan dari antarmuka (nilai yang ditarik sebelumnya "terakhir").
Jika satu blok dikembalikan dengan kurang dari 26 entri, itu adalah yang terakhir.

Atau gambar yang sama:

Karena sekarang sampel kami tidak memiliki "permulaan" yang pasti , tidak ada yang mencegah kami dari "membalikkan" permintaan ini di arah yang berlawanan dan menerapkan pemuatan dinamis blok data dari "titik referensi" di kedua arah - baik turun dan naik.

Komentar

, , « ». Index Only Scan.
, , ts , . — « 00:00:00.000», . , . , .