Егор Рогов erogov

Возможно будет интересна статья с похожей идеей: https://db.cs.cmu.edu/papers/2017/p1009-van-aken.pdf
Они там и набор параметров автоматически определяют. Правда, тренировались не на Оракле, но какая разница.

Вы ж так не шутите, а то поверит кто-нибудь (:

Выпилят примерно никогда. Тенденция такая, что стандартный heap останется универсальным движком, а альтернативы будут целиться в узкие области применения, где у них есть шансы показать лучший результат.
Конкретно насчет undo — это надо следить за успехами https://github.com/EnterpriseDB/zheap

почему нет?

Ну просто в стандарте SQL говорится:

The four isolation levels guarantee that each SQL-transaction will be executed completely or not at all, and that no updates will be lost.

То есть они безусловно требуют атомарность и отсутствие потерянных обновлений. Но при этом, что интересно, никак не определяют, что имеют в виду под lost updates.

Ну и получается как-то неловко — Read Committed не соответствует стандарту.

В моем понимании грязная запись — это когда транзакция 2 перезаписывает изменение, сделанное транзакцией 1, до того, как 1 зафиксируется. В PostgreSQL это очевидно невозможно в силу блокировок.

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

То есть это все-таки разные вещи, на мой взгляд.

Ага, вот и мне показалось, что возникло недоразумение.
А названия спорные, факт. Но int2 и int4 уже в Postgres95 были, а то и раньше, так что — "исторически сложилось".

Bigint и int8 — это ведь одно и то же. Или в какой-то базе это не так?

Значит, мистер Холмс, — сказал сэр Генри Баскервиль, — кто-то составил это письмо, вырезав ножницами…
— Маникюрными ножницами, — перебил его Холмс. — Вы обратили внимание, какие у них короткие концы? Для того, чтобы вырезать слова «держитесь подальше от», пришлось сделать два надреза.

В PostgreSQL есть такой индекс spgist, который в том числе реализует и k-d-деревья (https://habr.com/ru/company/postgrespro/blog/337502/). А в 12-й версии появился поиск ближайших соседей по этому индексу.
Можно класс операторов под свою задачу заточить, а не писать базу данных целиком.

It wasn't intentional (:

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

В 12-й версии сделали-таки параметр plan_cache_mode для управления generic-планами.

некоторые возможности PostgreSQL и вовсе нельзя сделать кроме как на С, например, в других языках не поддерживаются типы (особенно если возвращать значение из функции) ANYELEMENT, ANYARRAY и особенно важный VARIADIC.

Не, система типов работает безотносительно того, на каком языке написана функция. В частности, и на SQL, и в PL/pgSQL все это можно использовать.
Что, конечно, не умаляет достоинств Си.

Oh. Of course you're right, it was clearly a mistake. I've corrected the text, thank you.

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

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

Понятно, что HashAggregate тоже сколько-то «скушает», но и скармливать много дублей в массиве ключей — небесплатно.

Понятное дело небесплатно. Но что с чем вы сравниваете? В 1.3 же совсем другой план получился. Давайте сравнивать 1.2 с таким же запросом, только заменим UNION ALL на UNION.
Я увеличил в примере все числа в 10 раз, чтобы цифры были заметнее, и вот что получил в среднем:

• UNION ALL — 28.8 мс,
• UNION — 35.3 мс.

Без DISTINCT все-таки лучше.

На v12 планировщик еще немного поумнел

но достаточно person расширить до 10k, 100k,…

WITH T AS (
  SELECT *
  FROM task
  WHERE owner_id = 777
  ORDER BY task_date DESC
  LIMIT 100
)
, dict AS (
  SELECT *
  FROM person
  WHERE id IN (
      SELECT author_id FROM T -- DISTINCT тут не нужен
    )
)
SELECT *
FROM T LEFT JOIN dict ON T.author_id = dict.id;

habr=# EXPLAIN (analyze, costs off, timing off, buffers)
SELECT
  task.*
, person.name
FROM
  task
LEFT JOIN
  person
    ON person.id = task.author_id
WHERE
  owner_id = 777
ORDER BY
  task_date DESC
LIMIT 100;

                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Limit (actual rows=100 loops=1)
   Buffers: shared hit=116
   ->  Sort (actual rows=100 loops=1)
         Sort Key: task.task_date DESC
         Sort Method: quicksort  Memory: 34kB
         Buffers: shared hit=116
         ->  Hash Left Join (actual rows=116 loops=1)
               Hash Cond: (task.author_id = person.id)
               Buffers: shared hit=116
               ->  Bitmap Heap Scan on task (actual rows=116 loops=1)
                     Recheck Cond: (owner_id = 777)
                     Heap Blocks: exact=107
                     Buffers: shared hit=109
                     ->  Bitmap Index Scan on task_owner_id_task_date_idx (actual rows=116 loops=1)
                           Index Cond: (owner_id = 777)
                           Buffers: shared hit=2
               ->  Hash (actual rows=1000 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 62kB
                     Buffers: shared hit=7
                     ->  Seq Scan on person (actual rows=1000 loops=1)
                           Buffers: shared hit=7

Я там в hackers написал, что думаю, ну и тут повторю.
Имхо по уму корреляция должна вычисляться для пары (атрибут, индекс), т. е. для каждого индекса (или может типа индекса?) — по-своему, с учетом того, в каком порядке именно этот индекс возвращает значения.
А сейчас корреляция считается только для сортируемых типов в предположении, что в индексе значения лежат отсоортированными. То есть, по сути, это работает правильно только для B-деревьев.

Рад, что читаете!
Я как раз стараюсь показать, как самому во всем убедиться, благо в Постгресе много «интроспективных» возможностей.
А насчёт NULL — ну да, у него есть особенности, о которых надо помнить, и он делает SQL менее стройным и логичным. Об это теоретики много копий сломали (например). Но на практике без NULL было бы плохо, и все равно он уже есть. Так что не вижу причин его безудержно избегать.

Я-то здесь имел в виду не производительность, а расход места на диске.

Если говорить про производительность, то (несколько я себе представляю) основные потери в этом месте связаны с тем, что прочитанную версию строки приходится «разбирать» на отдельные поля. Если бы все поля имели постоянное смещение относительно начала версии строки, все было бы просто и быстро, но у нас есть поля переменной длины (такие как number, varchar и т. п.) и поля, допускающие NULL (потенциально нулевой длины). Поэтому, чтобы добраться до какого-то поля, приходится пробежать и по всем предыдущим.

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

Чтобы получить снимок, требуется прочитать список всех транзакций (ProcArray), и на время чтения его приходится блокировать. Чем больше соединений, тем больше транзакций (и, следовательно, чаще надо получать снимки) и тем больше времени удерживается блокировка.
В итоге получается так, что при увеличении количества соединений производительность начинает падать, даже если железяка мощная и ее ресурсы не исчерпаны.
Ну и другие неприятности могут быть, например проблемы с новыми подключениями, которые долго не могут взять монопольную блокировку ProcArray.
А пул ограничивает количество одновременно открытых соединений и становится легче.

Точно. Спасибо, поправил!

Это да.

Про кольца нигде особо не написано, но погрепайте buffer ring в исходниках.

Кольцо — одно на операцию. По сути, это просто кусочек общего кеша, который из него временно «откушен».
Думаю, ускорение там из-за того, что vacuum full ведь тоже чистит ненужные версии строк, ну и если перед этим vacuum уже прошелся, то и ему легче.

Насчёт Read Committed — скорее всего в чем-то другом дело было. Против Access Exclusive не попрешь.
(К тому же Serializable работает только если и остальные транзакции используют тот же уровень. Иначе все это выражается в Repeatable Read.)

Полезное дело делаете!
Пара уточнений.

Да и последующие запросы по этой таблице пойдут у нас по «горячему кэшу»

Vacuum использует буферное кольцо в кеше, чтобы не вымывать из него полезные данные. Обратная сторона медали — в кеше ничего (почти ничего) не останется после его работы.

Для этого нам необходимо включить SERIALIZABLE-изоляцию для нашей транзакции

Достаточно и Read Committed, вы же вручную ставите блокировку.

Да, но ее не было (:

Ну, первый-то сценарий совсем клинический.
А так мне кажется, что интерпретации зависят от того, считаем мы r и w одной SQL-операцией (UPDATE), или двумя (SELECT + UPDATE). Если одной, то второй сценарий в Постгресе вообще невозможен (транзакция 2 будет заблокирована до окончания 1). А если двумя, то увы, возможен, потому что в этом случае база ничего не знает о связи w с r. Речь про Read Committed, конечно.

А в CTE, подозреваю, все блокировки будут браться сразу

Нет, тоже постепенно.

Вот кстати есть формальный способ записи истории транзакций буквами r1(x), w1(x), c1, a1

Есть. Я его сознательно не стал тут приводить, чтобы не уводить разговор совсем в сторону теории. Почему? См. ниже.

непонятно как записывать self-join, CTE, "set value = value + 1" с помощью такой нотации. Нет ли тут проблемы с формализацией?

Есть такая проблема. Теория использует простую модель с элементарными операциями. Как эти операции сгруппированы по SQL-операторам, теорию не интересует.

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

r1(x) r2(x) w1(x) w2(x)

Пара операций r1(x) w1(x) подразумевает, что вы читаете объект x (допустим, строку таблицы), а потом записываете что-то в этот же x. Что именно — теория никак не определяет.

Если мы реализуем эти операции так, как в том примере (https://github.com/ept/hermitage/blob/master/postgres.md#lost-update-p4):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
select * from test where id = 1; -- T1
select * from test where id = 1; -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 11 where id = 1; -- T2, BLOCKS
commit; -- T1. This unblocks T2, so T1's update is overwritten
commit; -- T2

то мне непонятно, что именно мы тут потеряли? Оба оператора UPDATE записывают в x фиксированное значение — их в принципе не интересует, что в x было до этого.

Но если мы переделаем этот пример вот так:

...
update test set value = value + 11 where id = 1; -- T1
update test set value = value + 11 where id = 1; -- T2, BLOCKS
...

то увидим, что потери не случится: второй оператор UPDATE перечитает строку после того, как разблокируется.

Но вообще по-моему лучше по-другому интерпретировать эту запись. Я бы считал, что пара r1(x) w1(x) соответствует одному оператору

update t set x = f(x);

потому что такой оператор как раз сначала читает x, а потом записывает в него что-то на основе полученного значения.

Но так или иначе — результат один. Если у нас Read Committed, то мы не теряем обновление, но (поскольку перечитываем только одну строку) можем получить несогласованное чтение. А если Repeatable Read (или Serializable), то транзакция просто обрывается во избежание проблем.

Это два разных подхода.
В случае Read Committed мы предпочитаем получить аномалию, и никогда не обрываем из-за этого транзакции. Задача обеспечения согласованности ложится на плечи разработчика приложения.
В случае Serializable (и отчасть Repeatable Read) мы предотвращаем потенциальные проблемы тем, что обрываем транзакции, и приложение должно их просто повторять.

Ну вот, много написал, но не уверен, что что-то прояснил (:

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

Вне контекста конкретной СУБД — не получится. Ну, есть какие-то общие знаменатели, но в общем случае точно нет.

Внутри СУБД нужно что-то заблокировать

Если внутри CTE выполняются изменения, то конечно нужно. Но и если бы CTE выполнялся по шагам, тоже надо было бы блокировать. И в этом смысле разницы никакой нет, ведь блокировки снимаются только в конце транзакции (а не оператора).
Блокировкам отдельный цикл посвящен, кстати.

Стандарт не слишком много чего определяет, поэтому ориентироваться всегда надо на особенности конкретной реализации. За MS SQL не скажу, а в Постгресе весь запрос целиком видит согласованные данные.

то таким образом с помощью CTE можно хорошенько «просадить» конкурентность

Не понимаю, почему? Запрос (если мы о SELECT говорим), выполняясь, никому не мешает.

Рад, что статья понравилась! Свет попробую пролить.

какой уровень изоляции у одного «SQL-оператора»

Уровень изоляции — это свойство транзакции, а не отдельных операторов. Можете считать, что все операторы одной транзакции имеют один и тот же уровень, установленный для этой транзакции.

являются ли запросы CTE (а также подзапросы) отдельными операторами, в том числе рекурсивные

Нет, это составные части одного и того же запроса.

Да и что будет с банальным JOIN? Например, если мы соединяем тяжелую таблицу с собой по первичному ключу, а параллельно пролетает транзакция на обновление записи в этой таблице, может ли получится так, что значения в левой и правой части результата будут разными?

Ни в коем случае. Это один оператор, он всегда* видит только согласованные данные (на момент начала оператора или на момент начала транзакции, смотря по уровню изоляции).

* Ну, не совсем всегда. В статье я привожу два случая, когда в PostgreSQL внутри одного оператора можно увидеть несогласованные данные на уровне Read Committed:

• если в запросе вызывается volatile-функция, содержащая другой запрос;
• оператор UPDATE, который перечитывает только одну строку, а не весь набор.

Никакие другие ситуации мне неизвестны.

К моему удивлению, не удается найти какой-либо авторитетной информации по этим вопросам.

Зачем вам авторитеты — проверьте сами. Вдруг и я вас обманываю? (:
Вот скажем вас join интересует. Возьмите таблицу, соедините ее саму с собой, параллельно запустите обновление и сравните результат с известным правильным.

Ну, чтобы не сделать хуже, чем есть, можно всегда подключать «основные» языки плюс те, что используются в статье.

Да я тоже не фронтендер, просто пока прикручивал подсветку для PostgreSQL, угораздило записаться в мейнтейнеры highlightjs — так что обращайтесь, если что (:

А вообще мне вот как видится. Когда статья редактируется, можно подгружать просто все языки (автор как-нибудь переживет лишний мегабайт трафика). А когда статью сохраняют, список языков уже известен. Можно это знание сохранить в метаинформации о странице и потом для показа генерировать подключение только нужных языков (их можно по одному цеплять).
Надо только не забыть про языки, внутри которых могут быть фрагменты на других языках, но и в этом случае набор ограничен.

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

Там только с размером запроса может быть засада и возможно придется выкручивать track_activity_query_size.
А дыры — ну дыры, можно ж и логи потерять с тем же успехом.

К чему может приводить обнаружение взаимоблокировок при большом количестве сеансов: https://www.postgresql.org/message-id/flat/c9f840f4-b7fe-19c6-76e6-65c02a0c230c%40postgrespro.ru