pluzanov Jun 19 2019 at 17:16

Не очень большие данные

21 min

19K

Postgres Professional corporate blog PostgreSQL *SQL *

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

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

=> SELECT version();

                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12beta1 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 32-bit
(1 row)

В примерах используются таблицы bookings и tickets демонстрационной базы данных. Таблица бронирований содержит записи за три месяца с июня по август 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)

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

→ Подробнее познакомиться с демо-базой можно здесь

Секционирование по диапазону

В начале попробуем сделать таблицу bookings секционированной по диапазону дат. В таком случае таблица создавалась бы так:

=> CREATE TABLE bookings_range (
       book_ref     character(6),
       book_date    timestamptz,
       total_amount numeric(10,2)
   ) PARTITION BY RANGE(book_date);

Отдельные секции на каждый месяц:

=> CREATE TABLE bookings_range_201706 PARTITION OF bookings_range 
       FOR VALUES FROM ('2017-06-01'::timestamptz) TO ('2017-07-01'::timestamptz);
=> CREATE TABLE bookings_range_201707 PARTITION OF bookings_range 
       FOR VALUES FROM ('2017-07-01'::timestamptz) TO ('2017-08-01'::timestamptz);

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

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

Сортировка с помощью индекса

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

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

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

=> 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… CONCURRENTLY

При создании индекса на секционированной таблице нельзя указать CONCURRENTLY.

Но можно поступить следующим образом. Сначала создаем индекс только на основной таблице, он получит статус invalid:

=> 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

Секционирование по списку значений

В демо-базе первичным ключом таблицы bookings должен быть столбец book_ref. Однако выбранная схема секционирования не позволяет создать такой ключ:

=> ALTER TABLE bookings_range ADD PRIMARY KEY(book_ref);

ERROR:  insufficient columns in PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "bookings_range" lacks column "book_date" which is part of the partition key.

Ключ секционирования обязательно должен входить в первичный ключ.

Чтобы разбить по месяцам и всё-таки включить book_ref в первичный ключ, попробуем другую схему секционирования таблицы bookings — по списку значений. Для этого добавим избыточный столбец 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);

Секции сформируем динамически на основании данных таблицы bookings:

=> 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 говорит о том, нужно ли наследовать ограничение на всех таблицах-секциях. По умолчанию INHERIT:

=> 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

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

В демо-базе таблица tickets ссылается на bookings.

Чтобы внешний ключ был возможен, добавим столбец 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 стоит посмотреть внимательнее. C одной стороны это внешний ключ из секционированной таблицы(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)

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

Соединим две таблицы, секционированные одинаково:

=> EXPLAIN (COSTS OFF) 
   SELECT b.*
     FROM bookings_list b JOIN tickets_list t 
          ON (b.book_ref = t.book_ref and b.book_month = t.book_month);

                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Hash Join
   Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month))
   ->  Append
         ->  Seq Scan on tickets_list_201706 t
         ->  Seq Scan on tickets_list_201707 t_1
         ->  Seq Scan on tickets_list_201708 t_2
   ->  Hash
         ->  Append
               ->  Seq Scan on bookings_list_201706 b
               ->  Seq Scan on bookings_list_201707 b_1
               ->  Seq Scan on bookings_list_201708 b_2
(11 rows)

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

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

=> SET enable_partitionwise_join = ON;
=> EXPLAIN (COSTS OFF) 
   SELECT b.*
     FROM bookings_list b JOIN tickets_list t
          ON (b.book_ref = t.book_ref and b.book_month = t.book_month);

                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Append
   ->  Hash Join
         Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month))
         ->  Seq Scan on tickets_list_201706 t
         ->  Hash
               ->  Seq Scan on bookings_list_201706 b
   ->  Hash Join
         Hash Cond: ((t_1.book_ref = b_1.book_ref) AND (t_1.book_month = b_1.book_month))
         ->  Seq Scan on tickets_list_201707 t_1
         ->  Hash
               ->  Seq Scan on bookings_list_201707 b_1
   ->  Hash Join
         Hash Cond: ((t_2.book_ref = b_2.book_ref) AND (t_2.book_month = b_2.book_month))
         ->  Seq Scan on tickets_list_201708 t_2
         ->  Hash
               ->  Seq Scan on bookings_list_201708 b_2
(16 rows)

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

Похожая ситуация с агрегированием:

=> EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;

                            QUERY PLAN                             
-------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Append
                     ->  Parallel Seq Scan on bookings_list_201707
                     ->  Parallel Seq Scan on bookings_list_201708
                     ->  Parallel Seq Scan on bookings_list_201706
(8 rows)

Отметим, что сканирование секций может выполнятся параллельно. Но сначала объединяются секции, только потом начинается агрегация. А можно наоборот, выполнять агрегацию в каждой секции, затем объединить результат:

=> SET enable_partitionwise_aggregate = ON;
=> EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;

                            QUERY PLAN                             
-------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Parallel Append
               ->  Partial Aggregate
                     ->  Parallel Seq Scan on bookings_list_201707
               ->  Partial Aggregate
                     ->  Parallel Seq Scan on bookings_list_201708
               ->  Partial Aggregate
                     ->  Parallel Seq Scan on bookings_list_201706
(10 rows)

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

Секционирование по хешу

Третий способ разбиения таблицы — секционирование по хешу.

Создание таблицы:

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

В таком варианте book_ref, как ключ секционирвания, сразу можно объявлять первичным ключом.

Разобъем на три секции:

=> CREATE TABLE bookings_hash_p0 
       PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0);
=> CREATE TABLE bookings_hash_p1 
       PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 1);
=> CREATE TABLE bookings_hash_p2 
       PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 2);

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

