Antipattern PostgreSQL: navigasi registri

Saat ini tidak akan ada kasus rumit dan algoritma canggih dalam SQL. Semuanya akan menjadi sangat sederhana, di level Captain Obvious - ayo lakukan melihat registri acara diurutkan berdasarkan waktu.

Artinya, ada tanda di database events, dan dia memiliki ladang ts - tepatnya waktu di mana kita ingin menampilkan catatan-catatan ini secara teratur:

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

CREATE INDEX ON events(ts DESC);

Jelas bahwa kami tidak akan memiliki selusin rekaman di sana, jadi kami memerlukan suatu bentuk navigasi halaman.

#0. “Aku adalah pelaku pogrom 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]);

Ini hampir bukan lelucon - jarang terjadi, tetapi ditemukan di alam liar. Terkadang, setelah bekerja dengan ORM, sulit untuk beralih ke pekerjaan "langsung" dengan SQL.

Namun mari kita beralih ke masalah yang lebih umum dan tidak terlalu kentara.

#1. MENGIMBANGI

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

Dari manakah angka 26 itu berasal? Ini adalah perkiraan jumlah entri untuk mengisi satu layar. Lebih tepatnya, 25 catatan yang ditampilkan, ditambah 1, menandakan bahwa setidaknya ada sesuatu yang lain dalam sampel dan masuk akal untuk melanjutkan.

Tentu saja, nilai ini tidak dapat "dijahit" ke dalam isi permintaan, tetapi diteruskan melalui sebuah parameter. Namun dalam kasus ini, penjadwal PostgreSQL tidak akan dapat mengandalkan pengetahuan bahwa catatan yang ada seharusnya relatif sedikit - dan akan dengan mudah memilih rencana yang tidak efektif.

Dan saat berada di antarmuka aplikasi, melihat registri diimplementasikan sebagai peralihan antar “halaman” visual, tidak ada yang memperhatikan sesuatu yang mencurigakan untuk waktu yang lama. Tepat hingga saat, demi kenyamanan, UI/UX memutuskan untuk mengubah antarmuka menjadi "gulir tanpa akhir" - yaitu, semua entri registri digambar dalam satu daftar yang dapat digulir pengguna ke atas dan ke bawah.

Jadi, pada pengujian berikutnya, Anda tertangkap duplikasi catatan di registri. Mengapa, karena tabel tersebut memiliki indeks normal (ts), yang menjadi dasar kueri Anda?

Tepatnya karena Anda tidak memperhitungkannya ts bukanlah kunci unik di tabel ini. Sebenarnya, dan nilainya tidak unik, seperti "waktu" mana pun dalam kondisi nyata - oleh karena itu, catatan yang sama dalam dua kueri yang berdekatan dengan mudah "melompat" dari halaman ke halaman karena urutan akhir yang berbeda dalam kerangka pengurutan nilai kunci yang sama.

Faktanya, ada juga masalah kedua yang tersembunyi di sini, yang jauh lebih sulit untuk diperhatikan - beberapa entri tidak akan ditampilkan sama sekali! Lagi pula, catatan “duplikat” menggantikan orang lain. Penjelasan rinci dengan gambar-gambar indah dapat ditemukan baca di sini.

Memperluas indeks

Pengembang yang cerdik memahami bahwa kunci indeks perlu dibuat unik, dan cara termudah adalah memperluasnya dengan bidang yang jelas unik, yang mana PK sangat cocok untuk:

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

Dan permintaannya bermutasi:

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

#2. Beralih ke "kursor"

Beberapa waktu kemudian, DBA mendatangi Anda dan “senang” dengan permintaan Anda mereka memuat server dengan sangat keras dengan aturan OFFSET mereka, dan secara umum, saatnya beralih ke navigasi dari nilai terakhir yang ditampilkan. Kueri Anda bermutasi lagi:

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

Kamu menghela nafas lega hingga hal itu terjadi..

#3. Membersihkan indeks

Karena suatu hari DBA Anda membaca artikel tentang menemukan indeks yang tidak efektif dan menyadari itu Stempel waktu "bukan yang terbaru" tidak bagus. Dan saya mendatangi Anda lagi - sekarang dengan pemikiran bahwa indeks itu masih harus kembali menjadi (ts DESC).

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

Secara umum, siapa yang melarang kita membaca bukan “tepat 26”, tetapi “tidak kurang dari 26”? Misalnya agar di blok berikutnya ada catatan dengan arti yang jelas berbeda ts - maka tidak akan ada masalah dengan "melompat" rekor antar blok!

Berikut cara mencapainya:

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 di sini?

Kami melangkah 25 catatan "turun" dan mendapatkan nilai "batas". ts.
Jika belum ada apa-apa, ganti nilai NULL dengan -infinity.
Kami mengurangi seluruh segmen nilai antara nilai yang diterima ts dan parameter $1 diteruskan dari antarmuka (nilai render "terakhir" sebelumnya).
Jika sebuah blok dikembalikan dengan kurang dari 26 catatan, itu adalah yang terakhir.

Atau gambar yang sama:

Karena sekarang kita punya sampel tidak memiliki “awal” tertentu, maka tidak ada yang menghalangi kami untuk "memperluas" permintaan ini ke arah yang berlawanan dan menerapkan pemuatan dinamis blok data dari "titik referensi" di kedua arah - baik ke bawah maupun ke atas.

Catatan

Ya, dalam hal ini kita mengakses indeks dua kali, tetapi semuanya “murni berdasarkan indeks”. Oleh karena itu, subquery hanya akan menghasilkan ke satu Pemindaian Hanya Indeks tambahan.
Jelas sekali bahwa teknik ini hanya dapat digunakan bila Anda memiliki nilai ts hanya bisa menyeberang secara kebetulan, dan jumlahnya tidak banyak. Jika kasus umum Anda adalah “satu juta catatan pada 00:00:00.000”, Anda tidak boleh melakukan ini. Maksud saya, Anda tidak boleh membiarkan kasus seperti itu terjadi. Namun jika ini terjadi, gunakan opsi dengan indeks yang diperluas.

Sumber: www.habr.com

Antipattern PostgreSQL: Menavigasi Registri