Не Очень Большие Данные

В этой статье будут рассмотрены возможности встроенного или декларативного секционирования в версии 12 PostgreSQL. Демонстрация, подготовленная к одноименному названию отчет на конференции HighLoad++Siberia 2019 (upd: появилось видео с отчетом).

Все примеры сделаны на недавно вышедшей бета-версии:

   

 => SELECT version();
 

   

Таблица резервирования содержит записи за три месяца с июня по август 2017 года и имеет следующую структуру:

=> \d bookings



Table "bookings.bookings" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | not null | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | not null | Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

Бронирование может включать несколько билетов.

Структура стола с билетами:

=> \d tickets



Table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+--------- ticket_no | character(13) | | not null | book_ref | character(6) | | not null | passenger_id | character varying(20) | | not null | passenger_name | text | | not null | contact_data | jsonb | | | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

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

→ Вы можете узнать больше о демо-базе данных Здесь

Разделение по диапазону

Значение выражения вычисляется в момент создания раздела и сохраняется в системном каталоге:

=> CREATE TABLE bookings_range_201708 PARTITION OF bookings_range FOR VALUES FROM (to_timestamp('01.08.2017','DD.MM.YYYY')) TO (to_timestamp('01.09.2017','DD.MM.YYYY'));

Описание таблицы:

=> \d+ bookings_range



Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | | | extended | | book_date | timestamp with time zone | | | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: RANGE (book_date) Partitions: bookings_range_201706 FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03'), bookings_range_201707 FOR VALUES FROM ('2017-07-01 00:00:00+03') TO ('2017-08-01 00:00:00+03'), bookings_range_201708 FOR VALUES FROM ('2017-08-01 00:00:00+03') TO ('2017-09-01 00:00:00+03')

Всё, хватит. Нет необходимости в триггере для вставки записей, никаких ограничений CHECK не требуется.

Параметр CONSTRAINT_EXCLUSION также не нужен, его можно даже отключить:

=> SET constraint_exclusion = OFF;

Заполнение автоматической раскладкой по разделам:

=> INSERT INTO bookings_range SELECT * FROM bookings;



INSERT 0 262788

Декларативный синтаксис по-прежнему скрывает унаследованные таблицы, поэтому распределение строк по разделам можно просмотреть с помощью следующего запроса:

=> SELECT tableoid::regclass, count(*) FROM bookings_range GROUP BY tableoid;



tableoid | count -----------------------+-------- bookings_range_201706 | 7303 bookings_range_201707 | 167062 bookings_range_201708 | 88423 (3 rows)

Но в родительской таблице данных нет:

=> SELECT * FROM ONLY bookings_range;



book_ref | book_date | total_amount ----------+-----------+-------------- (0 rows)

Проверим исключение разделов в плане запроса:

=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;



QUERY PLAN ---------------------------------------------------------------------------- Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (2 rows)

Сканирование только одного раздела, как и ожидалось.

В следующем примере используется функция to_timestamp с категорией переменной STABLE вместо константы:

=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');



QUERY PLAN ------------------------------------------------------------------------------------ Append Subplans Removed: 2 -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (4 rows)

Значение функции рассчитывается при инициализации плана запроса и исключении некоторых разделов из просмотра (строка Subplans Removed).

Но это работает только для SELECT. При изменении данных исключение разделов на основе значений функций STABLE пока не реализовано:

=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');



QUERY PLAN ------------------------------------------------------------------------------------ Delete on bookings_range Delete on bookings_range_201706 Delete on bookings_range_201707 Delete on bookings_range_201708 -> Seq Scan on bookings_range_201706 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201708 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (10 rows)

Поэтому вам следует использовать константы:

=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;



QUERY PLAN ---------------------------------------------------------------------------------- Delete on bookings_range Delete on bookings_range_201707 -> Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (4 rows)

Сортировка по индексу

Поэтому в плане запроса мы видим узел СОРТИРОВКИ и высокую начальную стоимость плана:

=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;



QUERY PLAN ------------------------------------------------------------------------------------------ Sort (cost=24649.77.25077.15 rows=170952 width=52) Sort Key: bookings_range_201706.book_date -> Append (cost=0.00.4240.28 rows=170952 width=52) -> Seq Scan on bookings_range_201706 (cost=0.00.94.94 rows=4794 width=52) -> Seq Scan on bookings_range_201707 (cost=0.00.2151.30 rows=108630 width=52) -> Seq Scan on bookings_range_201708 (cost=0.00.1139.28 rows=57528 width=52) (6 rows)

Давайте создадим индекс по book_date. Вместо одного глобального индекса в каждой секции создаются индексы:

=> CREATE INDEX book_date_idx ON bookings_range(book_date);



=> \di bookings_range*



List of relations Schema | Name | Type | Owner | Table ----------+-------------------------------------+-------+---------+----------------------- bookings | bookings_range_201706_book_date_idx | index | student | bookings_range_201706 bookings | bookings_range_201707_book_date_idx | index | student | bookings_range_201707 bookings | bookings_range_201708_book_date_idx | index | student | bookings_range_201708 (3 rows)

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

Узел СОРТИРОВКИ не требуется, а первая строка результатов требует минимальных затрат:

=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;



QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.12.14880.88 rows=262788 width=52) -> Index Scan using bookings_range_201706_book_date_idx on bookings_range_201706 (cost=0.28.385.83 rows=7303 width=52) -> Index Scan using bookings_range_201707_book_date_idx on bookings_range_201707 (cost=0.42.8614.35 rows=167062 width=52) -> Index Scan using bookings_range_201708_book_date_idx on bookings_range_201708 (cost=0.42.4566.76 rows=88423 width=52) (4 rows)

Индексы созданных таким образом разделов поддерживаются централизованно.

При добавлении нового раздела по нему автоматически будет создан индекс.

Но нельзя удалить индекс только одного раздела:

=> DROP INDEX bookings_range_201706_book_date_idx;



ERROR: cannot drop index bookings_range_201706_book_date_idx because index book_date_idx requires it HINT: You can drop index book_date_idx instead.

Только полностью:

=> DROP INDEX book_date_idx;



DROP INDEX

СОЗДАТЬ ИНДЕКС… ОДНОВРЕМЕННО

Во-первых, мы создаем индекс только для основной таблицы; он получит недействительный статус:

=> CREATE INDEX book_date_idx ON ONLY bookings_range(book_date);



=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';



indisvalid ------------ f (1 row)

Затем создаем индексы по всем разделам с опцией CONCURRENTLY:

=> CREATE INDEX CONCURRENTLY book_date_201706_idx ON bookings_range_201706 (book_date); => CREATE INDEX CONCURRENTLY book_date_201707_idx ON bookings_range_201707 (book_date); => CREATE INDEX CONCURRENTLY book_date_201708_idx ON bookings_range_201708 (book_date);

Теперь подключаем локальные индексы к глобальным:

=> ALTER INDEX book_date_idx ATTACH PARTITION book_date_201706_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201707_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201708_idx;

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

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

=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';



indisvalid ------------ t (1 row)

Подключение и отключение секций

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

Создадим новый раздел, пока над таблицей выполняются другие транзакции, и заодно посмотрим на блокировки:

=> BEGIN; => SELECT count(*) FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');



count ------- 5 (1 row)



=> SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';



relation | mode -----------------------+----------------- bookings_range_201708 | AccessShareLock bookings_range_201707 | AccessShareLock bookings_range_201706 | AccessShareLock bookings_range | AccessShareLock (4 rows)

AccessShareLock помещается в основную таблицу, все разделы и индексы в начале оператора.

Вычисление функции to_timestamp и удаление разделов происходит позже.

Если бы вместо функции использовалась константа, то блокировались бы только основная таблица и раздел bookings_range_201707. Поэтому по возможности указывайте в запросе константы — это нужно сделать, иначе количество строк в pg_locks будет увеличиваться пропорционально количеству секций, что может привести к необходимости увеличения max_locks_per_transaction. Не завершая предыдущую транзакцию, создадим в новом сеансе следующий раздел за сентябрь:

|| => CREATE TABLE bookings_range_201709 (LIKE bookings_range); || => BEGIN; || => ALTER TABLE bookings_range ATTACH PARTITION bookings_range_201709 FOR VALUES FROM ('2017-09-01'::timestamptz) TO ('2017-10-01'::timestamptz); || => SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';



relation | mode -------------------------------------+-------------------------- bookings_range_201709_book_date_idx | AccessExclusiveLock bookings_range | ShareUpdateExclusiveLock bookings_range_201709 | ShareLock bookings_range_201709 | AccessExclusiveLock (4 rows)

