Bagikan melalui

Mengaktifkan dan menggunakan pg_partman di Azure Database for PostgreSQL - Server Fleksibel

BERLAKU UNTUK: Azure Database for PostgreSQL - Server Fleksibel

Dalam artikel ini, Anda mempelajari cara mengoptimalkan Azure Database for PostgreSQL - Server Fleksibel dengan menggunakan ekstensi PostgreSQL Partition Manager (pg_partman).

Ketika tabel dalam database menjadi besar, sulit untuk mengelola seberapa sering tabel dikosongkan, berapa banyak ruang yang mereka ambil, dan cara menjaga indeks mereka tetap efisien. Kesulitan ini dapat membuat kueri lebih lambat dan memengaruhi performa. Pemartisian tabel besar adalah solusi untuk situasi ini.

Dalam artikel ini, Anda menggunakan pg_partman untuk membuat partisi tabel berbasis rentang di server fleksibel Azure Database for PostgreSQL Anda.

Prasyarat

Untuk mengaktifkan pg_partman ekstensi, ikuti langkah-langkah berikut:

  1. Di portal Azure, pilih pg_partman dalam daftar parameter server untuk azure.extensions.

    Cuplikan layar yang memperlihatkan pilihan ekstensi pg_partman dalam daftar ekstensi Azure. 

    CREATE EXTENSION pg_partman;

  2. Sertakan ekstensi terkait pg_partman_bgw di shared_preload_libraries. Ini menawarkan fungsi run_maintenance()terjadwal . Ini mengurus set partisi yang telah automatic_maintenance diatur ke ON dalam part_config.

    Cuplikan layar yang memperlihatkan pilihan ekstensi pg_partman_bgw.

    Anda dapat menggunakan parameter server di portal Azure untuk mengubah opsi konfigurasi berikut yang memengaruhi proses Penulis Latar Belakang (BGW):

    • pg_partman_bgw.dbname:Diperlukan. Parameter ini harus berisi satu atau beberapa database di mana run_maintenance() perlu dijalankan. Jika ada lebih dari satu database, gunakan daftar yang dipisahkan koma. Jika tidak ada yang diatur, pg_partman_bgw tidak menjalankan prosedur.

    • pg_partman_bgw.interval: Jumlah detik antara panggilan ke run_maintenance() prosedur. Defaultnya adalah 3600 (1 jam). Anda dapat memperbarui nilai ini berdasarkan persyaratan proyek.

    • pg_partman_bgw.role: Peran yang run_maintenance() dijalankan prosedur sebagai. Defaultnya adalah postgres. Hanya satu nama peran yang diizinkan.

    • pg_partman_bgw.analyze: Tujuan yang sama dengan p_analyze argumen ke run_maintenance(). Secara default, diatur ke OFF.

    • pg_partman_bgw.jobmon: Tujuan yang sama dengan p_jobmon argumen ke run_maintenance(). Secara default, diatur ke ON.

Catatan

  • Saat fitur identitas menggunakan urutan, data dari tabel induk mendapatkan nilai urutan baru. Ini tidak menghasilkan nilai urutan baru saat data langsung ditambahkan ke tabel anak.

  • pg_partman Ekstensi menggunakan templat untuk mengontrol apakah tabel adalah UNLOGGED. Ini berarti ALTER TABLE perintah tidak dapat mengubah status ini untuk set partisi. Dengan mengubah status pada templat, Anda dapat menerapkannya ke semua partisi di masa mendatang. Tetapi untuk tabel anak yang ada, Anda harus menggunakan ALTER TABLE perintah secara manual. Bug ini menunjukkan alasannya.  

Menyiapkan izin

Peran superuser tidak diperlukan dengan pg_partman. Satu-satunya persyaratan adalah bahwa peran yang menjalankan pg_partman fungsi memiliki kepemilikan atas semua set partisi dan skema tempat objek baru akan dibuat.

Kami menyarankan agar Anda membuat peran terpisah untuk pg_partman dan memberikannya kepemilikan atas skema dan semua objek yang pg_partman akan beroperasi pada:

CREATE ROLE partman_role WITH LOGIN; 
CREATE SCHEMA partman; 
GRANT ALL ON SCHEMA partman TO partman_role; 
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_role; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_role; 
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_role; 
GRANT ALL ON SCHEMA <partition_schema> TO partman_role; 
GRANT TEMPORARY ON DATABASE <databasename> to partman_role; --  This allows temporary table creation to move data. 

Membuat partisi

Ekstensi ini pg_partman hanya mendukung partisi jenis rentang, bukan partisi berbasis pemicu. Kode berikut menunjukkan bagaimana pg_partman membantu pemartisian tabel:

CREATE SCHEMA partman; 
CREATE TABLE partman.partition_test 
(a_int INT, b_text TEXT,c_text TEXT,d_date TIMESTAMP DEFAULT now()) 
PARTITION BY RANGE(d_date); 
CREATE INDEX idx_partition_date ON partman.partition_test(d_date); 

Cuplikan layar output tabel untuk pg_partman.

Dengan menggunakan fungsi , create_parent Anda dapat menyiapkan jumlah partisi yang Anda inginkan pada tabel partisi:

SELECT public.create_parent( 
p_parent_table := 'partman.partition_test', 
p_control := 'd_date', 
p_type := 'native', 
p_interval := 'daily', 
p_premake :=20, 
p_start_partition := (now() - interval '10 days')::date::text  
);

UPDATE public.part_config   
SET infinite_time_partitions = true,  
    retention = '1 hour',   
    retention_keep_table=true   
        WHERE parent_table = 'partman.partition_test';  

Perintah sebelumnya dibagi p_parent_table menjadi bagian yang lebih kecil berdasarkan p_control kolom, dengan menggunakan partisi asli. (Opsi lainnya adalah partisi berbasis pemicu, tetapi pg_partman saat ini tidak mendukungnya.) Partisi dibuat pada interval harian.

Contohnya membuat 20 partisi masa depan terlebih dahulu, alih-alih menggunakan nilai 4default . Ini juga menentukan p_start_partition, di mana Anda menyebutkan tanggal lalu dari mana partisi harus dimulai.

Fungsi ini create_parent() mengisi dua tabel: part_config dan part_config_sub. Ada fungsi pemeliharaan, run_maintenance(). Anda dapat menjadwalkan cron pekerjaan agar prosedur ini berjalan secara berkala. Fungsi ini memeriksa semua tabel induk dalam part_config tabel dan membuat partisi baru untuk tabel tersebut, atau menjalankan kebijakan penyimpanan kumpulan tabel. Untuk mempelajari selengkapnya tentang fungsi dan tabel di pg_partman, lihat dokumentasi Ekstensi Manajer Partisi PostgreSQL di GitHub.

Untuk membuat partisi baru setiap kali run_maintenance() dijalankan di latar belakang melalui pg_partman_bgw ekstensi, jalankan pernyataan berikut UPDATE :

UPDATE partman.part_config SET premake = premake+1 WHERE parent_table = 'partman.partition_test'; 

Jika premake sama dan prosedur Anda run_maintenance() dijalankan, tidak ada partisi baru yang dibuat untuk hari itu. Untuk hari berikutnya, karena premake mendefinisikan dari hari ini, partisi baru untuk sehari dibuat dengan eksekusi fungsi Anda run_maintenance() .

