Представим, что у вас есть некоторая табличка статистики, куда вы периодически скидываете таймстамп последнего "текущего" состояния в паре координат - например, (ID организации, ID сотрудника).
Как больно наступить на грабли в совсем простом, казалось бы, запросе?
Вначале он обычно выглядит примерно как-то вот так:
INSERT INTO activity(
fk1
, fk2
, ts
)
VALUES
('8d335b64-8597-464c-affb-b4734bc67acc', '198d636e-1b35-4a20-a42a-38ec9abc47ee', now())
, ('8d335b64-8597-464c-affb-b4734bc67acc', '4fa31f97-c5ea-40a3-8a4b-1df598db39ca', now())
, ('8d335b64-8597-464c-affb-b4734bc67acc', '6b46ec7d-70c4-4b6d-b6bc-4a0a6cf45051', now())
, ('8d335b64-8597-464c-affb-b4734bc67acc', 'fc41beb4-f67d-45f4-b37a-c71cfe530f43', now())
, ('8d335b64-8597-464c-affb-b4734bc67acc', 'c868affb-3a8c-4692-92de-2a952134d52f', now())
, ('8d335b64-8597-464c-affb-b4734bc67acc', '75abe193-ab1f-41b3-8fc8-cd8f34b269f2', now())
, ('a227a9ee-bb05-48d6-a885-94f7cea06cd8', '388179c4-594e-4088-bb56-c99ff4df7efe', now())
, ('8d335b64-8597-464c-affb-b4734bc67acc', 'd8b0af23-53a5-45cb-8b08-7f27b04faa6d', now())
, ('8d335b64-8597-464c-affb-b4734bc67acc', '75aa761d-28da-4b9d-b5d5-a74a60725c36', now())
-- ... +100500 строк
ON CONFLICT
(fk1, fk2)
DO UPDATE SET
ts = greatest(activity.ts, EXCLUDED.ts);Давайте разберем те проблемы, которые создал таким запросом разработчик как для себя, так и для своей базы.
Проблема #1: deadlock при конкурентной вставке
LOG: process 19607 still waiting for ShareLock on transaction 3456521168 after 1000.105 ms
DETAIL: Process holding the lock: 18492. Wait queue: 19607.
CONTEXT: while inserting index tuple (16124,49) in relation "activity"Казалось бы, ничто не предвещало - у нас ведь "обычный INSERT"?
На самом деле, не совсем обычный - обратите внимание на часть DO UPDATE SET - то есть при наличии в таблице записи с той же ключевой парой (fk1, fk2), она должна быть обновлена.
И тут мы возвращаемся к проблеме плохо упорядоченных обновлений, о которой я рассказывал в статье "Борем deadlock при пакетных UPDATE".
Там же описано и решение - упорядочить обновляемые записи заранее. В нашем случае мы не знаем достоверно, какие из записей захотят обновляться, а какие будут вставлены впервые, поэтому правильным решением будет отсортировать заранее все.
Проблема #2: выполнение функции на каждой записи
В каждой записи у нас указана функция now(), которая в рамках одного запроса всегда возвращает одно и то же значение.
Если бы разработчик хотел действительно разные "реальные" значения, то использовал бы
clock_timestamp(). Подробнее про разные функции "сейчас" можно почитать в "SQL HowTo: наперегонки со временем".
Значит, у нас налицо просто техническая ошибка с повторным вычислением одного и того же, которая нам чего-то да стоила. Давайте попробуем оценить ее величину, вычислив now() 10M раз:
EXPLAIN (ANALYZE, COSTS OFF)
SELECT i, now() FROM generate_series(1, 1e7) i;Function Scan on generate_series i (actual time=2464.211..3729.080 rows=10000000 loops=1)
Planning Time: 0.068 ms
Execution Time: 4176.732 ms... или не вычислив:
EXPLAIN (ANALYZE, COSTS OFF)
SELECT i, NULL::timestamptz FROM generate_series(1, 1e7) i;Function Scan on generate_series i (actual time=2191.738..3374.842 rows=10000000 loops=1)
Planning Time: 0.035 ms
Execution Time: 3785.943 msНе сказать, что разница грандиозна, но ~400мс на 10M вызовов тут потрачены - это нам очень повезло, что now() достаточно "легкая", но все-таки лучше эти вызовы не клонировать.
Проблема #3: повторяющиеся значения в теле запроса
Очевидно, что организаций у нас всегда будет много меньше, чем сотрудников в них, поэтому значения ID организации будут повторяться достаточно часто.
Этой темы я уже подробно касался в статье "PostgreSQL Antipatterns: «слишком много золота»", и приведенное там решение достаточно тривиально - собрать все значения в один JSON-параметр, клонируя как можно меньше данных.
В нашем случае его структура будет примерно такой:
{
fk1_1 : [fk2_1, fk2_2]
, fk1_2 : [fk2_3, fk2_4, fk2_5]
}Избавляемся от проблем
Так как же, в итоге, должен выглядеть идеальный запрос для подобной задачи?
Примерно как-то вот так:
INSERT INTO activity(
fk1
, fk2
, ts
)
SELECT
fk1
, json_array_elements_text(fk2s) fk2 -- разворачиваем json-массив
, ts
FROM
json_each($1::json) T(fk1, fk2s) -- разворачиваем ключи json-объекта
, now() ts -- STABLE-функция вызовется однократно
ORDER BY
1, 2 -- сортируем по (fk1, fk2), чтобы гарантировать совпадение порядка
ON CONFLICT
(fk1, fk2)
DO UPDATE SET
ts = greatest(activity.ts, EXCLUDED.ts);
/*
$1 = {
"8d335b64-8597-464c-affb-b4734bc67acc" : [
"198d636e-1b35-4a20-a42a-38ec9abc47ee"
, "4fa31f97-c5ea-40a3-8a4b-1df598db39ca"
, "6b46ec7d-70c4-4b6d-b6bc-4a0a6cf45051"
]
, "a227a9ee-bb05-48d6-a885-94f7cea06cd8" : [
"388179c4-594e-4088-bb56-c99ff4df7efe"
]
}
*/Комментарии (29)
Mingun
01.12.2022 17:55Значит, у нас налицо просто техническая ошибка с повторным вычислением одного и того же, которая нам чего-то да стоила.
Неужели оптимизатор в PG настолько плохой, что не может вычислить эту функцию один раз? В чем гипотетический смысл повторных вычислений, если она
в рамках одного запроса всегда возвращает одно и то же значение.
? Ну кроме проведения бенчмарков по переносу ее с левой стороны FROM на правую?
Причем нужно вспомнить, что SQL — декларативный язык, и странно на нем оперировать императивными подходами. Проще тогда было бы взять нормальный язык, а не на птичьих диалектах SQL чего-то выражать.
Kilor Автор
01.12.2022 18:02+1Неужели оптимизатор в PG настолько плохой, что не может вычислить эту функцию один раз?
"Вычислит"-то он ее один раз (в смысле, сходит и посмотрит в системный таймер), а вот подставить это значение придется 100500 раз - в каждую из строк.
Причем нужно вспомнить, что SQL — декларативный язык, и странно на нем оперировать императивными подходами. Проще тогда было бы взять нормальный язык, а не на птичьих диалектах SQL чего-то выражать.
Видимо, есть некоторый универсальный язык, которым и БЛ писать, и с базой работать, и микроконтроллеры программировать, и "словами говорить" - все одинаково удобно... Жаль, я про него не знаю.
Mingun
01.12.2022 18:30+3а вот подставить это значение придется 100500 раз — в каждую из строк.
А есть какой-то иной способ записать значения 100500 строк?
универсальный язык
Зачем же универсальный. Я говорю, что ненормально сначала разрабатывать язык для декларативного описания того, что мы хотим получить, а затем натягивать на него описания того, как мы это хотим получить. Для как существуют традиционные императивные языки. В декларативном SQL же вы только боритесь с самим языком таким подходом, пытаясь впихнуть невпихуемое.
Kilor Автор
01.12.2022 18:55какой-то иной способ записать значения 100500 строк?
Например, приведенный в конце статьи?
Каждый ЯП в чем-то хорош, а в чем-то не очень. Но если SQL в работе с данными хорош, то почему бы не придумать способы обхода некоторых не очень удачных моментов.
Mingun
01.12.2022 22:35Например, приведенный в конце статьи?
Какая разница, как вы их вычислили, вы хотите сказать, этот запрос магически пишет значения на диск?
то почему бы не придумать способы обхода некоторых не очень удачных моментов.
И эти способы — использование другого, подходящего, языка.
Kilor Автор
01.12.2022 22:41Между "сформировать и записать" строки и просто "записать", очевидно, есть разница, которую мы можем попытаться уменьшить. А есть и общая часть, на которую мы, конечно, никак не повлияем.
использование другого, подходящего, языка
Так что же это за язык, например? Только сразу договоримся, что он должен быть столь же удобен, как SQL, при обработке данных.
Mingun
02.12.2022 07:18Сформировать — это вызов
now(). Вы сами утверждаете, что он делается только один раз, сколько бы он не был написан в запросе, а дальше только готовый результат используется. Т.е.now()в запросе нужно воспринимать просто как синтаксическую особенность сослаться на одно конкретное значение, а не выполнение каких-то вычислений. А раз так, то зачем его вручную выносить за пределы "цикла" (коим фактически является повторение вVALUES)? А если не так — то возвращаемся к изначальному вопросу — оптимизатор настолько глуп, что не видит очевидной оптимизации?Только сразу договоримся, что он должен быть столь же удобен, как SQL, при обработке данных.
Вот что-то я не вижу в последнем листинге из статьи никакого удобства при решении элементарной задачи из первого листинга. Паковать в JSON только чтобы потом тут же из него извлечь — это не удобство.
Kilor Автор
02.12.2022 08:27Сформировать — это вызов
now(). Вы сами утверждаете, что он делается только один раз, сколько бы он не был написан в запросе, а дальше только готовый результат используется.Я утверждал не это. Если внутри функции реализована мемоизация возвращаемого результата, это никак не влияет на количество ее вызовов. Ускоряет - да, но сделать 100500 вызовов "запоминающей" функции в любом случае дороже, чем 1.
я не вижу в последнем листинге из статьи никакого удобства
Это значит, что с проблемами генерируемых запросов вы не сталкивались: возможность SQL-инъекции, потеря производительности на передаче и парсинге объемного тела запроса, невозможность использовать prepared statements, ...
Mingun
02.12.2022 11:46+1Я утверждал не это.
Очевидно, что в контексте этой ветки написав "вычислив" я имел ввиду "вызов", и не важно, делает функция внутри мемоизацию, или нет. Для своей работы оптимизатор может использовать концепцию "вычислителя выражений", поэтому и такое слово.
Так вот, спрашиваю снова — почему оптимизатор сам не вытащит повторяющийся вызов за пределы "цикла"? Информация о том, что это безопасно (вызов всегда возвращает один и тот же результат), у него есть. Это базовая оптимизация в любом языке — переиспользование результатов вычислений, вынос инвариантов.
Это значит, что с проблемами генерируемых запросов вы не сталкивались: возможность SQL-инъекции, потеря производительности на передаче и парсинге объемного тела запроса, невозможность использовать prepared statements
Для генерируемых запросов нужно пользоваться Bulk/batch execution. Никаких из указанных вами минусов при этом нет. Не знаю, зачем вместо этого генерировать запрос со вшитыми данными и иметь с ним проблемы, а потом героически их решать. Разве только драйвер БД не поддерживает батчи, ну так это опять вопрос, почему это не сделано?
Kilor Автор
02.12.2022 12:02Это базовая оптимизация в любом языке — переиспользование результатов вычислений, вынос инвариантов.
Если это актуально в любом ЯП, то почему проделать то же самое в SQL считается какой-то неуместной ересью?
Замечу, что такой вынос "за скобки" - это действие самого разработчика, а не компилятора/оптимизатора языка. Иногда они могут это взять на себя, иногда - нет, но упрекать их в этом несправедливо.
Для генерируемых запросов нужно пользоваться Bulk/batch execution.
Давно хочу увидеть пример реализации INSERT .. ON CONFLICT в операторе COPY, только без промежуточных триггеров.
... почему оптимизатор сам...? ... почему это не сделано?
Вероятно, потому что кто-то (может быть, вы?) пока не взял на себя реализацию какого-то данного конкретного функционала в PostgreSQL.
Mingun
02.12.2022 12:11Если это актуально в любом ЯП, то почему проделать то же самое в SQL считается какой-то неуместной ересью?
Имеется ввиду конечно же, что это делает любой оптимизирующий компилятор любого языка, а не то, что вы должны делать это вручную. Вручную это можно делать, если от этого вырастает читабельность, а в SQL она от этого обычно падает.
Вероятно, потому что кто-то (может быть, вы?) пока не взял на себя реализацию какого-то данного конкретного функционала в PostgreSQL.
Вот это-то и странно. Сколько лет она уже разрабатывается, а такой простейшей вещи нет...
Kilor Автор
02.12.2022 12:27это делает любой оптимизирующий компилятор любого языка ... такой простейшей вещи нет
Правда-правда, это должен и делает любой компилятор любого языка?.. Возьмем какой-нибудь Brainfuck - чем не ЯП? Не нравится? Ок, BASIC, Pascal - вполне заслуженные ЯП.
Так откуда такая категоричность, что все это делают, один только PostgreSQL убогий?
Может все-таки, наоборот, - разного рода оптимизации (и не обязательно все) есть только в языках/компиляторах, в которые кто-то вложился (деньгами, временем, проработкой концепций и алгоритмики)? И наличие какой-либо оптимизации - это плюс, конечно, но ее отсутствие - не минус.
Mingun
02.12.2022 14:30+1Во-первых, кто вам сказал, что только PostgreSQL убогий? Всякий компилятор, который не делает эти базовые вещи, не заслуживает право называться оптимизирующим, ИМХО. Они потому и базовые, что их проще всего сделать, и если их нет, то с большой вероятностью и остальные оптимизации тоже отсутствуют.
Теперь по пунктам:
- Brainfuck — понятия не имею, кто-то вообще задумывался о написании оптимизирующего компилятора для него? Если задумывался, то первым делом должен был сделать выявление общих подвыражений — просто потому, что это банально самое простое, что можно сделать
- BASIC — к сожалению, сложно что-то гуглить по его имени, это слишком общее слово. Кое-как нагугленный за 5 минут FreeBasic использует в качестве бекенда gcc или llvm, а значит и все их оптимизации
- Pascal — одна из первых ссылок по запросу "list of optimizations in free pascal" — https://www.freepascal.org/docs-html/prog/progsu58.html. Там есть слова
CSE Use common subexpression eliminationПо крайней мере, компилятор знает об этом, хотя эта глава не о флажках командной строки, а о директивах внутри кода (если я правильно понял)
Kilor Автор
02.12.2022 14:41Всякий компилятор, который не делает эти базовые вещи, не заслуживает право называться оптимизирующим
О чем мы тогда спорим? У PG нет компилятора вообще, тем более оптимизирующего - так что наивно ждать от него их функций.
Mingun
02.12.2022 16:29Это мы просто случайно как-то ушли на примеры компилируемых языков, хотя говорили об оптимизаторах внутри них. А у PG вроде как есть оптимизатор запросов.
starik-2005
04.12.2022 09:45Большинство языков так или иначе оптимизируют поток выполнения (или как это все можно назвать). Но у меня вот сомнения по поводу now() - а есть уверенность, что в каждый момент выполнения запроса now() одна и та же - время ведь не остановилось, там даже 4 секунды прошло или сколтко там...
funca
01.12.2022 23:55Возможно, для задачи сбора метрик лучше бы подошла time series database, а не rdbms. Разные типы баз данных предлагают разные модели работы с этим самыми данными, что отражается и в языках запросов.
Kilor Автор
02.12.2022 00:04+1Тут нет timeseries-данных - у нас тут сохраняется лишь последнее значение для ключевой пары, хоть оно и timestamp.
funca
02.12.2022 00:12+1Теперь понятно. А почему не подходит key-value?
Kilor Автор
02.12.2022 08:50+1Вполне подходит, даже лучше, но не всегда архитектурно оправдано введение в проект еще одного элемента - будь то KV (Redis), columnar (Citus, ClickHouse), timeseries (TimeScale), ... - эксплуатационные издержки могут превысить профит от использования.
Но если оно уже есть и уже синхронизируется с основным - то, конечно.
freeExec
01.12.2022 18:21+6Блин, я ничего непонял. Ни какие проблемы это вызвало, ни объяснения их решения. Что за "сортировать все", ни откуда взялось fk2_5. Похоже что весь смысл был - сходи и прочитай две моих другие статьи.
Kilor Автор
01.12.2022 18:50-1Проблемы же приведены: deadlock, повторные вызовы функции и клон-значения в теле запроса. Не?
fk2_5 - это просто 5-е значение поля fk2 в качестве примера.
grSereger
02.12.2022 11:17Спасибо за статью, простите, не увидел сколько в итоге сэкономим на проблеме #3?
Kilor Автор
02.12.2022 11:42В зависимости от объема и структуры данных (сколько там повторяющихся значений) мы можем двое сократить сетевой трафик до сервера, вынести константное тело запроса в prepared statement и вообще убрать издержки на его парсинг/планнинг.
В очень грубой оценке на исходный запрос размером 1MB это будет ускорение и снижение cpu-нагрузки сервера СУБД примерно вдвое:
передача по сети 40мс вместо 80мс
parse: 0мс вместо 10мс
plan: 0мс вместо 10мс
exec: 10мс вместо 5мс (запрос-то сложнее)
saatanaperkele
03.12.2022 12:10Хех, думал что в Postgres 15 INSERT ON CONFLICT можно заменить на MERGE, но почитал интернеты, оказалось что в общем случае нельзя
datacompboy
не повезло - а так и задумано.
Kilor Автор
Повезло, что это была "легкая" now(), а не pg_sleep(1).