Mendistribusikan dan memodifikasi tabel di Azure Cosmos DB for PostgreSQL

  • Artikel

BERLAKU UNTUK: Azure Cosmos DB for PostgreSQL (didukung oleh ekstensi database Citus ke PostgreSQL)

Distribusikan tabel

Untuk membuat tabel terdistribusi, Anda harus menentukan skema tabel terlebih dahulu. Untuk melakukannya, Anda dapat menentukan tabel menggunakan pernyataan CREATE TABLE dengan cara yang sama seperti yang akan Anda lakukan dengan tabel PostgreSQL biasa.

CREATE TABLE github_events
(
    event_id bigint,
    event_type text,
    event_public boolean,
    repo_id bigint,
    payload jsonb,
    repo jsonb,
    actor jsonb,
    org jsonb,
    created_at timestamp
);

Selanjutnya, Anda dapat menggunakan fungsi create_distributed_table() untuk menentukan kolom distribusi tabel dan membuat shard pekerja.

SELECT create_distributed_table('github_events', 'repo_id');

Panggilan fungsi menginformasikan Azure Cosmos DB for PostgreSQL bahwa tabel github_events harus didistribusikan pada kolom repo_id (dengan hashing nilai kolom).

Membuat total 32 shard secara default, yang setiap shard-nya memiliki sebagian dari ruang hash dan direplikasi berdasarkan nilai konfigurasi citus.shard_replication_factor default. Replika shard yang dibuat pada pekerja memiliki skema tabel, indeks, dan definisi batasan yang sama dengan tabel pada koordinator. Setelah replika dibuat, fungsi ini menyimpan semua metadata terdistribusi pada koordinator.

Setiap shard yang dibuat diberi ID shard unik dan semua replikanya memiliki ID shard yang sama. Shard direpresentasikan pada node pekerja sebagai tabel PostgreSQL reguler bernama 'tablename_shardid' yang nama tabelnya adalah nama tabel terdistribusi dan ID shard adalah ID unik yang ditetapkan. Anda dapat menyambungkan ke instans postgres pekerja untuk melihat atau menjalankan perintah pada pecahan individual.

Sekarang Anda siap untuk menyisipkan data ke dalam tabel terdistribusi dan menjalankan kueri di atasnya. Anda juga dapat mempelajari selengkapnya tentang UDF yang digunakan di bagian ini dalam referensi tabel dan shardDDL.

Tabel Referensi

Metode di atas mendistribusikan tabel ke dalam beberapa pecahan horizontal. Kemungkinan lain adalah mendistribusikan tabel ke dalam satu pecahan dan mereplikasi pecahan ke setiap simpul pekerja. Tabel yang didistribusikan demikian disebut tabel referensi. Tabel tersebut digunakan untuk menyimpan data yang akan sering diakses oleh banyak node dalam sebuah kluster.

Kandidat umum untuk tabel referensi meliputi:

  • Tabel yang lebih kecil yang perlu bergabung dengan tabel terdistribusi yang lebih besar.
  • Tabel di aplikasi multi-penyewa yang tidak memiliki kolom ID penyewa atau yang tidak terkait dengan penyewa. (Atau, selama migrasi, bahkan untuk beberapa tabel yang terkait dengan penyewa.)
  • Tabel yang membutuhkan batasan unik di beberapa kolom dan cukup kecil.

Misalnya, situs eCommerce multi-penyewa perlu menghitung pajak penjualan untuk transaksi di salah satu tokonya. Informasi pajak tidak spesifik untuk penyewa mana pun. Hal yang masuk akal untuk memasukkannya ke dalam table bersama. Tabel referensi yang berpusat di AS mungkin terlihat seperti ini:

-- a reference table

CREATE TABLE states (
  code char(2) PRIMARY KEY,
  full_name text NOT NULL,
  general_sales_tax numeric(4,3)
);

-- distribute it to all workers

SELECT create_reference_table('states');

Sekarang pertanyaan seperti seseorang menghitung pajak untuk keranjang belanja dapat bergabung di states tabel tanpa overhead jaringan, dan dapat menambahkan kunci asing ke kode negara untuk validasi yang lebih baik.

