PolarDB for PostgreSQL mendukung dua metode untuk mengaktifkan dan menggunakan indeks penyimpanan kolom: menambahkan node read-only indeks penyimpanan kolom melalui konsol atau langsung menggunakan ekstensi indeks penyimpanan kolom. Anda dapat memilih metode yang paling sesuai dengan skenario bisnis Anda.
Applicability
Versi kluster:
PostgreSQL 16 (versi mesin minor 2.0.16.8.3.0 atau lebih baru)
PostgreSQL 14 (versi mesin minor 2.0.14.10.20.0 atau lebih baru)
CatatanAnda dapat melihat nomor versi mesin minor di konsol atau dengan menjalankan pernyataan
SHOW polardb_version;. Jika versi mesin minor tidak memenuhi persyaratan, Anda harus meningkatkan versi mesin minor.Tabel sumber harus memiliki kunci primer. Kolom kunci primer harus disertakan saat membuat indeks penyimpanan kolom.
Parameter
wal_levelharus diatur kelogical. Pengaturan ini menambahkan informasi yang diperlukan untuk mendukung replikasi logis ke dalam write-ahead logging (WAL).CatatanAnda dapat mengatur parameter wal_level di konsol. Modifikasi parameter ini akan me-restart kluster. Rencanakan operasi bisnis Anda dengan cermat dan lakukan dengan hati-hati.
Batasan
Hanya satu indeks penyimpanan kolom yang dapat dibuat untuk setiap tabel.
Modifikasi indeks penyimpanan kolom tidak didukung. Untuk menambahkan kolom ke indeks tersebut, Anda harus membuat ulang indeksnya.
Aktifkan fitur indeks penyimpanan kolom
Metode pengaktifan fitur indeks penyimpanan kolom bergantung pada versi mesin minor kluster PolarDB for PostgreSQL Anda:
Untuk kluster PolarDB for PostgreSQL dengan versi tersebut, tersedia dua metode. Perbedaan antara keduanya diuraikan dalam tabel berikut. Pilih metode yang paling sesuai dengan kebutuhan Anda.
Item perbandingan | [Direkomendasikan] Tambahkan node read-only indeks penyimpanan kolom | Langsung gunakan ekstensi indeks penyimpanan kolom pra-instal |
Metode | Anda dapat menambahkan node indeks penyimpanan kolom secara manual melalui antarmuka visual di konsol. | Tidak diperlukan tindakan tambahan. Anda dapat langsung menggunakan ekstensi tersebut. |
Alokasi sumber daya | Engine penyimpanan kolom menggunakan seluruh sumber daya secara eksklusif dan dapat memanfaatkan seluruh memori yang tersedia. | Engine penyimpanan kolom hanya dapat menggunakan 25% dari memori. Sisa memori dialokasikan ke engine penyimpanan baris. |
Dampak bisnis | Beban kerja pemrosesan transaksional (TP) dan pemrosesan analitis (AP) diisolasi pada node yang berbeda sehingga tidak saling memengaruhi. | Beban kerja TP dan AP berjalan pada node yang sama sehingga saling memengaruhi. |
Biaya | Node read-only Indeks Kolom dalam Memori (IMCI) dikenai biaya tambahan dan ditagih dengan tarif yang sama seperti node komputasi reguler. | Tidak ada biaya tambahan. |
Tambahkan node read-only indeks penyimpanan kolom
Anda dapat menambahkan node read-only indeks penyimpanan kolom dengan salah satu dari dua cara berikut:
Kluster harus memiliki setidaknya satu node read-only. Anda tidak dapat menambahkan node read-only indeks penyimpanan kolom ke kluster single-node.
Tambahkan di konsol
Masuk ke Konsol PolarDB dan pilih wilayah tempat kluster berada. Anda dapat membuka wizard Add/Remove Nodes dengan salah satu cara berikut:
Pada halaman Clusters, klik Add/Remove Node di kolom Actions.
Pada halaman Basic Information kluster target, klik Add/Remove Node di bagian Database Nodes.
Pilih Add Columnstore Index Read-only Node dan klik OK.
Pada halaman peningkatan/penurunan spesifikasi kluster, tambahkan node read-only indeks penyimpanan kolom dan selesaikan pembayaran.
Klik +Add a Columnstore Index Read-only Node dan pilih spesifikasi node.
Pilih waktu alih bencana.
(Opsional) Tinjau Ketentuan Layanan Produk dan Perjanjian Tingkat Layanan.
Klik Buy Now.
Setelah pembayaran selesai, kembali ke halaman detail kluster dan tunggu hingga node read-only indeks penyimpanan kolom ditambahkan. Node tersebut telah ditambahkan ketika statusnya berubah menjadi Running.
Tambahkan saat pembelian
Pada halaman pembelian PolarDB, pilih jumlah Columnstore Index Read-only Nodes untuk parameter Number of Nodes.
Untuk kluster PolarDB for PostgreSQL dengan versi tersebut, indeks penyimpanan kolom diterapkan sebagai ekstensi polar_csi di kluster database. Sebelum dapat menggunakan indeks penyimpanan kolom, Anda harus membuat ekstensi tersebut di database yang ditentukan.
Ekstensi
polar_csibersifat cakupan tingkat database. Untuk menggunakan indeks penyimpanan kolom di beberapa database dalam satu kluster, Anda harus membuat ekstensipolar_csiuntuk setiap database.Akun database yang digunakan untuk menginstal ekstensi harus merupakan akun istimewa.
Anda dapat menginstal ekstensi polar_csi dengan salah satu dari dua cara berikut.
Instal dari konsol
Masuk ke Konsol PolarDB. Di panel navigasi sebelah kiri, klik Clusters. Pilih wilayah tempat kluster Anda berada, lalu klik ID kluster untuk membuka halaman detail kluster.
Di panel navigasi sebelah kiri, pilih . Pada tab Manage Extensions, pilih Uninstalled Extensions.
Di pojok kanan atas halaman, pilih database target. Pada baris ekstensi polar_csi, klik Install di kolom Actions. Pada kotak dialog Install Extension yang muncul, pilih Database Account target dan klik OK untuk menginstal ekstensi di database target.
Instal dari command line
Hubungkan ke kluster database dan jalankan pernyataan berikut di database target tempat Anda memiliki izin yang diperlukan untuk membuat ekstensi polar_csi.
CREATE EXTENSION polar_csi;Buat indeks penyimpanan kolom
1. Buat indeks penyimpanan kolom untuk kolom tertentu atau semua kolom
Sintaks
-- Buat indeks untuk kolom tertentu
CREATE INDEX [ CONCURRENTLY ] csi_index_name ON table_name USING CSI(column1,column2,...);
-- Buat indeks untuk semua kolom
CREATE INDEX [ CONCURRENTLY ] csi_index_name ON table_name USING CSI;Parameter
Parameter | Deskripsi |
CONCURRENTLY | Opsi. Membuat indeks dalam mode konkuren. Ini tidak memblokir operasi tulis pada tabel sumber dan cocok untuk membuat indeks pada tabel online yang sudah ada. Catatan
|
csi_index_name | Nama kustom untuk indeks penyimpanan kolom. |
table_name | Nama tabel tempat indeks penyimpanan kolom akan dibuat. |
column1,column2,... | Kolom-kolom spesifik dari tabel yang akan disertakan dalam indeks penyimpanan kolom. Jika Anda tidak menentukan kolom, indeks akan dibuat untuk semua kolom. |
Contoh
Persiapkan tabel uji dasar bernama
sales.CREATE TABLE sales (sale_id int primary key, name CHAR(10), amount int);Buat indeks penyimpanan kolom. Karena hanya satu indeks penyimpanan kolom yang dapat dibuat per tabel, jalankan salah satu pernyataan berikut untuk membuat indeks pada tabel yang ditentukan.
Buat indeks penyimpanan kolom untuk kolom tertentu
CREATE INDEX idx_csi_sales ON sales USING CSI(sale_id, amount);Buat indeks penyimpanan kolom untuk semua kolom
CREATE INDEX idx_csi_sales ON sales USING CSI;Buat indeks penyimpanan kolom untuk kolom tertentu dalam mode konkuren
Membuat indeks penyimpanan kolom dalam mode konkuren tidak memblokir operasi tulis pada tabel sumber. Metode ini cocok untuk membuat indeks pada tabel online yang sudah ada.
CREATE INDEX CONCURRENTLY idx_csi_sales ON sales USING CSI(sale_id, amount);Setelah indeks dibuat, Anda dapat menjalankan pernyataan berikut untuk melihat informasi indeks:
SELECT * FROM pg_indexes WHERE tablename = 'sales';
2. Lihat progres pembuatan indeks
Membuat indeks penyimpanan kolom untuk tabel besar bisa memakan waktu lama. Anda dapat memeriksa progres pembuatan menggunakan metode berikut. Anda juga dapat menyesuaikan parameter terkait untuk meningkatkan kecepatan pembuatan.
SELECT * FROM pg_stat_progress_create_index;3. (Opsional) Batalkan pembuatan indeks
Jika pembuatan indeks memakan waktu lama dan proses tersebut memengaruhi layanan yang sudah ada, Anda dapat menggunakan pg_cancel_backend atau pg_terminate_backend untuk membatalkan pembuatan indeks. Fungsi-fungsi ini memerlukan pid, yang dapat Anda peroleh dengan mengikuti petunjuk di bagian 2. Lihat progres pembuatan indeks.
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);4. Lihat ukuran indeks
Gunakan fungsi pg_relation_size untuk melihat ukuran indeks. Anda dapat menggabungkannya dengan fungsi pg_size_pretty untuk menampilkan ukuran dalam format yang lebih mudah dibaca.
SELECT * FROM pg_size_pretty(pg_relation_size('csi_index_name'));Sebagai contoh, Anda dapat melihat ukuran penyimpanan indeks penyimpanan kolom yang sebelumnya dibuat.
SELECT * FROM pg_size_pretty(pg_relation_size('idx_csi_sales'));5. (Opsional) Konfigurasikan data real-time untuk indeks penyimpanan kolom
Kluster PolarDB for PostgreSQL mendukung pembuatan indeks penyimpanan kolom pada data historis dan secara otomatis menyinkronkan data baru dari tabel berorientasi baris ke indeks penyimpanan kolom. Setelah Anda membuat indeks penyimpanan kolom untuk sebuah tabel menggunakan pernyataan CREATE INDEX, data baru ditulis ke indeks penyimpanan kolom secara real time. Tidak diperlukan operasi tambahan untuk proses ini.
Untuk memenuhi kebutuhan data real-time dari skenario bisnis tertentu, Anda dapat menyesuaikan parameter polar_csi.update_interval dan polar_csi.update_batch_count untuk meningkatkan kinerja real-time indeks penyimpanan kolom.
Gunakan indeks penyimpanan kolom
1. Konfigurasikan parameter
Setelah membuat indeks penyimpanan kolom, Anda dapat menggunakan parameter berikut untuk mengontrol apakah kueri SQL menggunakan indeks tersebut.
Parameter | Deskripsi |
| Menentukan apakah kueri diizinkan menggunakan indeks penyimpanan kolom. Nilai yang valid adalah:
|
| Ambang batas biaya kueri untuk menggunakan indeks penyimpanan kolom. Jika biaya kueri kurang dari ambang batas saat ini, engine penyimpanan baris digunakan. Jika tidak, engine penyimpanan kolom digunakan. Catatan Jika Anda mengatur parameter ini ke 0, semua kueri akan memprioritaskan penggunaan indeks penyimpanan kolom. Kami tidak merekomendasikan mengatur nilai ke 0 di lingkungan produksi. Sesuaikan nilai ini secara dinamis berdasarkan beban engine penyimpanan baris dan kolom. |
2. Konfigurasikan cakupan parameter
Cakupan global
Anda dapat menggunakan konsol untuk mengatur parameter polar_csi.enable_query ke on. Hal ini memungkinkan semua pernyataan pencarian di semua database mencoba menggunakan indeks penyimpanan kolom.
Tingkat sesi
Semua kueri dalam sesi tersebut mencoba menggunakan indeks penyimpanan kolom.
SET polar_csi.enable_query = ON;Tingkat pernyataan SQL
Satu kueri SQL dalam sesi dapat menggunakan indeks penyimpanan kolom menggunakan petunjuk.
Fitur ini bergantung pada ekstensi pg_hint_plan. Anda harus menjalankan CREATE EXTENSION pg_hint_plan; di database target untuk menginstal ekstensi ini.
Hanya atur
polar_csi.enable_query.-- Izinkan kueri menggunakan indeks penyimpanan kolom /*+ SET (polar_csi.enable_query on) */ SELECT COUNT(*) FROM sales;Atur
polar_csi.enable_querydanpolar_csi.cost_threshold.-- Izinkan kueri menggunakan indeks penyimpanan kolom dan atur ambang batas biaya kueri ke 0 /*+SET (polar_csi.enable_query on) SET(polar_csi.cost_threshold 0)*/ SELECT COUNT(*) FROM sales;
3. Periksa apakah pernyataan SQL menggunakan indeks penyimpanan kolom
Anda dapat menggunakan alat seperti EXPLAIN atau EXPLAIN ANALYZE untuk mencetak rencana kueri. Jika rencana tersebut berisi kata kunci CSI Executor, kueri tersebut menggunakan indeks penyimpanan kolom.
EXPLAIN SELECT COUNT(*) FROM sales;Hasil berikut dikembalikan:
QUERY PLAN
--------------
CSI Executor
(1 row)4. (Opsional) Konfigurasikan tingkat konsistensi kueri untuk indeks penyimpanan kolom
Indeks penyimpanan kolom di PolarDB for PostgreSQL mendukung dua tingkat konsistensi kueri untuk memenuhi kebutuhan bisnis yang berbeda.
Konsistensi akhir (default): Cocok untuk kueri dengan beban tulis tinggi tetapi kebutuhan data real-time rendah.
Konsistensi kuat: Mengembalikan hasil kueri hanya setelah data di penyimpanan kolom sepenuhnya konsisten dengan data di penyimpanan baris.
Kinerja real-time indeks penyimpanan kolom di PolarDB for PostgreSQL dapat mencapai tingkat sub-detik. Namun, ketika beban tulis tinggi, latensi data untuk indeks penyimpanan kolom meningkat. Tingkat konsistensi default adalah konsistensi akhir. Oleh karena itu, jika beban tulis tinggi dan kueri SQL menggunakan indeks penyimpanan kolom, kueri tersebut mungkin tidak mengambil data terbaru.
Untuk skenario yang memerlukan konsistensi kuat antara penyimpanan baris dan penyimpanan kolom, Anda dapat mengatur parameter polar_csi.forward_replay_wait ke pembacaan konsistensi kuat (on). Saat kueri dijalankan, sistem hanya mengeksekusi kueri setelah data di indeks penyimpanan kolom konsisten dengan data di penyimpanan baris.
Hapus dan bangun ulang indeks penyimpanan kolom
Karena kluster PolarDB for PostgreSQL tidak mendukung modifikasi indeks penyimpanan kolom, Anda harus menghapus dan membuat ulang indeks atau membangun ulang indeks untuk menambahkan kolom lain ke indeks penyimpanan kolom yang sudah ada.
Hapus indeks penyimpanan kolom
Sintaks
DROP INDEX csi_index_name;Parameter
Parameter | Deskripsi |
csi_index_name | Nama indeks penyimpanan kolom yang akan dihapus. |
Contoh
Hapus indeks penyimpanan kolom yang sebelumnya dibuat.
DROP INDEX idx_csi_sales; Bangun ulang indeks penyimpanan kolom
Sintaks
REINDEX INDEX csi_index_name;Parameter
Parameter | Deskripsi |
csi_index_name | Nama indeks penyimpanan kolom yang akan dibangun ulang. |
Contoh
Bangun ulang indeks penyimpanan kolom yang sebelumnya dibuat.
REINDEX INDEX idx_csi_sales; Sesuaikan parameter indeks penyimpanan kolom untuk meningkatkan kinerja
Tingkatkan kecepatan pembuatan indeks
Kecepatan pembuatan indeks terkait dengan beberapa parameter, seperti yang ditunjukkan dalam tabel berikut:
Nama parameter | Rentang nilai | Nilai default | Deskripsi |
polar_csi.memory_limit | 1 hingga 1048576 | 1024 | Jumlah memori yang dapat digunakan oleh indeks penyimpanan kolom, dalam MB. Ambang batas memori yang lebih besar menghasilkan pembuatan indeks yang lebih cepat. Sesuaikan parameter ini berdasarkan spesifikasi kluster Anda. Kami menyarankan agar Anda tidak mengatur nilai ini lebih dari 20% dari memori kluster untuk menghindari risiko kesalahan kehabisan memori (OOM). |
polar_csi.flush_count | 2048 hingga 20480000 | 204800 | Jumlah baris dalam komitmen batch. Menambah jumlah baris dapat meningkatkan efisiensi pembuatan tetapi juga memerlukan lebih banyak memori. |
Tingkatkan kinerja real-time indeks penyimpanan kolom
PolarDB menambahkan parameter berikut dalam versi tertentu. Dengan menyesuaikan parameter-parameter ini, Anda dapat meningkatkan kecepatan transformasi baris-ke-kolom dan meningkatkan kinerja real-time data indeks penyimpanan kolom.
PostgreSQL 14: versi mesin minor 2.0.14.13.28.0 atau lebih baru.
PostgreSQL 16: Tidak ada parameter terkait.
Nama parameter | Rentang nilai | Nilai default | Fungsi |
polar_csi.update_interval | 0 hingga 3600 | 3 | Interval untuk pembaruan berkala, dalam detik. Menambah interval pembaruan dapat menggabungkan transaksi kecil dengan jenis yang sama, yang meningkatkan efisiensi pembaruan data ketika terdapat banyak transaksi. |
polar_csi.update_batch_count | 1024 hingga 4294967295 | 100000 | Ambang batas jumlah baris dalam pembaruan batch. Ini adalah jumlah maksimum baris dalam transaksi pembaruan. Menambah ambang batas ini dapat meningkatkan efisiensi pembaruan data. |
Tingkatkan kecepatan kueri
Kinerja kueri terutama terkait dengan parameter berikut. Anda dapat menyesuaikan nilainya untuk meningkatkan kinerja kueri:
Nama parameter | Rentang nilai | Nilai default | Fungsi |
polar_csi.exec_parallel | 1 hingga 512 | 2 | Tingkat paralelisme untuk satu kueri yang dieksekusi oleh indeks penyimpanan kolom. Secara umum, tingkat paralelisme yang lebih tinggi menghasilkan kinerja yang lebih baik. Hal ini terkait dengan spesifikasi sumber daya kluster. Sesuaikan parameter ini berdasarkan spesifikasi kluster Anda. Kami menyarankan agar Anda tidak mengatur nilai ini lebih tinggi dari jumlah core CPU node komputasi. |
polar_csi.memory_limit | 1 hingga 1048576 | 1024 | Jumlah memori yang dapat digunakan oleh indeks penyimpanan kolom, dalam MB. Sesuaikan parameter ini berdasarkan spesifikasi kluster Anda. Kami menyarankan agar Anda tidak mengatur nilai ini lebih dari 20% dari memori kluster untuk menghindari risiko kesalahan OOM. |
polar_csi.cost_threshold | 1 hingga 1000000000 | 50000 | Ambang batas biaya kueri untuk menggunakan indeks penyimpanan kolom. Jika biaya kueri kurang dari ambang batas saat ini, engine penyimpanan baris digunakan. Jika tidak, engine penyimpanan kolom digunakan. Jika parameter ini diatur ke nilai besar, pernyataan SQL yang memakan waktu lama mungkin tidak menggunakan indeks penyimpanan kolom, sehingga menghasilkan kueri yang lebih lambat. Jika parameter ini diatur ke nilai kecil, pernyataan SQL sederhana mungkin menggunakan indeks penyimpanan kolom, mengurangi konkurensi sistem. Sesuaikan parameter ini berdasarkan beban bisnis aktual Anda. Catatan
|
polar_csi.forward_replay_wait | on|off | nonaktif | Tingkat konsistensi kueri IMCI. Nilai yang valid adalah sebagai berikut:
|