Topik ini menjelaskan kasus penggunaan umum ekstensi pg_pathman.
Informasi latar belakang
PolarDB for PostgreSQL menyertakan ekstensi pg_pathman untuk meningkatkan performa tabel partisi. Ekstensi ini menyediakan mekanisme manajemen dan optimasi partisi.
Buat ekstensi pg_pathman
Untuk menggunakan fitur manajemen partisi dari ekstensi pg_pathman, hubungi kami.
CREATE EXTENSION IF NOT EXISTS pg_pathman;Setelah membuat ekstensi tersebut, Anda dapat menjalankan pernyataan SQL berikut untuk melihat versinya:
SELECT extname,extversion FROM pg_extension WHERE extname = 'pg_pathman';Hasil berikut dikembalikan:
extname | extversion
------------+------------
pg_pathman | 1.5
(1 row)Upgrade ekstensi
PolarDB for PostgreSQL secara rutin melakukan upgrade plugin-nya untuk menyediakan layanan database yang lebih baik. Anda harus meng-upgrade kluster ke versi terbaru sebelum dapat meng-upgrade plugin.
Fitur ekstensi
Mendukung hash partitioning dan range partitioning.
Mendukung manajemen partisi otomatis, yang menggunakan fungsi untuk membuat partisi dan memigrasikan data dari tabel utama. Juga mendukung manajemen partisi manual, yang memungkinkan Anda menyambungkan atau melepas tabel yang sudah ada menggunakan fungsi.
Mendukung tipe data umum untuk kolom kunci partisi, seperti int, float, dan date, termasuk domain kustom.
Menghasilkan rencana kueri yang efisien untuk tabel partisi, termasuk untuk operasi JOIN dan subselect.
Menggunakan node rencana kustom,
RuntimeAppenddanRuntimeMergeAppend, untuk mengimplementasikan pemilihan partisi dinamis.PartitionFiltermenyediakan alternatif yang efisien untuk pemicu insert.Mendukung pembuatan otomatis partisi baru. Fitur ini saat ini hanya tersedia untuk tabel terpartisi rentang.
Mendukung operasi baca dan tulis langsung pada tabel partisi menggunakan
copy from/tountuk efisiensi yang lebih baik.Mendukung pembaruan pada kolom kunci partisi melalui pemicu. Untuk menghindari potensi dampak performa, jangan tambahkan pemicu ini jika Anda tidak perlu memperbarui kolom kunci partisi.
Memungkinkan Anda menentukan fungsi callback kustom yang secara otomatis dipicu saat partisi dibuat.
Membuat tabel partisi dan memigrasikan data dari tabel utama ke partisi di latar belakang tanpa menghalangi operasi.
Mendukung Foreign Data Wrappers (FDWs). Anda dapat mengonfigurasi parameter
pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw)untuk mendukung postgres_fdw atau FDW lainnya.
Penggunaan
Untuk informasi lebih lanjut, lihat proyek di GitHub.
Tampilan dan tabel terkait
pg_pathman menggunakan fungsi untuk memelihara tabel partisi dan menyediakan beberapa tampilan agar Anda dapat memeriksa status tabel-tabel tersebut. Tampilan tersebut adalah sebagai berikut:
pathman_config
CREATE TABLE IF NOT EXISTS pathman_config ( partrel REGCLASS NOT NULL PRIMARY KEY, -- OID of the primary table attname TEXT NOT NULL, -- Name of the partition key column parttype INTEGER NOT NULL, -- Partitioning type (hash or range) range_interval TEXT, -- Interval for range partitions CHECK (parttype IN (1, 2)) /* check for allowed part types */ );pathman_config_params
CREATE TABLE IF NOT EXISTS pathman_config_params ( partrel REGCLASS NOT NULL PRIMARY KEY, -- OID of the primary table enable_parent BOOLEAN NOT NULL DEFAULT TRUE, -- Specifies whether to filter the primary table in the optimizer auto BOOLEAN NOT NULL DEFAULT TRUE, -- Specifies whether to automatically create a new partition if it does not exist during an insert operation init_callback REGPROCEDURE NOT NULL DEFAULT 0); -- OID of the callback function for partition creationpathman_concurrent_part_tasks
-- helper SRF function 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
-- helper SRF function 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();
Manajemen partisi
Range partitioning
Empat fungsi manajemen tersedia untuk membuat partisi rentang. Dua di antaranya memungkinkan Anda menentukan nilai awal, interval, dan jumlah partisi. Definisinya adalah sebagai berikut:
create_range_partitions(relation REGCLASS, -- OID of the primary table
attribute TEXT, -- Name of the partition key column
start_value ANYELEMENT, -- Start value
p_interval ANYELEMENT, -- Interval. Can be of any data type suitable for any type of partitioned table.
p_count INTEGER DEFAULT NULL, -- Number of partitions to create
partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.
create_range_partitions(relation REGCLASS, -- OID of the primary table
attribute TEXT, -- Name of the partition key column
start_value ANYELEMENT, -- Start value
p_interval INTERVAL, -- Interval. The interval data type is used for time-based partitioned tables.
p_count INTEGER DEFAULT NULL, -- Number of partitions to create
partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.Dua fungsi lainnya memungkinkan Anda menentukan nilai awal, nilai akhir, dan interval. Definisinya adalah sebagai berikut:
create_partitions_from_range(relation REGCLASS, -- OID of the primary table
attribute TEXT, -- Name of the partition key column
start_value ANYELEMENT, -- Start value
end_value ANYELEMENT, -- End value
p_interval ANYELEMENT, -- Interval. Can be of any data type suitable for any type of partitioned table.
partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.
create_partitions_from_range(relation REGCLASS, -- OID of the primary table
attribute TEXT, -- Name of the partition key column
start_value ANYELEMENT, -- Start value
end_value ANYELEMENT, -- End value
p_interval INTERVAL, -- Interval. The interval data type is used for time-based partitioned tables.
partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.Sebagai contoh:
Buat tabel utama yang akan dipartisi dan masukkan data uji.
--- Create the primary table to be partitioned CREATE TABLE part_test(id int, info text, crt_time timestamp not null); -- The partition key column must have a NOT NULL constraint --- Insert test data to simulate a primary table that already contains data INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id);Kueri data di tabel utama:
SELECT * FROM part_test limit 10;Hasil berikut dikembalikan:
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 rows)Buat partisi. Setiap partisi berisi data untuk rentang satu bulan.
--- Create partitions. Each partition contains data for a one-month span. SELECT create_range_partitions('part_test'::regclass, -- OID of the primary table 'crt_time', -- Name of the partition key column '2016-10-25 00:00:00'::timestamp, -- Start value interval '1 month', -- Interval. The interval data type is used for time-based partitioned tables. 24, -- Number of partitions to create false) ; -- Do not migrate dataGunakan fungsi migrasi non-blocking untuk memigrasikan data dari tabel utama.
--- Before data migration, the data is still in the primary table SELECT count(*) FROM ONLY part_test; count ------- 10000 (1 row) --- Non-blocking migration function partition_table_concurrently(relation REGCLASS, -- OID of the primary table batch_size INTEGER DEFAULT 1000, -- Number of records to migrate in a single transaction batch sleep_time FLOAT8 DEFAULT 1.0) -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries. --- Use the non-blocking migration function to migrate data from the primary table SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0); --- After migration, the primary table is empty. All data has been moved to the partitions. SELECT count(*) FROM ONLY part_test; count ------- 0 (1 row)Setelah migrasi data selesai, nonaktifkan tabel utama agar tidak muncul dalam rencana eksekusi.
--- Disable the primary table SELECT set_enable_parent('part_test'::regclass, false); --- Verification 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 rows)
Saat menggunakan tabel terpartisi rentang, ikuti praktik terbaik berikut:
Kolom kunci partisi harus memiliki kendala NOT NULL.
Jumlah partisi harus cukup untuk mencakup semua catatan yang ada.
Gunakan fungsi migrasi non-blocking.
Setelah migrasi data selesai, nonaktifkan tabel utama.
Hash partitioning
Fungsi manajemen memungkinkan Anda membuat partisi rentang dengan menentukan nilai awal, interval, dan jumlah partisi sebagai berikut:
create_hash_partitions(relation REGCLASS, -- OID of the primary table
attribute TEXT, -- Name of the partition key column
partitions_count INTEGER, -- Number of partitions to create
partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.Sebagai contoh:
Buat tabel utama yang akan dipartisi dan masukkan data uji.
--- Create the primary table to be partitioned CREATE TABLE part_test(id int, info text, crt_time timestamp not null); -- The partition key column must have a NOT NULL constraint --- Insert test data to simulate a primary table that already contains data INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval FROM generate_series(1,10000) t(id);Kueri data di tabel utama:
SELECT * FROM part_test limit 10;Hasil berikut dikembalikan:
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 rows)Buat partisi.
--- Create 128 partitions SELECT create_hash_partitions('part_test'::regclass, -- OID of the primary table 'crt_time', -- Name of the partition key column 128, -- Number of partitions to create false) ; -- Do not migrate dataGunakan fungsi migrasi non-blocking untuk memigrasikan data dari tabel utama.
--- Before data migration, the data is still in the primary table SELECT count(*) FROM ONLY part_test; count ------- 10000 (1 row) --- Non-blocking migration function partition_table_concurrently(relation REGCLASS, -- OID of the primary table batch_size INTEGER DEFAULT 1000, -- Number of records to migrate in a single transaction batch sleep_time FLOAT8 DEFAULT 1.0) -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries. --- Use the non-blocking migration function to migrate data from the primary table SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0); --- After migration, the primary table is empty. All data has been moved to the partitions. SELECT count(*) FROM ONLY part_test; count ------- 0 (1 row)Setelah migrasi data selesai, nonaktifkan tabel utama agar tidak muncul dalam rencana eksekusi.
--- Disable the primary table SELECT set_enable_parent('part_test'::regclass, false);Verifikasi rencana eksekusi:
--- Query a single partition 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 rows)Kendala berikut pada tabel partisi menunjukkan bahwa pg_pathman secara otomatis melakukan konversi. Sebaliknya, pewarisan tradisional tidak dapat melakukan pemangkasan partisi untuk 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 tabel terpartisi hash, ikuti praktik terbaik berikut:
Kolom kunci partisi harus memiliki kendala NOT NULL.
Gunakan fungsi migrasi non-blocking.
Setelah migrasi data selesai, nonaktifkan tabel utama.
pg_pathman tidak dibatasi oleh format ekspresi. Oleh karena itu, pernyataan seperti
select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;juga dapat digunakan untuk partisi hash.Kolom kunci partisi untuk partisi hash tidak terbatas pada tipe data int. Fungsi hash digunakan untuk konversi otomatis.
Migrasikan data ke partisi
Jika Anda tidak memigrasikan data dari tabel utama saat membuat tabel partisi, Anda dapat menggunakan fungsi migrasi non-blocking untuk memigrasikan data tersebut.
WITH tmp AS (DELETE FROM primary_table limit xx nowait returning *) INSERT INTO partition SELECT * FROM tmp;Atau, Anda dapat menggunakan pernyataan berikut untuk menandai baris-baris tersebut lalu melakukan operasi DELETE dan INSERT.
SELECT array_agg(ctid) FROM primary_table limit xx FOR UPDATE nowait;Fungsi tersebut didefinisikan sebagai berikut:
partition_table_concurrently(relation REGCLASS, -- OID of the primary table
batch_size INTEGER DEFAULT 1000, -- Number of records to migrate in a single transaction batch
sleep_time FLOAT8 DEFAULT 1.0) -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries.Sebagai contoh:
SELECT partition_table_concurrently('part_test'::regclass,
10000,
1.0);Anda dapat melihat tugas migrasi data latar belakang.
SELECT * FROM pathman_concurrent_part_tasks;Pemisahan partisi rentang
Jika partisi menjadi terlalu besar, Anda dapat membaginya menjadi dua partisi. Fitur ini saat ini hanya tersedia untuk tabel terpartisi rentang. Anda dapat menggunakan fungsi berikut:
split_range_partition(partition REGCLASS, -- OID of the partition
split_value ANYELEMENT, -- Split value
partition_name TEXT DEFAULT NULL) -- Name of the new partition table created after the splitSebagai contoh:
Gunakan tabel partisi dari contoh Range partitioning. Skema tabelnya adalah sebagai 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 tersebut.
SELECT split_range_partition('part_test_1'::regclass, -- OID of the partition '2016-11-10 00:00:00'::timestamp, -- Split value 'part_test_1_2'); -- Name of the partition tableDua tabel setelah pemisahan adalah sebagai 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-partisi baru.
SELECT count(*) FROM part_test_1; count ------- 373 (1 row) SELECT count(*) FROM part_test_1_2; count ------- 360 (1 row)Hubungan pewarisan adalah sebagai 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_1_2, -- New table 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
Gabungkan partisi rentang
Fitur ini saat ini hanya tersedia untuk partisi rentang, dan partisi-partisi tersebut harus bersebelahan. Anda dapat memanggil fungsi berikut:
--- Specify the two partitions to merge
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)Berikut adalah contohnya:
Gunakan tabel dari contoh Pemisahan partisi rentang untuk melakukan operasi penggabungan.
SELECT merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass);CatatanJika Anda mencoba menggabungkan partisi yang tidak bersebelahan, kesalahan akan dilaporkan.
SELECT merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass) ; ERROR: merge failed, partitions must be adjacent 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 penggabungan, salah satu partisi asli dihapus.
\d part_test_1_2 Did not find any relation named "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 row)
Tambahkan partisi rentang
Jika tabel utama sudah dipartisi, Anda dapat menambahkan partisi baru dengan beberapa cara. Bagian ini menjelaskan tiga metode: menambahkan partisi rentang di akhir (append), menambahkan di awal (prepend), dan menambahkan partisi rentang dengan nilai awal tertentu.
Append a range partition
Saat Anda menambahkan partisi rentang di akhir (append), interval yang ditentukan saat pembuatan awal tabel partisi digunakan. Anda dapat mengkueri tampilan `pathman_config` untuk menemukan interval awal untuk setiap tabel partisi, seperti yang ditunjukkan di bawah ini:
SELECT * FROM pathman_config;
partrel | attname | parttype | range_interval
-----------+----------+----------+----------------
part_test | crt_time | 2 | 1 mon
(1 row)Fungsi untuk menambahkan partisi adalah sebagai berikut. Menentukan ruang tabel saat ini tidak didukung.
append_range_partition(parent REGCLASS, -- OID of the primary table
partition_name TEXT DEFAULT NULL, -- Name of the new partition table. This is optional.
tablespace TEXT DEFAULT NULL) -- Tablespace for the new partition table. This is optional.Berikut adalah contohnya:
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_testPrepend a range partition
Untuk menambahkan partisi rentang di awal, gunakan fungsi berikut:
prepend_range_partition(parent REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)Sebagai 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_testAdd a partition with a specified start value
Anda dapat menambahkan partisi rentang dengan menentukan nilai awal dan akhirnya. Partisi dapat dibuat asalkan tidak tumpang tindih dengan partisi yang sudah ada. Metode ini tidak mengharuskan Anda membuat partisi yang bersebelahan. Misalnya, jika partisi yang ada mencakup rentang dari tahun 2010 hingga 2015, Anda dapat langsung membuat partisi untuk tahun 2020 tanpa mencakup rentang dari 2015 hingga 2020. Fungsi tersebut adalah sebagai berikut:
add_range_partition(relation REGCLASS, -- OID of the primary table
start_value ANYELEMENT, -- Start value
end_value ANYELEMENT, -- End value
partition_name TEXT DEFAULT NULL, -- Name of the partition
tablespace TEXT DEFAULT NULL) -- Tablespace where the partition is createdSebagai contoh:
SELECT add_range_partition('part_test'::regclass, -- OID of the primary table
'2020-01-01 00:00:00'::timestamp, -- Start value
'2020-02-01 00:00:00'::timestamp); -- End value
\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_testHapus partisi
Untuk menghapus satu partisi rentang, gunakan fungsi berikut:
drop_range_partition(partition TEXT, -- Name of the partition delete_data BOOLEAN DEFAULT TRUE) -- Specifies whether to delete the partition data. If false, the data is migrated to the primary table. Drop RANGE partition and all of its data if delete_data is true.Untuk menghapus semua partisi dan menentukan apakah data akan dimigrasikan ke tabel utama, gunakan fungsi berikut:
drop_partitions(parent REGCLASS, delete_data BOOLEAN DEFAULT FALSE) Drop partitions of the parent table (both foreign and local relations). If delete_data is false, the data is copied to the parent table first. Default is false.
Sebagai contoh:
Hapus partisi dan migrasikan datanya ke tabel utama.
SELECT drop_range_partition('part_test_1',false); SELECT drop_range_partition('part_test_2',false);Kueri volume data saat ini di tabel utama:
SELECT count(*) FROM part_test; count ------- 10000 (1 row)Hapus partisi beserta datanya. Data tidak dimigrasikan ke tabel utama.
SELECT drop_range_partition('part_test_3',true);Kueri volume data saat ini di tabel utama:
SELECT count(*) FROM part_test; count ------- 9256 (1 row) SELECT count(*) FROM ONLY part_test; count ------- 1453 (1 row)Hapus semua partisi.
SELECT drop_partitions('part_test'::regclass, false); -- Delete all partition tables and migrate the data to the primary tableKueri data di tabel utama:
SELECT count(*) FROM part_test; count ------- 9256 (1 row)
Menyambungkan partisi
Anda dapat menyambungkan tabel yang sudah ada ke tabel utama partisi. Tabel yang sudah ada harus memiliki skema yang sama dengan tabel utama, termasuk kolom yang di-drop. Anda dapat memeriksa tampilan `pg_attribute` untuk konsistensi. Fungsi tersebut adalah sebagai berikut:
attach_range_partition(relation REGCLASS, -- OID of the primary table
partition REGCLASS, -- OID of the partition table
start_value ANYELEMENT, -- Start value
end_value ANYELEMENT) -- End valueBerikut adalah contohnya:
Buat tabel yang akan digunakan sebagai partisi.
CREATE TABLE part_test_1 (like part_test including all);Sambungkan tabel yang sudah ada 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 Anda menyambungkan partisi, hubungan pewarisan 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
Melepas partisi
Anda dapat menghapus partisi dari hierarki pewarisan tabel utama. Operasi ini tidak menghapus data tetapi menghapus hubungan pewarisan dan kendala. Fungsi tersebut adalah sebagai berikut:
detach_range_partition(partition REGCLASS) -- Specify the partition name to convert it into a standard tableBerikut adalah contohnya:
Kueri volume data saat ini di tabel utama dan partisi.
SELECT count(*) FROM part_test; count ------- 9256 (1 row) SELECT count(*) FROM part_test_2; count ------- 733 (1 row)Lepaskan partisi tersebut.
SELECT detach_range_partition('part_test_2');Kueri volume data saat ini di tabel utama dan partisi.
SELECT count(*) FROM part_test_2; count ------- 733 (1 row) SELECT count(*) FROM part_test; count ------- 8523 (1 row)
Nonaktifkan pg_pathman
Anda dapat menonaktifkan pg_pathman untuk satu tabel utama partisi. Fungsi tersebut adalah sebagai berikut:
Operasi disable_pathman_for tidak dapat dibalik. Gunakan 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);
/* Notify backend about changes */
PERFORM public.on_remove_partitions(parent_relid);
END
$function$Berikut adalah contohnya:
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 Anda menonaktifkan ekstensi pg_pathman, hubungan pewarisan dan kendala tetap tidak berubah. Satu-satunya perbedaan adalah ekstensi pg_pathman tidak lagi menyediakan pemindaian kustom dalam rencana eksekusi. Rencana eksekusi setelah menonaktifkan ekstensi adalah sebagai berikut:
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 rows)Manajemen partisi lanjutan
Nonaktifkan tabel utama
Setelah semua data dari tabel utama dimigrasikan ke partisi, Anda dapat menonaktifkan tabel utama. Fungsi tersebut adalah sebagai berikut:
set_enable_parent(relation REGCLASS, value BOOLEAN)
Include/exclude parent table into/from query plan.
In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead.
You can use disable_parent() if you are never going to use parent table as a storage.
Default value depends on the partition_data parameter that was specified during initial partitioning in create_range_partitions() or create_partitions_from_range() functions.
If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled.
Otherwise it is enabled.Sebagai contoh:
SELECT set_enable_parent('part_test', false);Perluasan partisi otomatis
Untuk tabel terpartisi rentang, Anda dapat mengaktifkan pembuatan partisi otomatis. Jika data yang baru dimasukkan tidak termasuk dalam rentang partisi yang ada, partisi baru akan dibuat secara otomatis.
set_auto(relation REGCLASS, value BOOLEAN)
Enable/disable auto partition propagation (only for RANGE partitioning).
It is enabled by default.Sebagai contoh:
Kueri partisi saat ini dari tabel uji.
\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_testMasukkan nilai yang berada di luar rentang partisi yang ada. Ini secara otomatis membuat jumlah partisi yang diperlukan berdasarkan interval yang ditentukan saat pengaturan awal. Perhatikan bahwa operasi ini mungkin memakan waktu lama.
INSERT INTO part_test VALUES (1,'test','2222-01-01'::timestamp);Anda dapat mengkueri status partisi 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_100, part_test_1000, part_test_1001, ......
Kami menyarankan agar Anda tidak mengaktifkan pembuatan partisi otomatis untuk tabel terpartisi rentang. Membuat banyak partisi secara otomatis bisa sangat memakan waktu.
Fungsi callback
Fungsi callback adalah fungsi yang secara otomatis dipicu setiap kali partisi dibuat. Misalnya, Anda dapat menggunakan fungsi callback untuk replikasi logis DDL guna mencatat pernyataan DDL dalam tabel. Fungsi untuk mengatur callback adalah sebagai berikut:
set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)
Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE).
The callback must have the following signature:
part_init_callback(args JSONB) RETURNS VOID.
Parameter arg consists of several fields whose presence depends on partitioning type:
/* RANGE-partitioned table abc (child abc_4) */
{
"parent": "abc",
"parttype": "2",
"partition": "abc_4",
"range_max": "401",
"range_min": "301"
}
/* HASH-partitioned table abc (child abc_0) */
{
"parent": "abc",
"parttype": "1",
"partition": "abc_0"
}Sebagai contoh:
Buat fungsi callback.
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); --- Set the callback function for the test table SELECT set_init_callback('tt'::regclass, 'f_callback_test'::regproc); --- Create partitions SELECT create_range_partitions('tt'::regclass, -- OID of the primary table 'crt_time', -- Name of the partition key column '2016-10-25 00:00:00'::timestamp, -- Start value interval '1 month', -- Interval. The interval data type is used for time-based partitioned tables. 24, -- Number of partitions to create false) ;Periksa apakah fungsi callback dipanggil.
SELECT * FROM rec_part_ddl;Hasil berikut dikembalikan:
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 rows)