Selain mendistribusikan tabel sebagai pecahan yang direplikasi tunggal, create_reference_table UDF menandainya sebagai tabel referensi dalam tabel metadata Azure Cosmos DB for PostgreSQL. Azure Cosmos DB for PostgreSQL secara otomatis melakukan penerapan dua fase (2PC) untuk modifikasi pada tabel yang ditandai dengan cara ini, yang memberikan jaminan konsistensi yang kuat.

Untuk contoh lain menggunakan tabel referensi, lihat tutorial database multi-penyewa.

Mendistribusikan Data Koordinator

Jika database PostgreSQL yang ada dikonversi menjadi simpul koordinator untuk kluster, data dalam tabelnya dapat didistribusikan secara efisien dan dengan gangguan minimal pada aplikasi.

create_distributed_tableFungsi yang dijelaskan sebelumnya berfungsi pada tabel kosong dan tidak kosong, dan untuk yang terakhir secara otomatis mendistribusikan baris tabel di seluruh kluster. Anda akan tahu apakah fungsi tersebut menyalin data dengan adanya pesan, "PEMBERITAHUAN: Menyalin data dari tabel lokal..." Misalnya:

CREATE TABLE series AS SELECT i FROM generate_series(1,1000000) i;
SELECT create_distributed_table('series', 'i');
NOTICE:  Copying data from local table...
 create_distributed_table
 --------------------------

 (1 row)

Tulisan di atas tabel diblokir saat data dimigrasikan, dan tulisan yang tertunda ditangani sebagai kueri terdistribusi setelah fungsi melakukan. (Jika fungsi gagal maka kueri menjadi lokal lagi.) Bacaan dapat berlanjut seperti biasa dan akan menjadi kueri terdistribusi setelah fungsi berkomitmen.

Saat mendistribusikan tabel A dan B, di mana A memiliki kunci asing untuk B, distribusikan tabel tujuan utama B terlebih dahulu. Melakukannya dalam urutan yang salah akan menyebabkan kesalahan:

ERROR:  cannot create foreign key constraint
DETAIL:  Referenced table must be a distributed table or a reference table.

Jika tidak memungkinkan untuk mendistribusikan dalam urutan yang benar, maka hilangkan kunci asing, distribusikan tabel, dan buat ulang kunci asing.

Saat memigrasikan data dari database eksternal, seperti dari Amazon RDS ke Azure Cosmos DB for PostgreSQL, pertama-tama buat tabel terdistribusi Azure Cosmos DB for PostgreSQL melalui create_distributed_table, lalu salin data ke dalam tabel. Menyalin ke tabel terdistribusi menghindari kehabisan ruang pada node koordinator.

Mengkolokasi Tabel

Kolokasi berarti menempatkan informasi terkait pada mesin yang sama. Ini memungkinkan kueri yang efisien, sambil memanfaatkan skalabilitas horizontal untuk seluruh himpunan data. Untuk informasi selengkapnya, lihat kolokasi.

Tabel dikolokasi dalam grup. Untuk mengontrol penetapan grup kolokasi tabel secara manual, gunakan colocate_with parameter create_distributed_table opsional. Jika Anda tidak peduli dengan kolokasi tabel, hilangkan parameter ini. Dengan nilai default 'default', yang mengkelompokkan tabel dengan tabel kolokasi default lainnya yang memiliki tipe kolom distribusi, jumlah shard, dan faktor replikasi yang sama. Jika Anda ingin memecahkan atau memperbarui kolokasi implisit ini, Anda dapat menggunakan update_distributed_table_colocation().

-- these tables are implicitly co-located by using the same
-- distribution column type and shard count with the default
-- co-location group

SELECT create_distributed_table('A', 'some_int_col');
SELECT create_distributed_table('B', 'other_int_col');

Ketika tabel baru tidak terkait dengan orang lain dalam kelompok kolokasi implisitnya, tentukan colocated_with => 'none'.

-- not co-located with other tables

SELECT create_distributed_table('A', 'foo', colocate_with => 'none');

Memisahkan tabel yang tidak terkait ke dalam kelompok kolokasi mereka sendiri akan meningkatkan performa penyesuaian pecahan, karena pecahan dalam kelompok yang sama harus dipindahkan bersama-sama.

Ketika tabel memang terkait (misalnya kapan mereka akan bergabung), masuk akal untuk secara meng-kolokasi tabel tersebut secara kolokasi. Keuntungan dari kolokasi yang tepat lebih penting daripada overhead penyeimbangan ulang.

Untuk secara eksplisit menempatkan beberapa tabel, distribusikan satu dan kemudian masukkan yang lain ke dalam kelompok kolokasinya. Contohnya:

-- distribute stores
SELECT create_distributed_table('stores', 'store_id');

-- add to the same group as stores
SELECT create_distributed_table('orders', 'store_id', colocate_with => 'stores');
SELECT create_distributed_table('products', 'store_id', colocate_with => 'stores');

Informasi tentang grup kolokasi disimpan di tabel pg_dist_colocation, sementara pg_dist_partition mengungkapkan tabel mana yang ditetapkan untuk grup mana.

Menghilangkan tabel

Anda dapat menggunakan perintah PostgreSQL DROP TABLE standar untuk menghapus tabel terdistribusi Anda. Seperti tabel reguler, DROP TABLE menghapus indeks, aturan, pemicu, dan batasan apa pun yang ada untuk tabel target. Selain itu, ia juga menghilangkan pecahan pada simpul pekerja dan membersihkan metadata mereka.

DROP TABLE github_events;

Memodifikasi tabel

Azure Cosmos DB for PostgreSQL secara otomatis menyebarluaskan banyak jenis pernyataan DDL. Memodifikasi tabel terdistribusi pada simpul koordinator akan memperbarui pecahan pada pekerja juga. Pernyataan DDL lainnya memerlukan perambatan manual, dan beberapa lainnya dilarang seperti yang akan memodifikasi kolom distribusi. Mencoba menjalankan DDL yang tidak memenuhi syarat untuk perambatan otomatis akan menimbulkan kesalahan dan meninggalkan tabel pada simpul koordinator tidak berubah.

Berikut adalah referensi kategori pernyataan DDL yang merambat.

Menambahkan/Memodifikasi Kolom

Azure Cosmos DB for PostgreSQL menyebarkan sebagian besar perintah ALTER TABLE secara otomatis. Menambahkan kolom atau mengubah nilai defaultnya berfungsi seperti yang terjadi dalam database PostgreSQL satu mesin:

-- Adding a column

ALTER TABLE products ADD COLUMN description text;

-- Changing default value

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Perubahan signifikan pada kolom yang sudah ada seperti mengganti namanya atau mengubah tipe datanya juga baik-baik saja. Namun jenis data kolom distribusi tidak dapat diubah. Kolom ini menentukan bagaimana data tabel didistribusikan melalui kluster, dan memodifikasi jenis datanya akan memerlukan pemindahan data.

Mencoba melakukannya dapat menyebabkan kesalahan:

-- assumining store_id is the distribution column
-- for products, and that it has type integer

ALTER TABLE products
ALTER COLUMN store_id TYPE text;

/*
ERROR:  XX000: cannot execute ALTER TABLE command involving partition column
LOCATION:  ErrorIfUnsupportedAlterTableStmt, multi_utility.c:2150
*/

Menambahkan/Menghapus Batasan

Menggunakan Azure Cosmos DB for PostgreSQL memungkinkan Anda untuk terus menikmati keamanan database relasional, termasuk batasan database (lihat dokumen PostgreSQL). Karena sifat sistem terdistribusi, Azure Cosmos DB for PostgreSQL tidak akan merujuk silang batasan keunikan atau integritas referensial antara simpul pekerja.

Untuk menyiapkan kunci asing di antara tabel terdistribusi yang dikolokasi, selalu sertakan kolom distribusi di kunci. Termasuk kolom distribusi mungkin melibatkan pembuatan senyawa kunci.

