Samouczek: projektowanie wielodostępnej bazy danych z klastrami elastycznymi

W tym samouczku użyjesz usługi Azure Database for PostgreSQL z elastycznymi klastrami, aby dowiedzieć się, jak zaprojektować aplikację multitenant, która korzysta z horyzontalnego skalowania.

Wymagania wstępne
Tworzenie schematu za pomocą narzędzia psql
Tabele fragmentów między węzłami
Pozyskiwanie przykładowych danych
Wykonywanie zapytań dotyczących danych dzierżawy
Udostępnianie danych między dzierżawami
Dostosowywanie schematu dla dzierżawy

Wymagania wstępne

Utwórz klaster elastyczny na jeden z następujących sposobów:

Tworzenie schematu za pomocą narzędzia psql

Po nawiązaniu połączenia z klastrem elastycznym przy użyciu narzędzia psql można skonfigurować klaster elastyczny. Ten samouczek przeprowadzi Cię przez proces tworzenia platformy aplikacji, która umożliwia firmom śledzenie kampanii reklamowych.

Uwaga

Podczas dystrybucji danych w klastrze wszystkie unikatowe ograniczenia danych są związane z ich dystrybucją w ramach "segmentu". W naszym przykładzie wielonajemcy, unikatowość danych aplikacji jest wymuszana dla każdego najemcy (na przykład identyfikator firmy). Z tego powodu nasze rozproszone definicje tabel dla ograniczeń klucza podstawowego i obcego zawsze zawierają kolumnę identyfikatora firmy.

Utwórz tabelę do przechowywania informacji dotyczących firmy wielonajemnej oraz inną tabelę dla ich kampanii. W konsoli programu psql uruchom następujące polecenia:

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

Każda kampania płaci za uruchamianie reklam. Dodaj tabelę reklam w pliku psql przy użyciu następującego kodu:

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

Na koniec chcesz śledzić statystyki na podstawie wybranych reklam i wyświetleń:

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

Nowo utworzone tabele z bazy danych psql można teraz wyświetlić, uruchamiając polecenie:

\dt

Tabele fragmentów między węzłami

Do tego momentu utworzono standardowe tabele Bazy danych Postgres, ale ostatecznie trzeba utworzyć tabele rozproszone w klastrze elastycznym. Tabele rozproszone w elastycznym klastrze przechowują dane na różnych węzłach na podstawie wartości zdefiniowanych przez kolumnę dystrybucji. Ta kolumna służy do określania rozmieszczenia wierszy w przynależnych węzłach roboczych.

Skonfiguruj kolumnę dystrybucji jako company_id, która działa jako identyfikator dla wielu najemców. W narzędziu psql uruchom następujące funkcje:

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

Uwaga

Aby korzystać z klastrów elastycznych za pomocą funkcji wydajności usługi Azure Database for PostgreSQL, należy dystrybuować tabele. Jeśli nie dystrybuujesz tabele i schematy, węzły klastra nie uczestniczą w żadnych rozproszonych zapytaniach ani operacjach.

Pozyskiwanie przykładowych danych

Poza narzędziem psql w normalnym wierszu polecenia pobierz przykładowe zestawy danych:

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

Z powrotem wewnątrz narzędzia psql załaduj zbiorczo dane. Pamiętaj, aby uruchomić narzędzie psql w tym samym katalogu, w którym pobrano pliki danych.

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

Dane w tabelach rozproszonych są teraz rozproszone na węzłach obliczeniowych elastycznego klastra.

Wykonywanie zapytań dotyczących danych dzierżawy

Gdy aplikacja żąda danych dla określonej firmy, baza danych może teraz efektywnie wykonywać zapytanie w odpowiednim węźle roboczym. Na przykład następujące zapytanie (company_id = 5) filtruje reklamy i wyświetlenia. Spróbuj uruchomić go w narzędziu psql, aby wyświetlić wyniki.

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;

Do tej pory wszystkie tabele są dystrybuowane w klastrze za pomocą polecenia company_id. Jednak niektóre typy danych naturalnie "należą" do wszystkich najemców i mogą być umieszczane obok rozkładów wszystkich najemców. Na przykład wszystkie firmy na platformie reklamowej mogą chcieć uzyskać informacje geograficzne dla swoich odbiorców na podstawie szczegółów adresu IP.

Utwórz tabelę referencyjną do przechowywania tych geograficznych informacji o adresach IP. Uruchom następujące polecenia w narzędziu 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);

Następnie zidentyfikuj geo_ips jako "tabelę referencyjną". Klaster zarządza tą tabelą, przechowując zsynchronizowaną tabelę w każdym klastrowanym węźle roboczym.

SELECT create_reference_table('geo_ips');

Teraz załaduj tabelę referencyjną przy użyciu przykładowych danych. Pamiętaj, aby uruchomić to polecenie z katalogu, w którym pobrano plik zestawu danych.

\copy geo_ips from 'geo_ips.csv' with csv

Instrukcje SQL, które łączą wybraną tabelę z geo_ips , są teraz wydajne we wszystkich węzłach. Zwróć uwagę na to, że sprzężenie znajduje lokalizacje każdego adresu IP wybranego w reklamie 290. Spróbuj uruchomić zapytanie w narzędziu 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;

Dostosowywanie schematu dla każdego najemcy

W niektórych przypadkach indywidualni najemcy mogą wymagać przechowywania specjalnych informacji, których inni najemcy nie potrzebują. Jednak wszyscy dzierżawcy mają wspólną definicję z identycznym schematem bazy danych. Gdzie można umieścić dodatkowe dane?

Jednym z rozwiązań jest użycie elastycznego typu kolumny, takiego jak JSONB bazy danych PostgreSQL. Nasz schemat zawiera pole JSONB o clicks nazwie user_data. Firma (powiedzmy, że firma 5) może używać tej kolumny do śledzenia informacji o tym, czy użytkownik jest na urządzeniu przenośnym.

Oto zapytanie, którego firma 5 może użyć, aby dowiedzieć się, którzy wybierają więcej: odwiedzający mobilni czy tradycyjni.

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;

Baza danych PostgreSQL zawiera zaawansowaną funkcję, która umożliwia indeksowanie określonej części lub podzestawu danych. Możesz jeszcze bardziej zoptymalizować zapytanie dla firmy 5, tworząc indeks częściowy.

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

Ponadto innym sposobem na zwiększenie wydajności zapytań obejmujących kolumnę JSONB jest utworzenie indeksu GIN dla każdego klucza bazowego i wartości w kolumnie JSONB.

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;

Następny krok

Dowiedz się więcej o klastrach elastycznych

Sprzężenie zwrotne

Czy ta strona była pomocna?

Last updated on 2025-11-19