Artikel ini akan membahas fitur yang disediakan oleh partisi deklaratif built-in atau deklaratif dalam versi 12 PostgreSQL. Demonstrasi disiapkan untuk
ceramah eponymous di konferensi HighLoad ++ Siberia 2019 (upd: sebuah
video muncul dengan ceramah).
Semua contoh dieksekusi pada versi beta yang baru muncul:
=> SELECT version();
version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12beta1 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 32-bit (1 row)
Contoh-contoh menggunakan tabel pemesanan dan tiket dari database demo. Tabel reservasi berisi entri selama tiga bulan dari Juni hingga Agustus 2017 dan memiliki struktur berikut:
=> \d bookings
Table "bookings.bookings" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | not null | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | not null | Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Reservasi dapat mencakup beberapa tiket. Struktur meja dengan tiket:
=> \d tickets
Table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+--------- ticket_no | character(13) | | not null | book_ref | character(6) | | not null | passenger_id | character varying(20) | | not null | passenger_name | text | | not null | contact_data | jsonb | | | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
Informasi ini harus cukup untuk memahami contoh-contoh di mana kami akan mencoba membuat tabel dipartisi.
→ Pelajari lebih lanjut tentang basis demo di
sini.Kisaran Partisi
Pertama, cobalah membuat tabel pemesanan dipartisi berdasarkan rentang tanggal. Dalam hal ini, tabel akan dibuat seperti ini:
=> CREATE TABLE bookings_range ( book_ref character(6), book_date timestamptz, total_amount numeric(10,2) ) PARTITION BY RANGE(book_date);
Bagian terpisah untuk setiap bulan:
=> CREATE TABLE bookings_range_201706 PARTITION OF bookings_range FOR VALUES FROM ('2017-06-01'::timestamptz) TO ('2017-07-01'::timestamptz); => CREATE TABLE bookings_range_201707 PARTITION OF bookings_range FOR VALUES FROM ('2017-07-01'::timestamptz) TO ('2017-08-01'::timestamptz);
Untuk menunjukkan batas-batas bagian, Anda dapat menggunakan tidak hanya konstanta, tetapi juga ekspresi, misalnya, panggilan fungsi. Nilai ekspresi dihitung pada saat bagian dibuat dan disimpan di direktori sistem:
=> CREATE TABLE bookings_range_201708 PARTITION OF bookings_range FOR VALUES FROM (to_timestamp('01.08.2017','DD.MM.YYYY')) TO (to_timestamp('01.09.2017','DD.MM.YYYY'));
Deskripsi tabel:
=> \d+ bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | | | extended | | book_date | timestamp with time zone | | | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: RANGE (book_date) Partitions: bookings_range_201706 FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03'), bookings_range_201707 FOR VALUES FROM ('2017-07-01 00:00:00+03') TO ('2017-08-01 00:00:00+03'), bookings_range_201708 FOR VALUES FROM ('2017-08-01 00:00:00+03') TO ('2017-09-01 00:00:00+03')
Sudah cukup. Tidak ada pemicu untuk memasukkan catatan, tidak ada kendala PERIKSA yang dibutuhkan. Parameter CONSTRAINT_EXCLUSION juga tidak diperlukan, Anda bahkan dapat mematikannya:
=> SET constraint_exclusion = OFF;
Mengisi dengan tata letak otomatis di bagian:
=> INSERT INTO bookings_range SELECT * FROM bookings;
INSERT 0 262788
Sintaks deklaratif masih menyembunyikan tabel yang diwarisi, sehingga Anda dapat melihat distribusi baris di bagian dengan kueri:
=> SELECT tableoid::regclass, count(*) FROM bookings_range GROUP BY tableoid;
tableoid | count -----------------------+-------- bookings_range_201706 | 7303 bookings_range_201707 | 167062 bookings_range_201708 | 88423 (3 rows)
Tetapi tidak ada data di tabel induk:
=> SELECT * FROM ONLY bookings_range;
book_ref | book_date | total_amount ----------+-----------+-------------- (0 rows)
Periksa pengecualian bagian dalam rencana kueri:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------- Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (2 rows)
Memindai hanya satu bagian, seperti yang diharapkan.
Contoh berikut menggunakan fungsi to_timestamp dengan kategori variabilitas STABLE alih-alih konstanta:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Append Subplans Removed: 2 -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (4 rows)
Nilai fungsi dihitung ketika rencana kueri diinisialisasi dan bagian dari bagian dikeluarkan dari tampilan (Subplans Dihapus baris).
Tapi ini hanya berfungsi untuk SELECT. Saat mengubah data, pengecualian bagian berdasarkan nilai fungsi STABLE belum diterapkan:
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Delete on bookings_range Delete on bookings_range_201706 Delete on bookings_range_201707 Delete on bookings_range_201708 -> Seq Scan on bookings_range_201706 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201708 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (10 rows)
Karena itu, Anda harus menggunakan konstanta:
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------------- Delete on bookings_range Delete on bookings_range_201707 -> Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (4 rows)
Penyortiran indeks
Untuk melakukan kueri berikut, pengurutan hasil yang diperoleh dari bagian yang berbeda diperlukan. Oleh karena itu, dalam paket kueri, kita melihat simpul SORT dan biaya awal paket yang tinggi:
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN ------------------------------------------------------------------------------------------ Sort (cost=24649.77..25077.15 rows=170952 width=52) Sort Key: bookings_range_201706.book_date -> Append (cost=0.00..4240.28 rows=170952 width=52) -> Seq Scan on bookings_range_201706 (cost=0.00..94.94 rows=4794 width=52) -> Seq Scan on bookings_range_201707 (cost=0.00..2151.30 rows=108630 width=52) -> Seq Scan on bookings_range_201708 (cost=0.00..1139.28 rows=57528 width=52) (6 rows)
Buat indeks di book_date. Alih-alih satu indeks global, indeks dibuat di setiap bagian:
=> CREATE INDEX book_date_idx ON bookings_range(book_date);
=> \di bookings_range*
List of relations Schema | Name | Type | Owner | Table ----------+-------------------------------------+-------+---------+----------------------- bookings | bookings_range_201706_book_date_idx | index | student | bookings_range_201706 bookings | bookings_range_201707_book_date_idx | index | student | bookings_range_201707 bookings | bookings_range_201708_book_date_idx | index | student | bookings_range_201708 (3 rows)
Kueri sebelumnya dengan penyortiran sekarang dapat menggunakan indeks pada kunci partisi dan segera mengembalikan hasilnya dari bagian yang berbeda dalam bentuk diurutkan. Node SORT tidak diperlukan dan biaya minimum diperlukan untuk menghasilkan baris pertama dari hasilnya:
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.12..14880.88 rows=262788 width=52) -> Index Scan using bookings_range_201706_book_date_idx on bookings_range_201706 (cost=0.28..385.83 rows=7303 width=52) -> Index Scan using bookings_range_201707_book_date_idx on bookings_range_201707 (cost=0.42..8614.35 rows=167062 width=52) -> Index Scan using bookings_range_201708_book_date_idx on bookings_range_201708 (cost=0.42..4566.76 rows=88423 width=52) (4 rows)
Indeks yang dipartisi dibuat dengan cara ini didukung secara terpusat. Saat menambahkan bagian baru, indeks akan secara otomatis dibuat di atasnya. Dan Anda tidak dapat menghapus indeks hanya satu bagian:
=> DROP INDEX bookings_range_201706_book_date_idx;
ERROR: cannot drop index bookings_range_201706_book_date_idx because index book_date_idx requires it HINT: You can drop index book_date_idx instead.
Hanya seluruhnya:
=> DROP INDEX book_date_idx;
DROP INDEX
BUAT INDEKS ... CONCURRENTLY
Saat membuat indeks pada tabel yang dipartisi, Anda tidak dapat menentukan secara CONCURRENTLY.
Tetapi Anda dapat melakukan hal berikut. Pertama, kami membuat indeks hanya di tabel utama, itu akan menerima status tidak valid:
=> CREATE INDEX book_date_idx ON ONLY bookings_range(book_date);
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ f (1 row)
Kemudian buat indeks pada semua bagian dengan opsi CONCURRENTLY:
=> CREATE INDEX CONCURRENTLY book_date_201706_idx ON bookings_range_201706 (book_date); => CREATE INDEX CONCURRENTLY book_date_201707_idx ON bookings_range_201707 (book_date); => CREATE INDEX CONCURRENTLY book_date_201708_idx ON bookings_range_201708 (book_date);
Sekarang kami menghubungkan indeks lokal ke global:
=> ALTER INDEX book_date_idx ATTACH PARTITION book_date_201706_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201707_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201708_idx;
Ini mirip dengan menghubungkan tabel partisi, yang akan kita bahas nanti. Segera setelah semua bagian indeks terhubung, indeks utama akan mengubah statusnya:
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ t (1 row)
Hubungkan dan lepaskan bagian
Pembuatan bagian secara otomatis tidak disediakan. Oleh karena itu, mereka harus dibuat terlebih dahulu, sebelum catatan dengan nilai baru dari kunci partisi ditambahkan ke tabel.
Kami akan membuat bagian baru sementara transaksi lain bekerja dengan tabel, pada saat yang sama kami akan melihat kunci:
=> BEGIN; => SELECT count(*) FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
count ------- 5 (1 row)
=> SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -----------------------+----------------- bookings_range_201708 | AccessShareLock bookings_range_201707 | AccessShareLock bookings_range_201706 | AccessShareLock bookings_range | AccessShareLock (4 rows)
Kunci AccessShareLock dikenakan pada tabel utama, semua bagian dan indeks pada awal pernyataan. Perhitungan fungsi to_timestamp dan pengecualian bagian terjadi kemudian. Jika sebuah konstanta digunakan sebagai ganti fungsi, hanya tabel utama dan bagian booking_range_201707 yang akan dikunci. Oleh karena itu, jika mungkin, tetapkan konstanta dalam permintaan - ini harus dilakukan, jika tidak, jumlah baris dalam pg_locks akan meningkat secara proporsional dengan jumlah bagian, yang dapat menyebabkan kebutuhan untuk meningkatkan max_locks_per_transaction.
Tanpa menyelesaikan transaksi sebelumnya, buat bagian berikut untuk September di sesi baru:
|| => CREATE TABLE bookings_range_201709 (LIKE bookings_range); || => BEGIN; || => ALTER TABLE bookings_range ATTACH PARTITION bookings_range_201709 FOR VALUES FROM ('2017-09-01'::timestamptz) TO ('2017-10-01'::timestamptz); || => SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -------------------------------------+-------------------------- bookings_range_201709_book_date_idx | AccessExclusiveLock bookings_range | ShareUpdateExclusiveLock bookings_range_201709 | ShareLock bookings_range_201709 | AccessExclusiveLock (4 rows)
Saat membuat bagian baru, kunci ShareUpdateExclusiveLock, kompatibel dengan AccessShareLock, dikenakan di tabel utama. Oleh karena itu, operasi penambahan partisi tidak bertentangan dengan kueri terhadap tabel yang dipartisi.
=> COMMIT;
|| => COMMIT;
Partisi dilakukan dengan perintah ALTER TABLE ... DETACH PARTITION. Bagian itu sendiri tidak dihapus, tetapi menjadi tabel independen. Data dapat diunduh darinya, dapat dihapus, dan jika perlu dihubungkan kembali (ATTACH PARTITION).
Pilihan lain untuk menonaktifkan adalah menghapus bagian dengan perintah DROP TABLE.
Sayangnya, kedua opsi, DROP TABLE dan DETACH PARTITION, gunakan kunci AccessExclusiveLock di tabel utama.
Bagian default
Jika Anda mencoba untuk menambahkan catatan yang bagiannya belum dibuat, kesalahan akan terjadi. Jika perilaku ini tidak diinginkan, Anda dapat membuat bagian default:
=> CREATE TABLE bookings_range_default PARTITION OF bookings_range DEFAULT;
Misalkan ketika menambahkan catatan, mereka mencampuradukkan tanggal tanpa menentukan milenium:
=> INSERT INTO bookings_range VALUES('XX0000', '0017-09-01'::timestamptz, 0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount ------------------------+----------+------------------------------+-------------- bookings_range_default | XX0000 | 0017-09-01 00:00:00+02:30:17 | 0.00 (1 row) INSERT 0 1
Kami perhatikan bahwa frase RETURNING mengembalikan baris baru, yang jatuh ke bagian default.
Setelah mengatur tanggal saat ini (mengubah kunci partisi), catatan secara otomatis pindah ke bagian yang diinginkan, pemicu tidak diperlukan:
=> UPDATE bookings_range SET book_date = '2017-09-01'::timestamptz WHERE book_ref = 'XX0000' RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount -----------------------+----------+------------------------+-------------- bookings_range_201709 | XX0000 | 2017-09-01 00:00:00+03 | 0.00 (1 row) UPDATE 1
Bagian Daftar Nilai
Dalam database demo, kolom book_ref harus menjadi kunci utama dari tabel pemesanan. Namun, skema partisi yang dipilih tidak memungkinkan membuat kunci seperti itu:
=> ALTER TABLE bookings_range ADD PRIMARY KEY(book_ref);
ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "bookings_range" lacks column "book_date" which is part of the partition key.
Kunci partisi harus dimasukkan dalam kunci utama.
Untuk memecah berdasarkan bulan dan masih memasukkan book_ref di kunci utama, mari kita coba skema lain untuk mempartisi tabel pemesanan - sesuai dengan daftar nilai. Untuk melakukan ini, tambahkan book_month kolom redundan sebagai kunci partisi:
=> CREATE TABLE bookings_list ( book_ref character(6), book_month character(6), book_date timestamptz NOT NULL, total_amount numeric(10,2), PRIMARY KEY (book_ref, book_month) ) PARTITION BY LIST(book_month);
Kami akan membentuk bagian secara dinamis berdasarkan data tabel pemesanan:
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF bookings_list FOR VALUES IN (%L)', 'bookings_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
Inilah yang terjadi:
=> \d+ bookings_list
Partitioned table "bookings.bookings_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_date | timestamp with time zone | | not null | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: LIST (book_month) Indexes: "bookings_list_pkey" PRIMARY KEY, btree (book_ref, book_month) Partitions: bookings_list_201706 FOR VALUES IN ('201706'), bookings_list_201707 FOR VALUES IN ('201707'), bookings_list_201708 FOR VALUES IN ('201708')
Mengisi tata letak di bagian:
=> INSERT INTO bookings_list(book_ref,book_month,book_date,total_amount) SELECT book_ref,to_char(book_date, 'YYYYMM'),book_date,total_amount FROM bookings;
INSERT 0 262788
Sebagai retret. Untuk mengisi book_month secara otomatis, tergoda untuk menggunakan fungsionalitas baru dari versi 12 - GENERATED SELALU. Namun, sayangnya, mereka tidak dapat digunakan sebagai kunci partisi. Karena itu, tugas mengisi bulan harus diselesaikan dengan cara lain.
Batasan integritas seperti PERIKSA dan BUKAN NULL dapat dibuat pada tabel yang dipartisi. Seperti halnya pewarisan, menentukan INHERIT / NOINHERIT menunjukkan apakah batasan harus diwarisi pada semua tabel partisi. INHERIT Bawaan:
=> ALTER TABLE bookings_range ALTER COLUMN book_date SET NOT NULL;
=> \d bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition key: RANGE (book_date) Indexes: "book_date_idx" btree (book_date) Number of partitions: 5 (Use \d+ to list them.)
=> \d bookings_range_201706
Table "bookings.bookings_range_201706" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition of: bookings_range FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03') Indexes: "book_date_201706_idx" btree (book_date)
Batasan EXCLUDE hanya dapat dibuat secara lokal di partisi.
Pencarian di book_ref akan terlihat di semua bagian, tetapi berdasarkan indeks, berkat fakta bahwa book_ref terdaftar terlebih dahulu:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F';
QUERY PLAN -------------------------------------------------------------------------- Append -> Index Scan using bookings_list_201706_pkey on bookings_list_201706 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201708_pkey on bookings_list_201708 Index Cond: (book_ref = '00000F'::bpchar) (7 rows)
Pencarian di book_ref dan rentang bagian hanya akan terlihat dalam rentang yang ditentukan:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F' AND book_month = '201707';
QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: ((book_ref = '00000F'::bpchar) AND (book_month = '201707'::bpchar)) (2 rows)
Perintah INSERT ... ON CONFLICT dengan benar menemukan bagian yang diinginkan dan melakukan pembaruan:
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 0.00 (1 row) INSERT 0 1
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',100) ON CONFLICT(book_ref,book_month) DO UPDATE SET total_amount = 100 RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 100.00 (1 row) INSERT 0 1
Kunci asing
Dalam database demo, tabel tiket mengacu pada pemesanan.
Untuk memungkinkan kunci asing, tambahkan kolom book_month, dan pada saat yang sama pilah menjadi beberapa bagian per bulan, seperti booking_list.
=> CREATE TABLE tickets_list ( ticket_no character(13), book_month character(6), book_ref character(6) NOT NULL, passenger_id varchar(20) NOT NULL, passenger_name text NOT NULL, contact_data jsonb, PRIMARY KEY (ticket_no, book_month), FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list (book_ref, book_month) ) PARTITION BY LIST (book_month);
Pembatasan KUNCI ASING layak untuk dilihat lebih dekat. Di satu sisi, ini adalah kunci asing
dari tabel dipartisi (tickets_list), dan di sisi lain, ini adalah kunci
ke tabel dipartisi (booking_list). Dengan demikian, kunci asing untuk tabel dipartisi didukung di kedua arah.
Buat bagian:
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF tickets_list FOR VALUES IN (%L)', 'tickets_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
=> \d+ tickets_list
Partitioned table "bookings.tickets_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------+-----------+----------+---------+----------+--------------+------------- ticket_no | character(13) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_ref | character(6) | | not null | | extended | | passenger_id | character varying(20) | | not null | | extended | | passenger_name | text | | not null | | extended | | contact_data | jsonb | | | | extended | | Partition key: LIST (book_month) Indexes: "tickets_list_pkey" PRIMARY KEY, btree (ticket_no, book_month) Foreign-key constraints: "tickets_list_book_ref_book_month_fkey" FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list(book_ref, book_month) Partitions: tickets_list_201706 FOR VALUES IN ('201706'), tickets_list_201707 FOR VALUES IN ('201707'), tickets_list_201708 FOR VALUES IN ('201708')
Kami mengisi:
=> INSERT INTO tickets_list (ticket_no,book_month,book_ref,passenger_id,passenger_name,contact_data) SELECT t.ticket_no,b.book_month,t.book_ref, t.passenger_id,t.passenger_name,t.contact_data FROM bookings_list b JOIN tickets t ON (b.book_ref = t.book_ref);
INSERT 0 366733
=> VACUUM ANALYZE tickets_list;
Distribusi garis menjadi beberapa bagian:
=> SELECT tableoid::regclass, count(*) FROM tickets_list GROUP BY tableoid;
tableoid | count ---------------------+-------- tickets_list_201706 | 10160 tickets_list_201707 | 232755 tickets_list_201708 | 123818 (3 rows)
Permintaan koneksi dan agregasi
Bergabunglah dengan dua tabel yang dipartisi dengan cara yang sama:
=> EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ---------------------------------------------------------------------------- Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Append -> Seq Scan on tickets_list_201706 t -> Seq Scan on tickets_list_201707 t_1 -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Append -> Seq Scan on bookings_list_201706 b -> Seq Scan on bookings_list_201707 b_1 -> Seq Scan on bookings_list_201708 b_2 (11 rows)
Sebelum memulai koneksi, setiap tabel terlebih dahulu menggabungkan bagian yang termasuk dalam kondisi kueri.
Tapi pertama-tama Anda bisa menggabungkan bagian bulanan yang sesuai dari kedua tabel, dan kemudian menggabungkan hasilnya. Ini dapat dicapai dengan mengaktifkan parameter enable_partitionwise_join:
=> SET enable_partitionwise_join = ON; => EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ------------------------------------------------------------------------------------------ Append -> Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Seq Scan on tickets_list_201706 t -> Hash -> Seq Scan on bookings_list_201706 b -> Hash Join Hash Cond: ((t_1.book_ref = b_1.book_ref) AND (t_1.book_month = b_1.book_month)) -> Seq Scan on tickets_list_201707 t_1 -> Hash -> Seq Scan on bookings_list_201707 b_1 -> Hash Join Hash Cond: ((t_2.book_ref = b_2.book_ref) AND (t_2.book_month = b_2.book_month)) -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Seq Scan on bookings_list_201708 b_2 (16 rows)
Sekarang, pertama, bagian yang sesuai dari dua tabel bergabung, dan kemudian hasil gabungan digabungkan.
Situasi serupa dengan agregasi:
=> EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Append -> Parallel Seq Scan on bookings_list_201707 -> Parallel Seq Scan on bookings_list_201708 -> Parallel Seq Scan on bookings_list_201706 (8 rows)
Perhatikan bahwa pemindaian bagian dapat dilakukan secara paralel. Tetapi pertama-tama bagian-bagian itu bersatu, baru kemudian agregasi dimulai. Atau, Anda dapat melakukan agregasi di setiap bagian, lalu menggabungkan hasilnya:
=> SET enable_partitionwise_aggregate = ON; => EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Parallel Append -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201707 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201708 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201706 (10 rows)
Fitur-fitur ini sangat penting jika bagian dari bagian adalah tabel eksternal. Secara default, keduanya dinonaktifkan karena parameter yang sesuai memengaruhi waktu rencana, tetapi mungkin tidak selalu digunakan.
Partisi Hash
Cara ketiga untuk mempartisi tabel adalah partisi hash.
Membuat tabel:
=> CREATE TABLE bookings_hash ( book_ref character(6) PRIMARY KEY, book_date timestamptz NOT NULL, total_amount numeric(10,2) ) PARTITION BY HASH(book_ref);
Dalam versi book_ref ini, sebagai kunci partisi, Anda dapat segera mendeklarasikannya sebagai kunci utama.
Membagi menjadi tiga bagian:
=> CREATE TABLE bookings_hash_p0 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0); => CREATE TABLE bookings_hash_p1 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 1); => CREATE TABLE bookings_hash_p2 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 2);
Mengisi dengan tata letak otomatis di bagian:
=> INSERT INTO bookings_hash SELECT * FROM bookings;
INSERT 0 262788
Distribusi garis dalam bagian terjadi secara merata:
=> SELECT tableoid::regclass AS partition, count(*) FROM bookings_hash GROUP BY tableoid;
partition | count ------------------+------- bookings_hash_p0 | 87649 bookings_hash_p1 | 87651 bookings_hash_p2 | 87488 (3 rows)
Perintah baru untuk melihat objek yang dipartisi:
=> \dP+
List of partitioned relations Schema | Name | Owner | Type | Table | Total size | Description ----------+--------------------+---------+-------------------+----------------+------------+------------- bookings | bookings_hash | student | partitioned table | | 13 MB | bookings | bookings_list | student | partitioned table | | 15 MB | bookings | bookings_range | student | partitioned table | | 13 MB | bookings | tickets_list | student | partitioned table | | 50 MB | bookings | book_date_idx | student | partitioned index | bookings_range | 5872 kB | bookings | bookings_hash_pkey | student | partitioned index | bookings_hash | 5800 kB | bookings | bookings_list_pkey | student | partitioned index | bookings_list | 8120 kB | bookings | tickets_list_pkey | student | partitioned index | tickets_list | 19 MB | (8 rows)
=> VACUUM ANALYZE bookings_hash;
Subqueries dan nested loop bergabung
Pengecualian bagian saat runtime dimungkinkan dengan koneksi loop bersarang.
Distribusi 10 pemesanan pertama di bagian:
=> WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings_hash ORDER BY book_ref LIMIT 10 ) SELECT partition, count(*) FROM top10 GROUP BY 1 ORDER BY 1;
partition | count ------------------+------- bookings_hash_p0 | 3 bookings_hash_p1 | 3 bookings_hash_p2 | 4 (3 rows)
Mari kita lihat rencana eksekusi kueri dengan gabungan tabel tables_hash dan subquery sebelumnya: => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.632 ms Execution Time: 0.278 ms (13 rows)
Koneksi dibuat menggunakan metode nested loop. Lingkaran luar sesuai dengan ekspresi tabel umum dijalankan 10 kali. Tetapi perhatikan jumlah panggilan ke bagian-tabel (loop). Untuk setiap nilai book_ref dari loop luar, hanya bagian yang dipindai tempat nilai ini disimpan dalam tabel tables_hash.Bandingkan dengan pengecualian bagian yang dinonaktifkan: => SET enable_partition_pruning TO OFF; => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ------------------------------------------------------------------------------------------------------ Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.886 ms Execution Time: 0.771 ms (13 rows)
=> RESET enable_partition_pruning;
Jika Anda mengurangi pilihan menjadi satu reservasi, maka dua bagian tidak akan terlihat sama sekali: => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 1 ) SELECT bh.* FROM bookings_hash bh JOIN top ON bh.book_ref = top.book_ref;
QUERY PLAN --------------------------------------------------------------------------------------------------- Nested Loop (actual rows=1 loops=1) -> Limit (actual rows=1 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=1 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=1) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=1) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (never executed) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (never executed) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.250 ms Execution Time: 0.090 ms (13 rows)
Alih-alih subquery, Anda dapat menggunakan fungsi mengembalikan set dengan kategori variabilitas STABLE: => CREATE OR REPLACE FUNCTION get_book_ref(top int) RETURNS SETOF bookings AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM bookings ORDER BY book_ref LIMIT $1' USING top; END;$$ LANGUAGE plpgsql STABLE;
=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) SELECT * FROM bookings_hash bh JOIN get_book_ref(10) f ON bh.book_ref = f.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Function Scan on get_book_ref f (actual rows=10 loops=1) -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = f.book_ref) Planning Time: 0.175 ms Execution Time: 0.843 ms (11 rows)
Ringkasan
Kesimpulannya, kita dapat mengatakan bahwa partisi bawaan atau deklaratif di PostgreSQL 12 telah menerima serangkaian fitur yang kaya dan dapat dengan aman direkomendasikan untuk mengganti partisi melalui pewarisan.