Topik ini menjelaskan cara menggunakan ekstensi pg_pathman.
Informasi latar belakang
pg_pathman adalah ekstensi yang dapat digunakan dalam kluster PolarDB untuk PostgreSQL. Ekstensi ini menyediakan kemampuan partisi yang efisien, membantu mengelola partisi secara efektif, serta meningkatkan kinerja tabel terpartisi.
Membuat ekstensi
Untuk menggunakan fitur pengelolaan partisi dari ekstensi pg_pathman, hubungi kami.
CREATE EXTENSION IF NOT EXISTS pg_pathman;Setelah ekstensi pg_pathman dibuat, Anda dapat mengeksekusi pernyataan SQL berikut untuk melihat versinya:
SELECT extname,extversion FROM pg_extension WHERE extname = 'pg_pathman';Hasil contoh:
extname | extversion
------------+------------
pg_pathman | 1.5
(1 baris)Memperbarui ekstensi
PolarDB untuk PostgreSQL memperbarui ekstensi pg_pathman secara berkala untuk meningkatkan layanan basis data. Untuk memperbarui ekstensi pg_pathman secara manual, Anda dapat memperbarui kluster ke versi terbaru.
Fitur
Mendukung partisi hash dan partisi rentang.
Menyediakan pengelolaan partisi otomatis dan manual. Dalam pengelolaan partisi otomatis, gunakan fungsi untuk membuat partisi secara otomatis dan memigrasikan data dari tabel utama ke partisi. Dalam pengelolaan partisi manual, gunakan fungsi untuk menambahkan tabel yang ada ke tabel utama atau melepaskan tabel dari tabel utama.
Mendukung berbagai jenis kolom untuk partisi, termasuk tipe kolom INT, FLOAT, DATE, serta domain kustom.
Menyediakan rencana optimal untuk kueri pada tabel terpartisi, serta menggunakan strategi seperti join dan subquery dalam rencana tersebut.
Memungkinkan pemilihan partisi dinamis dengan menggunakan node rencana kustom:
RuntimeAppenddanRuntimeMergeAppend.Menggunakan fitur
PartitionFilteruntuk menyaring partisi secara dinamis berdasarkan kondisi kueri.Secara otomatis menambahkan partisi baru ketika data yang dimasukkan melebihi batas partisi saat ini. Fitur ini hanya tersedia dalam partisi rentang.
Membaca dan menulis data langsung ke dan dari tabel terpartisi dengan menggunakan pernyataan
COPY FROM/TO.Mengizinkan Anda memperbarui kunci partisi dengan menambahkan pemicu. Jika Anda tidak perlu memperbarui kunci partisi, disarankan untuk tidak menambahkan pemicu karena dapat berdampak negatif pada kinerja.
Mengizinkan Anda mengonfigurasi fungsi panggilan balik kustom yang dipanggil secara otomatis saat partisi dibuat.
Mengizinkan Anda membuat tabel terpartisi dan memigrasikan data dari tabel utama ke partisi secara non-blocking.
Mengizinkan Anda memasukkan data ke dalam tabel asing yang dikelola oleh Foreign Data Wrappers (FDWs) PostgreSQL atau FDW lainnya. Anda dapat menggunakan parameter
pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw)untuk mengonfigurasi fitur ini.
Penggunaan
Untuk informasi lebih lanjut, kunjungi GitHub.
Tampilan dan tabel
Ekstensi pg_pathman menggunakan fungsi untuk mengelola tabel terpartisi dan membuat tampilan untuk melihat status tabel terpartisi.
pathman_config
CREATE TABLE IF NOT EXISTS pathman_config ( partrel REGCLASS NOT NULL PRIMARY KEY, -- Pengenal objek (OID) dari tabel utama. attname TEXT NOT NULL, -- Nama kolom kunci partisi. parttype INTEGER NOT NULL, -- Jenis partisi (hash atau rentang). range_interval TEXT, -- Rentang atau span nilai yang dicakup oleh setiap partisi. CHECK (parttype IN (1, 2)) /* cek untuk jenis part yang diizinkan */ );pathman_config_params
CREATE TABLE IF NOT EXISTS pathman_config_params ( partrel REGCLASS NOT NULL PRIMARY KEY, -- OID dari tabel utama. enable_parent BOOLEAN NOT NULL DEFAULT TRUE, -- Menentukan apakah akan menyaring tabel utama dalam optimizer. auto BOOLEAN NOT NULL DEFAULT TRUE, -- Menentukan apakah akan menambahkan partisi baru secara otomatis. init_callback REGPROCEDURE NOT NULL DEFAULT 0); -- OID dari fungsi panggilan balik inisialisasi yang dipanggil setiap kali partisi dibuat.pathman_concurrent_part_tasks
-- fungsi SRF pembantu CREATE OR REPLACE FUNCTION show_concurrent_part_tasks() RETURNS TABLE ( userid REGROLE, pid INT, dbid OID, relid REGCLASS, processed INT, status TEXT) AS 'pg_pathman', 'show_concurrent_part_tasks_internal' LANGUAGE C STRICT; CREATE OR REPLACE VIEW pathman_concurrent_part_tasks AS SELECT * FROM show_concurrent_part_tasks();pathman_partition_list
-- fungsi SRF pembantu CREATE OR REPLACE FUNCTION show_partition_list() RETURNS TABLE ( parent REGCLASS, partition REGCLASS, parttype INT4, partattr TEXT, range_min TEXT, range_max TEXT) AS 'pg_pathman', 'show_partition_list_internal' LANGUAGE C STRICT; CREATE OR REPLACE VIEW pathman_partition_list AS SELECT * FROM show_partition_list();
Pengelolaan partisi
Melakukan partisi rentang
Empat fungsi manajemen digunakan untuk membuat partisi rentang. Dua fungsi digunakan untuk menentukan nilai awal, interval, dan jumlah partisi. Anda dapat mendefinisikan kedua fungsi ini dengan sintaksis berikut:
create_range_partitions(relation REGCLASS, -- OID dari tabel utama.
attribute TEXT, -- Nama kolom kunci partisi.
start_value ANYELEMENT, -- Nilai awal.
p_interval ANYELEMENT, -- Interval semua tipe data, yang berlaku untuk semua jenis tabel terpartisi.
p_count INTEGER DEFAULT NULL, -- Jumlah partisi.
partition_data BOOLEAN DEFAULT TRUE) -- Menentukan apakah akan segera memigrasikan data dari tabel utama ke partisi. Kami merekomendasikan Anda memanggil fungsi partition_table_concurrently() untuk menjalankan migrasi data non-blocking.
create_range_partitions(relation REGCLASS, -- OID dari tabel utama.
attribute TEXT, -- Nama kolom kunci partisi.
start_value ANYELEMENT, -- Nilai awal.
p_interval INTERVAL, -- Interval tipe data interval, yang berlaku untuk tabel terpartisi waktu ingest.
p_count INTEGER DEFAULT NULL, -- Jumlah partisi.
partition_data BOOLEAN DEFAULT TRUE) -- Menentukan apakah akan segera memigrasikan data dari tabel utama ke partisi. Kami merekomendasikan Anda memanggil fungsi partition_table_concurrently() untuk melakukan migrasi data non-blocking.Dua fungsi lainnya digunakan untuk menentukan nilai awal, nilai akhir, dan interval. Anda dapat mendefinisikan fungsi-fungsi tersebut dengan sintaksis berikut:
create_partitions_from_range(relation REGCLASS, -- OID dari tabel utama.
attribute TEXT, -- Nama kolom kunci partisi.
start_value ANYELEMENT, -- Nilai awal.
end_value ANYELEMENT, -- Nilai akhir.
p_interval ANYELEMENT, -- Interval semua tipe data, yang berlaku untuk semua jenis tabel terpartisi.
partition_data BOOLEAN DEFAULT TRUE) -- Menentukan apakah akan segera memigrasikan data dari tabel utama ke partisi. Kami merekomendasikan Anda memanggil fungsi partition_table_concurrently() untuk menjalankan migrasi data non-blocking.
create_partitions_from_range(relation REGCLASS, -- OID dari tabel utama.
attribute TEXT, -- Nama kolom kunci partisi.
start_value ANYELEMENT, -- Nilai awal.
end_value ANYELEMENT, -- Nilai akhir.
p_interval INTERVAL, -- Interval tipe data interval, yang berlaku untuk tabel terpartisi waktu ingest.
partition_data BOOLEAN DEFAULT TRUE) -- Menentukan apakah akan segera memigrasikan data dari tabel utama ke partisi. Kami merekomendasikan Anda memanggil fungsi partition_table_concurrently() untuk melakukan migrasi data non-blocking.Contoh:
Buat tabel utama yang ingin dipartisi dan masukkan data uji.
--- Buat tabel utama yang ingin Anda partisi. CREATE TABLE part_test(id int, info text, crt_time timestamp not null); -- -- Kolom kunci partisi harus berisi kendala NOT NULL. --- Masukkan data uji ke dalam tabel utama untuk mensimulasikan tabel utama yang sudah memiliki data. INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id);Kueri data dari tabel utama.
SELECT * FROM part_test limit 10;Hasil contoh:
id | info | crt_time ----+----------------------------------+---------------------------- 1 | 36fe1adedaa5b848caec4941f87d443a | 2016-10-25 10:27:13.206713 2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893 3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904 4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691 5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916 6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921 7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693 8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936 9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942 10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947 (10 baris)Buat partisi dan pastikan setiap partisi berisi data satu bulan.
--- Buat partisi dan pastikan setiap partisi berisi data satu bulan. SELECT create_range_partitions('part_test'::regclass, -- OID dari tabel utama. 'crt_time', -- Nama kolom kunci partisi. '2016-10-25 00:00:00'::timestamp, -- Nilai awal rentang untuk partisi pertama. interval '1 month', -- Rentang atau span nilai yang dicakup oleh setiap partisi. 24, -- Jumlah partisi. false) ; -- Data tidak dimigrasi.-- Lakukan migrasi data non-blocking.
--- Data masih berada di tabel utama sebelum migrasi. SELECT count(*) FROM ONLY part_test; count ------- 10000 (1 baris) --- API migrasi non-blocking partition_table_concurrently(relation REGCLASS, -- OID dari tabel utama. batch_size INTEGER DEFAULT 1000, -- Jumlah rekaman dalam transaksi yang ingin Anda migrasikan dari tabel utama. sleep_time FLOAT8 DEFAULT 1.0) -- Interval percobaan ulang setelah upaya gagal untuk mendapatkan kunci baris. Setelah 60 upaya gagal, tugas diakhiri. -- Lakukan migrasi data non-blocking. SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0); --- Setelah migrasi selesai, semua data dimigrasikan ke partisi, dan tabel utama kosong. SELECT count(*) FROM ONLY part_test; count ------- 0 (1 baris)Setelah memigrasikan data, disarankan untuk menonaktifkan tabel utama agar tidak disertakan dalam rencana eksekusi.
--- Nonaktifkan tabel utama. SELECT set_enable_parent('part_test'::regclass, false); --- Verifikasi rencana eksekusi. EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp; QUERY PLAN --------------------------------------------------------------------------------- Append (cost=0.00..16.18 rows=1 width=45) -> Seq Scan on part_test_1 (cost=0.00..16.18 rows=1 width=45) Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone) (3 baris)
Saat menggunakan partisi rentang, perhatikan hal-hal berikut:
Kolom kunci partisi harus berisi kendala NOT NULL.
Jumlah partisi harus cukup untuk mencakup semua rekaman yang ada.
Lakukan migrasi data non-blocking.
Setelah migrasi data selesai, nonaktifkan tabel utama.
Melakukan partisi hash
Anda dapat menggunakan fungsi manajemen berikut untuk membuat partisi rentang dan menentukan nilai awal, interval, serta jumlah partisi:
create_hash_partitions(relation REGCLASS, -- OID dari tabel utama.
attribute TEXT, -- Nama kolom kunci partisi.
partitions_count INTEGER, -- Jumlah partisi.
partition_data BOOLEAN DEFAULT TRUE) -- Menentukan apakah akan segera memigrasikan data dari tabel utama ke partisi. Kami merekomendasikan Anda memanggil fungsi partition_table_concurrently() untuk melakukan migrasi data non-blocking.Contoh:
Buat tabel utama yang ingin dipartisi dan masukkan data uji.
--- Buat tabel utama yang ingin Anda partisi. CREATE TABLE part_test(id int, info text, crt_time timestamp not null); -- Kolom kunci partisi harus berisi kendala NOT NULL. --- Masukkan data uji ke dalam tabel utama untuk mensimulasikan tabel utama yang sudah memiliki data. INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval FROM generate_series(1,10000) t(id);Kueri data dari tabel utama.
SELECT * FROM part_test limit 10;Hasil contoh:
id | info | crt_time ----+----------------------------------+---------------------------- 1 | 29ce4edc70dbfbe78912beb7c4cc95c2 | 2016-10-25 10:47:32.873879 2 | e0990a6fb5826409667c9eb150fef386 | 2016-10-25 11:47:32.874048 3 | d25f577a01013925c203910e34470695 | 2016-10-25 12:47:32.874059 4 | 501419c3f7c218e562b324a1bebfe0ad | 2016-10-25 13:47:32.874065 5 | 5e5e22bdf110d66a5224a657955ba158 | 2016-10-25 14:47:32.87407 6 | 55d2d4fd5229a6595e0dd56e13d32be4 | 2016-10-25 15:47:32.874076 7 | 1dfb9a783af55b123c7a888afe1eb950 | 2016-10-25 16:47:32.874081 8 | 41eeb0bf395a4ab1e08691125ae74bff | 2016-10-25 17:47:32.874087 9 | 83783d69cc4f9bb41a3978fe9e13d7fa | 2016-10-25 18:47:32.874092 10 | affc9406d5b3412ae31f7d7283cda0dd | 2016-10-25 19:47:32.874097 (10 baris)Buat partisi.
--- Buat 128 partisi. SELECT create_hash_partitions('part_test'::regclass, OID dari tabel utama. 'crt_time', -- Nama kolom kunci partisi. 128, -- Jumlah partisi yang ingin Anda buat. false) ; -- Data tidak dimigrasi.-- Lakukan migrasi data non-blocking.
--- Data masih berada di tabel utama sebelum migrasi. SELECT count(*) FROM ONLY part_test; count ------- 10000 (1 baris) --- API migrasi non-blocking partition_table_concurrently(relation REGCLASS, -- OID dari tabel utama. batch_size INTEGER DEFAULT 1000, -- Jumlah rekaman dalam transaksi yang ingin Anda migrasikan dari tabel utama. sleep_time FLOAT8 DEFAULT 1.0) -- Interval percobaan ulang setelah upaya gagal untuk mendapatkan kunci baris. Setelah 60 upaya gagal, tugas diakhiri. -- Lakukan migrasi data non-blocking. SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0); --- Setelah migrasi selesai, semua data dimigrasikan ke partisi, dan tabel utama kosong. SELECT count(*) FROM ONLY part_test; count ------- 0 (1 baris)Setelah memigrasikan data, disarankan untuk menonaktifkan tabel utama agar tidak disertakan dalam rencana eksekusi.
--- Nonaktifkan tabel utama. SELECT set_enable_parent('part_test'::regclass, false);Verifikasi rencana eksekusi.
--- Kueri partisi tunggal. EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp; QUERY PLAN --------------------------------------------------------------------------------- Append (cost=0.00..1.91 rows=1 width=45) -> Seq Scan on part_test_122 (cost=0.00..1.91 rows=1 width=45) Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone) (3 baris)-- Tabel terpartisi memiliki batasan berikut. Ekstensi pg_pathman secara otomatis mengonversi pernyataan. Dalam partisi berbasis warisan tradisional, Anda tidak dapat menyaring partisi menggunakan pernyataan seperti
SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;.\d+ part_test_122 Table "public.part_test_122" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_122_3_check" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122) Inherits: part_test
Saat menggunakan partisi hash, perhatikan hal-hal berikut:
Kolom kunci partisi harus berisi kendala NOT NULL.
Lakukan migrasi data non-blocking.
Setelah migrasi data selesai, nonaktifkan tabel utama.
Ekstensi pg_pathman dapat bekerja tanpa memandang bagaimana pernyataan ditulis. Misalnya, pernyataan seperti
select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;dapat dikenali dan dieksekusi bahkan dalam partisi hash.Kolom partisi HASH tidak terbatas pada kolom tipe int. Tipe kolom secara otomatis dikonversi oleh fungsi hash.
Migrasikan data ke partisi
Jika data tetap berada di tabel induk, lakukan migrasi data non-blocking untuk memigrasikan data ke partisi. Fungsi:
WITH tmp AS (DELETE FROM <Tabel utama> limit xx nowait returning *) INSERT INTO <Partisi> SELECT * FROM tmp;Anda juga dapat menggunakan pernyataan berikut untuk memberi label baris. Kemudian, lakukan operasi DELETE dan INSERT.
SELECT array_agg(ctid) FROM <Tabel utama> limit xx FOR UPDATE nowati;Fungsi:
partition_table_concurrently(relation REGCLASS, -- OID dari tabel utama.
batch_size INTEGER DEFAULT 1000, -- Jumlah rekaman dalam transaksi yang ingin Anda migrasikan dari tabel utama.
sleep_time FLOAT8 DEFAULT 1.0) -- Interval percobaan ulang setelah upaya gagal untuk mendapatkan kunci baris. Setelah 60 upaya gagal, tugas diakhiri.Contoh:
SELECT partition_table_concurrently('part_test'::regclass,
10000,
1.0);Lihat tugas migrasi data latar belakang.
SELECT * FROM pathman_concurrent_part_tasks;Membagi partisi rentang
Untuk membagi partisi rentang yang terlalu besar menjadi partisi yang lebih kecil, gunakan fungsi berikut:
split_range_partition(partition REGCLASS, -- OID dari partisi.
split_value ANYELEMENT, -- Nilai pemisah.
partition_name TEXT DEFAULT NULL) -- Nama partisi yang ditambahkanContoh:
Gunakan tabel dalam bagian Melakukan Partisi Rentang dengan struktur berikut.
\d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_1, part_test_10, part_test_11, part_test_12, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_2, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9 \d+ part_test_1 Table "public.part_test_1" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone) Inherits: part_testPisahkan partisi.
SELECT split_range_partition('part_test_1'::regclass, -- OID dari partisi. '2016-11-10 00:00:00'::timestamp, -- Titik waktu saat partisi dibagi. 'part_test_1_2'); -- Nama partisi baru.Partisi dibagi menjadi partisi berikut:
\d+ part_test_1 Table "public.part_test_1" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-10 00:00:00'::timestamp without time zone) Inherits: part_test \d+ part_test_1_2 Table "public.part_test_1_2" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_1_2_3_check" CHECK (crt_time >= '2016-11-10 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone) Inherits: part_testData secara otomatis dimigrasikan ke partisi lain.
SELECT count(*) FROM part_test_1; count ------- 373 (1 baris) SELECT count(*) FROM part_test_1_2; count ------- 360 (1 baris)Contoh berikut menggambarkan hubungan warisan.
\d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_1, part_test_10, part_test_11, part_test_12, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_1_2, -- Partisi yang ditambahkan. part_test_2, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9
Menggabungkan partisi rentang
Hanya partisi rentang yang didukung dan mereka harus berupa partisi yang berdekatan. Panggil fungsi berikut untuk menggabungkan partisi rentang:
--- Tentukan dua partisi yang ingin Anda gabungkan.
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)Contoh:
Gunakan tabel dalam bagian Membagi Partisi Rentang untuk menggabungkan partisi.
SELECT merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass);CatatanKesalahan berikut dikembalikan jika Anda menggabungkan partisi yang tidak berdekatan.
SELECT merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass) ; ERROR: penggabungan gagal, partisi harus berdekatan CONTEXT: PL/pgSQL function merge_range_partitions_internal(regclass,regclass,regclass,anyelement) line 27 at RAISE SQL statement "SELECT public.merge_range_partitions_internal($1, $2, $3, NULL::timestamp without time zone)" PL/pgSQL function merge_range_partitions(regclass,regclass) line 44 at EXECUTESetelah menggabungkan partisi, salah satu partisi dihapus.
\d part_test_1_2 Tidak ditemukan relasi bernama "part_test_1_2". \d part_test_1 Table "public.part_test_1" Column | Type | Modifiers ----------+-----------------------------+----------- id | integer | info | text | crt_time | timestamp without time zone | not null Check constraints: "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone) Inherits: part_test SELECT count(*) FROM part_test_1; count ------- 733 (1 baris)
Menambahkan partisi rentang baru
Anda dapat menggunakan beberapa metode untuk menambahkan partisi ke tabel utama yang telah dipartisi. Tiga metode dijelaskan di sini: tambahkan partisi rentang baru, sisipkan partisi rentang baru di awal, dan tentukan nilai awal partisi.
Tambahkan partisi rentang baru
Saat menambahkan partisi baru ke tabel utama, interval yang ditentukan saat tabel terpartisi dibuat digunakan. Anda dapat menanyakan nilai interval setiap tabel terpartisi dari tabel pathman_config.
SELECT * FROM pathman_config;
partrel | attname | parttype | range_interval
-----------+----------+----------+----------------
part_test | crt_time | 2 | 1 mon
(1 baris)Panggil fungsi berikut untuk menambahkan partisi rentang baru (Anda tidak dapat menentukan tablespace).
append_range_partition(parent REGCLASS, -- OID dari tabel utama.
partition_name TEXT DEFAULT NULL, -- Nama partisi baru. Parameter ini tidak wajib.
tablespace TEXT DEFAULT NULL) -- Tablespace untuk partisi baru. Parameter ini tidak wajib.Contoh:
SELECT append_range_partition('part_test'::regclass);
\d+ part_test_25
Table "public.part_test_25"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_25_3_check" CHECK (crt_time >= '2018-10-25 00:00:00'::timestamp without time zone AND crt_time < '2018-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test
\d+ part_test_24
Table "public.part_test_24"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_24_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_testSisipkan partisi rentang baru di awal
Panggil fungsi berikut untuk menyisipkan partisi rentang baru di awal.
prepend_range_partition(parent REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)Contoh:
SELECT prepend_range_partition('part_test'::regclass);
\d+ part_test_26
Table "public.part_test_26"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_26_3_check" CHECK (crt_time >= '2016-09-25 00:00:00'::timestamp without time zone AND crt_time < '2016-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test
\d+ part_test_1
Table "public.part_test_1"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_testTentukan nilai awal partisi untuk menambahkan partisi rentang baru
Anda dapat membuat partisi baru dengan menentukan nilai awal partisi. Jika rentang partisi yang ingin dibuat tidak tumpang tindih dengan partisi yang ada, partisi tersebut akan dibuat. Metode ini memungkinkan Anda membuat partisi yang tidak berdekatan. Misalnya, jika rentang partisi yang ada adalah dari 2010 hingga 2015, Anda dapat membuat partisi mulai dari 2020 tanpa perlu membuat partisi antara 2015 dan 2020. Fungsi:
add_range_partition(relation REGCLASS, -- OID dari tabel utama
start_value ANYELEMENT, -- Nilai awal.
end_value ANYELEMENT, -- Nilai akhir.
partition_name TEXT DEFAULT NULL, -- Nama partisi.
tablespace TEXT DEFAULT NULL) -- Tablespace untuk partisi.Contoh:
postgres=# select add_range_partition('part_test'::regclass, -- OID dari tabel utama.
'2020-01-01 00:00:00'::timestamp, -- Nilai awal.
'2020-02-01 00:00:00'::timestamp); -- Nilai akhir.
\d+ part_test_27
Table "public.part_test_27"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_27_3_check" CHECK (crt_time >= '2020-01-01 00:00:00'::timestamp without time zone AND crt_time < '2020-02-01 00:00:00'::timestamp without time zone)
Inherits: part_testMenghapus partisi
Untuk menghapus partisi rentang tunggal, gunakan fungsi berikut:
drop_range_partition(partition TEXT, -- Nama partisi. delete_data BOOLEAN DEFAULT TRUE) -- Menentukan apakah akan menghapus data partisi. Jika Anda mengatur nilainya ke FALSE, data partisi dimigrasikan ke tabel utama. Hapus partisi RANGE dan semua datanya jika delete_data bernilai true.Hapus semua partisi dan tentukan apakah data akan dimigrasikan ke tabel utama. Fungsi:
drop_partitions(parent REGCLASS, delete_data BOOLEAN DEFAULT FALSE) Hapus partisi dari tabel induk (baik relasi lokal maupun asing). Jika delete_data bernilai false, data disalin ke tabel induk terlebih dahulu. Nilai default adalah false.
Contoh:
Hapus partisi dan migrasikan data partisi ke tabel utama.
SELECT drop_range_partition('part_test_1',false); SELECT drop_range_partition('part_test_2',false);Kueri data dari tabel utama saat ini.
SELECT count(*) FROM part_test; count ------- 10000 (1 baris)Hapus partisi beserta data partisi tanpa memigrasikan data ke tabel induk.
SELECT drop_range_partition('part_test_3',true);Kueri data dari tabel utama saat ini.
SELECT count(*) FROM part_test; count ------- 9256 (1 baris) SELECT count(*) FROM ONLY part_test; count ------- 1453 (1 baris)Hapus semua partisi.
SELECT drop_partitions('part_test'::regclass, false); -- Hapus semua partisi dan migrasikan data partisi ke tabel utama.Kueri data dari tabel utama.
SELECT count(*) FROM part_test; count ------- 9256 (1 baris)
Lampirkan tabel ke partisi
Lampirkan tabel yang sudah ada ke partisi tabel utama. Tabel yang ingin dilampirkan harus memiliki skema yang sama dengan tabel utama, termasuk kolom yang dihapus. Parameter pg_attribute menentukan skema tabel. Fungsi:
attach_range_partition(relation REGCLASS, -- OID dari tabel utama.
partition REGCLASS, -- OID dari partisi.
start_value ANYELEMENT, -- Nilai awal rentang untuk partisi.
end_value ANYELEMENT) -- Nilai akhir rentang untuk partisi.Contoh:
Buat tabel.
CREATE TABLE part_test_1 (like part_test including all);Lampirkan tabel ke tabel utama.
\d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | \d+ part_test_1 Table "public.part_test_1" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | SELECT attach_range_partition('part_test'::regclass, 'part_test_1'::regclass, '2019-01-01 00:00:00'::timestamp, '2019-02-01 00:00:00'::timestamp);Saat melampirkan tabel, hubungan warisan dan kendala dibuat secara otomatis.
\d+ part_test_1 Table "public.part_test_1" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_1_3_check" CHECK (crt_time >= '2019-01-01 00:00:00'::timestamp without time zone AND crt_time < '2019-02-01 00:00:00'::timestamp without time zone) Inherits: part_test
Lepaskan partisi
Saat melepaskan partisi, data tidak dihapus, tetapi warisan dan kendala akan dihapus. Fungsi:
detach_range_partition(partition REGCLASS) -- Tentukan partisi yang ingin Anda ubah menjadi tabel biasa.Contoh:
Kueri data dari tabel utama dan tabel terpartisi saat ini.
SELECT count(*) FROM part_test; count ------- 9256 (1 baris) SELECT count(*) FROM part_test_2; count ------- 733 (1 baris)Lepaskan partisi.
SELECT detach_range_partition('part_test_2');Kueri data dari tabel utama dan tabel terpartisi saat ini.
SELECT count(*) FROM part_test_2; count ------- 733 (1 baris) SELECT count(*) FROM part_test; count ------- 8523 (1 baris)
Nonaktifkan ekstensi pg_pathman
Anda dapat menonaktifkan ekstensi pg_pathman untuk tabel induk. Fungsi:
Operasi disable_pathman_for tidak dapat dibatalkan. Lanjutkan dengan hati-hati.
\sf disable_pathman_for
CREATE OR REPLACE FUNCTION public.disable_pathman_for(parent_relid regclass)
RETURNS void
LANGUAGE plpgsql
STRICT
AS $function$
BEGIN
PERFORM public.validate_relname(parent_relid);
DELETE FROM public.pathman_config WHERE partrel = parent_relid;
PERFORM public.drop_triggers(parent_relid);
/* Beri tahu backend tentang perubahan */
PERFORM public.on_remove_partitions(parent_relid);
END
$function$Contoh:
SELECT disable_pathman_for('part_test');
\d+ part_test
Table "public.part_test"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Child tables: part_test_10,
part_test_11,
part_test_12,
part_test_13,
part_test_14,
part_test_15,
part_test_16,
part_test_17,
part_test_18,
part_test_19,
part_test_20,
part_test_21,
part_test_22,
part_test_23,
part_test_24,
part_test_25,
part_test_26,
part_test_27,
part_test_28,
part_test_29,
part_test_3,
part_test_30,
part_test_31,
part_test_32,
part_test_33,
part_test_34,
part_test_35,
part_test_4,
part_test_5,
part_test_6,
part_test_7,
part_test_8,
part_test_9
\d+ part_test_10
Table "public.part_test_10"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_10_3_check" CHECK (crt_time >= '2017-06-25 00:00:00'::timestamp without time zone AND crt_time < '2017-07-25 00:00:00'::timestamp without time zone)
Inherits: part_testSetelah menonaktifkan ekstensi pg_pathman, warisan dan kendala tetap tidak berubah. Ekstensi pg_pathman tidak lagi terlibat dalam rencana pemindaian kustom. Contoh berikut menggambarkan rencana eksekusi setelah ekstensi pg_pathman dinonaktifkan:
EXPLAIN SELECT * FROM part_test WHERE crt_time='2017-06-25 00:00:00'::timestamp;
QUERY PLAN
---------------------------------------------------------------------------------
Append (cost=0.00..16.00 rows=2 width=45)
-> Seq Scan on part_test (cost=0.00..0.00 rows=1 width=45)
Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
-> Seq Scan on part_test_10 (cost=0.00..16.00 rows=1 width=45)
Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
(5 baris)Pengelolaan partisi lanjutan
Nonaktifkan tabel utama
Setelah semua data tabel utama dimigrasikan ke partisi, Anda dapat menonaktifkan tabel utama. Fungsi:
set_enable_parent(relation REGCLASS, value BOOLEAN)
Sertakan/kecualikan tabel induk ke/dari rencana kueri.
Dalam planner PostgreSQL asli, tabel induk selalu disertakan dalam rencana kueri meskipun kosong yang dapat menyebabkan overhead tambahan.
Anda dapat menggunakan disable_parent() jika Anda tidak akan menggunakan tabel induk sebagai penyimpanan.
Nilai default bergantung pada parameter partition_data yang ditentukan selama partisi awal dalam fungsi create_range_partitions() atau create_partitions_from_range().
Jika parameter partition_data bernilai true maka semua data sudah dimigrasikan ke partisi dan tabel induk dinonaktifkan.
Sebaliknya, tabel induk diaktifkan.Contoh:
SELECT set_enable_parent('part_test', false);Aktifkan propagasi partisi otomatis
Anda dapat mengaktifkan propagasi partisi otomatis untuk tabel terpartisi rentang. Jika data yang dimasukkan tidak berada dalam rentang partisi yang ada, partisi baru akan dibuat secara otomatis.
set_auto(relation REGCLASS, value BOOLEAN)
Aktifkan/nonaktifkan propagasi partisi otomatis (hanya untuk partisi RANGE).
Secara default diaktifkan.Contoh:
Kueri partisi tabel saat ini.
\d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_10, part_test_11, part_test_12, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_25, part_test_26, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9 \d+ part_test_26 Table "public.part_test_26" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_26_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone) Inherits: part_test \d+ part_test_25 Table "public.part_test_25" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-08-25 00:00:00'::timestamp without time zone AND crt_time < '2018-09-25 00:00:00'::timestamp without time zone) Inherits: part_testJika Anda memasukkan data yang berada di luar rentang partisi yang ada, partisi baru akan dibuat berdasarkan interval yang ditentukan saat tabel dipartisi. Operasi ini mungkin memerlukan waktu yang lama untuk diselesaikan.
INSERT INTO part_test VALUES (1,'test','2222-01-01'::timestamp);Kueri partisi.
\d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_10, part_test_100, part_test_1000, part_test_1001, ......
Kami sarankan Anda menonaktifkan propagasi partisi otomatis untuk tabel terpartisi rentang karena propagasi otomatis yang tidak tepat dapat memerlukan waktu yang lama untuk diselesaikan.
Konfigurasikan fungsi panggilan balik
Fungsi panggilan balik dipanggil secara otomatis setiap kali partisi dibuat. Misalnya, Anda dapat mengonfigurasi fungsi panggilan balik untuk replikasi logis DDL guna menyimpan pernyataan dalam tabel. Fungsi:
set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)
Tetapkan panggilan balik pembuatan partisi untuk dipanggil untuk setiap partisi yang dilampirkan atau dibuat (baik HASH maupun RANGE).
Panggilan balik harus memiliki tanda tangan berikut:
part_init_callback(args JSONB) RETURNS VOID.
Parameter arg terdiri dari beberapa bidang yang keberadaannya bergantung pada jenis partisi:
/* Tabel abc terpartisi RANGE (anak abc_4) */
{
"parent": "abc",
"parttype": "2",
"partition": "abc_4",
"range_max": "401",
"range_min": "301"
}
/* Tabel abc terpartisi HASH (anak abc_0) */
{
"parent": "abc",
"parttype": "1",
"partition": "abc_0"
}Contoh:
Buat fungsi panggilan balik.
CREATE OR REPLACE FUNCTION f_callback_test(jsonb) RETURNS void AS $$ DECLARE BEGIN CREATE TABLE if NOT EXISTS rec_part_ddl(id serial primary key, parent name, parttype int, partition name, range_max text, range_min text); if ($1->>'parttype')::int = 1 then raise notice 'parent: %, parttype: %, partition: %', $1->>'parent', $1->>'parttype', $1->>'partition'; INSERT INTO rec_part_ddl(parent, parttype, partition) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name); elsif ($1->>'parttype')::int = 2 then raise notice 'parent: %, parttype: %, partition: %, range_max: %, range_min: %', $1->>'parent', $1->>'parttype', $1->>'partition', $1->>'range_max', $1->>'range_min'; INSERT INTO rec_part_ddl(parent, parttype, partition, range_max, range_min) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name, $1->>'range_max', $1->>'range_min'); END if; END; $$ LANGUAGE plpgsql strict;Siapkan tabel uji.
CREATE TABLE tt(id int, info text, crt_time timestamp not null); --- Konfigurasikan fungsi panggilan balik untuk tabel uji. SELECT set_init_callback('tt'::regclass, 'f_callback_test'::regproc); --- Buat partisi. SELECT create_range_partitions('tt'::regclass, -- OID dari tabel utama. 'crt_time', -- Nama kolom kunci partisi. '2016-10-25 00:00:00'::timestamp, -- Nilai awal rentang untuk partisi pertama. interval '1 month', -- Rentang atau span nilai yang dicakup oleh setiap partisi. 24, -- Jumlah partisi. false) ;-- Periksa apakah fungsi panggilan balik dipanggil.
SELECT * FROM rec_part_ddl;Hasil contoh:
id | parent | parttype | partition | range_max | range_min ----+--------+----------+-----------+---------------------+--------------------- 1 | tt | 2 | tt_1 | 2016-11-25 00:00:00 | 2016-10-25 00:00:00 2 | tt | 2 | tt_2 | 2016-12-25 00:00:00 | 2016-11-25 00:00:00 3 | tt | 2 | tt_3 | 2017-01-25 00:00:00 | 2016-12-25 00:00:00 4 | tt | 2 | tt_4 | 2017-02-25 00:00:00 | 2017-01-25 00:00:00 5 | tt | 2 | tt_5 | 2017-03-25 00:00:00 | 2017-02-25 00:00:00 6 | tt | 2 | tt_6 | 2017-04-25 00:00:00 | 2017-03-25 00:00:00 7 | tt | 2 | tt_7 | 2017-05-25 00:00:00 | 2017-04-25 00:00:00 8 | tt | 2 | tt_8 | 2017-06-25 00:00:00 | 2017-05-25 00:00:00 9 | tt | 2 | tt_9 | 2017-07-25 00:00:00 | 2017-06-25 00:00:00 10 | tt | 2 | tt_10 | 2017-08-25 00:00:00 | 2017-07-25 00:00:00 11 | tt | 2 | tt_11 | 2017-09-25 00:00:00 | 2017-08-25 00:00:00 12 | tt | 2 | tt_12 | 2017-10-25 00:00:00 | 2017-09-25 00:00:00 13 | tt | 2 | tt_13 | 2017-11-25 00:00:00 | 2017-10-25 00:00:00 14 | tt | 2 | tt_14 | 2017-12-25 00:00:00 | 2017-11-25 00:00:00 15 | tt | 2 | tt_15 | 2018-01-25 00:00:00 | 2017-12-25 00:00:00 16 | tt | 2 | tt_16 | 2018-02-25 00:00:00 | 2018-01-25 00:00:00 17 | tt | 2 | tt_17 | 2018-03-25 00:00:00 | 2018-02-25 00:00:00 18 | tt | 2 | tt_18 | 2018-04-25 00:00:00 | 2018-03-25 00:00:00 19 | tt | 2 | tt_19 | 2018-05-25 00:00:00 | 2018-04-25 00:00:00 20 | tt | 2 | tt_20 | 2018-06-25 00:00:00 | 2018-05-25 00:00:00 21 | tt | 2 | tt_21 | 2018-07-25 00:00:00 | 2018-06-25 00:00:00 22 | tt | 2 | tt_22 | 2018-08-25 00:00:00 | 2018-07-25 00:00:00 23 | tt | 2 | tt_23 | 2018-09-25 00:00:00 | 2018-08-25 00:00:00 24 | tt | 2 | tt_24 | 2018-10-25 00:00:00 | 2018-09-25 00:00:00 (24 baris)