Kunci asing dapat dibuat dalam situasi ini:

  • antara dua tabel lokal (tidak didistribusikan),
  • antara dua tabel referensi,
  • antara dua tabel terdistribusi yang terkolokasi saat kunci menyertakan kolom distribusi, atau
  • sebagai tabel terdistribusi yang merujuk pada tabel referensi

Kunci asing dari tabel referensi ke tabel terdistribusi tidak didukung.

Catatan

Kunci utama dan batasan keunikan harus menyertakan kolom distribusi. Menambahkannya ke kolom non-distribusi akan menghasilkan kesalahan

Contoh ini memperlihatkan cara membuat kunci utama dan asing pada tabel terdistribusi:

--
-- Adding a primary key
-- --------------------

-- We'll distribute these tables on the account_id. The ads and clicks
-- tables must use compound keys that include account_id.

ALTER TABLE accounts ADD PRIMARY KEY (id);
ALTER TABLE ads ADD PRIMARY KEY (account_id, id);
ALTER TABLE clicks ADD PRIMARY KEY (account_id, id);

-- Next distribute the tables

SELECT create_distributed_table('accounts', 'id');
SELECT create_distributed_table('ads',      'account_id');
SELECT create_distributed_table('clicks',   'account_id');

--
-- Adding foreign keys
-- -------------------

-- Note that this can happen before or after distribution, as long as
-- there exists a uniqueness constraint on the target column(s) which
-- can only be enforced before distribution.

ALTER TABLE ads ADD CONSTRAINT ads_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id);
ALTER TABLE clicks ADD CONSTRAINT clicks_ad_fk
  FOREIGN KEY (account_id, ad_id) REFERENCES ads (account_id, id);

Demikian pula, sertakan kolom distribusi dalam batasan keunikan:

-- Suppose we want every ad to use a unique image. Notice we can
-- enforce it only per account when we distribute by account id.

ALTER TABLE ads ADD CONSTRAINT ads_unique_image
  UNIQUE (account_id, image_url);

Batasan not-null dapat diterapkan ke kolom apa pun (distribusi atau tidak) karena tidak memerlukan pencarian di antara pekerja.

ALTER TABLE ads ALTER COLUMN image_url SET NOT NULL;

Menggunakan Batasan NOT VALID

Dalam beberapa situasi, Anda dapat memberlakukan batasan untuk baris baru, sekaligus memungkinkan baris yang tidak sesuai untuk tetap tidak berubah. Azure Cosmos DB for PostgreSQL mendukung fitur ini untuk batasan CHECK dan kunci asing, menggunakan penunjukan batasan "TIDAK VALID" PostgreSQL.

Sebagai contoh, pertimbangkan aplikasi yang menyimpan profil pengguna dalamtabel referensi.

-- we're using the "text" column type here, but a real application
-- might use "citext" which is available in a postgres contrib module

CREATE TABLE users ( email text PRIMARY KEY );
SELECT create_reference_table('users');

Dalam perjalanan waktu bayangkan bahwa beberapa non-alamat masuk ke tabel.

INSERT INTO users VALUES
   ('foo@example.com'), ('hacker12@aol.com'), ('lol');

Kita ingin memvalidasi alamat, tetapi PostgreSQL biasanya tidak memungkinkan kita untuk menambahkan batasan CHECK yang gagal untuk baris yang ada. Namun, ia memungkinkan batasan yang ditandai tidak valid:

ALTER TABLE users
ADD CONSTRAINT syntactic_email
CHECK (email ~
   '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
) NOT VALID;

Baris baru sekarang dilindungi.

INSERT INTO users VALUES ('fake');

/*
ERROR:  new row for relation "users_102010" violates
        check constraint "syntactic_email_102010"
DETAIL:  Failing row contains (fake).
*/

Kemudian, di luar jam sibuk, administrator database dapat mencoba memperbaiki baris yang buruk dan me-revalidasi batasan.

-- later, attempt to validate all rows
ALTER TABLE users
VALIDATE CONSTRAINT syntactic_email;

Dokumentasi PostgreSQL memiliki informasi lebih lanjut tentang NOT VALID dan VALIDATE CONSTRAINT di bagian ALTER TABLE.

