Tutorial: Diseño de una base de datos multiinquilino con clústeres elásticos

En este tutorial, usará Azure Database for PostgreSQL con clústeres elásticos para aprender a diseñar una aplicación multiinquilino que se beneficia del escalado horizontal.

Requisitos previos
Uso de la utilidad psql para crear un esquema
Particiones de tablas entre nodos
Ingesta de datos de ejemplo
Consulta de datos de inquilino
Datos compartidos entre los inquilinos
Personalización de esquema por inquilino

Requisitos previos

Cree un clúster elástico de una de las maneras siguientes:

Uso de la utilidad psql para crear un esquema

Después de conectarse al clúster elástico mediante psql, puede configurar el clúster elástico. Este tutorial le guía a través de la creación de una plataforma de aplicaciones que permite a las empresas realizar un seguimiento de sus campañas publicitarias.

Nota:

Al distribuir datos en el clúster, las restricciones de datos únicas se limitan a su "extensión" de distribución. En nuestro ejemplo multiinquilino, se aplica la unicidad de datos de aplicación por inquilino (por ejemplo, identificador de empresa). Por este motivo, las definiciones de tabla distribuida para las restricciones de clave principal y externa siempre incluyen la columna id. de empresa.

Cree una tabla para almacenar la información de la empresa multiinquilino y otra tabla para sus campañas. En la consola de psql, ejecute estos comandos:

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

Cada campaña paga por ejecutar anuncios. Agregue la tabla de anuncios en psql con el código siguiente:

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

Por último, quiere realizar un seguimiento de las estadísticas basadas en las selecciones e impresiones de anuncios:

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

Ahora puede ver las tablas recién creadas desde psql mediante la ejecución de:

\dt

Particiones de tablas entre nodos

Hasta este punto, ha creado tablas de Postgres estándar, pero en última instancia debe crear tablas distribuidas en el clúster elástico. Las tablas distribuidas dentro de un clúster elástico almacenan datos en distintos nodos en función de los valores definidos por la columna de distribución. Esta columna se usa para determinar la distribución de las filas en los nodos de trabajo subyacentes.

Configura la columna de distribución para que sea company_id, que actúa como identificador multiinquilino. En psql, ejecute estas funciones:

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

Nota:

Para aprovechar las ventajas de los clústeres elásticos con las características de rendimiento de Azure Database for PostgreSQL, debe distribuir tablas. A menos que distribuya las tablas y esquemas, los nodos del clúster no participan en ninguna consulta o operación distribuidas.

Ingesta de datos de ejemplo

Fuera de psql, en la línea de comandos normal, descargue conjuntos de datos de ejemplo:

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

De vuelta en psql, realice una carga masiva de los datos. No olvide ejecutar psql en el mismo directorio donde descargó los archivos de datos.

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

Los datos de las tablas distribuidas ahora se distribuyen entre los nodos de trabajo del clúster elástico.

Consulta de datos de inquilino

Cuando la aplicación solicita datos para una empresa específica, la base de datos ahora puede ejecutar eficazmente la consulta en el nodo de trabajo adecuado. Por ejemplo, la consulta siguiente (company_id = 5) filtra los anuncios y las impresiones. Intente ejecutarla en psql y observe los resultados.

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;

Hasta ahora, distribuían todas sus tablas en el clúster por company_id. Sin embargo, algunos tipos de datos "pertenecen" naturalmente a todos los inquilinos y se pueden colocar junto con todas las distribuciones de inquilinos. Por ejemplo, todas las empresas de la plataforma de anuncios pueden querer obtener información geográfica para su audiencia en función de los detalles de la dirección IP.

Cree una tabla de referencia para contener esta información de IP geográfica. Ejecute los siguientes comandos en 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);

A continuación, identifique geo_ips como una "tabla de referencia". El clúster administra esta tabla almacenando una tabla sincronizada en cada nodo de trabajo agrupado.

SELECT create_reference_table('geo_ips');

Ahora, cargue la tabla de referencia con los datos de ejemplo. Recuerde ejecutar este comando desde el directorio donde descargó el archivo de conjunto de datos.

\copy geo_ips from 'geo_ips.csv' with csv

Las instrucciones SQL que unen la tabla seleccionada con geo_ips ahora son eficaces en todos los nodos. Observe esta combinación para buscar las ubicaciones de cada dirección IP seleccionada en el anuncio 290. Pruebe a ejecutar la consulta en 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;

Personalización del esquema por inquilino

En algunos casos, es posible que los inquilinos individuales necesiten almacenar información especial que otros inquilinos no necesiten. Sin embargo, todos los inquilinos comparten una definición común con un esquema de base de datos idéntico. ¿Dónde puede colocar los datos adicionales?

Una solución consiste en usar un tipo de columna flexible como JSONB de PostgreSQL. Nuestro esquema tiene un campo JSONB en clicks llamado user_data. Una empresa (por ejemplo, la empresa 5) puede usar esta columna para realizar un seguimiento de la información sobre si un usuario está en un dispositivo móvil.

Aquí hay una consulta que la empresa 5 puede usar para determinar quién selecciona más entre dispositivos móviles o visitantes tradicionales.

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 incluye una característica eficaz que permite indexar una parte o subconjunto específicos de los datos. Puede optimizar aún más su consulta para la empresa 5 mediante la creación de un índice parcial.

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

Además, otra manera de mejorar el rendimiento entre las consultas que incluyen la columna JSONB es crear un índice GIN en cada clave y valor subyacentes dentro de la columna 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;

Paso siguiente

Más información sobre los clústeres elásticos

Comentarios

¿Le ha resultado útil esta página?

Last updated on 2025-11-19