Öğretici: Elastik kümelerle çok kiracılı bir veritabanı tasarlama

Bu öğreticide, yatay ölçeği genişletmeden yararlanan çok kiracılı bir uygulama tasarlamayı öğrenmek için PostgreSQL için Azure Veritabanı'nı elastik kümelerle birlikte kullanacaksınız.

• Önkoşullar
• Şema oluşturmak için psql yardımcı programını kullanma
• Düğümler arasında parça tabloları
• Örnek verileri ekleme
• Kiracı verilerini sorgulama
• Kiracılar arasında veri paylaşma
• Kiracı başına şemayı özelleştirme

Önkoşullar

Aşağıdaki yollardan biriyle elastik küme oluşturun:

• Portalı kullanarak elastik küme oluşturma
• Bicep kullanarak elastik küme oluşturma
• ARM şablonuyla elastik küme oluşturma

Şema oluşturmak için psql yardımcı programını kullanma

Psql kullanarak elastik kümeye bağlandıktan sonra elastik kümenizi yapılandırabilirsiniz. Bu öğretici, şirketlerin reklam kampanyalarını izlemesine olanak tanıyan bir uygulama platformu oluşturma işleminde size yol gösterir.

Not

Verileri kümenize dağıtırken, tüm benzersiz veri kısıtlamalarının kapsamı kendi dağıtım "parçası" olarak belirlenmiştir. Çok kiracılı örneğimizde, uygulama verilerinin benzersizliği kiracı başına (örneğin, şirket kimliği) sağlanır. Bu nedenle, birincil ve yabancı anahtar kısıtlamalarına yönelik dağıtılmış tablo tanımlarımız her zaman şirket kimliği sütununu içerir.

Çok kiracılı şirket bilgilerinizi barındıracak bir tablo ve kampanyaları için başka bir tablo oluşturun. psql konsolunda şu komutları çalıştırın:

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)
);

Her kampanya reklam yayınlamak için ödeme yapılır. Psql'de aşağıdaki kodla reklam tablonuzu ekleyin:

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)
);

Son olarak, reklam seçimlerine ve gösterimlerine göre istatistikleri izlemek istiyorsunuz:

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)
);

Şimdi aşağıdakini çalıştırarak psql'den yeni oluşturulan tabloları görebilirsiniz:

\dt

Düğümler arasında parça tabloları

Bu noktaya kadar standart Postgres tabloları oluşturdunuz, ancak nihai olarak elastik kümeniz genelinde dağıtılmış tablolar oluşturmanız gerekir. Elastik küme içindeki dağıtılmış tablolar, dağıtım sütununuz tarafından tanımlanan değerlere göre verileri farklı düğümlerde depolar. Bu sütun, altta yatan çalışan düğümler arasında satır yerleşimini belirlemek için kullanılır.

Dağıtım sütununuzu, çoklu kiracı tanımlayıcısı olarak işlev gören company_id olacak şekilde ayarlayın. psql'de şu işlevleri çalıştırın:

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');

Not

PostgreSQL için Azure Veritabanı performans özellikleriyle elastik kümelerden yararlanmak için tabloları dağıtmanız gerekir. Tablolarınızı ve şemalarınızı dağıtmadığınız sürece, küme düğümleriniz dağıtılmış sorgulara veya işlemlere katılmaz.

Örnek verileri ekleme

psql dışında, normal komut satırında örnek veri kümelerini indirin:

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

psql'nin içine dönüp verileri toplu olarak yükleyin. psql'yi veri dosyalarını indirdiğiniz dizinde çalıştırdığınızdan emin olun.

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

Dağıtılmış tablolarınızdaki verileriniz artık elastik küme çalışan düğümlerinize yayılır.

Kiracı verilerini sorgulama

Uygulamanız belirli bir şirket için veri istediğinde, veritabanı artık sorguyu uygun çalışan düğümünde verimli bir şekilde yürütebilir. Örneğin, aşağıdaki sorgu (company_id = 5) reklamları ve gösterimleri filtreler. Sonuçları görmek için psql'de çalıştırmayı deneyin.

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;

Kiracılar arasında veri paylaşma

Şimdiye kadar, tüm tablolarınızı kümeniz genelinde company_id ile dağıttınız. Ancak, bazı veri türleri doğal olarak tüm kiracılara "aittir" ve tüm kiracı dağıtımlarının yanına yerleştirilebilir. Örneğin, reklam platformunuzda yer alan tüm şirketler IP adresi ayrıntılarına göre hedef kitleleri için coğrafi bilgi almak isteyebilir.

Bu coğrafi IP bilgilerini tutmak için bir başvuru tablosu oluşturun. psql'de aşağıdaki komutları çalıştırın:

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);

Ardından geo_ips öğesini "başvuru tablosu" olarak tanımlayın. Kümeniz bu tabloyu her kümelenmiş çalışan düğümünde senkronize edilmiş bir tablo depolayarak yönetir.

SELECT create_reference_table('geo_ips');

Şimdi başvuru tablonuzu örnek verilerinizle yükleyin. Bu komutu veri kümesi dosyasını indirdiğiniz dizinden çalıştırmayı unutmayın.

\copy geo_ips from 'geo_ips.csv' with csv

Seçili tabloyu geo_ips ile birleştiren SQL deyimleri artık tüm düğümlerde etkilidir. Reklam 290'da seçilen her IP'nin konumunu bulmak için bu bağlantıya dikkat çekin. Sorguyu psql'de çalıştırmayı deneyin:

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;

Şemayı kiracı başına özelleştirme

Bazı durumlarda, tek tek kiracılarınızın diğer kiracıların ihtiyaç duymadığı özel bilgileri depolaması gerekebilir. Ancak tüm kiracılar ortak bir tanımı aynı veritabanı şemasıyla paylaşır. Ek verileri nereye koyabilirsiniz?

Bir çözüm, PostgreSQL'in JSONB'sı gibi esnek bir sütun türü kullanmaktır. Şemamızda adlı clicksbir JSONB alanı user_data var. Bir şirket (örneğin, 5. şirket), bir kullanıcının mobil cihazda olup olmadığı hakkındaki bilgileri izlemek için bu sütunu kullanabilir.

İşte 5. şirketin daha fazlasını seçen kişileri bulmak için kullanabileceği bir sorgu: mobil veya geleneksel ziyaretçiler.

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, verilerinizin belirli bir bölümünü veya alt kümesini dizine almanızı sağlayan güçlü bir özellik içerir. Kısmi dizin oluşturarak 5. şirket için sorgunuzu daha da iyileştirebilirsiniz.

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

Ayrıca, JSONB sütununuzu içeren sorgular arasında performansı artırmanın bir diğer yolu da JSONB sütununuzun içindeki temel alınan her anahtar ve değer üzerinde bir GIN dizini oluşturmaktır.

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;

Sonraki adım

Elastik kümeler hakkında daha fazla bilgi edinin