Skip to content

Instantly share code, notes, and snippets.

@mitallast
Last active December 6, 2019 11:58
Show Gist options
  • Save mitallast/bb14bfcbd94a7f7388c5e41c9a9169f1 to your computer and use it in GitHub Desktop.
Save mitallast/bb14bfcbd94a7f7388c5e41c9a9169f1 to your computer and use it in GitHub Desktop.

Postgresql 12: Best practices

В общем и целом, postgresql при использовании во многом схож с mysql, однако есть много расхождений в хранении данных, соответствия стандарту SQL 2011, поддержке встраиваемых языков программирования, реализации объектной модели, поддержке нереляционных и слабо структурированных данных. Разумеется, это дает разницу как в программном использовании, производительности при таких типах нагрузки, как OLTP и OLAP, и разумеется администрировании.

Data types

Sequence

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

Postgresql позволяет явно задавать sequence :

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] имя
    [ AS тип_данных ]
    [ INCREMENT [ BY ] шаг ]
    [ MINVALUE мин_значение | NO MINVALUE ] [ MAXVALUE макс_значение | NO MAXVALUE ]
    [ START [ WITH ] начало ] [ CACHE кеш ] [ [ NO ] CYCLE ]
    [ OWNED BY { имя_таблицы.имя_столбца | NONE } ]

Можно либо явно использовать sequence в различных запросах:

CREATE SEQUENCE serial START 101;
SELECT nextval('serial');
 nextval
---------
     101

Либо указать как default value для нужной таблицы и ее колонки:

CREATE SEQUENCE blog_id_seq;
CREATE TABLE blog (
  id BIGINT NOT NULL DEFAULT nextval('blog_id_seq')
);
ALTER SEQUENCE blog_id_seq OWNED BY blog.id;

Указание OWNED BY нужно только для автоматического удаления sequence при удалении поля из этой таблицы.

Разумеется, есть более простой способ:

CREATE TABLE blog (
  id BIGSERIAL
);

smallserial, serial и bigserial это ненастоящие типы, просто синтаксический сахар - в результате будет ровно то же самое, что и ручном создании с параметрами по дефолту.

Я предпочитаю создавать sequence руками, т.к. на тестовых базах данных иногда остаются старые sequence и тогда postgresql сгенерирует новое название для sequence, и будет косяк при последующих миграциях.

Primary Key

Замечу, что указание типа SERIAL не делает поле автоматически PRIMARY KEY, это нужно задавать явно CONSTRAINT:

CREATE TABLE blog (
  id SERIAL,
  CONSTRAINT blog_pkey PRIMARY KEY (id)
);

Разумеется, есть короткий синтаксис:

CREATE TABLE blog (
  id SERIAL PRIMARY KEY
);

Аналогично sequence, я предпочитаю задавать PRIMARY KEY явно, позже будет пояснение почему

Text и Varchar(n)

По сути, с точки хранения данных, между типами text,varchar, varchar(n) разницы нет, однако postgresql считает их разными типами, и в случае выполнения запросов над ними будет вынужден кастить (конвертировать) типы друг к другу:

SELECT 'test'::text = 'test'::varchar(4)

В этом примере мы пытаемся сравнить два разных типа, text и varchar(4), и postgresql будет их приводить к общему типу, в данном случае к text, и запрос с точки зрения планировщика будет выглядеть так:

SELECT 'test'::text = 'test'::varchar(4)::text

Это уменьшает производительность, создает проблемы при миграции данных и т.д. На практике лучше использовать практически всегда тип text а для задания ограничений на ту же длину использовать CONSTRAINT , что собственно и описано в стандартах sql:

CREATE TABLE blog (
  id BIGSERIAL,
  title TEXT NOT NULL,
  CONSTRAINT title_max_len CHECK (char_length(title) <= 255),
  CONSTRAINT title_min_len CHECK (char_length(title) >= 10)
);

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

UUID

Postgresql поддерживает тип UUID нативно:

CREATE TABLE external_request (
  id UUID NOT NULL PRIMARY KEY
);

INSERT INTO external_request (id) VALUES ('a81bc81b-dead-4e5d-abff-90865d1e13b1');