Dengan menggunakan perintah berikut INSERT INTO , sisipkan 100.000 baris untuk setiap bulan:

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(1,100000),GENERATE_SERIES(1, 100000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(1, 100000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-03-01',timestamp '2024-03-30', interval '1 day ') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(100000,200000),GENERATE_SERIES(100000,200000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(100000,200000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-04-01',timestamp '2024-04-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(200000,300000),GENERATE_SERIES(200000,300000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(200000,300000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-05-01',timestamp '2024-05-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(300000,400000),GENERATE_SERIES(300000,400000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(300000,400000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-06-01',timestamp '2024-06-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(400000,500000),GENERATE_SERIES(400000,500000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(400000,500000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-07-01',timestamp '2024-07-30', interval '1 day') ; 

Jalankan perintah berikut pada PostgreSQL untuk melihat partisi yang dibuat:

\d+ partman.partition_test;

Cuplikan layar output tabel dengan partisi.

Berikut adalah output pernyataan yang Anda jalankan SELECT :

Cuplikan layar output rencana penjelasan.

Menjalankan prosedur pemeliharaan secara manual

Anda dapat menjalankan partman.run_maintenance() perintah secara manual alih-alih menggunakan pg_partman_bgw. Gunakan perintah berikut untuk menjalankan prosedur pemeliharaan secara manual:

SELECT partman.run_maintenance(p_parent_table:='partman.partition_test');

Peringatan

Jika Anda menyisipkan data sebelum membuat partisi, data masuk ke partisi default. Jika partisi default memiliki data milik partisi baru yang ingin Anda buat nanti, Anda mendapatkan kesalahan pelanggaran partisi default dan prosedur tidak berfungsi. Ubah nilai premake yang direkomendasikan sebelumnya lalu jalankan prosedur.

Menjadwalkan prosedur pemeliharaan

Jalankan prosedur pemeliharaan dengan menggunakan pg_cron:

  1. Pertama, aktifkan pg_cron di server Anda. Di portal Azure, tambahkan pg_cron ke azure. extensionsparameter server , , shared_preload_librariesdan cron.database_name .

    Cuplikan layar yang memperlihatkan penambahan pg_cron ke parameter server untuk ekstensi Azure.

    Cuplikan layar yang memperlihatkan penambahan pg_cron ke parameter server untuk pustaka pramuat bersama.

    Cuplikan layar yang memperlihatkan parameter server untuk nama database cron.

  2. Pilih tombol Simpan dan biarkan penyebaran selesai.

    Setelah penyebaran selesai, pg_cron dibuat secara otomatis. Jika Anda mencoba menginstalnya, Anda akan mendapatkan pesan berikut:

    CREATE EXTENSION pg_cron;   

    ERROR: extension "pg_cron" already exists

  3. Untuk menjadwalkan cron pekerjaan, gunakan perintah berikut:

    SELECT cron.schedule_in_database('sample_job','@hourly', $$SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test')$$,'postgres'); 

  4. Untuk melihat semua cron pekerjaan, gunakan perintah berikut:

    SELECT * FROM cron.job; 

    -[ RECORD 1 ]----------------------------------------------------------------------- 

jobid    | 1 
schedule | @hourly 
command  | SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test') 
nodename | /tmp 
nodeport | 5432 
database | postgres 
username | postgres 
active   | t 
jobname  | sample_job 

  5. Untuk memeriksa riwayat eksekusi pekerjaan, gunakan perintah berikut:

    SELECT * FROM cron.job_run_details; 

    Hasilnya menunjukkan nol rekaman karena Anda belum menjalankan pekerjaan.

  6. Untuk membatalkan jadwal cron pekerjaan, gunakan perintah berikut:

    SELECT cron.unschedule(1); 

Tanya jawab umum

  • pg_partman_bgw Mengapa tidak menjalankan prosedur pemeliharaan berdasarkan interval yang saya berikan?

    Periksa parameter pg_partman_bgw.dbname server dan perbarui dengan nama database yang tepat. Selain itu, periksa parameter pg_partman_bgw.role server dan berikan peran yang sesuai. Anda juga harus memastikan bahwa Anda terhubung ke server dengan menggunakan pengguna yang sama untuk membuat ekstensi, bukan Postgres.

  • Saya mengalami kesalahan saat pg_partman_bgw menjalankan prosedur pemeliharaan. Apa alasannya?

    Lihat jawaban sebelumnya.

  • Bagaimana cara mengatur partisi untuk memulai dari hari sebelumnya?

    Fungsi p_start_partition ini mengacu pada tanggal dari mana partisi harus dibuat. Jalankan perintah berikut:

    SELECT public.create_parent( 
p_parent_table := 'partman.partition_test', 
p_control := 'd_date', 
p_type := 'native', 
p_interval := 'daily', 
p_premake :=20, 
p_start_partition := (now() - interval '10 days')::date::text  
);

Konten terkait