По следам meetup «Новые возможности PostgreSQL 11»

Сегодня мы расскажем о самых главных фичах PostgreSQL 11. Почему только о них — потому что некоторые возможности нужны далеко не всем, поэтому мы остановились на самых востребованных.

Содержание

• JIT-компиляция
• Секционирование
• Индексы
• Покрывающие индексы
• SP GiST
• Производительность
• WAL
• Бэкап и репликация
• Для DBA
• Параллельное выполнение
• Оптимизаторы
• Window-функции
• Изменения в полнотекстовом поиске
• Json(b) и полнотекст
• PL/* процедуры
• pgbench
• Улучшения PSQL

JIT-компиляция

В PostgreSQL наконец-то появилась JIT-компиляция, то есть компиляция запросов в бинарный код. Для этого нужно скомпилировать PostgreSQL с поддержкой JIT-компиляции (Compile time 1 (--with-llvm)). При этом на машине должен быть LLVM версии не ниже 3.9.

Что же умеет ускорять JIT?

• Запросы с условием WHERE, то есть всё, что идёт после этого ключевого слова. Это нужно далеко не всегда, но возможность полезная.
• Вычисление целевого списка (target list): в терминологии PostgreSQL это всё, что находится между select и from.
• Агрегаты.
• Преобразование записи от одного вида к другому (Projection). Например, когда вы к двум таблицам применяете join и в результате получается новый кортеж, содержащий поля из обеих таблиц.
• Tuple deforming. Одна из проблем любой базы данных, по крайней мере, строчной, реляционной, заключается в том, как достать поле из записи на диске. Ведь там могут встречаться null, они имеют разные записи и вообще, это не самая дешевая операция.

Compile time 2 означает, что JIT не используется. В PostgreSQL есть момент планирования запроса, когда система решает, что стоит JIT’ить, а что не стоит. В этот момент он JIT’ится и дальше executor выполняет, как есть.

JIT сделан подключаемым. По умолчанию он работает с LLVM, но вы можете подключить любой другой JIT.

У вас есть все наборы, контролирующие JIT.

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

Следующий тонкий момент связан с jit_above_cost. Сам JIT не бесплатный. Поэтому PostgreSQL по умолчанию занимается JIT-оптимизацией, если стоимость запроса превысила 100 тыс. условных попугаев, в которых меряется explain, analyze и так далее. Это значение выбрано наугад, так что обратите на него внимание.

Но не всегда после включения JIT сразу всё работает. Обычно все начинают экспериментировать с JIT с помощью запроса select * from table where id=600 и у них ничего не получается. Наверное, надо как-то усложнять запрос, и тогда все генерируют гигантскую базу и сочиняют запрос. В результате PostgreSQL упирается в возможности диска, ему не хватает ни ёмкости общих буферов, ни кэшей.

Вот совершенно абстрактный пример. Здесь 9 полей null с разной частотой, чтобы можно было заметить влияние tuple deforming.

select i as x1,
  case when i % 2 = 0 then i else null end as x2,
  case when i % 3 = 0 then i else null end as x3,
  case when i % 4 = 0 then i else null end as x4,
  case when i % 5 = 0 then i else null end as x5,
  case when i % 6 = 0 then i else null end as x6,
  case when i % 7 = 0 then i else null end as x7,
  case when i % 8 = 0 then i else null end as x8,
  case when i % 9 = 0 then i else null end as x9
  into t
from generate_series(0, 10000000) i;

vacuum t;
analyze t;

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

set max_parallel_workers=0;
set max_parallel_workers_per_gather=0;
set jit_above_cost=0;
set jit_inline_above_cost=0;
set jit_optimize_above_cost=0;

Вот сам запрос:

set jit=off;
explain analyze
select count(*) from t where
  sqrt(pow(x9, 2) + pow(x8,2)) < 10000;

set jit=on;
explain analyze
select count(*) from t where
  sqrt(pow(x9, 2) + pow(x8,2)) < 10000;

И вот результат:

Planning Time: 0.71 ms
Execution Time: 1986.323 ms

VS

Planning Time: 0.060 ms
JIT:
  Functions: 4
  Generation Time: 0.911 ms
  Inlining: true
  Inlining Time: 23.876 ms
  Optimization: true
  Optimization Time: 41.399 ms
  Emission Time: 21.856 ms
Execution Time: 949.112 ms

JIT помог ускорить запрос вдвое. Planning time приблизительно одно и то же, но это, скорее всего, следствие того, что PostgreSQL что-то закэшировал, поэтому не обращайте на это внимания.

Если просуммировать, то на JIT-компиляцию ушло порядка 80 мс. Почему JIT не бесплатный? Прежде чем выполнять запрос, нужно его скомпилировать, а это тоже занимает время. Причём на три порядка больше, чем планирование. Недешевое удовольствие, но зато окупается за счёт длительности исполнения.

Вот таким образом можно использовать JIT, хотя он приносит пользу далеко не всегда.

Секционирование

Если вы обращали внимание на секционирование в PostgreSQL, то, наверное, замечали, что оно там сделано для галочки. Ситуация несколько улучшилась в версии 10, когда появилось декларативное объявление партиций (секций). С другой стороны, внутри всё оставалось по-старому и работало примерно так же, как и в предыдущих версиях, то есть плохо.
Во многом эту проблему решал модуль pg_pathman, который позволял работать с секциями и достаточно оптимально отсекать их во время выполнения.

В версии 11 секционирование значительно улучшено:

• Во-первых, таблица секционирования может иметь первичный ключ, в который должен входить ключ секционирования. На самом деле, это то ли полупервичный ключ, то ли первичный полуключ. К сожалению, на него нельзя сделать foreign key. Надеюсь, в дальнейшем это будет поправлено.
• Также теперь можно секционировать не только по диапазону, но и по списку и хэшу. Хэш достаточно примитивный, для него берётся остаток выражения.
• При обновлении строка перемещается между секциями. Раньше нужно было писать триггер, а сейчас это делается автоматически.

Большой вопрос: сколько можно иметь секций? Честно говоря, с большим количеством секций (тысячи и десятки тысяч) фича работает плохо. У pg_pathman получается лучше.

Также сделали секции по умолчанию. Опять же, в pg_pathman можно сделать автоматическое создание секций, что удобнее. Здесь же в секции сваливается всё, что не удалось куда-нибудь запихнуть. Если в реальной системе сделать такое по умолчанию, то через какое-то время получится такая каша, которую потом замучаешься разгребать.

PostgreSQL 11 теперь умеет оптимизировать секционирование, если две таблицы соединяются по ключу секционирования, и схемы секционирования совпадают. Этим управляет специальный параметр, который по умолчанию выключен.

Можно вычислять агрегаты для каждой секции отдельно, а потом суммировать. И наконец, можно создать индекс на родительскую секционированную таблицу, и тогда будут созданы локальные индексы на все таблицы, которые к ней подключены.

В разделе «Что нового» упомянута замечательная вещь — возможность выкидывать секции при выполнении запроса. Давайте проверим, как это работает. Получилась вот такая таблица:

Делаем тип и таблицу из двух колонок с первичным ключом, с колонкой bigserial, вставляем данные. Создаем вторую таблицу, которая будет партиционирована и будет копией первой. Добавляем первичный ключ на секционированную таблицу.

Таблица будет состоять из записей двух типов: «женщины-няни» и «мужчины-водители». И будет одна женщина-водитель. Делаем две секции, разделяем по списку, добавляем первичный ключ и вставляем все данные из той таблицы, в которой всё это сгенерировано. Результат получился совершенно неинтересный:

Обратите внимание на запрос. Мы выбираем всё из несекционированной таблицы, соединяем с секционированной. Берем небольшой кусочек и выбираем только один тип, они идут через один. Указываем, что колонка осс должна иметь одно значение. Получается выборка сплошных водителей.

При выполнении мы специально отключаем распараллеливание, потому что PostgreSQL 11 по умолчанию очень активно распараллеливает более-менее сложные запросы. Если мы посмотрим на план выполнения (explain analyze), то видно, что система сложила даные в обе секции: и в няней, и в водителей, хотя нянь там не было. К буферу обращений не было. Время потрачено, условие использовано, хотя PostgreSQL мог всё это вычислить. То есть, объявление partition elimination сходу не работает. Возможно, в следующих сборках это поправят. При этом модуль pg_pathman в этом случае работает без проблем.

Индексы

• Оптимизации ставки в монотонном порядке, то есть b-tree. Все знают, что когда вставляешь монотонно растущие данные, то получается не очень быстро. Сейчас PostgreSQL умеет особым образом кэшировать концевую страницу и не проходить для вставки весь путь от корня. Это заметно ускоряет работу.
• В версии PostgreSQL 10 хэш-индексом стало можно пользоваться, потому что он начал применять WAL (журнал упреждающей записи). Раньше мы получали значение, разблокировали страницу, возвращали значение. Для следующего значения нужно было снова блокировать страницу, возвращать, разблокировать и так далее. Сейчас хэш стал работать значительно быстрее. Он позволяет для получения записи из хэш-индекса за один раз блокировать страницу, выдать оттуда все значения и разблокировать. Сейчас это реализовано для HASH, GiST и GIN. В будущем это будет, возможно, реализовано для SP-GiST. А для BRIN с его min/max логикой сделать такое нельзя в принципе.
• Если раньше вы строили функциональные индексы, то у вас эффективно отключался HOT update (Heap Only Tuple). Когда в PostgreSQL обновляется запись, на самом деле создаётся новая копия, и это требует вставки во все индексы, которые имеются в таблице, чтобы новое значение указывало на новый tuple. Достаточно давно реализована такая оптимизация: если при обновлении не меняются поля, которые не входят в индексы, и есть свободное место на этой же странице, то индексы не обновляются, а в старой версии tuple ставится указатель на новую версию. Это позволяет несколько снизить остроту проблему с обновлениями. Однако такая оптимизация совершенно не работала, если у вас есть функциональные индексы. В PostgreSQL 11 она начала работать. Если вы построили функциональный индекс и обновляете tuple, который не меняет то, от чего зависит функциональный индекс, то HOT update будет работать.

Покрывающие индексы

Эта функциональность была реализована PostgresPro три года назад, и всё это время ее пытались пропихнуть в ванильный PostgreSQL. Покрывающие индексы означают, что вы можете к уникальному индексу, прямо в индексные tuple, добавить дополнительные колонки.

Зачем? Все любят index-only scan за быструю работу. Для этого строят условно «покрывающие» индексы:

CREATE UNIQUE INDEX idx ON TBL
      (a, b) INCLUDE (c, d)

VS

CREATE UNIQUE INDEX idx1 ON tbl
      (a, b)
CREATE UNIQUE INDEX idx1 ON tbl
      (a, b, c, d)
... PRIMARY KEY (a, b) INCLUDE (c, d)

Но при этом нужно сохранить уникальность. Поэтому строится два индекса, узкий и широкий.
Недостаток в том, что когда вы применяете к таблице vacuum, insert или update, то в обязательном порядке нужно обновить оба индекса. Так что вставка в индекс — операция медленная. А покрывающий индекс позволит обойтись только одним индексом.

Правда, у него есть некие ограничения. Точнее, преимущества, которые, может быть, будут не сразу понятны. Колонки c и d в первом create index не обязаны быть скалярными типами, для которых определен b-tree индекс. То есть, они необязательно имеют сравнение «больше-меньше». Это могут быть точки или полигоны. Единственное, кортеж должен быть меньше 2,7 Кб, потому что toasting в индексе нет, но туда можно уместить то, что невозможно сравнить.

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

Ну и кроме того, вы можете эти покрытые колонки добавить в первичный ключ.

SP GiST

Этот индекс мало кто использует, потому что он достаточно специфичен. Тем не менее, появилась возможность хранить в нём не совсем то, что вставили. Имеется в виду lossy — индекс, компрессия. В качестве примера возьмём полигоны. Вместо них в индекс кладётся bounding box, то есть минимальный прямоугольник, который содержит нужный полигон. В данном случае мы представляем прямоугольник как точку в четырехмерном пространстве, и дальше работаем классическим quad3, в четырехмерном пространстве.

Также для SP-GiST ввели операцию «префиксный поиск». Она возвращает true, если одна строка является префиксом другой. Ввели это не просто так, а ради вот такого запроса с поддержкой SP-GiST.

SELECT * FROM table WHERE c ^@ „abc“

В b-tree есть лимит 2,7 Кб на всю строчку, а у SP-GiST такого ограничения нет. Правда, есть ограничение у PostgreSQL: одно значение не может превышать 1 Гб.

Производительность

• Появился bitmap index only scan. Он работает так же, как классический index only scan, за исключением того, что не может гарантировать никакого порядка. Поэтому он применим только для каких-то агрегатов типа count (*), ведь bitmap не способен передавать поля из индекса в executor. Он может только сообщить о факте записи, удовлетворяющей условиям.
• Следующее нововведение — обновление Free Space Map (карты свободных страниц) во время применения vacuum. К сожалению, никто из разработчиков систем, работающих с PostgreSQL, не задумывается, что удалять надо в конце таблицы, иначе появляются дыры, незанятое пространство. Чтобы это отслеживать реализовали FSM, которая позволяет не увеличивать таблицу, а вставлять tuple в пустоты. Раньше это делалось с помощью vacuum, но уже в конце. А сейчас vacuum способен это делать в процессе работы, и в высоконагруженных системах это помогает держать размер таблицы под контролем.
• Возможность пропуска сканирования индекса во время выполнения vacuum. Дело в том, что все индексы PostgreSQL, согласно теории баз данных, называются вторичными. Это значит, что индексы хранятся в стороне от таблицы, в неё из них ведут указатели. Index only scan позволяет не делать этого прыжка по указателям, а забирать из индекса напрямую. Но vacuum, который удаляет записи, не может глядя на них в индексе решить, стоит удалять или нет, просто потому, что в индексе нет таких данных. Поэтому vacuum всегда выполняется в два прохода. Сначала проходит по таблице и узнаёт, что ему нужно удалить. Потом идет в индексы, привязанные к этой таблице, удаляет записи, которые ссылаются на найденное, возвращается к таблице и удаляет то, что собирался. И стадия хождения в индексы не всегда обязательна.

  Если со времени последнего vacuum не было delete или update, то у вас нет мертвых записей, их не нужно удалять. В этом случае в индекс можно не ходить. Там есть дополнительные тонкости, b-tree удаляет свои страницы не сразу, а в два прохода. Поэтому, если вы удалили много данных в таблице, то vacuum делать нужно. Но если вы хотите освободить место в индексах, то сделайте vacuum дважды.

  Кто-то удивится, что это за таблица, в которой не было delete или update? На самом деле многие имеют с этим дело, только не задумываются. Это таблицы append only, куда складываются, например, логи. В них удаление происходит крайне редко. И это сильно экономит продолжительность vacuum/autovacuum, снижает нагрузку на диск, использование кэшей и так далее.

• Одновременный commit конкурентных транзакций. Это не нововведение, а улучшение. Теперь PostgreSQL обнаруживает, что сейчас будет коммититься, и задерживает commit текущей транзакции, ожидая остальные коммиты. Обратите внимание, что эта фича оказывает мало эффекта, если у вас небольшой сервер с 2—4 ядрами.
• postgres_fdw (Foreign Data Wrappers). FDW — это способ подключения внешнего источника данных так, чтобы он выглядел как настоящий постгрессовый. postgres_fdw позволяет подключить к своему экземпляру таблицу из соседнего экземпляра, и она будет выглядеть почти как настоящая. Теперь снято одно из ограничений для update и delete. Зачастую PostgreSQL может догадаться, что нужно отправлять не сырые данные. Способ выполнения запроса с join достаточно прост: выполняем его у себя на машине, вытаскиваем из экземпляра таблицу с помощью FDW, выясняем id primary key, который нужно удалить, и потом применяем update и/или delete, то есть данные у нас ходят туда и обратно. Сейчас это возможно сделать. Конечно, если таблицы на разных машинах, это не так легко, но FDW позволяет сделать так, чтобы удаленная машина выполнила операции, а мы просто ждали.
• toast_tuple_target. Бывают ситуации когда данные чуть-чуть выходят за пределы, после которых надо toast’ить, но при этом toast таких значений не всегда приятен. Допустим, у вас предел 90 байтов, а вам нужно уместить 100. Приходится ради 10 байтов заводить toast, отдельно их складывать, потом при select этого поля нужно обращаться к toast-индексу, узнать, где лежат нужные данные, пойти в toast-таблицу, собрать и отдать.

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

WAL

• WAL (Write ahead log) — это журнал упреждающей записи. Размер WAL-сегмента теперь задается в initdb. Слава богу, не при компилировании.
• Также поменялась логика. Раньше комплект WAL-сегментов сохранялся с момента предпоследнего checkpoint’а, а теперь с последнего. Это позволяет заметно сократить объём хранимых данных. Но если у вас база данных на 1 Тб, а TPS = 1, то есть один запрос в секунду, то разницы вы не увидите.

Бэкап и репликация

• В логической репликации появилась truncate. Она была последней из DML операций, которая не отражалась в логической репликации. Теперь отражается.
• В логической репликации появилось сообщение о prepare. Теперь можно отловить prepare transaction, двухфазный коммит в логической репликации. Это реализовано ради построения кластеров — гетерогенных, гомогенных, шардированных и не шардированных, мультимастеров и так далее.
• Исключение из pg_basebackup временных и unlogged таблиц. Многие жаловались, что pg_basebackup включает в себя перечисленные таблицы. А исключив их, мы уменьшаем размер резервной копии. Но при условии, что вы используете временные и unlogged таблицы, в противном случае эта опция будет вам бесполезна.
• Контроль чексуммы в потоковой репликации (для таблиц). Это позволяет понимать, что у вас произошло с репликой. Пока функция реализована только для таблиц.
• Появилась промотка позиций replication slot. Как всегда можно мотать только вперед по времени, назад только если есть WAL. Более того, нужно очень хорошо понимать, что вы с этим делаете и почему. На мой взгляд, это больше разработческая опция, но те, кто использует логическую репликацию для каких-нибудь экзотический применений, могут ей порадоваться.

Для DBA

• Alter table, add column, not null default X, записать всю таблицу. За это есть небольшая плата: дефолтное значение хранится отдельно. Если вы поднимаете tuple и требуете эту колонку, то PostgreSQL вынужден идти по дополнительному пути кодирования, чтобы выдернуть временное значение, подставить его в tuple и отдать вам. Тем не менее, с этим можно жить.
• Vacuum/analyze. Раньше можно было применять vacuum или analyze только ко всей базе данных или одной таблице. Теперь появилась возможность сделать это к нескольким таблицам, причём одной командой.

Параллельное выполнение

• Параллельное построение b-tree индексов. В версии 11 стало возможно встроить b-tree индексы в несколько воркеров. Если у вас действительно хорошая машина, много дисков и много ядер, то можно строить индексы параллельно, это обещает заметный рост производительности.
• Параллельное соединение по хэшу с использованием общей хэш-таблицы для исполнителей. Проблема была в том, что для каждого воркера нужно было держать свою отдельную локальную хэш-таблицу. Если таблица небольшая, то никаких трудностей не возникает. Но если хэш-таблица огромная, это превращалось в заметную проблему. Теперь можно использовать общую таблицу.
• Кроме того, в некоторых случаях теперь можно распараллелить union, create table as, select и create materialized view!
• Наконец-то появилась передача ограничений (limit) в параллельные исполнители. Тоже очень полезная вещь.

Пример параллельного создания индексов:

alter table usr reset (parallel_workers)
create index on usr(lower((so).occ)) — 2 сек
alter table usr set (parallel_workers=2)
create index on usr(upper((so).occ)) — 1.8 сек

У таблицы есть параметр parallel worker. С его помощью можно задать нужное количество воркеров. На тестируемой машине с 16 Гб памяти и 4 ядрами (таблица влезает в кэш ОС) создание в один поток занимает 2 сек., с двумя воркерами — 1,8 сек. Будь в налличии много дисков, и таблица покрупнее, то разница была бы ощутимее. То есть, это нововведение полезно для больших серьезных таблиц и серверов.

А вот пример распараллеливания запроса:

explain analyze
select u1.* from usr u, usr1 u1 where
u.id=u1.id+0

Это две таблицы из тех, которые упомянуты в главе про секционирование. Напоминаем, user — это обычная таблица, секционированная на две части. Обратите внимание на условия соединения. Мы специально добавляем ноль, чтобы соединение произошло не по индексу, а по хэшу.

Мы специально выключаем параллельное выполнение, потому что PostgreSQL 11 в этом плане весьма агрессивен и имеет большую склонность к распараллеливанию.

Этот запрос выполнялся 1425 мс, почти 1,5 сек. Запрос внутри обрабатывался 1,4 сек. Выбрано 2 млн строк. Для сравнения, в версии 9.6 было эмпирическое правило: 1 млн не очень больших строк — примерно 1 сек., а тут 2 млн за 1 сек. Это связано с тем, что в версии 10 ускорили обращение к tuple. А в версии 11 обращение ещё больше ускорено. В этом примере получилось соединение по хэшированию: сначала собрали хэш из таблицы user, получился один batch, оттуда с помощью x-scan выбраны данные и с помощью append собраны две таблицы.

Следующий вариант параллельного выполнения:

Посмотрим время выполнения. В верхнем узле система начала отдавать данные через 211 мс, закончила через 702 мс. В предыдущем варианте, начала отдавать через 510 мс и закончила через 1473. То есть и первая, и последняя строки отданы в 2 раза быстрее.

Теперь соединим с помощью parallel hash join. Было поднято два воркера на верхнем уровне. Ограничение по числу процессоров — 4. Один воркер строил паралел хэш, второй выбирал из другой таблицы.

В таблице parallel index scan всё весьма агрессивно распараллелилось. Данные поместились в один batch в хэше. Что это означает? У нас может не хватить памяти для hash join, и тогда система может выполнять задачу в несколько проходов. Таблица user прочитана в два потока. Также в два потока мы читали другую таблицу, строили parallel hash, который уже работает.

Время выполнения стало меньше 1 сек. Есть мнение, что параллельное выполнение надо применять для OLAP-задач, а OLTP выполнять последовательно. Но это если OLTP совсем тупой, вроде получения значения по первичному ключу.

Оптимизаторы

Радикальных изменений здесь почти не произошло.

• Улучшен сбор статистики для неравномерно распределенных значений. Как вы помните, в таблице с нянями и водителями есть одна строка с женщиной водителем. Если мы захотим выбрать все строки, в которых специальность «водитель» и пол «женщина», то сервер неожиданно врубит index scan, потому что выборка совсем маленькая. Для таких случаев (highly skewed data), когда из очень большой таблицы нас интересуют считанные значения, рекомендуется делать условный индекс. Это хорошо работает во всех случаях. И также переработана гистограмма, теперь она строится корректнее, показывает более точные данные.
• Несколько лучше теперь собирается статистика по «неравно», аккуратнее получается предсказание.

Window-функции

Они теперь соответствуют стандарту SQL:2011, и появилась возможность задавать группы ограничений.

Изменения в полнотекстовом поиске

Как вы знаете, есть несколько способов написания полнотекстовых запросов, в том числе руками. Очевидно, что у того, кто это пытался сделать, всё получалось, но потом он стрелялся, либо его пользователи убивали.

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

# select websearch_to_tsquery('dog or cat');
----------------------
'dor' | 'cat'
# select websearch_to_tsquery('dog -cat');
----------------------
'dor' & !'cat'
# select websearch_to_tsquery('or cat');
----------------------
'cat'

Первый пример — dog or cat — собака или кошка. Websearch понимает суть и формирует такой запрос. Второй запрос видит символ | и понимает, что мы хотим найти только про собаку. И третий запрос “or cat”. Тут трудно сказать, что хотел пользователь. В данном случае websearch трактует “or” как слово. Другое дело, что оно является стоп-словом, поэтому выкинуто из запроса.

Websearch — это средство построения полнотекстового поиска. В следующих версиях планируется реализовать две операции: поиск по фразе и по расстоянию между словами, с указанием диапазона. Сейчас ищет по точному вхождению, понимает двойные кавычки.

Json(b) и полнотекст

В 10-й версии появилась индексация всех текстов, а в 11-й добавилась индексация и численных полей. Реализована функция для json и json(b), которая порождает tsvector. Второй аргумент (тоже json(b)) обязателен из-за требований полиморфизма. Сейчас это может быть либо скаляр, либо массив названий того, что хотим проиндексировать, поля bull, numeric, string, ключи. Всё это можно указывать вместе.

# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '"string"');
-------------------
'text':1
# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '["string", "numeric"]');
-------------------
'12':3 'text':1

Мы намеренно используем здесь json(b), чтобы в будущем можно было безболезненно расширить функциональность. Хотя аргументов в виде исполняемого кода, который извлекает то, что нужно проиндексировать, не будет никогда.

PL/* процедуры

В некоторых языках появились процедуры.

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
  FOR i IN 0..9 LOOP
    INSERT INTO test1 (a) VALUES (i);
    IF i % 2 = 0 THEN
      COMMIT;
    ELSE
      ROLLBACK;
    END IF;
  END LOOP;
END
$$;
CALL transaction_test1();

Вызывать их можно с помощью call, но основное преимущество процедур сегодня в том, что прямо внутри них можно начинать и заканчивать транзакции. Побочным следствием будет невозможность вызова процедуры внутри транзакции. Процедура должна работать вне транзакции. Она не может участвовать внутри select, insert и так далее.

Это не автономная транзакция, это просто вызов функции, которая внутри PostgreSQL может создать несколько транзакций. Она доступна для Perl, Python, TСL и PL/pgSQL. В языках типа Perl нужно явно использовать sp begin, без этого вы просто не сможете обратиться к базе.

PL/pgSQL немного отличается от остальных: он начинает транзакцию неявно, а остальные языки явно.

pgbench

Была цель реализовать на pgbench функциональность ICSB bench — инструмента, моделирующего сетевую нагрузку, но не хватило всевозможных функций и операторов. В результате реализовано if, который позволяет выбирать в тесте, какой запрос вы хотите выполнить. Появился case, чтобы можно было прямо в запросе что-то поменять. Появилась трассировка --init-steps, чтобы можно было узнать, что там происходит.

Теперь можно для повышения стабильности ваших бенчмарков указать random-seed. Появилось несколько zipfian-распределений цифр. Действия пользователей в соцсетях/интернете — это неравномерные распределения, не Гауссиана и не распределение Пуассона. Они по какой-то причине подчиняются распределению цифр, поэтому оно подходит для написания тестов, моделирующих какую-то нагрузку, генерируемую людьми.

Наконец, появился хэшинг, чтобы повысить случайность каких-то операций.

Улучшения PSQL

Для тех, кто застревает в PSQL, теперь есть два новых способа выйти из него. Можно просто напечатать exit или quit.

• Следующее улучшение — исправление ошибки в copy, теперь можно одновременно вставить больше 2³² строк. У copy было странное и очень подлое ограничение: он совершенно незаметно вставлял каждую 2³²-ю строчку. Забавно, что при вставке от 2³¹ до 2³² copy рапортовал о вставке отрицательного количества. Теперь счетчик сделали 64-битным в надежде, что вы не будете вставлять 2⁶⁴ строк.
• В POSIX внесены изменения: совершенно неожиданное NaN⁰ = 1 и 1^NaN= 1.

Автор: tspavel

Источник