При создании нового раздела в основную таблицу помещается ShareUpdateExclusiveLock, совместимый с AccessShareLock. Таким образом, операции добавления разделов не конфликтуют с запросами к секционированной таблице.

=> COMMIT;



|| => COMMIT;

Отключение разделов осуществляется командой ALTER TABLE… DETACH PARTITION. Сам раздел не удаляется, а становится самостоятельной таблицей.

С него можно скачать данные, можно удалить, а при необходимости подключить заново (ATTACH PARTITION).

Другой вариант отключения — удаление раздела командой DROP TABLE. К сожалению, оба варианта, DROP TABLE и DETACH PARTITION, используют AccessExclusiveLock для основной таблицы.

Раздел по умолчанию

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

=> CREATE TABLE bookings_range_default PARTITION OF bookings_range DEFAULT;

Предположим, что при добавлении записи перепутали дату и не указали тысячелетие:

=> INSERT INTO bookings_range VALUES('XX0000', '0017-09-01'::timestamptz, 0) RETURNING tableoid::regclass, *;



tableoid | book_ref | book_date | total_amount ------------------------+----------+------------------------------+-------------- bookings_range_default | XX0000 | 0017-09-01 00:00:00+02:30:17 | 0.00 (1 row) INSERT 0 1

Обратите внимание, что предложение RETURNING возвращает новую строку, которая попадает в раздел по умолчанию.

После установки текущей даты (изменения ключа раздела) запись автоматически перемещается в нужный раздел, триггеры не нужны:

=> UPDATE bookings_range SET book_date = '2017-09-01'::timestamptz WHERE book_ref = 'XX0000' RETURNING tableoid::regclass, *;



tableoid | book_ref | book_date | total_amount -----------------------+----------+------------------------+-------------- bookings_range_201709 | XX0000 | 2017-09-01 00:00:00+03 | 0.00 (1 row) UPDATE 1

Разделение по списку значений

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

Для этого добавьте избыточный столбец book_month в качестве ключа разделения:

=> CREATE TABLE bookings_list ( book_ref character(6), book_month character(6), book_date timestamptz NOT NULL, total_amount numeric(10,2), PRIMARY KEY (book_ref, book_month) ) PARTITION BY LIST(book_month);

Разделы будем создавать динамически на основе данных из таблицы бронирований:

=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF bookings_list FOR VALUES IN (%L)', 'bookings_list_' || partition.book_month, partition.book_month) FROM partition\gexec



CREATE TABLE CREATE TABLE CREATE TABLE

Вот что произошло:

=> \d+ bookings_list



Partitioned table "bookings.bookings_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_date | timestamp with time zone | | not null | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: LIST (book_month) Indexes: "bookings_list_pkey" PRIMARY KEY, btree (book_ref, book_month) Partitions: bookings_list_201706 FOR VALUES IN ('201706'), bookings_list_201707 FOR VALUES IN ('201707'), bookings_list_201708 FOR VALUES IN ('201708')

Наполнение макетом по разделам:

=> INSERT INTO bookings_list(book_ref,book_month,book_date,total_amount) SELECT book_ref,to_char(book_date, 'YYYYMM'),book_date,total_amount FROM bookings;



INSERT 0 262788

В качестве отступления.

Для автоматического заполнения book_month заманчиво использовать новую функциональность версии 12 — столбцы GENERATED ALWAYS. Но, к сожалению, их нельзя использовать в качестве ключа раздела.

Поэтому проблему заполнения месяца следует решать другими способами.

Ограничения целостности, такие как CHECK и NOT NULL, могут быть созданы в секционированной таблице.

Как и в случае наследования, индикация INHERIT/NOINHERIT указывает, должно ли ограничение наследоваться для всех таблиц разделов.

НАСЛЕДОВАНИЕ по умолчанию:

=> ALTER TABLE bookings_range ALTER COLUMN book_date SET NOT NULL;



=> \d bookings_range



Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition key: RANGE (book_date) Indexes: "book_date_idx" btree (book_date) Number of partitions: 5 (Use \d+ to list them.)



=> \d bookings_range_201706



Table "bookings.bookings_range_201706" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition of: bookings_range FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03') Indexes: "book_date_201706_idx" btree (book_date)

Ограничения EXCLUDE можно создавать только локально в разделах.

Поиск по book_ref будет искать по всем разделам, но по индексу, поскольку book_ref указан первым:

=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F';



QUERY PLAN -------------------------------------------------------------------------- Append -> Index Scan using bookings_list_201706_pkey on bookings_list_201706 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201708_pkey on bookings_list_201708 Index Cond: (book_ref = '00000F'::bpchar) (7 rows)

Поиск по book_ref и диапазону разделов должен просматривать только указанный диапазон:

=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F' AND book_month = '201707';



QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: ((book_ref = '00000F'::bpchar) AND (book_month = '201707'::bpchar)) (2 rows)

Команда INSERT. ON CONFLICT корректно находит нужный раздел и выполняет обновление:

=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',0) RETURNING tableoid::regclass, *;



tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 0.00 (1 row) INSERT 0 1



=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',100) ON CONFLICT(book_ref,book_month) DO UPDATE SET total_amount = 100 RETURNING tableoid::regclass, *;



tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 100.00 (1 row) INSERT 0 1

Внешние ключи

Чтобы сделать внешний ключ возможным, мы добавим столбец book_month и в то же время разобьем его на разделы по месяцам, как и bookings_list.

=> CREATE TABLE tickets_list ( ticket_no character(13), book_month character(6), book_ref character(6) NOT NULL, passenger_id varchar(20) NOT NULL, passenger_name text NOT NULL, contact_data jsonb, PRIMARY KEY (ticket_no, book_month), FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list (book_ref, book_month) ) PARTITION BY LIST (book_month);

Ограничение FOREIGN KEY заслуживает более пристального внимания.

С одной стороны, это внешний ключ от секционированная таблица (tickets_list), а с другой стороны это ключ на секционированная таблица (bookings_list).

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

Создайте разделы:

=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF tickets_list FOR VALUES IN (%L)', 'tickets_list_' || partition.book_month, partition.book_month) FROM partition\gexec



CREATE TABLE CREATE TABLE CREATE TABLE



=> \d+ tickets_list



Partitioned table "bookings.tickets_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------+-----------+----------+---------+----------+--------------+------------- ticket_no | character(13) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_ref | character(6) | | not null | | extended | | passenger_id | character varying(20) | | not null | | extended | | passenger_name | text | | not null | | extended | | contact_data | jsonb | | | | extended | | Partition key: LIST (book_month) Indexes: "tickets_list_pkey" PRIMARY KEY, btree (ticket_no, book_month) Foreign-key constraints: "tickets_list_book_ref_book_month_fkey" FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list(book_ref, book_month) Partitions: tickets_list_201706 FOR VALUES IN ('201706'), tickets_list_201707 FOR VALUES IN ('201707'), tickets_list_201708 FOR VALUES IN ('201708')

Заполнять:

=> INSERT INTO tickets_list (ticket_no,book_month,book_ref,passenger_id,passenger_name,contact_data) SELECT t.ticket_no,b.book_month,t.book_ref, t.passenger_id,t.passenger_name,t.contact_data FROM bookings_list b JOIN tickets t ON (b.book_ref = t.book_ref);



INSERT 0 366733



=> VACUUM ANALYZE tickets_list;

Распределение линий по разделам:

=> SELECT tableoid::regclass, count(*) FROM tickets_list GROUP BY tableoid;



tableoid | count ---------------------+-------- tickets_list_201706 | 10160 tickets_list_201707 | 232755 tickets_list_201708 | 123818 (3 rows)

Запросы соединения и агрегации

• Рекомендации По Хорошему Поисковому Контенту

  19 Oct, 24

• Узнайте О Восстановлении Реестра Для Windows

  19 Oct, 24

• Установите Драйверы Dell Vostro 3800 Абсолютно Бесплатно

  19 Oct, 24

• Как Продвигать Сайт Электронной Коммерции

  19 Oct, 24

• Издатели Российских Cosmopolitan, Esquire И Grazia Объединились Под Одним Брендом

  19 Oct, 24

• За «Дистанционными» Студентами Будут Следить С Помощью Веб-Камер

  19 Oct, 24

• Кпк | Умная Версия Сайта

  19 Oct, 24

• Неделя Отзывов О Кроссовере

  19 Oct, 24

• Разница В Разрешениях Устройств. Советы Разработчикам

  19 Oct, 24

• Альтернатива Гудению Фазоинвертора: Линии Передачи (Tqwt, Alt)

  19 Oct, 24