Esercitazione: Progettare un database multi-tenant con cluster elastici

In questa esercitazione si usa Database di Azure per PostgreSQL con cluster elastici per apprendere come progettare un'applicazione multitenant che trae vantaggio dalla scalabilità orizzontale.

Prerequisiti
Usare l'utilità psql per creare uno schema
Ripartire le tabelle tra i nodi
Inserire dati di esempio
Eseguire query sui dati dei tenant
Condividere dati tra i tenant
Personalizzare lo schema in base al tenant

Prerequisiti

Creare un cluster elastico in uno dei modi seguenti:

Usare l'utilità psql per creare uno schema

Dopo la connessione al cluster elastico tramite psql, è possibile configurare il cluster elastico. Questa esercitazione illustra la creazione di una piattaforma applicativa che consente alle aziende di tenere traccia delle campagne pubblicitarie.

Note

Quando si distribuiscono i dati nel cluster, tutti i vincoli di dati univoci hanno come ambito la relativa distribuzione "shard". Nell'esempio multi-tenant l'univocità dei dati dell'applicazione viene applicata per ogni tenant( ad esempio, ID società). Per questo motivo, le definizioni di tabella distribuita per i vincoli di chiave primaria ed esterna includono sempre la colonna ID società.

Creare una tabella per contenere le informazioni sulle aziende multitenant e un'altra tabella per le loro campagne. Nella console di psql eseguire questi comandi:

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

Per la gestione delle inserzioni di ogni campagna è previsto un addebito. Aggiungere la tabella degli annunci in psql con il codice seguente:

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

Infine, vuoi tenere traccia delle statistiche in base alle selezioni e alle impressioni degli annunci:

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

È ora possibile visualizzare le tabelle appena create da psql eseguendo:

\dt

Ripartire le tabelle tra i nodi

Fino a questo momento, avete creato le tabelle Postgres standard, ma in definitiva è necessario creare tabelle distribuite nel vostro cluster elastico. Le tabelle distribuite all'interno di un cluster elastico archiviano i dati in nodi diversi in base ai valori definiti dalla colonna di distribuzione. Questa colonna viene usata per determinare il posizionamento delle righe tra i nodi di lavoro sottostanti.

Imposta la colonna di distribuzione su company_id, che funge da identificatore multitenant. In psql eseguire queste funzioni:

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

Note

Per sfruttare i vantaggi dei cluster elastici con le funzionalità delle prestazioni di Database di Azure per PostgreSQL, è necessario distribuire le tabelle. A meno che non si distribuiscano tabelle e schemi, i nodi del cluster non partecipano a query o operazioni distribuite.

Inserire dati di esempio

All'esterno di psql, nella riga di comando normale scaricare set di dati di esempio:

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

Tornare quindi in psql ed eseguire il caricamento bulk dei dati. Assicurarsi di eseguire psql nella stessa directory in cui sono stati scaricati i file di dati.

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

I dati all'interno delle tabelle distribuite vengono ora distribuiti tra i nodi di lavoro del cluster elastico.

Eseguire query sui dati dei tenant

Quando l'applicazione richiede dati per una società specifica, il database può ora eseguire in modo efficiente la query nel nodo di lavoro appropriato. Ad esempio, la query seguente (company_id = 5) filtra gli annunci e le impressioni. Provare a eseguirla in psql ed esaminare i risultati.

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;

Fino ad ora, hai distribuito tutte le tue tabelle nel tuo cluster tramite company_id. Tuttavia, alcuni tipi di dati appartengono naturalmente a tutti i tenant e possono essere collocati insieme a tutte le distribuzioni di tenant. Ad esempio, tutte le aziende della piattaforma pubblicitaria potrebbero voler ottenere informazioni geografiche per il pubblico in base ai dettagli dell'indirizzo IP.

Creare una tabella di riferimento per contenere queste informazioni ip geografiche. Eseguire i comandi seguenti in 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);

Quindi, identificare geo_ips come "tabella di riferimento". Il cluster gestisce questa tabella archiviando una tabella sincronizzata in ogni nodo di lavoro clusterizzato.

SELECT create_reference_table('geo_ips');

Caricare ora la tabella di riferimento con i dati di esempio. Ricordarsi di eseguire questo comando dalla directory in cui è stato scaricato il file del set di dati.

\copy geo_ips from 'geo_ips.csv' with csv

Le istruzioni SQL che si uniscono alla tabella selezionata con geo_ips sono ora efficienti in tutti i nodi. Questo join serve per trovare le posizioni di ogni indirizzo IP selezionato sull'annuncio 290. Provare a eseguire la query in 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;

Personalizzare lo schema per locatario

In alcuni casi, i singoli tenant potrebbero dover archiviare informazioni speciali non necessarie per altri tenant. Tuttavia, tutti i tenant condividono una definizione comune con uno schema di database identico. Dove è possibile inserire i dati aggiuntivi?

Una soluzione consiste nell'usare un tipo di colonna flessibile, ad esempio JSONB di PostgreSQL. Nello schema dell'esercitazione è presente un campo JSONB in clicks denominato user_data. Una società (ad esempio la società 5) può usare questa colonna per tenere traccia delle informazioni sull'eventuale presenza di un utente in un dispositivo mobile.

Ecco una query che l'azienda 5 può utilizzare per scoprire chi seleziona di più: visitatori mobili o tradizionali.

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 include una potente funzionalità che consente di indicizzare una parte o un subset specifico dei dati. È possibile ottimizzare ulteriormente la query per la società 5 creando un indice parziale.

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

Inoltre, un altro modo per migliorare le prestazioni tra query che includono la colonna JSONB consiste nel creare un indice GIN in ogni chiave e valore sottostante all'interno della colonna 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;

Passaggio successivo

Altre informazioni sui cluster elastici

Commenti e suggerimenti

Questa pagina è stata utile?

Last updated on 2025-11-19