3.1. Миграция схемы базы данных
Пред. НаверхГлава 3. РазработкаНачало След.

3.1. Миграция схемы базы данных

3.1.1. Исходная схема базы данных
3.1.2. Конфигурация кластера Shardman
3.1.3. Выбор ключа сегментирования

В качестве примера для разработки воспользуемся демонстрационной базой данных «Авиакомпании». Подробное описание схемы базы данных доступно по ссылке https://postgrespro.ru/education/demodb. Эта демонстрационная схема используется в образовательных программах Postgres Professional, например в курсе «QPT. Оптимизация запросов».

Авторы схемы охарактеризовали её так: «Схема демонстрационной базы данных достаточно проста, чтобы быть понятной каждому, и в то же время достаточно сложна, чтобы строить нетривиальные запросы.»

Схема базы данных содержит несколько таблиц с осмысленным содержимым. Для примера возьмём версию демонстрационной БД от 13.10.2016. Ссылки для загрузки дампов базы данных и схемы (на русском языке) находятся на странице https://postgrespro.ru/education/courses/QPT. Примеры запросов, помимо приведённых ниже, можно взять из вышеупомянутого курса и книги «Postgres. Первое знакомство».

В этом разделе показаны два примера модификации схемы и адаптации запроса:

  • Наивный подход прост, требует минимальных преобразований схемы и призван внести ясность в работу запросов в распределённой схеме.

  • Комплексный подход более сложный и предназначен для лучшего понимания проблем и процессов, с которыми может столкнуться разработчик при переходе на распределённую схему и адаптации приложений к такой схеме.

3.1.1. Исходная схема базы данных

Рисунок 3.1. Исходная схема базы данных

Исходная схема базы данных


Авторы описывают базу данных «Airlines» следующим образом:

Основной сущностью является бронирование (bookings).

В одно бронирование можно включить несколько пассажиров, каждому из которых выписывается отдельный билет (tickets). Билет имеет уникальный номер и содержит информацию о пассажире. Как таковой пассажир не является отдельной сущностью. Как имя, так и номер документа пассажира могут меняться с течением времени, так что невозможно однозначно найти все билеты одного человека; для простоты можно считать, что все пассажиры уникальны.

Билет включает один или несколько перелетов (ticket_flights). Несколько перелетов могут включаться в билет в случаях, когда нет прямого рейса, соединяющего пункты отправления и назначения (полет с пересадками), либо когда билет взят «туда и обратно». В схеме данных нет жёсткого ограничения, но предполагается, что все билеты в одном бронировании имеют одинаковый набор перелетов.

Каждый рейс (flights) следует из одного аэропорта (airports) в другой. Рейсы с одним номером имеют одинаковые пункты вылета и назначения, но будут отличаться датой отправления.

При регистрации на рейс пассажиру выдаётся посадочный талон (boarding_passes), в котором указано место в самолете. Пассажир может зарегистрироваться только на тот рейс, который есть у него в билете. Комбинация рейса и места в самолете должна быть уникальной, чтобы не допустить выдачу двух посадочных талонов на одно место.

Количество мест (seats) в самолете и их распределение по классам обслуживания зависит от модели самолета (aircrafts), выполняющего рейс. Предполагается, что каждая модель самолета имеет только одну компоновку салона. Схема данных не контролирует, что места в посадочных талонах соответствуют имеющимся в самолете (такая проверка может быть сделана с использованием табличных триггеров или в приложении).

Давайте посмотрим на общие сущности и размеры таблиц в приведённой выше схеме. Можно заметить, что таблицы ticket_flights, boarding_passes и tickets связаны полем ticket_no. Кроме того, объём данных в этих таблицах составляет 95% от общего объёма БД.

Посмотрите на таблицу bookings. Хотя кажется, что у неё довольно компактная структура, со временем эта таблица может значительно вырасти.

