Tutorial: Mendesain database multitenant dengan kluster elastis

Dalam tutorial ini, Anda menggunakan Azure Database for PostgreSQL dengan kluster elastis untuk mempelajari cara merancang aplikasi multipenyewa yang mendapat manfaat dari peluasan skala horizontal.

Prasyarat
Menggunakan utilitas psql untuk membuat skema
Mendistribusikan tabel di seluruh simpul
Menyerap data sampel
Menjalankan kueri data penyewa
Berbagi data di antara penyewa
Menyesuaikan skema per penyewa

Prasyarat

Buat kluster elastis dengan salah satu cara berikut:

Menggunakan utilitas psql untuk membuat skema

Setelah terhubung ke kluster elastis dengan menggunakan psql, Anda dapat mengonfigurasi kluster elastis Anda. Tutorial ini memandu Anda dalam membuat platform aplikasi yang memungkinkan perusahaan melacak kampanye iklan mereka.

Catatan

Saat mendistribusikan data di seluruh kluster Anda, batasan data unik apa pun tercakup dalam distribusinya "shard". Dalam contoh multitenant kami, keunikan data aplikasi diberlakukan untuk setiap penyewa (misalnya, ID perusahaan). Untuk alasan ini, definisi tabel terdistribusi kami untuk batasan kunci primer dan asing selalu menyertakan kolom ID perusahaan.

Buat tabel untuk menyimpan informasi perusahaan multipenyewa Anda, dan tabel lain untuk kampanye mereka. Di konsol psql, jalankan perintah-perintah ini:

CREATE TABLE companies (
  id bigserial PRIMARY KEY,
  name text NOT NULL,
  image_url text,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL
);

CREATE TABLE campaigns (
  id bigserial,
  company_id bigint REFERENCES companies (id),
  name text NOT NULL,
  cost_model text NOT NULL,
  state text NOT NULL,
  monthly_budget bigint,
  blocked_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id)
);

Setiap kampanye membayar untuk menjalankan iklan. Tambahkan tabel iklan Anda di psql dengan kode berikut:

CREATE TABLE ads (
  id bigserial,
  company_id bigint,
  campaign_id bigint,
  name text NOT NULL,
  image_url text,
  target_url text,
  impressions_count bigint DEFAULT 0,
  clicks_count bigint DEFAULT 0,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, campaign_id)
    REFERENCES campaigns (company_id, id)
);

Terakhir, Anda ingin melacak statistik berdasarkan pilihan iklan dan tayangan:

CREATE TABLE clicks (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  clicked_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_click_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

CREATE TABLE impressions (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  seen_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_impression_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

Sekarang Anda dapat melihat tabel yang baru dibuat dari psql dengan menjalankan:

\dt

Mendistribusikan tabel di seluruh simpul

Hingga titik ini, Anda membuat tabel Postgres standar, tetapi pada akhirnya Anda perlu membuat tabel terdistribusi di seluruh kluster elastis Anda. Tabel terdistribusi dalam kluster elastis menyimpan data pada simpul yang berbeda berdasarkan nilai yang ditentukan oleh kolom distribusi Anda. Kolom ini digunakan untuk menentukan penempatan baris di seluruh simpul pekerja yang mendasar.

Siapkan kolom distribusi Anda dengan menggunakan company_id, yang berfungsi sebagai pengidentifikasi tenant ganda Anda. Dalam psql, jalankan fungsi ini:

SELECT create_distributed_table('companies',   'id');
SELECT create_distributed_table('campaigns',   'company_id');
SELECT create_distributed_table('ads',         'company_id');
SELECT create_distributed_table('clicks',      'company_id');
SELECT create_distributed_table('impressions', 'company_id');

Catatan

Untuk memanfaatkan kluster elastis dengan fitur performa Azure Database for PostgreSQL, Anda perlu mendistribusikan tabel. Kecuali Anda mendistribusikan tabel dan skema, node kluster Anda tidak berpartisipasi dalam kueri atau operasi terdistribusi apa pun.

Menyerap data sampel

Di luar psql, di baris perintah normal, unduh himpunan data sampel:

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://raw.githubusercontent.com/Azure-Samples/azure-postgresql-elastic-clusters/main/multi-tenant/${dataset}.csv
done

Kembali ke dalam psql, muat data secara massal. Pastikan untuk menjalankan psql di direktori yang sama tempat Anda mengunduh file data.

SET client_encoding TO 'UTF8';

\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv
\copy clicks from 'clicks.csv' with csv
\copy impressions from 'impressions.csv' with csv

Data Anda dalam tabel terdistribusi Anda sekarang tersebar di simpul pekerja kluster elastis Anda.

Menjalankan kueri data penyewa

Saat aplikasi Anda meminta data untuk perusahaan tertentu, database sekarang dapat menjalankan kueri secara efisien pada simpul pekerja yang sesuai. Misalnya, kueri berikut (company_id = 5) memfilter iklan dan impresi. Cobalah menjalankannya dalam psql untuk melihat hasilnya.

SELECT a.campaign_id,
       RANK() OVER (
         PARTITION BY a.campaign_id
         ORDER BY a.campaign_id, count(*) DESC
       ), count(*) AS n_impressions, a.id
  FROM ads AS a
  JOIN impressions AS i
    ON i.company_id = a.company_id
   AND i.ad_id      = a.id
 WHERE a.company_id = 5
GROUP BY a.campaign_id, a.id
ORDER BY a.campaign_id, n_impressions DESC;

Hingga saat ini, Anda mendistribusikan semua tabel Anda di seluruh kluster Anda dengan company_id. Namun, beberapa jenis data sudah secara alami dimiliki oleh semua penyewa dan dapat ditempatkan bersama semua distribusi penyewa. Misalnya, semua perusahaan di platform iklan Anda mungkin ingin mendapatkan informasi geografis untuk audiens mereka berdasarkan detail alamat IP.

Buat tabel referensi untuk menyimpan informasi IP geografis ini. Jalankan perintah berikut dalam psql:

CREATE TABLE geo_ips (
  addrs cidr NOT NULL PRIMARY KEY,
  latlon point NOT NULL
    CHECK (-90  <= latlon[0] AND latlon[0] <= 90 AND
           -180 <= latlon[1] AND latlon[1] <= 180)
);
CREATE INDEX ON geo_ips USING gist (addrs inet_ops);

Selanjutnya, identifikasi geo_ips sebagai "tabel referensi". Kluster Anda mengelola tabel ini dengan menyimpan tabel yang disinkronkan pada setiap simpul pekerja berkluster.

SELECT create_reference_table('geo_ips');

Sekarang, muat tabel referensi Anda dengan data sampel Anda. Ingatlah untuk menjalankan perintah ini dari direktori tempat Anda mengunduh file himpunan data.

\copy geo_ips from 'geo_ips.csv' with csv

Pernyataan SQL yang menggabungkan tabel yang dipilih dengan geo_ips sekarang efisien di semua node. Perhatikan penggabungan ini untuk menemukan lokasi dari setiap IP yang telah dipilih pada iklan 290. Coba jalankan kueri di psql:

SELECT c.id, clicked_at, latlon
  FROM geo_ips, clicks c
 WHERE addrs >> c.user_ip
   AND c.company_id = 5
   AND c.ad_id = 290;

Menyesuaikan skema per penyewa

Dalam beberapa kasus, penyewa individual Anda mungkin perlu menyimpan informasi khusus yang tidak diperlukan penyewa lain. Namun, semua penyewa berbagi definisi umum dengan skema database yang identik. Di mana Anda dapat meletakkan data tambahan?

Salah satu solusinya adalah menggunakan jenis kolom fleksibel seperti JSONB PostgreSQL. Skema kami memiliki bidang JSONB di clicks yang disebut user_data. Perusahaan (misalnya perusahaan 5) dapat menggunakan kolom ini untuk melacak informasi tentang apakah pengguna berada di perangkat seluler.

Berikut adalah kueri yang dapat digunakan oleh perusahaan untuk menemukan siapa yang lebih memilih: pengunjung seluler atau pengunjung tradisional.

SELECT
  user_data->>'is_mobile' AS is_mobile,
  count(*) AS count
FROM clicks
WHERE company_id = 5
GROUP BY user_data->>'is_mobile'
ORDER BY count DESC;

PostgreSQL menyertakan fitur canggih yang memungkinkan Anda mengindeks bagian atau subset data tertentu. Anda dapat mengoptimalkan kueri untuk perusahaan 5 lebih lanjut dengan membuat indeks parsial.

CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;

Selain itu, cara lain untuk meningkatkan performa di seluruh kueri yang menyertakan kolom JSONB Anda adalah dengan membuat indeks GIN pada setiap kunci dan nilai yang mendasari dalam kolom JSONB Anda.

CREATE INDEX click_user_data
ON clicks USING gin (user_data);

-- this speeds up queries like, "which clicks have the is_mobile key present in user_data?"

SELECT id
  FROM clicks
 WHERE user_data ? 'is_mobile'
   AND company_id = 5;

Langkah selanjutnya

Pelajari selengkapnya tentang kluster elastis

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-11-19