SELECT 'a81bc81b-dead-4e5d-abff-90865d1e13b1'::uuid;

Так же есть extension uuid-ossp, при помощи которого можно генерить значения:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();

           uuid_generate_v1
--------------------------------------
 0e37df36-f698-11e6-8dd4-cb9ced3df976
(1 row)

SELECT uuid_generate_v4();
 
           uuid_generate_v4
--------------------------------------
 a81bc81b-dead-4e5d-abff-90865d1e13b1
(1 row)

CREATE TABLE external_request (
  id UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4() 
)

Однако, генерация большого объема UUID это серьезная нагрузка для CPU, т.к большинство реализаций используют примитивы синхронизации и native entropy random generators, так что если есть возможность, генерить на стороне приложения.

Arrays

Postgresql позволяет объявлять array из существующих типов данных, хранить их в таблицах, предоставляет набор операций по ним, и так же поддержку индексов:

CREATE TABLE example (
  years integer[]
);

CREATE INDEX example_years_idx ON example USING GIN (years);

INSERT INTO example (years) VALUES (ARRAY[2007,2011,2019]);
INSERT INTO example (years) VALUES ('{2007,2020}')

-- search by concrete array offset
SELECT * FROM example WHERE years[1] = 2007;
-- search by intersects
SELECT * FROM example WHERE years && '{2007,2020}';
-- search by contains
SELECT * FROM example WHERE years @> '{2019}';

Возможностей использования array много, лучше почитать в официальной документации.

JSON & JSONB

Postgresql предоставляет поддержку типов json и jsonb. Исторически, тип json появился раньше, а jsonb является логичным продолжением ранее заложенных идей. С точки зрения запросов данных разницы между ними нет, однако есть значительная разница в производительности и эффективной поддержке операторов и индексов.

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

Postgresql 12 добавляет поддержку jsonpath для эффективных запросов с json/json.

Отдельно стоит упомянуть функцию jsonb_array_elements, которую иногда полезно применять при CROSS JOIN LATERAL:

CREATE TABLE tracking_parcel (
  tracking_number TEXT NOT NULL PRIMARY KEY,
  tracking_events JSONB NOT NULL
);

SELECT *
FROM tracking_parcel tp
CROSS JOIN LATERAL (
  SELECT *
  FROM jsonb_array_elements(tp.events) AS tpe(event)
) AS tpe;

HStore

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

Numeric & Money

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

Вместо этого предлагается использовать тип общего назначения numeric , в котором можно задавать хранимую точность явно. Он примерно соответствует типа BigDecimal в различных языках программирования. Однако, в большинстве случаев, лучше точность не задавать, чтобы не потерять дробную часть при расчетах, иначе всегда придется явно кастить к numeric без ограничения типа, так же можно получить ошибку numeric field overflow

CIDR, INET & MACADDR

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

Data Definition

Во многом DDL postgresql совпадает с mysql, однако предлагает больше возможностей:

  • UNLOGGED позволяет создать нежурналируемую (и следовательно нереплицируемую) таблицу, полезно для создания каких-нибуть узких таблиц для промежуточной обработки данных, не нагружая репликацию избыточными данными. Относительно опасная штука, т.к при переключении мастера при падении могут возникнуть неожиданные ситуации, т.к таблица же не реплицируется.
  • INHERITANCE позволяет использовать такой мощный механизм, как наследование таблиц. До появления декларативного партицирования в postgresql 10 это часто использовали для реализации партицирования. Кроме оного, это подобно тому, как в обычных ЯП используется наследование классов с расширением типа. Если честно, то мне ни разу не понадобилось.
  • PARTITIONING предоставляет очень мощный механизм, позволяющий эффективно работать с историческими данными, с таблицами огромных размеров, ускорять работу аналитических запросов, и многое другое.
  • CHECK позволяет описать практически любые выражения для проверки входных данных в таблицу. Выражения не могут содержать подзапросы или ссылаться на какие-либо переменные, кроме как на столбцы предыдущей версии строки.
  • GENERATED позволяет задать декларативное вычисляемое поле, которое может даже не храниться физически в таблице.
  • WITH позволяет задать параметры хранения таблицы и индексов. Одним из важнейших параметров является fillfactor, который для таблиц с частыми update и delete рекомендую задать явно, можно начать с 70
  • TABLESPACE позволяет определить, где именно хранить саму таблицу - в бд можно определить несколько tablespace и разнести их по различным дискам, таким образом можно изолировать нагрузку нагруженных таблиц или выделить медленное хранилище для архивных таблиц
  • USING INDEX TABLESPACE аналогично, только для индексов

PARTITIONING

Начиная с postgresql 10 поддерживается декларативное партицирование (секционирование). Поддерживается партицирование по диапазонам значений (RANGE), хешу (HASH), либо по списку значений (LIST).

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

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

Range partitioning

Используется в основном для исторических данных.

Для начала зададим партицированные таблицы:

CREATE TABLE purchase (
  id UUID NOT NULL,
  created_at DATE NOT NULL,
  buyer_id BIGINT NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE INDEX purchase_buyer_id_idx ON purchase (buyer_id);

Postgresql не позволяет создавать partition-wise (т.е такие, которые работают через все партишены) уникальные ключи, которые не включают в себя ключ партицирования, поэтому created_at включен в primary key. А вот неуникальные индексы можно спокойно задавать, поэтому индекс по buyer_id не содержит ключа партицирования.

Создадим партишены:

CREATE TABLE purchase_201901 PARTITION OF purchase
    FOR VALUES FROM ('2019-01-01') TO ('2019-01-02');
CREATE TABLE purchase_201901 PARTITION OF purchase
    FOR VALUES FROM ('2019-01-02') TO ('2019-01-03');
CREATE TABLE purchase_201901 PARTITION OF purchase
    FOR VALUES FROM ('2019-01-03') TO ('2019-01-04');

Hash partitioning

Как правило, используется для hash based партицирования:

CREATE TABLE tracking_event (
  tracking_number     TEXT      NOT NULL,
  datetime            DATE      NOT NULL,
  system              SMALLINT  NOT NULL,
  type                SMALLINT  NOT NULL,
) PARTITION BY HASH(tracking_number);

CREATE TABLE tracking_event_00 PARTITION OF tracking_event FOR VALUES WITH (MODULUS 5, REMAINDER 0);
CREATE TABLE tracking_event_01 PARTITION OF tracking_event FOR VALUES WITH (MODULUS 5, REMAINDER 1);
CREATE TABLE tracking_event_02 PARTITION OF tracking_event FOR VALUES WITH (MODULUS 5, REMAINDER 2);
CREATE TABLE tracking_event_03 PARTITION OF tracking_event FOR VALUES WITH (MODULUS 5, REMAINDER 3);
CREATE TABLE tracking_event_04 PARTITION OF tracking_event FOR VALUES WITH (MODULUS 5, REMAINDER 4);

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

Индексы

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] имя ] ON имя_таблицы [ USING метод ]
    ( { имя_столбца | ( выражение ) } [ COLLATE правило_сортировки ] [ класс_операторов ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( параметр_хранения = значение [, ... ] ) ]
    [ TABLESPACE табл_пространство ]
    [ WHERE предикат ]

Базовые описания индексов postgresql в целом соответствуют mysql, а так же дают расширенные возможности. Особняком стоят индексы общего назначения GIN и GiST, которые используются для работы со специальными типами данных, а так же для полнотекстового поиска (tsvector).

  • CONCURRENTLY позволяет создать индекс, не блокируя основную таблицу - очень полезно для OLTP нагрузки, где время даунтайма критично. Однако есть ряд особенностей, из-за которых операция не всегда может выполниться успешно.
  • WITH позволяет задать параметры хранения индекса, как и в случае таблиц большое значение имеет параметр fillfactor
  • WHERE позволяет задать partial index (частичный индекс), который будет индексировать только часть данных. Например, можно сделать индекс по ордерам в определенном статусе или для определенных shipping providers.

GIN

GIN - Generalized Inverted Index - индекс общего назначения, предназначен для работы со сложными композитными типами, из коробки предоставляет массу готовых операторов для работы с массивами, json и jsonb, tsvector. Расширения могут использовать этот индекс как бекенд.

Так же можно использовать для ускорения даже таких операций, как like '%some%' - pg_trgm.

GiST

GiST - Generalized Search Tree - так же индекс общего назначения, из коробки работает с такими типами данных, как box, circle, inet,cidr,point,polygon, tsquery, tsvector а так же типы, которые поддерживают диапазоны значений.

В общем и целом, использовать GIN и GiST следует с осторожностью, т.к запись в такие индексы, и обращение к ним куда дороже, чем b-tree.

Schemas

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

SELECT *
  FROM this_database.table_1 t1
  JOIN that_database.table_2 t2 ON t2.column = t1.column

Postgresql такое не позволяет. Вместо этого, как и в других базах данных, рекомендуется использовать одну базу и множество scheme в ней:

CREATE SCHEMA IF NOT EXISTS oltp;
CREATE TABLE oltp.order (<...>);

CREATE SCHEMA IF NOT EXISTS etl;
CREATE TABLE etl.order_history (<...>);

SELECT * FROM oltp.order LEFT JOIN etl.order_history ON <...>;

Если же прямо есть острая необходимость работать с разными бд, физически расположенными на разных серверах, есть расширение FDW - Foreign data wrapper.

Queries

Во многом mysql и postgresql совпадают, однако есть и существенные различия - в postgresql поведение group by гораздо строже. Так же postgresql поддерживает CTE, в том числе рекурсивные

Group by

В отличии от mysql, postgresql не может вернуть значения, которые не участвуют в группировке, и такой запрос вызовет ошибку:

CREATE TABLE log (
  user_id BIGINT,
  amount  INT
);

SELECT user_id, amount
FROM log
GROUP BY user_id;

В этом примере поле amount не используется ни в group by, ни в агрегатных функциях, а следовательно не определена логика, по которой можно понять, какое должно быть значение - по стандартам sql сервер в праве в абсолютно любом порядке выполнять запрос.

В подобных ситуациях можно использовать оператор DISTINCT ON:

SELECT DISTINCT ON (user_id) amount
FROM log
ORDER BY user_id, amount DESC;

Выражения DISTINCT ON обязаны соответствовать самым левым выражениям ORDER BY, иначе получится недетерминированный результат. Для агрегатов можно использовать оконные функции.

Так же можно задать в элементе группировки GROUPING SETS, ROLLUP или CUBE, которые очень полезны при аналитических запросах для промежуточных и финальных итогов, что позволит избежать многократного выполнения запроса или ручной обработки результата.

CTE

CTE - Common Table Expressions - мощный синтаксис для описания именованных подзапросов, которые могут выполнять практически любые Query и Data Manipulation выражения:

WITH имя_запроса [ ( имя_столбца [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( выборка | values | insert | update | delete )

Пример использования в аналитических запросах:

CREATE TABLE buyer (id SERIAL PRIMARY KEY);
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  buyer_id INT NOT NULL,
  created_at DATE NOT NULL,
  status TEXT NOT NULL, 
  amount NUMERIC NOT NULL
);

WITH orders_stats AS (
  SELECT buyer_id, created_at, SUM(amount) as total_amount
  FROM orders
  GROUP BY 1, 2
)
SELECT * 
FROM buyers AS b
INNER JOIN orders_stats AS s ON s.buyer_id = b.id;

MATERIALIZED дает указание планировщику, как именно выполнять CTE выражение - inline или materialized, это может в ряде случаев ускорить выполнение запроса.

CTE выражения можно использовать и в Data Manipulation запросах.

Window functions

Оконные функции в mysql появились только в версии 8.0, в ветку релизов 5.* бэкпорта пока что не видел.

Postgresql же предоставляет данный инструмент в полной мере. Вызов оконной функции имеет вид:

имя_функции ([выражение [, выражение ... ]]) [ FILTER ( WHERE предложение_фильтра ) ] OVER имя_окна
имя_функции ([выражение [, выражение ... ]]) [ FILTER ( WHERE предложение_фильтра ) ] OVER ( определение_окна )
имя_функции ( * ) [ FILTER ( WHERE предложение_фильтра ) ] OVER имя_окна
имя_функции ( * ) [ FILTER ( WHERE предложение_фильтра ) ] OVER ( определение_окна )

Здесь определение_окна записывается в виде:

[ имя_существующего_окна ]
[ PARTITION BY выражение [, ...] ]
[ ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ определение_рамки ]

Необязательное определение_рамки может иметь вид:

{ RANGE | ROWS | GROUPS } начало_рамки [ исключение_рамки ]
{ RANGE | ROWS | GROUPS } BETWEEN начало_рамки AND конец_рамки [ исключение_рамки ]

Здесь начало_рамки и конец_рамки задаются одним из следующих способов:

UNBOUNDED PRECEDING
смещение PRECEDING
CURRENT ROW
смещение FOLLOWING
UNBOUNDED FOLLOWING

и исключение_рамки может быть следующим:

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

В качестве оконной функции может быть использована например агрегатная функция, например SUM для классической задачи подсчета итогового баланса пользователя:

CREATE TABLE credits (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  amount  NUMERIC NOT NULL
);

INSERT INTO credits(user_id, amount) VALUES
(1,10),
(1,-11),
(1,12),
(2,100),
(2,-300),
(2,500);

-- определение окна inline
SELECT id, user_id, amount, 
SUM(amount) OVER (PARTITION BY user_id ORDER BY id ASC) as balance
FROM credits;

-- определение окна через WINDOW
SELECT id, user_id, amount, 
SUM(amount) OVER w as balance
FROM credits
WINDOW w AS (PARTITION BY user_id ORDER BY id ASC);

 id | user_id | amount | balance
----+---------+--------+---------
  1 |       1 |     10 |      10
  2 |       1 |    -11 |      -1
  3 |       1 |     12 |      11
  4 |       2 |    100 |     100
  5 |       2 |   -300 |    -200
  6 |       2 |    500 |     300
(6 rows)

Так же есть предопределенные оконные функции общего назначения, такие как row_number(), rank(), lag(), lead(), first_value(), last_value() и другие. Например, можно решить задачу fill gaps (заполнения пропусков):

CREATE TABLE price_history (
  product_id     INT NOT NULL,
  logged_at      DATE NOT NULL,
  price          NUMERIC NOT NULL,
  
  PRIMARY KEY (product_id, logged_at)
);

INSERT INTO price_history (product_id, logged_at, price) VALUES
(1, '2019-11-01', 100),
(1, '2019-11-04', 110),
(1, '2019-11-06', 120),
(2, '2019-11-01', 400),
(2, '2019-11-04', 410),
(2, '2019-11-06', 420);


WITH dates(date) AS (
  SELECT generate_series('2019-11-01'::date, '2019-11-07'::date, '1 day'::interval)
),
products AS (
  SELECT DISTINCT product_id FROM price_history
),
histogram AS (
  SELECT p.product_id, d.date::date
  FROM dates as d
  FULL OUTER JOIN products as p ON true
)
SELECT * FROM (
  SELECT 
    product_id, 
    date, 
    first_value(price) OVER (PARTITION BY product_id, value_partition ORDER BY date) AS price
  FROM (
    SELECT
     h.product_id, 
     h.date,
     SUM(CASE WHEN p.price IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY h.product_id ORDER BY h.date) AS value_partition,
     price
    FROM histogram h
    LEFT JOIN price_history p ON p.logged_at = h.date AND p.product_id = h.product_id
  ) as t
) as t;

 product_id |    date    | price
------------+------------+-------
          1 | 2019-11-01 |   100
          1 | 2019-11-02 |   100
          1 | 2019-11-03 |   100
          1 | 2019-11-04 |   110
          1 | 2019-11-05 |   110
          1 | 2019-11-06 |   120
          1 | 2019-11-07 |   120
          2 | 2019-11-01 |   400
          2 | 2019-11-02 |   400
          2 | 2019-11-03 |   400
          2 | 2019-11-04 |   410
          2 | 2019-11-05 |   410
          2 | 2019-11-06 |   420
          2 | 2019-11-07 |   420

Data Manipulation

INSERT

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

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER} VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

Основным отличием от mysql является поддержка выражений ON CONFLICT и RETURNING

ON CONFLICT

Если заранее не известно, есть ли в таблице нужная запись, можно использовать паттерн INSERT .. ON CONFLICT ... DO UPDATE или DO NOTHING:

CREATE TABLE tracking_event (
  external_id					UUID      NOT NULL PRIMARY KEY,
  tracking_number     TEXT      NOT NULL,
  datetime            DATE      NOT NULL,
  system              SMALLINT  NOT NULL,
  type                SMALLINT  NOT NULL
);

-- если данные immutable
INSERT INTO tracking_event(external_id, tracking_number, datetime, system, type)
VALUES (<...>), (<...>), (<...>), (<...>)
ON CONFLICT DO NOTHING;

-- если данные мутабельны
INSERT INTO tracking_event(external_id, tracking_number, datetime, system, type)
VALUES (<...>), (<...>), (<...>), (<...>)
ON CONFLICT (external_id) DO UPDATE
SET 
  tracking_number = EXCLUDED.tracking_number,
  datetime = EXCLUDED.datetime,
  system = EXCLUDED.system,
  type = EXCLUDED.type
WHERE type <> 1;

Если задано выражение WHERE, то строки, которые отфильтрованы предикатом, будут выполнены как DO NOTHING.

RETURNING

Выражение RETURNING позволяет вернуть значения, которые записались в таблицу в результате работы запроса - помогает избежать дополнительных запросов в бд для актуализации загруженных в приложение данных, можно работать по принципу immutable DTO. Если какая-то строка не записалась из-за выражения ON CONFLICT или WHERE, значит эта строка не будет возвращена через RETURNING:

CREATE SEQUENCE test_id_seq;
CREATE TABLE test (
  id INT NOT NULL PRIMARY KEY DEFAUL nextval('test_id_seq'),
  title TEXT NOT NULL
);

INSERT INTO test(title) 
VALUES ('hello'), ('world') 
RETURNING id;
 id
---------
       1
---------
       2

(2 rows)
       
INSERT INTO test(id, title) 
VALUES (1, 'hello'), (2, 'world') 
ON CONFLICT DO NOTHING
RETURNING id;
 id
---------

(0 rows)

INSERT INTO test(id, title) 
VALUES (1, 'hello 2'), (2, 'world 2') 
ON CONFLICT (id) DO UPDATE
SET title = EXCLUDED.title
WHERE id = 1
RETURNING id, title;
 id      | title
---------+---------
       1 | hello 2

(1 rows)

Так же можно использовать CTE:

CREATE TABLE test_history (
  id INT NOT NULL PRIMARY KEY,
  title TEXT NOT NULL
);

WITH old_orders (
  SELECT id, title FROM test
  FOR UPDATE
)
INSERT INTO test (id, title)
SELECT id, title FROM old_orders
ON CONFLICT DO NOTHING
RETURNING id;

UPDATE

В целом, повторяет возможности INSERT в урезанном виде - так же поддерживает CTE и RETURNING.

[ WITH [ RECURSIVE ] запрос_WITH [, ...] ]
UPDATE [ ONLY ] имя_таблицы [ * ] [ [ AS ] псевдоним ]
    SET { имя_столбца = { выражение | DEFAULT } |
          ( имя_столбца [, ...] ) = ( { выражение | DEFAULT } [, ...] ) |
          ( имя_столбца [, ...] ) = ( вложенный_SELECT )
        } [, ...]
    [ FROM список_FROM ]
    [ WHERE условие | WHERE CURRENT OF имя_курсора ]
    [ RETURNING * | выражение_результата [ [ AS ] имя_результата ] [, ...] ]

DELETE

Аналогично

[ WITH [ RECURSIVE ] запрос_WITH [, ...] ]
DELETE FROM [ ONLY ] имя_таблицы [ * ] [ [ AS ] псевдоним ]
    [ USING список_USING ]
    [ WHERE условие | WHERE CURRENT OF имя_курсора ]
    [ RETURNING * | выражение_результата [ [ AS ] имя_результата ] [, ...] ]

COPY

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

COPY имя_таблицы [ ( имя_столбца [, ...] ) ]
    FROM { 'имя_файла' | PROGRAM 'команда' | STDIN }
    [ [ WITH ] ( параметр [, ...] ) ]
    [ WHERE условие ]

COPY { имя_таблицы [ ( имя_столбца [, ...] ) ] | ( запрос ) }
    TO { 'имя_файла' | PROGRAM 'команда' | STDOUT }
    [ [ WITH ] ( параметр [, ...] ) ]

Использование STDIN/STDOUT позволяет работать через драйвер клиента postgresql, и на лету как читать поток CSV, так и записывать его в базу.

В случае вставки данных в таблицу, не получится использовать выражения ON CONFLICT - произойдет ошибка. Обходной путь - использование временных таблиц с последующей переливкой данных. Собственно, это и есть цена за скорость записи в таблицу.

Насколько мне известно, COPY отсутствует в стандартах SQL и других базах данных, так что vendor-lock имеет место. С другой стороны, ускорение вставки для какого-нить ETL просто божессно работает и сохраняет нервные клетки.

Rules

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

Triggers

Как и другие бд, триггеры разумеется поддерживаются:

CREATE [ CONSTRAINT ] TRIGGER имя { BEFORE | AFTER | INSTEAD OF } { событие [ OR ... ] }
    ON имя_таблицы
    [ FROM ссылающаяся_таблица ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] имя_переходного_отношения } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( условие ) ]
    EXECUTE { FUNCTION | PROCEDURE } имя_функции ( аргументы )

