В общем и целом, postgresql при использовании во многом схож с mysql, однако есть много расхождений в хранении данных, соответствия стандарту SQL 2011, поддержке встраиваемых языков программирования, реализации объектной модели, поддержке нереляционных и слабо структурированных данных. Разумеется, это дает разницу как в программном использовании, производительности при таких типах нагрузки, как OLTP и OLAP, и разумеется администрировании.
Для таблиц, которым нужно монотонно возрастающее генерируемое значение, 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, и будет косяк при последующих миграциях.
Замечу, что указание типа 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, 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, и будет работать только при добавлении или изменении данных, а так же упростит написание миграций.
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, так что если есть возможность, генерить на стороне приложения.
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 много, лучше почитать в официальной документации.
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;
Старое расширение, которое до сих пор можно встретить в различных проектах и туториалах, однако ввиду появления jsonb является устаревшим и крайне не рекомендуется к использованию.
Тип money предназначен для хранения денежных сум, однако по сути является устаревшим и крайне не рекомендуется к использованию.
Вместо этого предлагается использовать тип общего назначения numeric , в котором можно задавать хранимую точность явно. Он примерно соответствует типа BigDecimal в различных языках программирования. Однако, в большинстве случаев, лучше точность не задавать, чтобы не потерять дробную часть при расчетах, иначе всегда придется явно кастить к numeric без ограничения типа, так же можно получить ошибку numeric field overflow
Postgresql предоставляет специальные типы и операции над ними для работы с адресами сетей. Очень полезно, когда нужно хранить списки каких-нибудь серверов, и искать ближайший например.
Во многом DDL postgresql совпадает с mysql, однако предлагает больше возможностей:
UNLOGGEDпозволяет создать нежурналируемую (и следовательно нереплицируемую) таблицу, полезно для создания каких-нибуть узких таблиц для промежуточной обработки данных, не нагружая репликацию избыточными данными. Относительно опасная штука, т.к при переключении мастера при падении могут возникнуть неожиданные ситуации, т.к таблица же не реплицируется.INHERITANCEпозволяет использовать такой мощный механизм, как наследование таблиц. До появления декларативного партицирования в postgresql 10 это часто использовали для реализации партицирования. Кроме оного, это подобно тому, как в обычных ЯП используется наследование классов с расширением типа. Если честно, то мне ни разу не понадобилось.PARTITIONINGпредоставляет очень мощный механизм, позволяющий эффективно работать с историческими данными, с таблицами огромных размеров, ускорять работу аналитических запросов, и многое другое.CHECKпозволяет описать практически любые выражения для проверки входных данных в таблицу. Выражения не могут содержать подзапросы или ссылаться на какие-либо переменные, кроме как на столбцы предыдущей версии строки.GENERATEDпозволяет задать декларативное вычисляемое поле, которое может даже не храниться физически в таблице.WITHпозволяет задать параметры хранения таблицы и индексов. Одним из важнейших параметров являетсяfillfactor, который для таблиц с частыми update и delete рекомендую задать явно, можно начать с 70TABLESPACEпозволяет определить, где именно хранить саму таблицу - в бд можно определить несколько tablespace и разнести их по различным дискам, таким образом можно изолировать нагрузку нагруженных таблиц или выделить медленное хранилище для архивных таблицUSING INDEX TABLESPACEаналогично, только для индексов
Начиная с postgresql 10 поддерживается декларативное партицирование (секционирование). Поддерживается партицирование по диапазонам значений (RANGE), хешу (HASH), либо по списку значений (LIST).
Далеко не всегда стоит использовать партицирование, т.к. в случае неудачного выбора ключа партицирования работа с бд может замедлиться. Ключ партицирования по хорошему должен практически всегда указан в запросах.
В случае аналитических запросов у партицирования множество плюсов, в ряде случаев можно выполнить ряд операций намного быстрее и эффективнее, с лучшей паралеллизацией запросов, чем с плоской таблицей. Хотя за все разумеется надо платить.
Используется в основном для исторических данных.
Для начала зададим партицированные таблицы:
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 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позволяет задать параметры хранения индекса, как и в случае таблиц большое значение имеет параметрfillfactorWHEREпозволяет задатьpartial index(частичный индекс), который будет индексировать только часть данных. Например, можно сделать индекс по ордерам в определенном статусе или для определенных shipping providers.
GIN - Generalized Inverted Index - индекс общего назначения, предназначен для работы со сложными композитными типами, из коробки предоставляет массу готовых операторов для работы с массивами, json и jsonb, tsvector. Расширения могут использовать этот индекс как бекенд.
Так же можно использовать для ускорения даже таких операций, как like '%some%' - pg_trgm.
GiST - Generalized Search Tree - так же индекс общего назначения, из коробки работает с такими типами данных, как box, circle, inet,cidr,point,polygon, tsquery, tsvector а так же типы, которые поддерживают диапазоны значений.
В общем и целом, использовать GIN и GiST следует с осторожностью, т.к запись в такие индексы, и обращение к ним куда дороже, чем b-tree.
Почему-то 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.
Во многом mysql и postgresql совпадают, однако есть и существенные различия - в postgresql поведение group by гораздо строже. Так же postgresql поддерживает CTE, в том числе рекурсивные
В отличии от 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 - 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 запросах.
Оконные функции в 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
В случае заливки и перезаливки внешних данных - 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
Если заранее не известно, есть ли в таблице нужная запись, можно использовать паттерн 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 позволяет вернуть значения, которые записались в таблицу в результате работы запроса - помогает избежать дополнительных запросов в бд для актуализации загруженных в приложение данных, можно работать по принципу 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;В целом, повторяет возможности INSERT в урезанном виде - так же поддерживает CTE и RETURNING.
[ WITH [ RECURSIVE ] запрос_WITH [, ...] ]
UPDATE [ ONLY ] имя_таблицы [ * ] [ [ AS ] псевдоним ]
SET { имя_столбца = { выражение | DEFAULT } |
( имя_столбца [, ...] ) = ( { выражение | DEFAULT } [, ...] ) |
( имя_столбца [, ...] ) = ( вложенный_SELECT )
} [, ...]
[ FROM список_FROM ]
[ WHERE условие | WHERE CURRENT OF имя_курсора ]
[ RETURNING * | выражение_результата [ [ AS ] имя_результата ] [, ...] ]Аналогично
[ WITH [ RECURSIVE ] запрос_WITH [, ...] ]
DELETE FROM [ ONLY ] имя_таблицы [ * ] [ [ AS ] псевдоним ]
[ USING список_USING ]
[ WHERE условие | WHERE CURRENT OF имя_курсора ]
[ RETURNING * | выражение_результата [ [ AS ] имя_результата ] [, ...] ]Специальный запрос для копирования данных между таблицами и файлами, в основном в формате CSV - самый главный плюс в том, что работа этого инструмента выполняется на порядок быстрее!
COPY имя_таблицы [ ( имя_столбца [, ...] ) ]
FROM { 'имя_файла' | PROGRAM 'команда' | STDIN }
[ [ WITH ] ( параметр [, ...] ) ]
[ WHERE условие ]
COPY { имя_таблицы [ ( имя_столбца [, ...] ) ] | ( запрос ) }
TO { 'имя_файла' | PROGRAM 'команда' | STDOUT }
[ [ WITH ] ( параметр [, ...] ) ]Использование STDIN/STDOUT позволяет работать через драйвер клиента postgresql, и на лету как читать поток CSV, так и записывать его в базу.
В случае вставки данных в таблицу, не получится использовать выражения ON CONFLICT - произойдет ошибка. Обходной путь - использование временных таблиц с последующей переливкой данных. Собственно, это и есть цена за скорость записи в таблицу.
Насколько мне известно, COPY отсутствует в стандартах SQL и других базах данных, так что vendor-lock имеет место. С другой стороны, ускорение вставки для какого-нить ETL просто божессно работает и сохраняет нервные клетки.
Postgesql поддерживает создание правил, которые могут на лету переписывать запросы. Однако, в целом это ад похлеще триггеров, и крайне не рекомендуется к использованию.
Как и другие бд, триггеры разумеется поддерживаются:
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, вторая может быть любой:
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 - и для каждой строки выполняет следующее:
- Проверяет, есть ли
hashот ключаjoinв хештаблице - Если нет, то строка игнорируется
- Если есть, то берутся строки из бакета и генерируется вывод строк
Важные моменты:
- Операции обоих сторон join выполняются ровно один раз
- Операции обоих сторон могут быть любыми -
Seq Scan,Index Scan,Index Only Scan, и т.д
Аналогично, имеет две субоперации, обе могут быть любыми:
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
Аналогично, имеет две субоперации, обе могут быть любыми, но должны возвращать отсортированные данные по 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может привести к сортировке на диске таблицы
- Официальная документация
- Перевод официальной документации от postgrespro
- Это как jsfiddle, только dbfiddle.uk
- Telegram группа pgsql
- Утилита визуализации плана запроса
- База данных запросов с explain