Примеры миграции приведены для кластера Shardman, содержащего четыре сегмента. Сегментированные таблицы разделены на четыре части, так что одна часть сегментированной таблицы находится только в одном сегменте. Это сделано специально, чтобы планы запросов отображались более наглядно. В реальной жизни количество секций должно выбираться исходя из максимального количества узлов кластера.

При миграции реальной схемы БД следует заранее подумать о количестве секций, на которые могут быть разделены данные в распределённых таблицах. Также следует помнить, что лучший подход к миграции — использование таких SQL-преобразований, которые накладывают минимальные ограничения на объекты базы данных.

3.1.2. Конфигурация кластера Shardman

Кластер Shardman состоит из четырёх узлов — node1, node2, node3 и node4. Каждый узел кластера представляет собой отдельный сегмент.

В примерах предполагается, что таблицы разделены на четыре секции по ключу сегментирования (num_parts = 4) и распределены по узлам кластера. Каждая часть таблицы с данными расположена в соответствующем сегменте:

  • shard-1 находится на узле кластера node1

  • shard-2 находится на узле кластера node2

  • shard-3 находится на узле кластера node3

  • shard-4 находится на узле кластера node4

Кластер намеренно представлен в упрощённой конфигурации. У узлов кластера нет реплик, а конфигурация не является отказоустойчивой.

3.1.3. Выбор ключа сегментирования

3.1.3.1. Наивный [1] подход — ключ сегментирования ticket_no

При таком подходе выбор ключа сегментирования довольно очевиден. Это номер билета ticket_no. Номер билета является первичным ключом таблицы tickets и внешним ключом таблиц ticket_flights и boarding_passes.

Первичный ключ таблиц ticket_flights и boarding_passes является составным. Это уникальный индекс, состоящий из ticket_no и flight_id.

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

Остальные таблицы — airports, flights, aircrafts и seats достаточно малы и редко изменяются. Эти свойства позволяют сделать их глобальными таблицами, или таблицами-справочниками.

Рисунок 3.2. Схема наивного подхода

Схема наивного подхода


Основное преимущество данного подхода, с точки зрения создания схемы и запросов к БД — практически полное отсутствие каких-либо изменений, кроме тех, которые присущи при работе с распределённой системой, то есть явных указаний при создании таблиц, последовательностей и т. д. сделать их распределёнными.

После выбора ключа сегментирования, можно перейти к созданию распределённой схемы.

[1]

3.1.3.1.1. Создание схемы, распределённой по ticket_no

Сначала включите трансляцию DDL-операторов во все сегменты кластера: 

SET shardman.broadcast_ddl TO on;

Создайте схему bookings для всех сегментов: 

CREATE SCHEMA bookings;

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

база данных содержит «снимки» данных — аналогично резервной копии реальной системы, сделанной в определённый момент времени. Например, статус рейса Departed означает, что воздушное судно уже вылетело и находилось в воздухе на момент создания резервной копии. Время «снимка» сохраняется в функции bookings.now(). Этой функцией можно пользоваться в демонстрационных запросах там, где в обычной жизни использовалась бы функция now(). Кроме того, значение, возвращаемое этой функцией, определяет версию демонстрационной базы данных. Последняя доступная версия — от 13.10.2016: 

SELECT bookings.now() as now;
          now
-----------------------
2016-10-13 17:00:00+03

Относительно именно этого момента времени все рейсы делятся на прошедшие и будущие.

Давайте создадим вспомогательную функцию bookings.now(): 

    CREATE FUNCTION bookings.now() RETURNS timestamp with time zone
    LANGUAGE sql IMMUTABLE COST 0.00999999978
    AS
    $sql$
    SELECT $qq$2016-10-13 17:00:00$qq$::TIMESTAMP AT TIME ZONE
$zz$Europe/Moscow$zz$;
    $sql$;

Кроме самих таблиц нам потребуется глобальная последовательность для генерирования идентификаторов (ID) при вставке данных в таблицу flights. В данном примере последовательность создаётся явно и далее связывается со столбцом этой таблицы, задавая значения, генерируемые последовательностью по умолчанию.