Здесь допускается событие:

    INSERT
    UPDATE [ OF имя_столбца [, ... ] ]
    DELETE
    TRUNCATE

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

CREATE TABLE orders (
  id     SERIAL   NOT NULL, 
  status SMALLINT NOT NULL, 
  amount NUMERIC  NOT NULL
);

CREATE TABLE order_history (
  logged_at TIMESTAMP NOT NULL DEFAULT NOW(),
  id     		SERIAL 		NOT NULL, 
  status 		SMALLINT 	NOT NULL, 
  amount 		NUMERIC 	NOT NULL,
);

CREATE OR REPLACE FUNCTION orders_on_update()
RETURNS trigger AS $$
BEGIN
    INSERT INTO order_history(id, status, amount)
    SELECT id, status, amount FROM newrows;

    RETURN NULL;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS orders_on_update ON orders;
CREATE TRIGGER orders_on_update
    AFTER UPDATE
    ON orders
    REFERENCING NEW TABLE AS newrows
    FOR EACH STATEMENT
    EXECUTE PROCEDURE orders_on_update();

Оптимизация запросов

EXPLAIN позволяет анализировать не только работу SELECT запросов, но и DML запросы, INSERT,UPDATE,DELETE

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }
  • ANALYZE - непосредственно выполняет запрос, и показывает реальные тайминги и прочую статистику. Важно - если выполнить DML запрос с ANALYZE, то он тоже выполнится и данные будут изменены, так что лучше оборачивать в BEGIN и ROLLBACK, а в опциях IDE лучше отключить автокоммит
  • BUFFERS позволяет анализировать использование памяти
  • FORMAT полезен для последующей визуализации плана запроса, особенно JSON

