AnalyticDB:Mendefinisikan\ tabel\ partisi

Kapan menggunakan partisi

Partisi bekerja paling baik ketika ketiga kondisi berikut terpenuhi:

• Tabel berskala besar. Tabel dengan jumlah baris yang besar merupakan kandidat yang baik untuk dipartisi.

• Performa kueri tidak memuaskan. Partisi hanya diterapkan pada tabel yang menghasilkan waktu respons kueri lebih lambat daripada yang dapat diterima.

• Predikat kueri memiliki pola akses yang jelas. Periksa klausa WHERE dalam kueri tipikal Anda. Jika kolom yang sama muncul berulang kali, kolom tersebut merupakan kandidat alami untuk kunci partisi.

Jenis partisi yang didukung

Memilih kunci partisi

Kunci partisi menentukan efektivitas pemangkasan partisi (partition pruning):

• Sesuaikan dengan predikat kueri Anda. Pilih kolom yang paling sering muncul dalam klausa WHERE. Pemangkasan partisi hanya efektif jika kondisi kueri mencakup kunci partisi.

• Selaraskan dengan strategi retensi data Anda. Jika Anda secara rutin menghapus data lama secara massal, rancang partisi sehingga data yang akan dihapus berada dalam satu partisi saja. Menghapus partisi jauh lebih cepat daripada menjalankan pernyataan DELETE.

• Batasan kunci primer. Jika tabel memiliki primary key, kunci partisi harus merupakan kolom kunci primer. Tanpa primary key, kolom apa pun dapat digunakan.

Membuat tabel partisi range

Tentukan START, END, dan EVERY untuk membuat partisi secara otomatis dalam suatu rentang. Secara default, START bersifat inklusif dan END bersifat eksklusif.

Partisi berdasarkan tanggal:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 day') );

Partisi berdasarkan bilangan bulat dengan partisi default:

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1),
  DEFAULT PARTITION extra );

DEFAULT PARTITION menampung baris yang berada di luar semua rentang yang telah ditentukan.

Membuat tabel partisi list

Kunci partisi dapat berupa kolom apa pun dengan tipe data yang mendukung perbandingan nilai. Deklarasikan daftar nilai untuk setiap partisi.

CREATE TABLE rank (id int, rank int, year int, gender
char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'),
  DEFAULT PARTITION other );

Membuat tabel partisi multi-level

Contoh berikut membuat tabel partisi tiga level:

• Level 1: dipartisi berdasarkan year menggunakan range

• Level 2 (subpartisi): dipartisi berdasarkan month menggunakan range

• Level 3 (subpartisi): dipartisi berdasarkan region menggunakan list

