Руководство по проектированию мультитенантной базы данных с помощью эластичных кластеров

В этом руководстве вы используете Базу данных Azure для PostgreSQL с эластичными кластерами, чтобы узнать, как разработать мультитенантное приложение, которое обеспечивает горизонтальное масштабирование.

Необходимые компоненты
Использование служебной программы psql для создания схемы
Сегментирование таблиц по узлам
Принятие демонстрационных данных
Запрос данных клиента
Обмен данными между клиентами
Настройка схемы для каждого клиента

Необходимые компоненты

Создайте эластичные кластеры одним из следующих способов:

Использование служебной программы psql для создания схемы

После подключения к эластичному кластеру с помощью PSQL можно настроить эластичные кластеры. В этом руководстве описывается создание платформы приложений, которая позволяет компаниям отслеживать рекламные кампании.

Примечание.

При распределении данных в кластере все ограничения уникальных данных ограничены их распределением "сегмент". В нашем мультитенантном примере уникальность данных приложения применяется для каждого клиента (например, идентификатор компании). По этой причине наши определения распределенной таблицы для ограничений первичного и внешнего ключа всегда включают столбец идентификатора компании.

Создайте таблицу для хранения мультитенантных корпоративных сведений и другую таблицу для их кампаний. В консоли psql выполните следующие команды:

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

Каждая кампания платит за запуск рекламы. Добавьте таблицу рекламы в psql со следующим кодом:

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

Наконец, вы хотите отслеживать статистику на основе выборов и впечатлений рекламы:

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

Теперь вы можете просмотреть только что созданные таблицы из psql, выполнив следующую команду:

\dt

Сегментирование таблиц по узлам

До этого момента вы создали стандартные таблицы Postgres, но в конечном счете необходимо создать распределенные таблицы в эластичном кластере. Распределенные таблицы в эластичном кластере хранят данные на разных узлах на основе значений, определенных столбцом распространения. Этот столбец используется для определения размещения строк на базовых рабочих узлах.

Настройте столбец для распределения как company_id, который будет служить мультитенантным идентификатором. Выполните в psql такие функции:

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

Примечание.

Чтобы воспользоваться преимуществами эластичных кластеров с функциями производительности Базы данных Azure для PostgreSQL, необходимо распределить таблицы. Если вы не распространяете таблицы и схемы, узлы кластера не участвуют в распределенных запросах или операциях.

Принятие демонстрационных данных

За пределами psql в обычной командной строке скачайте примеры наборов данных:

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 и выполните массовую загрузку данных. Команду psql нужно выполнять в том же каталоге, куда вы скачали файлы данных.

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

Теперь данные в ваших распределенных таблицах распределяются по рабочим узлам эластичного кластера.

Запрос данных клиента

Когда приложение запрашивает данные для конкретной компании, база данных теперь может эффективно выполнять запрос на соответствующем рабочем узле. Например, следующий запрос (company_id = 5) фильтрует рекламу и показы. Попробуйте выполнить его в psql и изучите результаты.

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;

До настоящего времени вы распределяли все таблицы по кластеру с помощью company_id. Однако некоторые типы данных естественно принадлежат всем арендаторам и могут быть размещены вместе с распределением всех арендаторов. Например, все компании на рекламной платформе могут получить географические сведения для своей аудитории на основе сведений об IP-адресе.

Создайте справочную таблицу для хранения этой географической IP-информации. Выполните следующие команды в 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);

Затем определите geo_ips как "эталонную таблицу". Кластер управляет этой таблицей, сохраняя синхронизированную таблицу на каждом кластеризованном рабочем узле.

SELECT create_reference_table('geo_ips');

Теперь загрузите эталонную таблицу с примерами данных. Не забудьте выполнить эту команду из каталога, в котором вы скачали файл набора данных.

\copy geo_ips from 'geo_ips.csv' with csv

SQL-операторы, объединяющие выбранную таблицу с geo_ips, теперь эффективны на всех узлах. Обратите внимание на это соединение для поиска местоположений всех IP-адресов, которые выбраны по объявлению 290. Попробуйте выполнить запрос в 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;

Настройка схемы для каждого клиента

В некоторых случаях отдельным тенантам может потребоваться хранить специальные сведения, которые не нужны прочим тенантам. Однако все арендаторы разделяют общее понятие с идентичной схемой базы данных. Куда можно поместить дополнительные данные?

Одним из решений является использование гибкого типа столбца, например JSONB PostgreSQL. В нашей схеме в таблице clicks есть поле типа JSONB с именем user_data. Компания (например, компания 5) может использовать этот столбец для отслеживания сведений о том, находится ли пользователь на мобильном устройстве.

Вот запрос, который компания 5 может использовать, чтобы определить, кто предпочитает больше: мобильные или традиционные посетители.

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 включает мощную функцию, которая позволяет индексировать определенную часть или подмножество данных. Вы можете оптимизировать запрос для компании 5, создав частичный индекс.

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

Кроме того, другим способом повышения производительности запросов, включающих столбец JSONB, является создание индекса GIN для каждого базового ключа и значения в столбце 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;

Следующий шаг

Дополнительные сведения об эластичных кластерах

Обратная связь

Была ли эта страница полезна?

Last updated on 2025-11-19