PostgreSQL 10 dirilis kembali pada awal Oktober 2017, hampir setahun yang lalu.
Salah satu "fitur" baru yang paling menarik adalah partisi deklaratif tanpa syarat. Tetapi bagaimana jika Anda tidak terburu-buru untuk meningkatkan ke 10k? Amazon, misalnya, tidak terburu-buru, dan
memperkenalkan dukungan PostgreSQL 10 hanya pada hari-hari terakhir Februari 2018.
Kemudian partisi tua yang baik melalui warisan datang untuk menyelamatkan. Saya adalah arsitek perangkat lunak departemen keuangan di perusahaan taksi, jadi semua contoh akan terkait dengan perjalanan dengan satu atau lain cara (kami akan meninggalkan masalah uang untuk waktu lain).
Sejak kami mulai menulis ulang sistem keuangan kami pada tahun 2015, ketika saya baru saja bergabung dengan perusahaan, tidak ada pembicaraan tentang partisi deklaratif. Jadi hingga hari ini, teknik yang dijelaskan di bawah ini telah berhasil digunakan.
Alasan asli untuk menulis artikel ini adalah bahwa sebagian besar contoh partisi di PostgreSQL yang saya temui sangat mendasar. Ini adalah tabel, ini adalah satu kolom yang kita lihat, dan mungkin bahkan tahu sebelumnya nilai apa yang ada di dalamnya. Tampaknya semuanya sederhana. Tapi kehidupan nyata membuat penyesuaiannya sendiri.
Dalam kasus kami, kami mempartisi tabel dalam dua kolom, yang salah satunya berisi tanggal perjalanan. Kasus inilah yang akan kami pertimbangkan.
Mari kita mulai dengan seperti apa tabel kita:
create table rides ( id bigserial not null primary key, tenant_id varchar(20) not null, ride_id varchar(36) not null, created_at timestamp with time zone not null, metadata jsonb
Untuk setiap penyewa, tabel berisi jutaan baris per bulan. Untungnya, data antara penyewa tidak pernah bersinggungan, dan permintaan yang paling sulit dibuat hanya dalam satu atau dua bulan.
Bagi mereka yang belum mempelajari cara kerja partisi PostgreSQL (beruntung dari Oracle, halo!), Saya akan menjelaskan secara singkat prosesnya.
PostgreSQL bergantung pada dua "fitur" untuk ini: kemampuan untuk mewarisi tabel, pewarisan tabel, dan memeriksa kondisi.
Mari kita mulai dengan warisan. Dengan menggunakan kata kunci INHERIT, kami menunjukkan bahwa tabel yang kami buat mewarisi semua bidang tabel yang diwarisi. Ini juga menciptakan hubungan antara dua tabel: membuat kueri dari orang tua, kami juga mendapatkan semua data dari anak-anak.
Kondisi yang diperiksa melengkapi gambar dengan memastikan bahwa data tidak berpotongan. Dengan demikian, pengoptimal PostgreSQL dapat memotong bagian dari tabel anak dengan mengandalkan data dari kueri.
Perangkap pertama dari pendekatan ini akan tampak sangat jelas: permintaan apa pun harus mengandung tenant_id. Namun demikian, jika Anda tidak selalu mengingatkan diri sendiri tentang hal ini, cepat atau lambat Anda sendiri akan menulis custom SQL di mana Anda lupa menentukan tenant_id ini. Akibatnya, pemindaian semua partisi dan database tidak berfungsi.
Tetapi kembali ke apa yang ingin kita capai. Pada tingkat aplikasi, saya ingin transparansi - kami selalu menulis ke tabel yang sama, dan database sudah memilih di mana tepatnya untuk meletakkan data ini.
Untuk melakukan ini, kami menggunakan prosedur tersimpan berikut:
CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER AS $BODY$ DECLARE partition_env TEXT; partition_date TIMESTAMP; partition_name TEXT; sql TEXT; BEGIN
Hal pertama yang harus Anda perhatikan adalah penggunaan TG_TABLE_NAME. Karena ini adalah pemicu, PostgreSQL mengisi beberapa variabel untuk kita yang dapat kita akses. Daftar lengkapnya dapat
ditemukan di sini .
Dalam kasus kami, kami ingin mendapatkan nama induk dari tabel tempat pemicunya bekerja. Dalam kasus kami, itu akan menjadi wahana. Kami menggunakan pendekatan serupa di beberapa layanan microser, dan bagian ini dapat ditransfer secara praktis tanpa perubahan.
PERFORM berguna jika kita ingin memanggil fungsi yang tidak mengembalikan apa pun. Biasanya, dalam contoh, mereka mencoba untuk menempatkan semua logika dalam satu fungsi, tetapi kami mencoba untuk berhati-hati.
USING NEW menunjukkan bahwa dalam kueri ini kami menggunakan nilai-nilai dari string yang kami coba tambahkan.
$1.* akan memperluas semua nilai baris baru. Bahkan, ini bisa diterjemahkan ke dalam
NEW.* . Apa yang diterjemahkan menjadi
NEW.ID, NEW.TENANT_ID, …Prosedur berikut, yang kami sebut dengan
PERFORM , akan membuat partisi baru, jika belum ada. Ini akan terjadi satu kali per periode untuk setiap penyewa.
CREATE OR REPLACE FUNCTION create_new_partition(parent_table_name text, env text, partition_date timestamp, partition_name text) RETURNS VOID AS $BODY$ DECLARE sql text; BEGIN
Seperti dijelaskan sebelumnya, kami menggunakan
INHERITS untuk membuat tabel yang mirip dengan induk, dan
CHECK untuk menentukan data apa yang harus ada di sana.
RAISE NOTICE hanya mencetak string ke konsol. Jika sekarang kita menjalankan
INSERT dari psql, kita dapat melihat apakah partisi telah dibuat.
Kami memiliki masalah baru.
INHERITS tidak mewarisi indeks. Untuk melakukan ini, kami memiliki dua solusi:
Buat indeks menggunakan warisan:
Gunakan
CREATE TABLE LIKE dan kemudian
ALTER TABLE INHERITSAtau buat indeks secara prosedural:
CREATE OR REPLACE FUNCTION index_partition(partition_name text) RETURNS VOID AS $BODY$ BEGIN
Sangat penting untuk tidak melupakan pengindeksan tabel anak, karena bahkan setelah dipartisi, masing-masing akan memiliki jutaan baris. Indeks pada orang tua tidak diperlukan dalam kasus kami, karena orang tua akan selalu tetap kosong.
Terakhir, kami membuat pemicu yang akan dipanggil saat baris baru dibuat:
CREATE TRIGGER before_insert_row_trigger BEFORE INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE insert_row();
Ada kehalusan lain yang jarang diperhatikan. Partisi adalah yang terbaik di kolom di mana data tidak pernah berubah. Dalam kasus kami, ini berfungsi: perjalanan tidak pernah mengubah tenant_id dan Created_at. Masalah yang muncul jika tidak demikian adalah PostreSQL tidak akan mengembalikan sebagian data kepada kami. Kami kemudian berjanji kepadanya PERIKSA bahwa semua data valid.
Ada beberapa solusi (kecuali yang sudah jelas - jangan bermutasi data yang kami partisi):
Alih-alih
UPDATE kami selalu melakukan
DELETE+INSERT di tingkat aplikasi
Kami menambahkan satu lagi pemicu pada
UPDATE yang akan mentransfer data ke partisi yang benar
Peringatan lain yang patut dipertimbangkan adalah bagaimana cara mengindeks kolom yang berisi tanggal dengan benar. Jika kita menggunakan AT TIME ZONE dalam kueri, kita tidak boleh lupa bahwa ini sebenarnya adalah panggilan fungsi. Dan itu berarti indeks kami harus berbasis fungsi. Saya lupa. Akibatnya, pangkalan mati lagi dari beban.
Aspek terakhir yang patut dipertimbangkan adalah bagaimana partisi berinteraksi dengan berbagai kerangka kerja ORM, baik itu ActiveRecord di Ruby atau GORM in Go.
Partisi di PostgreSQL bergantung pada fakta bahwa tabel induk akan selalu kosong. Jika Anda tidak menggunakan ORM, Anda dapat dengan aman kembali ke prosedur tersimpan pertama, dan mengubah KEMBALI BARU; pada KEMBALI NULL; Maka baris dalam tabel induk tidak akan ditambahkan, yang persis seperti yang kita inginkan.
Tetapi kenyataannya adalah bahwa sebagian besar ORM menggunakan KEMBALI klausa dengan INSERT. Jika kami mengembalikan NULL dari pemicu kami, ORM akan panik, meyakini bahwa baris tersebut belum ditambahkan. Ini ditambahkan, tetapi tidak di mana ORM mencari.
Ada beberapa cara untuk mengatasi ini:
- Jangan gunakan ORM untuk INSERT
- Patch ORM (yang terkadang disarankan dalam kasus ActiveRecord)
- Tambahkan pemicu lain, yang akan menghapus garis dari induknya.
Opsi terakhir tidak diinginkan, karena untuk setiap operasi kami akan melakukan tiga. Namun demikian, kadang-kadang tidak dapat dihindari, karena kami akan mempertimbangkannya secara terpisah:
CREATE OR REPLACE FUNCTION delete_parent_row() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN delete from only rides where id = NEW.ID; RETURN null; END; $BODY$ LANGUAGE plpgsql;
CREATE TRIGGER after_insert_row_trigger AFTER INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE delete_parent_row();
Hal terakhir yang perlu kita lakukan adalah menguji solusi kita. Untuk melakukan ini, kami membuat sejumlah baris:
DO $script$ DECLARE year_start_epoch bigint := extract(epoch from '20170101'::timestamptz at time zone 'UTC'); delta bigint := extract(epoch from '20171231 23:59:59'::timestamptz at time zone 'UTC') - year_start_epoch; tenant varchar; tenants varchar[] := array['tenant_a', 'tenant_b', 'tenant_c', 'tenant_d']; BEGIN FOREACH tenant IN ARRAY tenants LOOP FOR i IN 1..100000 LOOP insert into rides (tenant_id, created_at, ride_id) values (tenant, to_timestamp(random() * delta + year_start_epoch) at time zone 'UTC', i); END LOOP; END LOOP; END $script$;
Dan mari kita lihat bagaimana perilaku database:
explain select * from rides where tenant_id = 'tenant_a' and created_at AT TIME ZONE 'UTC' > '20171102' and created_at AT TIME ZONE 'UTC' <= '20171103';
Jika semuanya berjalan dengan baik, kita akan melihat hasil berikut:
Append (cost=0.00..4803.76 rows=4 width=196) -> Seq Scan on rides (cost=0.00..4795.46 rows=3 width=196) Filter: (((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone) AND ((tenant_id)::text = 'tenant_a'::text)) -> Index Scan using rides_tenant_a_2017_11_tenant_timezone_idx on rides_tenant_a_2017_11 (cost=0.28..8.30 rows=1 width=196) Index Cond: (((tenant_id)::text = 'tenant_a'::text) AND ((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone)) (5 rows)
Terlepas dari kenyataan bahwa setiap penyewa memiliki ratusan ribu baris, kami hanya memilih dari potongan data yang diinginkan. Sukses!
Saya harap artikel ini menarik bagi mereka yang belum mengenal apa itu partisi dan bagaimana penerapannya di PostgreSQL. Tetapi mereka yang topik ini tidak lagi baru, tetap belajar beberapa trik menarik.
UPD:
Seperti yang diamati
bigtrot dengan benar, semua street magic ini tidak akan berfungsi jika pengaturan
CONSTRAINT_EXCLUSION dimatikan.
Anda dapat memverifikasi ini menggunakan perintah
show CONSTRAINT_EXCLUSION
Pengaturan memiliki tiga nilai: hidup, mati dan partisi
Konfigurasi partisi lebih optimal jika Anda tiba-tiba suka menggunakan PERIKSA KENDALA tidak hanya untuk partisi, tetapi juga untuk normalisasi data.