CREATE TABLE sales (id int, year int, month int, day int,
region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
    SUBPARTITION BY RANGE (month)
       SUBPARTITION TEMPLATE (
        START (1) END (13) EVERY (1),
        DEFAULT SUBPARTITION other_months )
           SUBPARTITION BY LIST (region)
             SUBPARTITION TEMPLATE (
               SUBPARTITION usa VALUES ('usa'),
               SUBPARTITION europe VALUES ('europe'),
               SUBPARTITION asia VALUES ('asia'),
               DEFAULT SUBPARTITION other_regions )
( START (2002) END (2012) EVERY (1),
  DEFAULT PARTITION outlying_years );

Menambahkan partisi

Gunakan ALTER TABLE ... ADD PARTITION untuk menambahkan partisi ke tabel partisi yang sudah ada.

Jika tabel dibuat dengan templat subpartisi, partisi baru akan secara otomatis disubpartisi:

ALTER TABLE sales ADD PARTITION
            START (date '2017-02-01') INCLUSIVE
            END (date '2017-03-01') EXCLUSIVE;

Jika tidak menggunakan templat subpartisi, definisikan subpartisi secara inline saat menambahkan partisi:

ALTER TABLE sales ADD PARTITION
            START (date '2017-02-01') INCLUSIVE
            END (date '2017-03-01') EXCLUSIVE
      ( SUBPARTITION usa VALUES ('usa'),
        SUBPARTITION asia VALUES ('asia'),
        SUBPARTITION europe VALUES ('europe') );

Untuk mensubpartisi partisi yang sudah ada:

ALTER TABLE sales ALTER PARTITION FOR (RANK(12))
      ADD PARTITION africa VALUES ('africa');

Partisi tidak dapat ditambahkan ke tabel yang memiliki default partition. Untuk menambahkan partisi ke tabel semacam itu, lakukan split pada default partition. Lihat Split a partition.

Menentukan nama partisi

Mulai dari AnalyticDB for PostgreSQL V6.3.10.9, gunakan klausa WITH(tablename=<tablename>) untuk memberikan nama eksplisit pada partisi saat membuat tabel:

CREATE TABLE partition_with_name_list (a int, b int, c int) DISTRIBUTED BY (a) PARTITION BY LIST (a)
(
    PARTITION p1 VALUES (1)  WITH (tablename='partition_with_name_list_p1'),
    PARTITION p2 VALUES (2)  WITH (tablename='partition_with_name_list_p2'),
    PARTITION p3 VALUES (3)  WITH (tablename='partition_with_name_list_p3'),
    PARTITION p4 VALUES (4)  WITH (tablename='partition_with_name_list_p4')
);

Mengganti nama partisi

Mulai dari AnalyticDB for PostgreSQL V6.3.10.9, ganti nama partisi dengan:

ALTER TABLE partition_with_name_list_p1 RENAME TO partition_with_name_list_p1r;

Split partisi

Gunakan ALTER TABLE ... SPLIT PARTITION untuk membagi satu partisi menjadi dua.

Batasan:

• Jika terdapat subpartisi, hanya subpartisi di level terendah yang dapat di-split.

• Nilai split dalam klausa AT dialokasikan ke partisi kedua.

Split partisi yang sudah ada:

Contoh berikut membagi partisi Januari 2017 pada tanggal 16 Januari. Data dari 1–15 Januari masuk ke partisi pertama; data dari 16–31 Januari masuk ke partisi kedua.

ALTER TABLE sales SPLIT PARTITION FOR ('2017-01-01')
AT ('2017-01-16')
INTO (PARTITION jan171to15, PARTITION jan1716to31);

Membagi partisi default untuk menambahkan partisi baru:

Jika tabel memiliki default partition, split partisi tersebut untuk membuat partisi bernama. Default partition harus menjadi partisi kedua dalam klausa INTO.

ALTER TABLE sales SPLIT DEFAULT PARTITION
START ('2017-01-01') INCLUSIVE
END ('2017-02-01') EXCLUSIVE
INTO (PARTITION jan17, default partition);

Menentukan granularitas partisi

Granularitas partisi melibatkan pertimbangan antara jumlah data dalam setiap partisi dan jumlah total partisi. Granularitas yang lebih halus menghasilkan data lebih sedikit per partisi tetapi jumlah partisi yang lebih banyak.

Sebagai panduan umum, batasi jumlah partisi hingga 200 atau kurang per tabel. Jumlah partisi yang terlalu banyak dapat menurunkan performa database — misalnya, pengoptimal kueri memerlukan waktu lebih lama untuk menghasilkan rencana eksekusi, dan operasi VACUUM membutuhkan waktu lebih lama untuk diselesaikan.

Mengoptimalkan kueri tabel partisi

AnalyticDB for PostgreSQL mendukung partition pruning: ketika kueri mencakup kondisi pada kunci partisi, pengoptimal hanya memindai partisi yang sesuai, bukan seluruh tabel.

Verifikasi bahwa pruning aktif:

EXPLAIN
  SELECT * FROM sales
  WHERE year = 2008
    AND month = 1
    AND day = 3
    AND region = 'usa';

Output yang diharapkan (pruning bekerja dengan benar):

Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..431.00 rows=1 width=24)
  ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
        ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=25 width=4)
              Filter: year = 2008 AND month = 1 AND region = 'usa'::text
              Partitions selected:  1 (out of 468)
        ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
              Filter: year = 2008 AND month = 1 AND day = 3 AND region = 'usa'::text

Dalam rencana ini, Partitions selected: 1 (out of 468) mengonfirmasi bahwa pruning aktif. Jika Anda melihat jumlah partisi yang dipilih tetap tinggi meskipun kondisi filter spesifik, periksa apakah kolom filter sesuai dengan kunci partisi.

Menanyakan definisi partisi

Peroleh semua partisi dalam suatu tabel dari tampilan sistem pg_partitions:

SELECT
  partitionboundary,
  partitiontablename,
  partitionname,
  partitionlevel,
  partitionrank
FROM pg_partitions
WHERE tablename='sales';

Operasi terkait

Untuk daftar lengkap operasi manajemen partisi — termasuk menghapus, memotong (truncate), dan menukar (exchange) partisi — lihat Partitioning Large Tables.

FAQ

Bagaimana cara memilih kunci partisi?

Jika tabel memiliki primary key, kunci partisi harus merupakan salah satu kolom kunci primer. Tanpa primary key, Anda dapat menentukan kolom apa pun sebagai kunci partisi. Untuk hasil pemangkasan terbaik, pilih kolom yang paling sering muncul dalam klausa WHERE.