Basis data Messenger (bagian 2): mempartisi “untuk keuntungan”

Kami telah berhasil merancang struktur database PostgreSQL kami untuk menyimpan korespondensi, satu tahun telah berlalu, pengguna secara aktif mengisinya, dan sekarang berisi jutaan catatan, dan... sesuatu mulai melambat.

• Bagian 1: mendesain rangka dasar
• Bagian 2: membagi “untuk keuntungan”

Fakta adalah bahwa Seiring bertambahnya ukuran tabel, “kedalaman” indeks juga meningkat. - meskipun secara logaritmik. Namun seiring berjalannya waktu, hal ini memaksa server untuk melakukan tugas baca/tulis yang sama memproses halaman data berkali-kali lebih banyakdaripada di awal.

Di sinilah ia datang untuk menyelamatkan pembagian.

Izinkan saya mencatat bahwa kita tidak berbicara tentang sharding, yaitu mendistribusikan data antara database atau server yang berbeda. Karena bahkan membagi data menjadi beberapa server, Anda tidak akan terbebas dari masalah indeks yang “membengkak” seiring waktu. Jelas bahwa jika Anda mampu mengoperasikan server baru setiap hari, maka masalah Anda tidak lagi terletak pada basis data tertentu.

Kami tidak akan mempertimbangkan skrip khusus untuk mengimplementasikan partisi "dalam perangkat keras", tetapi pendekatan itu sendiri - apa dan bagaimana yang harus "dipotong-potong", dan apa tujuan dari keinginan tersebut.

Konsep

Mari kita tentukan tujuan kita sekali lagi: kita ingin memastikan bahwa hari ini, besok, dan dalam satu tahun, jumlah data yang dibaca oleh PostgreSQL selama operasi baca/tulis tetap kurang lebih sama.

Untuk apa pun akumulasi data secara kronologis (pesan, dokumen, log, arsip, ...) pilihan yang wajar sebagai kunci partisi adalah tanggal/waktu acara. Dalam kasus kami, peristiwa seperti itu terjadi momen pengiriman pesan.

Perhatikan bahwa pengguna hampir selalu hanya bekerja dengan yang "terbaru". data tersebut - mereka membaca pesan terbaru, menganalisis log terbaru,... Tidak, tentu saja, mereka dapat menelusuri lebih jauh ke masa lalu, tetapi mereka sangat jarang melakukan hal ini.

Dari kendala-kendala tersebut jelas bahwa solusi pesan yang optimal adalah bagian "harian". - lagipula, pengguna kami hampir selalu membaca apa yang datang kepadanya “hari ini” atau “kemarin”.

Jika kita menulis dan membaca hampir hanya di satu bagian dalam sehari, maka ini juga memberi kita penggunaan memori dan disk yang lebih efisien - karena semua indeks bagian dengan mudah masuk ke dalam RAM, berbeda dengan indeks "besar dan gemuk" di seluruh tabel.

selangkah demi selangkah

Secara umum, semua hal di atas terdengar seperti keuntungan berkelanjutan. Dan itu bisa dicapai, tapi untuk ini kita harus berusaha keras - karena keputusan untuk mempartisi salah satu entitas mengarah pada kebutuhan untuk “melihat” entitas terkait.

Pesan, properti dan proyeksinya

Karena kami memutuskan untuk memotong pesan berdasarkan tanggal, masuk akal juga untuk membagi properti entitas yang bergantung padanya (file terlampir, daftar penerima), dan juga berdasarkan tanggal pesan.

Karena salah satu tugas khas kita adalah melihat register pesan (belum dibaca, masuk, semua), maka logis juga untuk “menariknya” ke dalam partisi berdasarkan tanggal pesan.

Kami menambahkan kunci partisi (tanggal pesan) ke semua tabel: penerima, file, pendaftar. Anda tidak perlu menambahkannya ke pesan itu sendiri, tetapi gunakan DateTime yang ada.

benang

Karena hanya ada satu topik untuk beberapa pesan, tidak ada cara untuk “memotongnya” dalam model yang sama; Anda harus mengandalkan sesuatu yang lain. Dalam kasus kami, ini ideal tanggal pesan pertama dalam korespondensi — yaitu, momen penciptaan topik.

Tambahkan kunci partisi (tanggal topik) ke semua tabel: topik, peserta.

Tapi sekarang kita punya dua masalah sekaligus:

• Di bagian mana saya harus mencari pesan tentang topik tersebut?
• Di bagian mana saya harus mencari topik dari pesan tersebut?

Tentu saja kita bisa terus mencari di semua bagian, tapi ini akan sangat menyedihkan dan akan meniadakan semua kemenangan kita. Oleh karena itu, untuk mengetahui di mana tepatnya mencarinya, kami akan membuat tautan/petunjuk logis ke bagian:

• kami akan menambahkan dalam pesan bidang tanggal topik
• mari kita tambahkan ke topik tanggal pesan ditetapkan korespondensi ini (dapat berupa tabel terpisah, atau serangkaian tanggal)

Karena akan ada sedikit perubahan pada daftar tanggal pesan untuk setiap korespondensi individu (bagaimanapun juga, hampir semua pesan jatuh pada 1-2 hari yang berdekatan), saya akan fokus pada opsi ini.

Secara total, struktur database kami mengambil bentuk berikut, dengan mempertimbangkan partisi:

Tabel: RU, jika Anda tidak menyukai alfabet Cyrillic pada nama tabel/bidang, lebih baik tidak melihat

-- секции по дате сообщения
CREATE TABLE "Сообщение_YYYYMMDD"(
  "Сообщение"
    uuid
      PRIMARY KEY
, "Тема"
    uuid
, "ДатаТемы"
    date
, "Автор"
    uuid
, "ДатаВремя" -- используем как дату
    timestamp
, "Текст"
    text
);

CREATE TABLE "Адресат_YYYYMMDD"(
  "ДатаСообщения"
    date
, "Сообщение"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Сообщение", "Персона")
);

CREATE TABLE "Файл_YYYYMMDD"(
  "ДатаСообщения"
    date
, "Файл"
    uuid
      PRIMARY KEY
, "Сообщение"
    uuid
, "BLOB"
    uuid
, "Имя"
    text
);

CREATE TABLE "РеестрСообщений_YYYYMMDD"(
  "ДатаСообщения"
    date
, "Владелец"
    uuid
, "ТипРеестра"
    smallint
, "ДатаВремя"
    timestamp
, "Сообщение"
    uuid
, PRIMARY KEY("Владелец", "ТипРеестра", "Сообщение")
);
CREATE INDEX ON "РеестрСообщений_YYYYMMDD"("Владелец", "ТипРеестра", "ДатаВремя" DESC);

-- секции по дате темы
CREATE TABLE "Тема_YYYYMMDD"(
  "ДатаТемы"
    date
, "Тема"
    uuid
      PRIMARY KEY
, "Документ"
    uuid
, "Название"
    text
);

CREATE TABLE "УчастникТемы_YYYYMMDD"(
  "ДатаТемы"
    date
, "Тема"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Тема", "Персона")
);

CREATE TABLE "ДатыСообщенийТемы_YYYYMMDD"(
  "ДатаТемы"
    date
, "Тема"
    uuid
      PRIMARY KEY
, "Дата"
    date
);

Hemat banyak uang

Nah, bagaimana jika kita menggunakan bukan opsi pembagian klasik berdasarkan distribusi nilai bidang (melalui pemicu dan pewarisan atau PARTITION BY), dan "secara manual" di tingkat aplikasi, Anda akan melihat bahwa nilai kunci partisi sudah disimpan dalam nama tabel itu sendiri.

Jadi, jika Anda memang demikian Apakah Anda sangat khawatir dengan jumlah data yang disimpan?, lalu Anda dapat menghilangkan bidang "ekstra" ini dan menangani tabel tertentu. Benar, semua pilihan dari beberapa bagian dalam hal ini harus ditransfer ke sisi aplikasi.

Sumber: www.habr.com