Оптимизация запросов

Большинство операций имеют несколько субопераций, план которых можно получить при помощи explain.

Hash Join:

Имеет две субоперации, одна всегда Hash, вторая может быть любой:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM pg_class c 
JOIN pg_namespace n ON c.relnamespace = n.oid;

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1.14..19.50 rows=370 width=375) (actual time=0.059..0.305 rows=350 loops=1)
   Hash Cond: (c.relnamespace = n.oid)
   Buffers: shared hit=17
   ->  Seq Scan on pg_class c  (cost=0.00..16.70 rows=370 width=262) (actual time=0.009..0.077 rows=350 loops=1)
         Buffers: shared hit=13
   ->  Hash  (cost=1.06..1.06 rows=6 width=117) (actual time=0.033..0.033 rows=6 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=1
         ->  Seq Scan on pg_namespace n  (cost=0.00..1.06 rows=6 width=117) (actual time=0.005..0.011 rows=6 loops=1)
               Buffers: shared hit=1
 Planning Time: 2.119 ms
 Execution Time: 0.384 ms
(12 rows)

Сначала Hash Join вызвывает операциюHash, которая в свою очередь вызывает какую-нибуть другую операцию - в нашем случае, Seq Scan по pg_namespace - и строит по нему хештаблицу размером в 1024 бакета в памяти. Если памяти недостаточно, то будет использоваться диск, где аналогично будут храниться бакеты хештаблицы.

Далее Hash Join вызывает вторую операцию - в нашем случае Seq Scan по pg_class - и для каждой строки выполняет следующее:

  1. Проверяет, есть ли hash от ключа join в хештаблице
  2. Если нет, то строка игнорируется
  3. Если есть, то берутся строки из бакета и генерируется вывод строк

Важные моменты:

  • Операции обоих сторон join выполняются ровно один раз
  • Операции обоих сторон могут быть любыми - Seq Scan, Index Scan, Index Only Scan, и т.д
Nested Loop Join:

Аналогично, имеет две субоперации, обе могут быть любыми:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT a.* FROM pg_class c 
JOIN pg_attribute a ON c.oid = a.attrelid 
WHERE c.relname in ( 'pg_class', 'pg_namespace' );
                                                                                                                                                
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.55..15.51 rows=14 width=238) (actual time=0.178..0.246 rows=50 loops=1)
   Buffers: shared hit=13
   ->  Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.27..4.57 rows=2 width=4) (actual time=0.153..0.161 rows=2 loops=1)
         Index Cond: (relname = ANY ('{pg_class,pg_namespace}'::name[]))
         Buffers: shared hit=5
   ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.28..5.39 rows=8 width=238) (actual time=0.011..0.022 rows=25 loops=2)
         Index Cond: (attrelid = c.oid)
         Buffers: shared hit=8
 Planning Time: 0.424 ms
 Execution Time: 0.346 ms
(10 rows)

Сначала Nested Loop запускает первую субоперацию объединения один раз - в нашем случае Index Scan по pg_class - затем, для каждой возвращенной строки каждый раз заново запускает вторую операцию - в нашем случае, Index Scan по pg_attribute. У второй операции в выводе плана запроса есть атрибут loops=2, это значит что операция запускалась два раза, прочие атрибуты являются средними значениями.

Важные моменты:

  • Хорошо работает при малом количестве loop, в худшем случае может выродиться в довольно тяжелые запросы, поэтому важно запускать с ANALYZE
Merge Join

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

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM
( SELECT oid, * FROM pg_class ORDER BY oid) AS c
JOIN
( SELECT * FROM pg_attribute a ORDER BY attrelid) AS a
ON c.oid = a.attrelid;

                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.55..193.59 rows=2804 width=504) (actual time=0.062..4.975 rows=2634 loops=1)
   Merge Cond: (pg_class.oid = a.attrelid)
   Buffers: shared hit=294
   ->  Index Scan using pg_class_oid_index on pg_class  (cost=0.27..22.82 rows=370 width=266) (actual time=0.024..0.450 rows=350 loops=1)
         Buffers: shared hit=113
   ->  Materialize  (cost=0.28..130.56 rows=2592 width=238) (actual time=0.030..2.090 rows=2634 loops=1)
         Buffers: shared hit=181
         ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.28..98.16 rows=2592 width=238) (actual time=0.023..1.270 rows=2634 loops=1)
               Buffers: shared hit=181
 Planning Time: 0.449 ms
 Execution Time: 5.453 ms
(11 rows)

Аналогично, Merge Join запускает две субоперации, одновременно - в нашем случае это Index Scan и Materialize соответственно сторонам объединения - и далее оба набора данных одновременно сканируются, проверяя совпадают ли ключи join.

Важные моменты:

  • Относительно редкая операция объединения
  • Требование, что обе стороны объединения должны быть отсортированы, на практике означает либо принудительную сортировку, либо явную через ORDER BY - в случае больших таблиц, аналитических запросах, неудачно написанных запросах с MATERIALIZED может привести к сортировке на диске таблицы

Полезные ссылки:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment