Lernprogramm: Entwerfen einer Mehrinstanzendatenbank mit elastischen Clustern

In diesem Lernprogramm verwenden Sie Azure Database for PostgreSQL mit elastischen Clustern, um zu erfahren, wie Sie eine mehrinstanzenfähige Anwendung entwerfen, die von horizontaler Skalierung profitiert.

Voraussetzungen
Verwenden des psql-Hilfsprogramms zum Erstellen eines Schemas
Knotenübergreifende Shard-Tabellen
Erfassen von Beispieldaten
Abfragen von Mandantendaten
Teilen von Daten zwischen Mandanten
Mandantenweises Anpassen des Schemas

Voraussetzungen

Erstellen Sie einen elastischen Cluster auf eine der folgenden Arten:

Verwenden des psql-Hilfsprogramms zum Erstellen eines Schemas

Nachdem Sie mithilfe von psql eine Verbindung mit dem elastischen Cluster hergestellt haben, können Sie Ihren elastischen Cluster konfigurieren. Dieses Lernprogramm führt Sie durch die Erstellung einer Anwendungsplattform, mit der Unternehmen ihre Anzeigenkampagnen nachverfolgen können.

Hinweis

Wenn Sie Daten über Ihren Cluster verteilen, werden alle eindeutigen Dateneinschränkungen auf ihre Verteilung „Shard“ begrenzt. In unserem mehrinstanzenfähigen Beispiel wird die Eindeutigkeit von Anwendungsdaten pro Mandant erzwungen (z. B. Unternehmens-ID). Aus diesem Grund enthalten unsere verteilten Tabellendefinitionen für Primär- und Fremdschlüsseleinschränkungen immer die Spalte "Firmen-ID".

Erstellen Sie eine Tabelle, die Ihre mehrinstanzenübergreifenden Unternehmensinformationen und eine andere Tabelle für ihre Kampagnen enthält. Führen Sie in der psql-Konsole diese Befehle aus:

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

Jede Kampagne zahlt für die Ausführung von Werbung. Fügen Sie Ihre Anzeigentabelle in psql mit dem folgenden Code hinzu:

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

Schließlich möchten Sie Statistiken basierend auf Anzeigenauswahlen und Aufrufen nachverfolgen:

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

Sie können nun die neu erstellten Tabellen aus psql anzeigen, indem Sie Folgendes ausführen:

\dt

Knotenübergreifende Shard-Tabellen

Bis zu diesem Punkt haben Sie Standard-Postgres-Tabellen erstellt, aber Sie müssen letztendlich verteilte Tabellen über Ihren elastischen Cluster erstellen. Verteilte Tabellen in einem elastischen Cluster speichern Daten auf unterschiedlichen Knoten basierend auf den werten, die von Ihrer Verteilungsspalte definiert werden. Diese Spalte wird verwendet, um die Zeilenplatzierung über die zugrunde liegenden Workerknoten zu bestimmen.

Richten Sie die Verteilungsspalte so ein, dass sie company_id ist und als Multitenantenkennung fungiert. Führen Sie in psql diese Funktionen aus:

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

Hinweis

Um die Vorteile von elastischen Clustern mit Azure Database for PostgreSQL-Leistungsfeatures nutzen zu können, müssen Sie Tabellen verteilen. Sofern Sie Ihre Tabellen und Schemas nicht verteilen, nehmen Ihre Clusterknoten nicht an verteilten Abfragen oder Vorgängen teil.

Erfassen von Beispieldaten

Außerhalb von psql laden Sie in der normalen Befehlszeile Beispieldatensätze herunter:

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

Führen Sie – jetzt wieder in psql – ein Massenladen der Daten aus. Achten Sie darauf, psql in dem gleichen Verzeichnis auszuführen, in das Sie die Datendateien heruntergeladen hatten.

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

Ihre Daten in Ihren verteilten Tabellen sind jetzt auf Ihre elastischen Clusterarbeitsknoten verteilt.

Abfragen von Mandantendaten

Wenn Ihre Anwendung Daten für ein bestimmtes Unternehmen anfordert, kann die Datenbank die Abfrage jetzt effizient auf dem entsprechenden Arbeitsknoten ausführen. Beispielsweise filtert die folgende Abfrage (company_id = 5) nach Anzeigen und Impressionen. Versuchen Sie, sie in psql auszuführen, um die Ergebnisse anzuzeigen.

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;

Bis jetzt haben Sie alle Ihre Tabellen über Ihren Cluster mit company_id verteilt. Einige Arten von Daten sind jedoch natürlich für alle Mandanten bestimmt und können innerhalb aller Mandantenverteilungen platziert werden. Beispielsweise möchten alle Unternehmen in Ihrer Anzeigenplattform basierend auf den IP-Adressdetails geografische Informationen für ihr Publikum abrufen.

Erstellen Sie eine Referenztabelle, um diese geografischen IP-Informationen zu enthalten. Führen Sie die folgenden Befehle in psql aus:

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

Identifizieren Sie geo_ips als Nächstes eine "Referenztabelle". Ihr Cluster verwaltet diese Tabelle, indem er eine synchronisierte Tabelle auf jedem geclusterten Workerknoten speichert.

SELECT create_reference_table('geo_ips');

Laden Sie nun Ihre Referenztabelle mit Ihren Beispieldaten. Denken Sie daran, diesen Befehl aus dem Verzeichnis auszuführen, in das Sie die Datasetdatei heruntergeladen haben.

\copy geo_ips from 'geo_ips.csv' with csv

SQL-Anweisungen, die die ausgewählte Tabelle mit geo_ips verknüpfen, sind jetzt auf allen Knoten effizient. Beachten Sie diesen Join, um die Speicherorte aller IP-Adressen zu finden, die für Anzeige 290 ausgewählt wurden. Versuchen Sie, die Abfrage in psql auszuführen:

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;

Schema für jeden Mandant anpassen

In einigen Fällen müssen Ihre einzelnen Mandanten möglicherweise spezielle Informationen speichern, die andere Mandanten nicht benötigen. Allerdings verwenden alle Mandanten eine gemeinsame Definition mit einem identischen Datenbankschema. Wo können Sie die zusätzlichen Daten einfügen?

Eine Lösung besteht darin, einen flexiblen Spaltentyp wie den JSONB von PostgreSQL zu verwenden. Unser Schema weist ein JSONB-Feld in clicks mit dem Namen user_data auf. Ein Unternehmen (z. B. Unternehmen 5) kann diese Spalte verwenden, um Informationen darüber nachzuverfolgen, ob sich ein Benutzer auf einem mobilen Gerät befindet.

Hier ist eine Abfrage, die von Unternehmen 5 verwendet werden kann, um festzustellen, wer mehr auswählt: mobile oder traditionelle Besucher.

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 enthält ein leistungsfähiges Feature, mit dem Sie einen bestimmten Teil oder eine Teilmenge Ihrer Daten indizieren können. Sie können Ihre Abfrage für Unternehmen 5 weiter optimieren, indem Sie einen Teilindex erstellen.

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

Darüber hinaus besteht eine weitere Möglichkeit zur Verbesserung der Leistung in Abfragen, die Ihre JSONB-Spalte enthalten, darin, einen GIN-Index für jeden zugrunde liegenden Schlüssel und Wert in Ihrer JSONB-Spalte zu erstellen.

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;

Nächster Schritt

Weitere Informationen zu elastischen Clustern

Feedback

War diese Seite hilfreich?

Last updated on 2025-11-20