Menambahkan/Menghapus Indeks

Azure Cosmos DB for PostgreSQL mendukung penambahan dan penghapusan indeks:

-- Adding an index

CREATE INDEX clicked_at_idx ON clicks USING BRIN (clicked_at);

-- Removing an index

DROP INDEX clicked_at_idx;

Menambahkan indeks membutuhkan penguncian tulis, yang tidak diharapkan dalam "system-of-record" multi-penyewa. Sebagai gantinya, untuk meminimalkan waktu henti aplikasi, buat indeks secara bersamaan. Metode ini membutuhkan lebih banyak total pekerjaan dibanding build indeks standar dan membutuhkan waktu lebih lama untuk diselesaikan. Namun, karena metode ini memungkinkan operasi normal berlanjut saat indeks dibangun, metode ini berguna untuk menambahkan indeks baru di lingkungan produksi.

-- Adding an index without locking table writes

CREATE INDEX CONCURRENTLY clicked_at_idx ON clicks USING BRIN (clicked_at);

Jenis dan Fungsi

Membuat jenis SQL ubah suaian dan fungsi yang ditentukan pengguna disebarkan ke simpul pekerja. Namun, membuat objek database tersebut dalam transaksi dengan operasi terdistribusi melibatkan tradeoff.

Azure Cosmos DB for PostgreSQL memparalelkan operasi seperti create_distributed_table() di seluruh pecahan menggunakan beberapa koneksi per pekerja. Sedangkan, saat membuat objek database, Azure Cosmos DB for PostgreSQL menyebarkannya ke simpul pekerja menggunakan satu koneksi per pekerja. Menggabungkan kedua operasi dalam satu transaksi dapat menyebabkan masalah, karena koneksi paralel tidak akan dapat melihat objek yang dibuat melalui satu koneksi tetapi belum diterapkan.

Pertimbangkan blok transaksi yang membuat jenis, tabel, memuat data, dan mendistribusikan tabel:

BEGIN;

-- type creation over a single connection:
CREATE TYPE coordinates AS (x int, y int);
CREATE TABLE positions (object_id text primary key, position coordinates);

-- data loading thus goes over a single connection:
SELECT create_distributed_table(‘positions’, ‘object_id’);

SET client_encoding TO 'UTF8';
\COPY positions FROM ‘positions.csv’

COMMIT;

Sebelum Citus 11.0, Citus akan menangguhkan pembuatan jenis pada simpul pekerja, dan menerapkannya secara terpisah saat membuat tabel terdistribusi. Ini memungkinkan penyalinan create_distributed_table() data terjadi secara paralel. Namun, itu juga berarti bahwa jenis tidak selalu ada pada simpul pekerja Citus - atau jika transaksi digulung balik, jenisnya akan tetap berada di simpul pekerja.

Dengan Citus 11.0, perilaku default berubah untuk memprioritaskan konsistensi skema antara simpul koordinator dan pekerja. Perilaku baru memiliki kelemahan: jika propagasi objek terjadi setelah perintah paralel dalam transaksi yang sama, maka transaksi tidak dapat lagi diselesaikan, seperti yang disorot oleh ERROR di blok kode di bawah ini:

BEGIN;
CREATE TABLE items (key text, value text);
-- parallel data loading:
SELECT create_distributed_table(‘items’, ‘key’);
SET client_encoding TO 'UTF8';
\COPY items FROM ‘items.csv’
CREATE TYPE coordinates AS (x int, y int);

ERROR:  cannot run type command because there was a parallel operation on a distributed table in the transaction

Jika Anda mengalami masalah ini, ada dua solusi sederhana:

  1. Gunakan setcitus.create_object_propagation ke automatic untuk menunda pembuatan jenis dalam situasi ini, dalam hal ini mungkin ada beberapa inkonsistensi antara objek database mana yang ada pada simpul yang berbeda.
  2. Gunakan setcitus.multi_shard_modify_mode ke sequential untuk menonaktifkan paralelisme per simpul. Beban data dalam transaksi yang sama mungkin lebih lambat.

Langkah berikutnya

Kueri diagnostik yang berguna