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

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

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

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

Locks in PostgreSQL: 2. Row-level locks

It wasn't intentional (:

Индексы в PostgreSQL — 5

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

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

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

PostgreSQL: Разработка расширений (функций) на языке С

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

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

Indexes in PostgreSQL — 4 (Btree)

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

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

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

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

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

PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN

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

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

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

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

PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN

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

Если других проблем нет, то делаю вывод: не надо тут hstore/json использовать (:

PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN

На 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

Это, конечно, увлекательное упражнение. Но первая мысль, которая приходит в голову — зачем 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

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

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

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

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

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

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

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

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

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

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

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

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

Это да.

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

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