=> INSERT INTO bookings_hash SELECT * FROM bookings;

INSERT 0 262788

Распределение строк по секциям происходит равномерно:

=> SELECT tableoid::regclass AS partition, count(*) FROM bookings_hash GROUP BY tableoid;

    partition     | count 
------------------+-------
 bookings_hash_p0 | 87649
 bookings_hash_p1 | 87651
 bookings_hash_p2 | 87488
(3 rows)

Новая команда для просмотра секционированных объектов:

=> \dP+

                                      List of partitioned relations
  Schema  |        Name        |  Owner  |       Type        |     Table      | Total size | Description 
----------+--------------------+---------+-------------------+----------------+------------+-------------
 bookings | bookings_hash      | student | partitioned table |                | 13 MB      | 
 bookings | bookings_list      | student | partitioned table |                | 15 MB      | 
 bookings | bookings_range     | student | partitioned table |                | 13 MB      | 
 bookings | tickets_list       | student | partitioned table |                | 50 MB      | 
 bookings | book_date_idx      | student | partitioned index | bookings_range | 5872 kB    | 
 bookings | bookings_hash_pkey | student | partitioned index | bookings_hash  | 5800 kB    | 
 bookings | bookings_list_pkey | student | partitioned index | bookings_list  | 8120 kB    | 
 bookings | tickets_list_pkey  | student | partitioned index | tickets_list   | 19 MB      | 
(8 rows)

=> VACUUM ANALYZE bookings_hash;

Подзапросы и соединения вложенными циклами

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

Распределение первых 10 бронирований по секциям:

=> WITH top10 AS (
     SELECT tableoid::regclass AS partition, * FROM bookings_hash ORDER BY book_ref LIMIT 10
   ) SELECT partition, count(*) FROM top10 GROUP BY 1 ORDER BY 1;

    partition     | count 
------------------+-------
 bookings_hash_p0 |     3
 bookings_hash_p1 |     3
 bookings_hash_p2 |     4
(3 rows)

Посмотрим на план выполнения запроса с соединением таблицы bookings_hash и предыдущего подзапроса:

=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) 
     WITH top10 AS (
         SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10
   ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Nested Loop (actual rows=10 loops=1)
   ->  Limit (actual rows=10 loops=1)
         ->  Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1)
               Heap Fetches: 0
   ->  Append (actual rows=1 loops=10)
         ->  Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3)
               Index Cond: (book_ref = bookings.book_ref)
         ->  Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3)
               Index Cond: (book_ref = bookings.book_ref)
         ->  Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4)
               Index Cond: (book_ref = bookings.book_ref)
 Planning Time: 0.632 ms
 Execution Time: 0.278 ms
(13 rows)

Выполняется соединение методом вложенных циклов. Внешний цикл по общему табличному выражению выполняется 10 раз. Но обратите внимание на количество обращений к таблицам-секциям(loops). Для каждого значения book_ref внешнего цикла просматривается только секция, где это значение хранится в таблице bookings_hash.

Сравните с отключенным исключением секций:

=> SET enable_partition_pruning TO OFF;
=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) 
     WITH top10 AS (
         SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10
   ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;

                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Nested Loop (actual rows=10 loops=1)
   ->  Limit (actual rows=10 loops=1)
         ->  Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1)
               Heap Fetches: 0
   ->  Append (actual rows=1 loops=10)
         ->  Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=0 loops=10)
               Index Cond: (book_ref = bookings.book_ref)
         ->  Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=0 loops=10)
               Index Cond: (book_ref = bookings.book_ref)
         ->  Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=0 loops=10)
               Index Cond: (book_ref = bookings.book_ref)
 Planning Time: 0.886 ms
 Execution Time: 0.771 ms
(13 rows)

=> RESET enable_partition_pruning;

Если сократить выборку до одного бронирования, то две секции вообще не будут просматриваться:

=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) 
     WITH top AS (
         SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 1
   ) SELECT bh.* FROM bookings_hash bh JOIN top ON bh.book_ref = top.book_ref;

                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Nested Loop (actual rows=1 loops=1)
   ->  Limit (actual rows=1 loops=1)
         ->  Index Only Scan using bookings_pkey on bookings (actual rows=1 loops=1)
               Heap Fetches: 0
   ->  Append (actual rows=1 loops=1)
         ->  Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=1)
               Index Cond: (book_ref = bookings.book_ref)
         ->  Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (never executed)
               Index Cond: (book_ref = bookings.book_ref)
         ->  Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (never executed)
               Index Cond: (book_ref = bookings.book_ref)
 Planning Time: 0.250 ms
 Execution Time: 0.090 ms
(13 rows)

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

=> CREATE OR REPLACE FUNCTION get_book_ref(top int) RETURNS SETOF bookings AS $$
       BEGIN
           RETURN QUERY EXECUTE 'SELECT * FROM bookings ORDER BY book_ref LIMIT $1' 
                        USING top;
       END;$$ LANGUAGE plpgsql STABLE;

=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) 
       SELECT * FROM bookings_hash bh JOIN get_book_ref(10) f ON bh.book_ref = f.book_ref;

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Nested Loop (actual rows=10 loops=1)
   ->  Function Scan on get_book_ref f (actual rows=10 loops=1)
   ->  Append (actual rows=1 loops=10)
         ->  Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3)
               Index Cond: (book_ref = f.book_ref)
         ->  Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3)
               Index Cond: (book_ref = f.book_ref)
         ->  Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4)
               Index Cond: (book_ref = f.book_ref)
 Planning Time: 0.175 ms
 Execution Time: 0.843 ms
(11 rows)

Итоги

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

Tags:

Hubs: