Comments 23
GiST работает медленней, чем btree, это факт.
Но триггер-то у вас неправильный, он может пропустить одновременную вставку взаимопересекающихся интервалов. А сделаете правильно — разрыв будет уже не такой драматичный.
Поясните свою мысль, пожалуйста. Откуда могут взяться взаимопересекающиеся интервалы в триггере FOR EACH ROW?
Даже проверил с перепугу:
INSERT INTO tmp_test_not_range (Id,
ValidFrom, ValidUntil, Code, Amt)
VALUES (0, '2020-04-01'::date, '2020-05-01'::date, 1, 1),
(0, '2020-04-20'::date, '2020-05-20'::date, 1, 1);
SQL Error [P0001]: ERROR: Id 0 ValidFrom 2020-04-20 intersect with ValidFrom 2020-04-01 and ValidUntil 2020-05-01
Where: PL/pgSQL function tmp_test_not_range_before_insert_update_tfn() line 25 at RAISE
GiST работает медленней, чем btree, это факт.
То что медленней, это понятно. Без триггера INSERT в таблицу с BTREE отработал бы за три секунды, вместо 12. А вот то, что даже с триггером, пожирающим 3/4 времени выполнения INSERT, BTREE выиграет в 4(!) раза - для меня самого было неожиданно.
разрыв будет уже не такой драматичный
Наоборот получилось. При переходе на CONSTRAINT INITIALLY DEFERRED триггер стало даже немного быстрее.
Ммм, не вижу изменений в тексте статьи, но если вы имеете в виду отложенный constraint trigger, то это ничем не поможет.
Можете привети пример? Я как ни пытался, но обойти его ограничения не смог.
Самый простой способ — вставьте в функцию PERFORM pg_sleep(10); после SELECT ... INTO и перед проверкой. А затем в двух терминалах вставьте по строке с пересекающимися датами.
Без задержки это тоже прекрасно воспроизводится, когда несколько потоков постоянно пишут в таблицу, но шансы, конечно, намного меньше.
Это я как раз делал. Но у меня же ситуация такая, что если запись X пересекается в диапазоне с записью Y, то и запись Y пересекается в диапазоне с записью X. Соответственно, валится по ошибке то, что коммитится позже.
Все исходники в статье. Неужели сложно просто привести здесь пример кода, который вставляет у Вас пересекающиеся диапазоны?
Проверочный запрос видит только уже зафиксированные строки. Поэтому если фиксация двух пересекающихся строк происходит плюс-минус одновременно, запросы в обоих триггерах ничего не обнаружат и в итоге будут вставлены обе строки. Хотя одна за другой они бы, конечно, не вставились.
Я использовал ровно приведенный в статье пример. Добавьте pg_sleep(10) и выполните:
в первой транзакции
INSERT INTO tmp_test_not_range (Id, ValidFrom, ValidUntil, Code, Amt) VALUES (1, '2024-01-01'::date, '2024-01-03'::date, 10, 10.0);в пределах 10 секунд во второй транзакции
INSERT INTO tmp_test_not_range (Id, ValidFrom, ValidUntil, Code, Amt) VALUES (1, '2024-01-02'::date, '2024-01-04'::date, 10, 10.0);
Спасибо!
Пока получилось этот пример решить следующим образом.
Создал таблицу заголовка и связал по внешнему ключу с ней версионированную таблицу:
CREATE TABLE tmp_test_range_header (
Id integer NOT NULL,
Description varchar NULL,
CONSTRAINT tmp_test_range_header_PK_idx PRIMARY KEY (Id)
);
CREATE TABLE tmp_test_not_range (
Id integer NOT NULL,
ValidFrom date NOT NULL,
ValidUntil date NULL,
Code integer NOT NULL,
Amt decimal(16,2) NOT NULL,
CONSTRAINT tmp_test_not_range_PK_idx
PRIMARY KEY (Id, ValidFrom) INCLUDE (ValidUntil),
CONSTRAINT tmp_test_not_range_FK_Id_idx
FOREIGN KEY (Id) REFERENCES tmp_test_range_header (Id)
);В таблицу tmp_test_range_header залил все необходимые записи.
Функцию триггера изменил следующим образом:
CREATE OR REPLACE FUNCTION
tmp_test_not_range_after_insert_update_tfn()
RETURNS TRIGGER AS $func$
<<func>>
DECLARE
ValidFrom date;
ValidUntil date;
BEGIN
IF NEW.ValidFrom>COALESCE(NEW.ValidUntil,NEW.ValidFrom) THEN
RAISE EXCEPTION 'ValidUntil must be higher or equal ValidFrom';
END IF;
LOCK TABLE tmp_test_range_header IN SHARE ROW EXCLUSIVE MODE;
SELECT F.ValidFrom, F.ValidUntil
FROM (
SELECT T.ValidFrom, T.ValidUntil
FROM tmp_test_not_range T
JOIN tmp_test_range_header H ON H.Id=T.Id
WHERE T.Id=NEW.Id AND T.ValidFrom<>NEW.ValidFrom
AND T.ValidFrom<=COALESCE(NEW.ValidUntil,'infinity'::date)
ORDER BY T.ValidFrom DESC
LIMIT 1 ) F
WHERE COALESCE(F.ValidUntil,'infinity'::date)>=NEW.ValidFrom
INTO func.ValidFrom, func.ValidUntil;
PERFORM pg_sleep(10);
IF func.ValidFrom IS NOT NULL THEN
RAISE EXCEPTION 'Id % ValidFrom % intersect with ValidFrom % and ValidUntil %',
NEW.Id, NEW.ValidFrom, func.ValidFrom, func.ValidUntil;
END IF;
RETURN NEW;
END;
$func$ LANGUAGE plpgsql;Теперь Ваш пример уже не позволяет вставить пересекающиеся диапазоны. Но цена этого - вставка миллиона записей стала уже не 11-12 секунд, а 19-20 секунд. Что все равно, более чем в два раза быстрее, чем в случае btree_gist.
Впрочем, можно попробовать еще сделать триггер FOR EACH STATEMENT. Возможно, он окажется быстрее, но в нем нужно будет проверять пересечение диапазонов еще и в new_table.
С LOCK TABLE можно было и в старом варианте сделать, без заголовочной таблицы. Но раз уж она появилась, то теперь достаточно блокировать в ней одну строку с нашим id. Однопоточная вставка от этого, скорее всего, только пострадает, зато многопоточная может выиграть.
Но в общем да, это я и имел в виду, когда говорил, что разрыв сократится.
С
LOCK TABLEможно было и в старом варианте сделать, без заголовочной таблицы.
А что блокировать то? Запись, которая еще не зафиксирована? Так я блокирую целиком Id, не позволяя одновременно один и тот же Id модифицировать в версионной таблице из разных соединений.
Ммм, сейчас в триггере блокируется вся заголовочная таблица. Можно было с тем же успехом блокировать всю основную таблицу.
А вот и нет. Так как основная таблица не блокируется, то блокировка распостраняется только на проверку в триггере, но не на модификацию основной таблицы до проверки.
Но записи действительно блокировать эффективней. Так?
CREATE OR REPLACE FUNCTION
tmp_test_not_range_after_insert_update_tfn()
RETURNS TRIGGER AS $func$
<<func>>
DECLARE
ValidFrom date;
ValidUntil date;
BEGIN
IF NEW.ValidFrom>COALESCE(NEW.ValidUntil,NEW.ValidFrom) THEN
RAISE EXCEPTION 'ValidUntil must be higher or equal ValidFrom';
END IF;
PERFORM H.Id
FROM tmp_test_range_header H
WHERE H.Id=NEW.Id
FOR NO KEY UPDATE OF H;
SELECT F.ValidFrom, F.ValidUntil
FROM (
SELECT T.ValidFrom, T.ValidUntil
FROM tmp_test_not_range T
WHERE T.Id=NEW.Id AND T.ValidFrom<>NEW.ValidFrom
AND T.ValidFrom<=COALESCE(NEW.ValidUntil,'infinity'::date)
ORDER BY T.ValidFrom DESC
LIMIT 1 ) F
WHERE COALESCE(F.ValidUntil,'infinity'::date)>=NEW.ValidFrom
INTO func.ValidFrom, func.ValidUntil;
-- PERFORM pg_sleep(10);
IF func.ValidFrom IS NOT NULL THEN
RAISE EXCEPTION 'Id % ValidFrom % intersect with ValidFrom % and ValidUntil %',
NEW.Id, NEW.ValidFrom, func.ValidFrom, func.ValidUntil;
END IF;
RETURN NEW;
END;
$func$ LANGUAGE plpgsql;
Да, примерно так, но это уже гомеопатия — без тестирования в реальных условиях не поймёшь, что окажется эффективнее.
Есть ещё рекомендательные блокировки, это совсем быстро, но с ними надо аккуратно.
не поймёшь, что окажется эффективнее
Более чем двукратный запас на модификацию - это очень много. А 20% выигрыша на выборке тоже никуда не делись.
Есть ещё рекомендательные блокировки, это совсем быстро, но с ними надо аккуратно.
Если речь про advisory_lock, то их количество ограничено max_locks_per_transaction * max_connections. В отличии от количества заблокированных строк, которое не лимитируется.
Попробовал на полном пересечии по Id без пересечений по диапазонам по миллиону записей в двух соединениях.
Для btree_gist вырос два запроса параллельно выполнялись 48 секунд. В случае с триггером и блокировками - 32 секунды. А это самый худший случай полного ожидания.
Все же на практике подобные таблицы обновляются либо одним потоком из Кафки, либо при модификации пользователями по одной записи. Массовые конкурентные обновления тут редки.
Но в общем да, это я и имел в виду, когда говорил, что разрыв сократится.
8 секунд добавилось, но разрыв в более чем в два раза - это все равно очень много. А с учетом 20% выигрыша на чтении - так вообще ставит крест на btree_gist. По крайней мере в этой области применения.
T.Id=NEW.Id AND NOT (T.Id=NEW.Id AND T.ValidFrom=NEW.ValidFrom)
Какое-то странное условие. Нет ли тут ошибки?
Потому что
Пусть A = T.Id=NEW.Id, B = T.ValidFrom=NEW.ValidFrom, тогда выражение можно записать так A & !(A & B), вносим отрицание внутрь скобок A & (!A | !B), по закону дистрибутивности A & !A | A & !B, первая часть по определению не выполняется, т.о. смысл имеет только A & !B (T.Id=NEW.Id AND T.ValidFrom!=NEW.ValidFrom).
Т.е. де-факто ищем текущую запись (равенство идентификаторов) с другим началом периода?
По сути (согласен с @erogov): Вы сделали собственное решение (которое всё ещё содержит ошибки), которое имплементирует только часть функциональности «btree_gist» и сравниваете скорость с полнофункциональным механизмом. Какой смысл?
Postgres — СУБД общего назначения, и её механизмы спроектированы и работают для всех представимых случаев использования (т.е. в среднем случае хуже, чем специально спроектированное решение под конкретную задачу). Это удивляет?
Если Вы точно знаете, как будет использоваться таблица, то — OK. Но проблема в том, что требования (и технические решения) меняются, и иногда — неожиданно и в предположении, что всё работает корректно: «это же БД с ACID!». Например, те же самые вставки в разных транзакциях: если скорость вставки вдруг перестаёт удовлетворять, приходится распараллеливать по разным connection-ам; и, если код к этому не готов (а он и в первоначальном варианте не был готов), то могут быть неожиданные проблемы.
Для простой выборки можно вылизать код, использующий B-Tree. Но для более сложной выборки код будет адовым и будет сложно доказать его корректность (а потом, когда потребуется, — и изменять; но чтобы ничего не поломалось!).
Для примера такой (условный и минималистичный) код (но я видел и писал большие запросы с цепочкой зависимостей и «valid»-интервалами, а не «звезды» от payment, как здесь):
SELECT
p.id,
p.stamp,
g.name,
c.name,
pr.name
FROM payment p
JOIN good g ON (g.valid @> p.stamp AND g.id = p.good_id)
JOIN company c ON (c.valid @> p.stamp AND c.id = p.company_id)
JOIN payer r ON (r.valid @> p.stamp AND r.id = p.payer_id)
WHERE ...
Типы range умеют не только поиск интервала, в котором есть значение, но и поиск пересечений диапазонов (оператор &&), и разных других отношений (стыковка: -|-; строго меньше: << и пр.), для которых будет использоваться индекс.
Можно, например, написать простой запрос: «какие были данные [с изменениями!] в этот месячный период по всем клиентам»:
SELECT * FROM tmp_test_range WHERE valid && daterange('2020-04-01', '2020-05-01')
и я не уверен, что сразу корректно напишу это с ValidFrom и ValidUntil.
Ещё одно соображение:
В случае, если есть не одно измерение изменений, а два (см. BiTemporal modeling[1]), то с «btree_gist» будет небольшое изменение в DDL и в DQL, в случае же с custom-ным решением — будет сложный код даже для [выборки из] одной таблицы.
BiTemporal таблица — это когда у неё два «valid»-поля: одно — бизнес изменения (например, разные название компаний-контрагентов), а второе — интервалы, когда об этом стало известно текущему бизнесу.
Неподдерживаемые feature-ы текущей custom-реализации (которые поддерживаются «btree_gist»):
Диапазоны бывают не только с открытой верхней границей, но и с открытой нижней (от «-∞» до некоторой даты или до «+∞» — полностью открытый интервал).
Нет полноценной поддержки INSERT (требуется начальное заполнение таблицы «…_header»; в тесте есть измерение заполнения таблицы, если в триггер вставить INSERT…ON CONFLICT DO NOTHING и это меняет timing-и).
Нет поддержки ACID при UPDATE/DELETE (если более конкретно — её часть «Isolation»).
Нет поддержки DEFERRABLE.
Пример с ошибкой вставки в «tmp_test_not_range»:
# INSERT INTO tmp_test_not_range (Id, ValidFrom, ValidUntil, Code, Amt)
VALUES
(0, '2020-04-20', '2020-04-30', 1, 1),
(0, '2020-04-30', '2020-05-01', 1, 1)
;
ERROR: Id 0 ValidFrom 2020-04-30 intersect with ValidFrom 2020-04-20 and ValidUntil 2020-04-30
А в «btree_gist» всё нормально:
# INSERT INTO tmp_test_range (Id, Valid, Code, Amt)
VALUES
(0, daterange('2020-04-20', '2020-04-30'), 1, 1),
(0, daterange('2020-04-30', '2020-05-01'), 1, 1)
;
INSERT 0 2
Пример с ошибкой при простом UPDATE:
# INSERT INTO tmp_test_not_range (Id, ValidFrom, ValidUntil, Code, Amt)
VALUES (10, '2020-04-20', '2020-04-30', 1, 1);
INSERT 0 1
# UPDATE tmp_test_not_range SET validfrom=validfrom-5, validuntil=validuntil-5 WHERE id=10;
ERROR: Id 10 ValidFrom 2020-04-15 intersect with ValidFrom 2020-04-20 and ValidUntil 2020-04-30
Пример ошибки с CONCURRENT DELETE:
-- init
# INSERT INTO tmp_test_not_range (Id, ValidFrom, ValidUntil, Code, Amt)
VALUES (1, '2020-04-20', '2020-04-30', 1, 1);
-- conn1
# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
# DELETE FROM tmp_test_not_range WHERE id=1;
DELETE 1
# -- NO "COMMIT" yet!
-- conn2
# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
# INSERT INTO tmp_test_not_range (Id, ValidFrom, ValidUntil, Code, Amt)
VALUES (1, '2020-04-21', '2020-04-30', 1, 1);
ERROR: Id 1 ValidFrom 2020-04-21 intersect with ValidFrom 2020-04-20 and ValidUntil 2020-04-30
но с «btree_gist» всё получается:
-- init
# INSERT INTO tmp_test_range (Id, Valid, Code, Amt)
VALUES (1, daterange('2020-04-20', '2020-04-30'), 1, 1);
-- conn1
# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
# DELETE FROM tmp_test_range WHERE id=1;
DELETE 1
# -- NO "COMMIT" yet!
-- conn2
# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
# INSERT INTO tmp_test_range (Id, Valid, Code, Amt)
VALUES (1, daterange('2020-04-21', '2020-04-30'), 1, 1);
-- conn1
# COMMIT
-- conn2
INSERT 0 1
Пример ошибки с CONCURRENT UPDATE:
-- init
# INSERT INTO tmp_test_not_range (Id, ValidFrom, ValidUntil, Code, Amt)
VALUES (2, '2020-04-20', '2020-04-30', 1, 1);
-- conn1
# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
# UPDATE tmp_test_not_range SET id=3 WHERE id=2;
# -- NO "COMMIT" yet!
-- conn2
# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
# INSERT INTO tmp_test_not_range (Id, ValidFrom, ValidUntil, Code, Amt)
VALUES (2, '2020-04-25', '2020-04-30', 1, 1);
ERROR: Id 2 ValidFrom 2020-04-25 intersect with ValidFrom 2020-04-20 and ValidUntil 2020-04-30
P.S.: для «btree_gist» работает корректно, не буду увеличивать длину комментария.
Ещё раз напомню, что всё вышеперечисленное уже работает в «btree_gist» (и без дополнительной таблицы; это ещё DEFERRABLE не касались).
—
Про скорость:
Тестирование на PG-16:
# INSERT INTO tmp_test_range…
INSERT 0 1000000
Time: 25638.124 ms (00:25.638)
# INSERT INTO tmp_test_not_range…
INSERT 0 1000000
Time: 21379.724 ms (00:21.380)
# INSERT INTO tmp_test_not_range… /* with internal INSERT INTO tmp_test_range_header */
INSERT 0 1000000
Time: 26812.624 ms (00:26.813)
# EXPLAIN ANALYZE
SELECT…
JOIN tmp_test_range
Planning Time: 0.279 ms
Execution Time: 136.775 ms
Time: 137.558 ms
# EXPLAIN ANALYZE
SELECT…
FROM tmp_test_not_range
Planning Time: 0.591 ms
Execution Time: 138.906 ms
Time: 140.276 ms
Но это не совсем корректно, потому что для «tmp_test_range» будет вычисляться hash, а это очень долго и для действительно больших таблиц крайне вредно. Более того, generate_series не замена множественным выборкам.
Для примера возьмём три значения
для таких запросов top-node будет одинаковый («Nested Loop»): 10000, 999990 и 10900.
# EXPLAIN ANALYZE
SELECT R.Id, R.Valid, R.Code, R.Amt
FROM (VALUES(10000),(999990),(10900)) G(n)
JOIN tmp_test_range R ON R.Id=G.n AND R.Valid@>'2023-06-12'::date;
…
Nested Loop (cost=0.29..24.98 rows=3 width=28) (actual time=0.027..0.044 rows=2 loops=1)
…
Planning Time: 0.095 ms
Execution Time: 0.058 ms
(6 rows)
Time: 0.408 ms
# EXPLAIN ANALYZE
SELECT R.Id, R.ValidFrom, R.ValidUntil, R.Code, R.Amt
FROM (VALUES(10000),(999990),(10900)) G(n)
CROSS JOIN LATERAL (
SELECT T.Id, T.ValidFrom, T.ValidUntil, T.Code, T.Amt
FROM tmp_test_not_range T
WHERE T.Id=G.n AND T.ValidFrom<='2023-06-12'::date
ORDER BY T.ValidFrom DESC
LIMIT 1) R
WHERE '2023-06-12'::date <= COALESCE(R.ValidUntil,'infinity'::date);
…
Nested Loop (cost=0.42..9.81 rows=3 width=23) (actual time=0.049..0.081 rows=2 loops=1)
…
Planning Time: 0.333 ms
Execution Time: 0.133 ms
(9 rows)
Time: 1.174 ms
Но разовые запуски могут давать сильный разброс, а полноценный тест делать лень.
—
Итого:
Таким образом, нам удалось выяснить, что btree_gist следует использовать с осторожностью.
Я думаю, что более корректная формулировка такая:
«btree_gist» может являться узким горлышком при запросах. Наверняка его можно как-то улучшить.
Но вот такие пользовательские решения использовать бы точно не стал. Как минимум, до момента, когда метрики начнут говорить, что именно эти запросы (вставки/выборки) начинают притормаживать (а там, скорее всего, потребуется переработка архитектуры проекта).
—
P.S.: несколько замечаний по статье:
1. При создании таблиц для PK лучше не использовать колонки с меняющимися бизнес-данными. То есть EXCLUDE — это всё-таки отдельный CONSTRAINT, не «tmp_test_range_PK_idx» (в «tmp_test_range»), и «tmp_test_not_range» тоже не должен быть PK.
После изменений статьи при copy-paste DDL-ей пользователь получит ошибку, т.к. поменялось название функции-триггера:
CREATE OR REPLACE FUNCTION
tmp_test_not_range_after_insert_update_tfn()
…
но
CREATE OR REPLACE TRIGGER
…
EXECUTE FUNCTION tmp_test_not_range_before_insert_update_tfn();
Съелась перавя «C» в «REATE TABLE tmp_test_range_header».
Есть таблица «tmp_test_not_range», «tmp_test_range» и «tmp_test_range_header», которая по названию относится как бы к «tmp_test_range», но на самом деле — к «…_not_range».
Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL