• Метод научного тыка, или как подобрать конфигурацию субд с помощью бенчмарков и оптимизационного алгоритма
    0

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

  • PostgreSQL Antipatterns: уникальные идентификаторы
    +1

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

  • PostgreSQL 14: Часть 1 или «июльский разогрев» (Коммитфест 2020-07)
    +3

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

  • MVCC-1. Изоляция
    0
    почему нет?

    Ну просто в стандарте 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 не соответствует стандарту.

  • MVCC-1. Изоляция
    0

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


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


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

  • Сводные таблицы в SQL
    0

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

  • Сводные таблицы в SQL
    0

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

  • Зачем принтеры ставят невидимые точки на документах
    +6

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

  • Как помнить всех в лицо, или эффективный поиск лиц в большой базе
    +5

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

  • Locks in PostgreSQL: 2. Row-level locks
    0

    It wasn't intentional (:

  • Индексы в PostgreSQL — 5
    +1

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

  • PostgreSQL и JDBC выжимаем все соки. Владимир Ситников
    0

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

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

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

  • Indexes in PostgreSQL — 4 (Btree)
    0

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

  • PostgreSQL 13: параллельный VACUUM
    0

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

  • PostgreSQL 13: параллельный VACUUM
    0

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

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

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


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

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

  • PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN
    +1
    1. Ну может, но DISTINCT там все равно не нужен, потому что =ANY сам по себе не пропустит дубликаты. А в варианте с DISTINCT вы на ровном месте получили лишний HashAggregate в плане.
    2. В отсутствие индексов (CTE ведь у нас) у Nested Loop нет никаких шансов. Hash Join будет гарантированно, что нам и надо.

    Если других проблем нет, то делаю вывод: не надо тут hstore/json использовать (:
  • PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN
    +2
    На v12 планировщик еще немного поумнел

    У меня нет под рукой 11, но 10-ка выдает точно такой же план после ANALYZE. Так что тестовые данные так себе.

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

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

    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;
    

    и получить то же самое, а скорее всего и лучше?
  • PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN
    0
    Это, конечно, увлекательное упражнение. Но первая мысль, которая приходит в голову — зачем Nested Loop, почему бы не прочитать person целиком и не соединить хешированием? Тогда вместо накрутки буферов из-за индексного сканирования получим константу.
    Решил я воспроизвести пример (на 12) и только собрался отключить индексное сканирование, как таблицы проанализировались и планировщик сам догадался до такого решения:

    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
    

    То есть без всяких костылей мы читаем 116 страниц вместо 142 в решении с hstore.
    Ммм?
  • Индексы в PostgreSQL — 9
    0

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

  • MVCC-3. Версии строк
    0

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

  • MVCC-3. Версии строк
    0

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


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


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

  • MVCC-4. Снимки данных
    0

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

  • MVCC-3. Версии строк
    0

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

  • DBA: когда пасует VACUUM — чистим таблицу вручную
    0

    Это да.

  • DBA: когда пасует VACUUM — чистим таблицу вручную
    0

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

  • DBA: когда пасует VACUUM — чистим таблицу вручную
    0

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


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

  • DBA: когда пасует VACUUM — чистим таблицу вручную
    +1

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


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

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


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

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

  • Краткая и на 146% точная история языков программирования
    +3

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

  • MVCC-1. Изоляция
    0

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

  • MVCC-1. Изоляция
    0
    А в 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) мы предотвращаем потенциальные проблемы тем, что обрываем транзакции, и приложение должно их просто повторять.


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

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

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


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

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

  • MVCC-1. Изоляция
    +1

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


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

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

  • MVCC-1. Изоляция
    +1

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


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

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


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

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


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

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


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


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

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


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

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

  • АМА с Хабром, #13: важные новости для пользователей и компаний
    0

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

  • АМА с Хабром, #13: важные новости для пользователей и компаний
    +1

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


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

  • АМА с Хабром, #13: важные новости для пользователей и компаний
    0

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

  • pg_stat_statements + pg_stat_activity + loq_query = pg_ash?
    0

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

  • Блокировки в PostgreSQL: 3. Блокировки других объектов
    0

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