Создайте последовательность, используя следующий DDL-оператор: 

CREATE SEQUENCE bookings.flights_flight_id_seq
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1 with(global);

Выражение with(global) создаёт единую распределённую последовательность, доступную на всех узлах кластера, которая выдаёт значения из определённого диапазона в каждом сегменте кластера, при этом диапазоны разных сегментов не пересекаются. За более подробным описанием работы глобальных последовательностей обратитесь к Разделу 7.6 и Разделу 6.4.

На практике глобальные последовательности в каждом сегменте являются регулярные последовательности, которым выделяются последовательные блоки (по умолчанию — 65536 номеров). Когда все номера в блоке будут выделены, следующий блок выделяется локальной последовательности в сегменте. То есть числа из глобальных последовательностей уникальны, но строгой монотонности нет, и в значениях, заданных генератором последовательностей, могут быть «дыры» [2].

Последовательности могут иметь тип bigserial или serial8 и применяться как для сегментированных, так и для глобальных таблиц.

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

[2]

Далее следует создать глобальные таблицы. Как уже говорилось выше, эти таблицы небольшого размера, данные в них изменяются редко, логично воспринимать их как таблицы-справочники, которые должны содержать одинаковые данные во всех сегментах кластера. Обязательное требование при создании глобальных таблиц — наличие первичного ключа.

Создайте глобальные таблицы, используя следующие DDL-операторы: 

CREATE TABLE bookings.aircrafts (
    aircraft_code character(3) NOT NULL primary key,
    model text NOT NULL,
    range integer NOT NULL,
    CONSTRAINT aircrafts_range_check CHECK ((range > 0))
) with (global);

CREATE TABLE bookings.seats (
    aircraft_code character(3) references bookings.aircrafts(aircraft_code),
    seat_no character varying(4) NOT NULL,
    fare_conditions character varying(10) NOT NULL,
    CONSTRAINT seats_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::character varying)::text, ('Business'::character varying)::text]))),
    PRIMARY KEY (aircraft_code, seat_no)
) with (global);

CREATE TABLE bookings.airports (
    airport_code character(3) NOT NULL primary key,
    airport_name text NOT NULL,
    city text NOT NULL,
    longitude double precision NOT NULL,
    latitude double precision NOT NULL,
    timezone text NOT NULL
)  with (global);

CREATE TABLE bookings.bookings (
    book_ref character(6) NOT NULL,
    book_date timestamp with time zone NOT NULL,
    total_amount numeric(10,2) NOT NULL,
    PRIMARY KEY (book_ref)
) with (global);

CREATE TABLE bookings.flights (
    flight_id bigint NOT NULL PRIMARY KEY,-- <= a sequence will be assigned
    flight_no character(6) NOT NULL,
    scheduled_departure timestamp with time zone NOT NULL,
    scheduled_arrival timestamp with time zone NOT NULL,
    departure_airport character(3) REFERENCES bookings.airports(airport_code),
    arrival_airport character(3) REFERENCES bookings.airports(airport_code),
    status character varying(20) NOT NULL,
    aircraft_code character(3) references bookings.aircrafts(aircraft_code),
    actual_departure timestamp with time zone,
    actual_arrival timestamp with time zone,
    CONSTRAINT flights_check CHECK ((scheduled_arrival > scheduled_departure)),
    CONSTRAINT flights_check1 CHECK (((actual_arrival IS NULL) OR ((actual_departure IS NOT NULL) AND (actual_arrival IS NOT NULL) AND (actual_arrival > actual_departure)))),
    CONSTRAINT flights_status_check CHECK (((status)::text = ANY (ARRAY[('On Time'::character varying)::text, ('Delayed'::character varying)::text, ('Departed'::character varying)::text, ('Arrived'::character varying)::text, ('Scheduled'::character varying)::text, ('Cancelled'::character varying)::text])))
) with (global);

-- свяжите последовательность со столбцом таблицы
ALTER SEQUENCE bookings.flights_flight_id_seq OWNED BY bookings.flights.flight_id;


-- задайте значение для столбца по умолчанию
ALTER TABLE bookings.flights ALTER COLUMN flight_id SET DEFAULT shardman.next_value('bookings.flights_flight_id_seq');

ALTER TABLE bookings.flights ADD CONSTRAINT flights_flight_no_scheduled_departure_key UNIQUE (flight_no, scheduled_departure);

Далее создайте сегментированные таблицы tickets, ticket_flights и boarding_passes в схеме bookings: 

CREATE TABLE bookings.tickets (
    ticket_no character(13) PRIMARY KEY,
    book_ref character(6) REFERENCES bookings.bookings(book_ref),
    passenger_id character varying(20) NOT NULL,
    passenger_name text NOT NULL,
    contact_data jsonb
) with (distributed_by='ticket_no', num_parts=4);

CREATE TABLE bookings.ticket_flights (
    ticket_no character(13) NOT NULL,
    flight_id bigint references bookings.flights(flight_id),
    fare_conditions character varying(10) NOT NULL,
    amount numeric(10,2) NOT NULL,
    CONSTRAINT ticket_flights_amount_check CHECK ((amount >= (0)::numeric)),
    CONSTRAINT ticket_flights_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::character varying)::text, ('Business'::character varying)::text]))),
    PRIMARY KEY (ticket_no, flight_id)
) with (distributed_by='ticket_no', colocate_with='bookings.tickets');

CREATE TABLE bookings.boarding_passes (
    ticket_no character(13) NOT NULL,
    flight_id bigint NOT NULL,
    boarding_no integer NOT NULL,
    seat_no character varying(4) NOT NULL,
    FOREIGN KEY (ticket_no, flight_id) REFERENCES bookings.ticket_flights(ticket_no, flight_id),
    PRIMARY KEY (ticket_no, flight_id)
) with (distributed_by='ticket_no', colocate_with='bookings.tickets');

-- ограничения обязательно должны содержать ключ сегментирования
ALTER TABLE bookings.boarding_passes ADD CONSTRAINT boarding_passes_flight_id_boarding_no_key UNIQUE (ticket_no, flight_id, boarding_no);

ALTER TABLE bookings.boarding_passes ADD CONSTRAINT boarding_passes_flight_id_seat_no_key UNIQUE (ticket_no, flight_id, seat_no);

При создании распределённых таблиц можно дополнительно указать параметр num_parts, который отвечает за количество секций, на которые будут разделены распределённые таблицы. В данном примере он равен 4, в целях сокращения вывода планов запросов. Значение данного параметра по умолчанию — 20. Этот параметр может быть важен, если в будущем планируется добавлять сегменты в кластер и выполнять горизонтальное масштабирование.

На основании будущих нагрузки и объёма данных, значение параметра num_parts должно быть достаточным для выполнения перебалансировки данных при добавлении новых сегментов (num_parts должно быть больше или равно количеству узлов в кластере). С другой стороны, слишком большое количество секций приводит существенному увеличению времени планирования запроса. Поэтому нужно стремиться к разумному балансу между количеством секций и количеством узлов кластера.

Последнее, что осталось сделать – создать представление, которое понадобится при выполнении некоторых запросов: 

CREATE VIEW bookings.flights_v AS
 SELECT f.flight_id,
    f.flight_no,
    f.scheduled_departure,
    timezone(dep.timezone, f.scheduled_departure) AS scheduled_departure_local,
    f.scheduled_arrival,
    timezone(arr.timezone, f.scheduled_arrival) AS scheduled_arrival_local,
    (f.scheduled_arrival - f.scheduled_departure) AS scheduled_duration,
    f.departure_airport,
    dep.airport_name AS departure_airport_name,
    dep.city AS departure_city,
    f.arrival_airport,
    arr.airport_name AS arrival_airport_name,
    arr.city AS arrival_city,
    f.status,
    f.aircraft_code,
    f.actual_departure,
    timezone(dep.timezone, f.actual_departure) AS actual_departure_local,
    f.actual_arrival,
    timezone(arr.timezone, f.actual_arrival) AS actual_arrival_local,
    (f.actual_arrival - f.actual_departure) AS actual_duration
   FROM bookings.flights f,
    bookings.airports dep,
    bookings.airports arr
  WHERE ((f.departure_airport = dep.airport_code) AND (f.arrival_airport = arr.airport_code));

Теперь создание распределённой схемы завершено. Отключите трансляцию DDL-операторов: 

SET shardman.broadcast_ddl TO off;

3.1.3.2. Комплексный подход — ключ сегментирования book_ref

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

Допустим, авиакомпания работает на рынке более 10 лет, и таблица bookings в этом случае вырастет в объёме до такого размера, что сделать её глобальной не представляется возможным. Но и распределить данные этой таблицы нельзя, так как она не содержит общих полей с другими таблицами, по которым её можно распределить (как в варианте 1).

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

Если посмотреть на таблицу bookings, можно заметить, что поле book_ref — уникальное и является первичным ключом для данной таблицы, а также служит внешним ключом к таблице tickets. Поэтому это поле кажется подходящим для выбора в качестве ключа сегментирования. Однако поле book_ref отсутствует в таблицах ticket_flights и boarding_passes.

Если добавить поле book_ref в таблицы ticket_flights и boarding_passes, то станет возможным распределение всех таблиц: bookings, tickets, ticket_flights и boarding_passes по ключу сегментирования book_ref.

Операцию по добавлению ключа book_ref в таблицы ticket_flights и boarding_passes следует производить в исходной схеме, при этом, поле book_ref должно быть заполнено данными из таблицы bookings.

Рисунок 3.3. Изменение исходной схемы

Изменение исходной схемы


3.1.3.2.1. Изменение исходной схемы

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

  1. Добавьте поле book_ref в таблицы ticket_flights и boarding_passes: 

    ALTER TABLE ticket_flights
   ADD COLUMN book_ref char(6);

ALTER TABLE boarding_passes
   ADD COLUMN book_ref char(6);

  2. В этих таблицах заполните добавленное поле book_ref данными: 

    WITH batch AS (SELECT book_ref,
                       ticket_no
                 FROM tickets)
UPDATE ticket_flights
   SET book_ref = batch.book_ref
  FROM batch
 WHERE ticket_flights.ticket_no = batch.ticket_no
   AND ticket_flights.book_ref IS NULL;


WITH batch AS (SELECT book_ref,
                      ticket_no
                 FROM tickets)
UPDATE boarding_passes
   SET book_ref = batch.book_ref
  FROM batch
 WHERE boarding_passes.ticket_no = batch.ticket_no
   AND boarding_passes.book_ref IS NULL;

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

Теперь схема БД готова к переносу.

3.1.3.2.2. Создание схемы, распределённой по ключу book_ref

Здесь используется функция Shardman shardman.broadcast_all_sql() для трансляции DDL-операторов на все узлы кластера. Создайте схему bookings для всех сегментов: 

SELECT shardman.broadcast_all_sql('CREATE SCHEMA bookings');

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

Сначала создайте вспомогательную функцию bookings.now(): 

SELECT shardman.broadcast_all_sql(
    $sql$
    CREATE FUNCTION bookings.now() RETURNS timestamp with time zone
    LANGUAGE sql IMMUTABLE COST 0.00999999978
    AS 
    $q$
    SELECT $qq$2016-10-13 17:00:00$qq$::TIMESTAMP 
                    AT TIME ZONE $zz$Europe/Moscow$zz$;
    $q$;
    $sql$
);

Таблицы, пользователи и последовательности создаются с помощью обычных SQL-функций. Эта функция для этого не нужна.

В данном примере глобальная последовательность не создаётся явно, так как Shardman при указании типа поля bigserial создаёт глобальную последовательность автоматически.

Теперь создайте глобальные таблицы, используя следующие DDL-операторы: 

CREATE TABLE bookings.aircrafts (
    aircraft_code character(3) NOT NULL PRIMARY KEY,
    model text NOT NULL,
    range integer NOT NULL,
    CONSTRAINT aircrafts_range_check CHECK ((range > 0))
) WITH (global);

CREATE TABLE bookings.seats (
    aircraft_code character(3) REFERENCES bookings.aircrafts(aircraft_code),
    seat_no character varying(4) NOT NULL,
    fare_conditions character varying(10) NOT NULL,
    CONSTRAINT seats_fare_conditions_check CHECK ((
          (fare_conditions)::text = ANY (ARRAY[
             ('Economy'::character varying)::text,
             ('Comfort'::character varying)::text,
             ('Business'::character varying)::text])
           )),
    PRIMARY KEY (aircraft_code, seat_no)
) WITH (global);

CREATE TABLE bookings.airports (
    airport_code character(3) NOT NULL PRIMARY KEY,
    airport_name text NOT NULL,
    city text NOT NULL,
    longitude double precision NOT NULL,
    latitude double precision NOT NULL,
    timezone text NOT NULL
)  WITH (global);

CREATE TABLE bookings.flights (
-- the global sequence will be created automatically
-- the default value will be assigned
    flight_id bigserial NOT NULL PRIMARY KEY,
    flight_no character(6) NOT NULL,
    scheduled_departure timestamp with time zone NOT NULL,
    scheduled_arrival timestamp with time zone NOT NULL,
    departure_airport character(3) REFERENCES bookings.airports(airport_code),
    arrival_airport character(3) REFERENCES bookings.airports(airport_code),
    status character varying(20) NOT NULL,
    aircraft_code character(3) REFERENCES bookings.aircrafts(aircraft_code),
    actual_departure timestamp with time zone,
    actual_arrival timestamp with time zone,
    CONSTRAINT flights_check CHECK ((scheduled_arrival > scheduled_departure)),
    CONSTRAINT flights_check1 CHECK ((
                  (actual_arrival IS NULL) 
               OR ((actual_departure IS NOT NULL) 
              AND (actual_arrival IS NOT NULL) 
              AND (actual_arrival > actual_departure)))),
    CONSTRAINT flights_status_check CHECK (
           ((status)::text = ANY (
      ARRAY[('On Time'::character varying)::text,
            ('Delayed'::character varying)::text,
            ('Departed'::character varying)::text,
            ('Arrived'::character varying)::text,
            ('Scheduled'::character varying)::text,
            ('Cancelled'::character varying)::text])))
) WITH (global);

ALTER TABLE bookings.flights 
   ADD CONSTRAINT flights_flight_no_scheduled_departure_key 
   UNIQUE (flight_no, scheduled_departure);

Теперь создайте сегментированные таблицы bookings, tickets, ticket_flights и boarding_passes в схеме bookings, как в предыдущем примере: 

-- в данных таблицах не предполагается изменений, кроме их распределения
CREATE TABLE bookings.bookings (
   book_ref character(6) NOT NULL PRIMARY KEY,
   book_date timestamp with time zone NOT NULL,
   total_amount numeric(10,2) NOT NULL
) WITH (distributed_by='book_ref', num_parts=4);


CREATE TABLE bookings.tickets (
   ticket_no character(13) NOT NULL,
   book_ref character(6) REFERENCES bookings.bookings(book_ref),
   passenger_id character varying(20) NOT NULL,
   passenger_name text NOT NULL,
   contact_data jsonb,
   PRIMARY KEY (book_ref, ticket_no)
) WITH (distributed_by='book_ref', colocate_with='bookings.bookings');

-- в данных таблицах добавляется дополнительный внешний ключ book_ref
CREATE TABLE bookings.ticket_flights (
   ticket_no character(13) NOT NULL,
   flight_id bigint NOT NULL,
   fare_conditions character varying(10) NOT NULL,
   amount numeric(10,2) NOT NULL,
   book_ref character(6) NOT NULL, -- <= added book_ref
   CONSTRAINT ticket_flights_amount_check 
        CHECK ((amount >= (0)::numeric)),
   CONSTRAINT ticket_flights_fare_conditions_check 
        CHECK (((fare_conditions)::text = ANY (
        ARRAY[('Economy'::character varying)::text,
              ('Comfort'::character varying)::text,
              ('Business'::character varying)::text]))),
   FOREIGN KEY (book_ref, ticket_no) 
        REFERENCES bookings.tickets(book_ref, ticket_no),
   PRIMARY KEY (book_ref, ticket_no, flight_id) -- <= changed the primary key
) with (distributed_by='book_ref', colocate_with='bookings.bookings');


CREATE TABLE bookings.boarding_passes (
    ticket_no character(13) NOT NULL,
    flight_id bigint NOT NULL,
    boarding_no integer NOT NULL,
    seat_no character varying(4) NOT NULL,
    FOREIGN KEY (book_ref, ticket_no, flight_id) 
         REFERENCES bookings.ticket_flights(book_ref, ticket_no, flight_id),
    PRIMARY KEY (book_ref, ticket_no, flight_id)
) WITH (distributed_by='book_ref', colocate_with='bookings.tickets');

-- ограничения должны обязательно содержать ключ сегментирования
ALTER TABLE bookings.boarding_passes 
  ADD CONSTRAINT boarding_passes_flight_id_boarding_no_key 
  UNIQUE (book_ref, ticket_no, flight_id, boarding_no);

ALTER TABLE bookings.boarding_passes 
  ADD CONSTRAINT boarding_passes_flight_id_seat_no_key 
  UNIQUE (book_ref, ticket_no, flight_id, seat_no);

Создайте представление bookings.flights: 

SELECT shardman.broadcast_all_sql($$
CREATE VIEW bookings.flights_v AS
SELECT f.flight_id,
      f.flight_no,
      f.scheduled_departure,
      timezone(dep.timezone, f.scheduled_departure) AS scheduled_departure_local,
      f.scheduled_arrival,
      timezone(arr.timezone, f.scheduled_arrival)   AS scheduled_arrival_local,
      (f.scheduled_arrival - f.scheduled_departure) AS scheduled_duration,
      f.departure_airport,
      dep.airport_name                              AS departure_airport_name,
      dep.city                                      AS departure_city,
      f.arrival_airport,
      arr.airport_name                              AS arrival_airport_name,
      arr.city                                      AS arrival_city,
      f.status,
      f.aircraft_code,
      f.actual_departure,
      timezone(dep.timezone, f.actual_departure)    AS actual_departure_local,
      f.actual_arrival,
      timezone(arr.timezone, f.actual_arrival)      AS actual_arrival_local,
      (f.actual_arrival - f.actual_departure)       AS actual_duration
FROM bookings.flights f,
    bookings.airports dep,
    bookings.airports arr
WHERE ((f.departure_airport = dep.airport_code) AND (f.arrival_airport = arr.airport_code));
$$);

Теперь создание схемы завершено. Следующая тема — миграция данных.


[1] В контексте компьютерных наук выражение «naïve approach» (дословно – наивный метод, наивный подход) очень похоже на «метод решения в лоб» и означает первую базовую идею, приходящую в голову при столкновении с проблемой, которая часто не учитывает сложности, крайние случаи или полный объём требований. С одной стороны, это грубый и прямой метод, направленный на получение работоспособного решения, с другой — такие решения легко понять и реализовать, но при этом системные ресурсы могут использоваться неэффективно.

[2] Возможно выделение значений из разных диапазонов, то есть значение может скакать. Например, в первом сегменте можно получить значение 5, во втором — 140003, в третьем — 70003 и т. д.

Пред. Наверх След.
Глава 3. Разработка Начало 3.2. Миграция данных