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

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных Azure для PostgreSQL — гибкий сервер

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

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

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

• Создание эластичного кластера с помощью шаблона ARM
• Создание эластичного кластера с помощью интерфейса командной строки
• Создание эластичного кластера с помощью портала

Использование служебной программы 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

Мультитенантные приложения могут применять уникальность только для каждого клиента, поэтому все первичные и внешние ключи включают идентификатор компании.

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

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

Давайте назначим идентификатор клиента 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');

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

Теперь в обычной командной строке (вне psql) скачайте примеры наборов данных:

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://examples.citusdata.com/mt_ref_arch/${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-адресов.

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

Заполните ее данными из примера. Не забудьте, что эту команду нужно выполнять в psql в том же каталоге, куда вы скачали набор данных.

\copy geo_ips from 'geo_ips.csv' with csv

Объединение таблицы clicks с geo_ips применяется одновременно на всех узлах. Ниже приведено соединение, позволяющее найти расположение всех, кто щелкнул объявление 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. Любая организация (например, пятая из нашего примера) может хранить в этом столбце сведения об использовании мобильных устройств.

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

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;

Мы оптимизируем этот запрос для одной организации, создав частичный индекс.

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

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

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;

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

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