PostgreSQL *

В продолжении https://habr.com/ru/posts/787966/

Интересно, а будет ли иметь какой то практический смысл использование скользящих средних для анализа производительности СУБД.

Например классический сигнал на продажу - как сигнал о деградации производительности ?

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

Тут представлена попытка заNULLить значение, если оно равно чему-то.

Но ведь в PostgreSQL есть функция nullif, которая делает ровно то же самое:

NULLIF(значение1, значение2)

Функция NULLIF выдаёт значение NULL, если значение1 равно значение2; в противном случае она возвращает значение1. Это может быть полезно для реализации обратной операции к COALESCE. В частности, для примера, показанного выше:

SELECT NULLIF(value, '(none)') ...

В данном примере если value равно (none), выдаётся null, а иначе возвращается значение value.

То есть в примере выше стоит переписать короче и понятнее:

, nullif(sdate, '1900-01-01') sdate
, nullif(mdate, '1900-01-01') mdate

В своей лекции про "сложные" SELECT я уже рассказывал про возможности оператора CASE, а еще раньше - про возможности оптимизации выполнения запросов с его помощью.

Но иногда он вовсе не нужен! Обратите внимание на картинку сверху...

Посмотрим на использованный тут синтаксис CASE:

CASE
  WHEN условие THEN результат
  [WHEN ...]
  [ELSE результат]
END

Или еще конкретнее:

CASE
  WHEN условие THEN TRUE -- [условие IS TRUE]
  ELSE FALSE             -- [условие IS FALSE, IS NULL]
END

Хм... То есть результат этого CASE эквивалентен значению условия с точностью до NULL!

При обращении условия в NULL такой CASE вернет FALSE, но этого же поведения можно добиться с помощью coalesce:

coalesce(условие, FALSE)

Но если мы говорим о конкретном примере с условием EXISTS, то уж оно-то точно никак не может принимать значение NULL! Значит, coalesce-обертка нам тут не требуется и эту часть запроса можно сократить до одного лишь условия, без всяких CASE:

EXISTS(
  SELECT
    NULL
  FROM
    _inforg20687 t15
  WHERE
    t15._fld1329 = 0::numeric AND
    t15._fld20688rref = t6._idrref AND
    t15._fld20689_type = '\\010'::bytea AND
    t15._fld20689_rtref = '\\000\\000\\001\\010'::bytea AND
    t15._fld20689_rrref = t4._fld6883rref
)

В общем, пишите меньше SQL-кода - и ваши запросы "будут мягкими и шелковистыми"!

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

Фредерик Брукс — американский учёный в области теории вычислительных систем, автор книги «Мифический человеко-месяц». Управлял разработкой OS/360 в IBM. Награждён Премией Тьюринга в 1999 году.

Как оценить производительность СУБД в целом ? Как сравнить производительность СУБД в течении времени ?

А может быть производительность СУБД это вектор: (N1, N2, N3), где:

N1 - количество активных сессий

N2 - количество транзакций

N3 - количество запросов к СУБД в секунду.

Тогда можно получить модуль вектора , как квадратный корень из N1^2 + N2^2 + N3^2 , и уже сравнивать изменение значения и собирать историю.

Затем , построить метрику , и вообще применить механизм статистического анализа - скользящее среднее и т.п.

Почему они это делают? Или очередной риторической вопрос.

Интересная особенность современного поколения разработчиков - они очень любят эксклюзивные блокировки.

1) select ... for update - "самый распространенный вариант"

2) pg_advisory_lock - "у нас фреймворк такой"

В результате - стандартная ситуация, которой лет 30, если не больше - "на тесте все работает, а в продуктивном контуре, при реальных пользователях нет".

Я в СУБД с 97-го года , пока не могу представить сценарий при котором , в современных СУБД нужна эксклюзивная блокировка строк и таблиц, особенно в высоконагруженной OLTP.

Наверное мои знания и представления о современной разработке для СУБД несколько устарели.

Компания решает выполнить решение государства о переходе на отечественное ПО.

Как обстоят дела сейчас:

• Ищется поставщик решения , параллельно пишется техническое решение/пояснительная записка с максимальной размытыми формулировками и цифрами с потолка.

• Поставщик решения разрабатывает решение , в компании формируется  проектная команда, формируется инфраструктура.

• Поставщику решения главное - получить оплату по договору.

• Проектной команде - закрыть этапы и KPI.

• Нагрузочное тестирование проводится в режиме - для галочки. Можно вообще в продакшн выводить без НТ.

• Стресс тестирование проводится - никак.

Результат - ХYZовый код:  "у нас проблемы после перехода на отечественное ПО". Потому, что поставщик решения один и качество ему не важно от слова вообще. В случае проблемы, все списывается на "у вас с инфраструктурой проблемы, у нас на тестовом сервере все работает"

Как было бы лучше:

• Формируется проектная команда.

• Проектная команда определяет доступные ресурсы инфраструктуры.

• Проектная команда формирует требования к решению.

• Объявляется конкурс.

• Поставщики готовят решения.

• Проводятся сравнительные испытания.

• По итогам испытаний выбирается поставщик решения для компании.

Результат — побеждает сильнейший и качественный продукт.

Стратегия давно реализована и применяется госзаказах и конкурсах между разными КБ. В результате получаются отличные изделия которыми можно гордится.

Почему не реализовано в IT ? Это риторический вопрос.

Если вдруг вам понадобилось базу IP2Location перевести из DECIMAL-представления IP-адресов в "родной" для PostgreSQL тип inet, то для IPv4-адресов все будет тривиально:

'0.0.0.0'::inet + ipnum::bigint

А вот для преобразования числа к формату IPv6-адреса придется проявить немного изобретательности:

• "математически" разбиваем число на 8 двухбайтовых сегментов по (2 ^ 16) ^ i

• каждое значение преобразуем в шестнадцатиричную систему счисления и добиваем лидирующими нулями

• склеиваем сегменты через двоеточие и кастуем к inet

array_to_string(ARRAY(
  SELECT
    lpad(to_hex(trunc(
      ipnum % (2::numeric(39,0) ^ ((i + 1) * 16)) / (2::numeric(39,0) ^ (i * 16))
    )::integer), 4, '0')
  FROM
    generate_series(7, 0, -1) i
), ':')::inet

В принципе, после этого мы можем "свернуть" ip_from и ip_to в подсеть, не обращая внимания на исходный формат:

inet_merge(ip_from, ip_to) subnet

А если проиндексируем эти подсети с помощью gist...

CREATE INDEX ON country_inet USING gist(subnet inet_ops);

... то сможем по индексу быстро определять принадлежность произвольного IPv4/IPv6-адреса подсетям с помощью соответствующих операторов примерно таким запросом:

SELECT
  *
FROM
  country_inet
WHERE
  subnet >> '8.8.8.8' AND
  country <> '-'
ORDER BY
  masklen(subnet) DESC
LIMIT 1;

IS NOT NULL + OR

Иногда внутри SQL-запроса возникает необходимость проверить наличие/отсутствие NULL-значения в некотором наборе полей:

a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL

Но то же самое по смыслу условие можно записать гораздо короче с помощью функции coalesce:

coalesce(a, b, c) IS NOT NULL

Подробнее об особенностях работы со сложными выражениями можно прочитать в статье "PostgreSQL Antipatterns: вычисление условий в SQL".

IS NOT NULL + AND

Немного изменим условие - заменим OR на AND:

a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL

Тут нам поможет ROW-конструктор:

(a, b, c) IS NOT NULL

IS NULL + AND

Теперь заменим IS NOT NULL на IS NULL:

a IS NULL AND b IS NULL AND c IS NULL

Тут достаточно вспомнить из логики, что (A and B) эквивалентно not(not A or not B), а (A or B) - not(not A and not B), поэтому легко применяем not к варианту IS NOT NULL + OR:

coalesce(a, b, c) IS NULL

Или с помощью ROW-конструктора:

(a, b, c) IS NULL

Разница будет заключаться в том, что coalesce вычисляет выражения "лениво" (см. "«Ленивый сахар» PostgreSQL").

IS NULL + OR

Остался последний вариант:

a IS NULL OR b IS NULL OR c IS NULL

Тут мы можем "обратить" вариант IS NOT NULL + AND:

NOT (a, b, c) IS NOT NULL

Заметьте, что пара NOT тут "не сокращается", иначе получился бы предыдущий вариант.

Периодически в коде запросов и "заточенных" под них индексов наблюдаю примерно подобные куски:

coalesce("Фамилия", '') || ' ' || coalesce("Имя", '') || ' ' || coalesce("Отчество", '')

Понятно, что тут хотели обезопасить себя от заполненности любого из полей NULL-значением, чтобы случайно вся строка не заNULL'илась.

Правда, тут возникают некоторые артефакты в виде "висящих пробелов" типа ' Иван Иванович' или 'Иванов Иван '.

Но ведь есть решение изящнее и проще - функция concat_ws:

concat_ws(' ', "Фамилия", "Имя", "Отчество")

RTFM!

Хитрый способ в PostgreSQL перебрать всю таблицу медленно и аккуратно, но эффективно, используя указатель ссылающийся на конкретный tuple - ctid.

	var endBlock int
	row := db.QueryRow(ctx, `select relpages from pg_class where oid = 'table1'::regclass::oid`)
	err = row.Scan(&endBlock)
	if err != nil {
		return
	}

	startBlock := 0
	blocksPerIteration := 50
	maxTuplesPerBlock := 150

	for {
		var rows pgx.Rows
		rows, err = db.Query(ctx, `
				select id
				from table1
				where ctid = any (
					array(
    					select format('(%s, %s)', a, b)::tid
    					from generate_series($1::int, $2::int) a(a)
							cross join generate_series(0, $3) b(b)
					))
					and value = '100000'`,
			startBlock,
			startBlock+blocksPerIteration,
			maxTuplesPerBlock,
		)
		if err != nil {
			return
		}
		var id int
		for rows.Next() {
			err = rows.Scan(&id)
			if err != nil {
				return
			}

			slog.Info("found row", "id", id)
		}
		rows.Close()

		startBlock += blocksPerIteration
		if startBlock > endBlock {
			break
		}
		time.Sleep(100 * time.Millisecond)
	}

Нюансы:

• из-за того что тип tid не оптимизирует операции больше/меньше, приходится использовать ctid = any (...)

• для определения maxTuplesPerBlock можно использовать запрос

select 8096 / min(x)
from (
    select pg_column_size(table1) x
    from table1 tablesample system(1)
) d

• можно делать не только select , но и update и delete, но помнить что строки могут и перемещаются как внутри блока, так и между ними

Небольшой пример как копировать данные между базами данных используя go, pgx, и copy.

Предположим что у нас есть два коннекта к базе (одной или нескольким, это не важно). Далее используя io.Pipe() создаём Reader и Writer, и используя CopyTo() и CopyFrom() переносим данные.

  r, w := io.Pipe()

  go func() {
      _, err := db1.PgConn().CopyTo(ctx, w, `copy table1 to stdout binary`)
      if err != nil {
          slog.Error("error", "err", err)
          return
      }
      _ = w.Close()
  }()

  _, err = db2.PgConn().CopyFrom(ctx, r, `copy table1 from stdin binary`)
  _ = r.Close()

Вся прелесть тут в том что используем наиболее быстрый способ с точки зрения PostgreSQL.

Используя copy (select * from where ... order by ... limit ...) to stdout можем регулировать нагрузку на чтение, следить за прогрессом и управлять копированием данных.

В качестве Reader может выступать что угодно, хоть файл csv, хоть другая СУБД, но тогда данные придётся дополнительно конвертировать в формат понимаемый PostgreSQL - csv или tsv, и использовать copy ... from stdin (format csv).

Нюанс: copy ... from stdin binary , binary обязывает использовать одинаковые типы данных, нельзя будет integer колонку перенести в колонку smallint, если такое требуется, то параметр binary надо опустить.

Весь код туть. И ещё немного кода для вдохновения.

Java-разработчики, мы вас ждём на One day offer!

Что такое One day offer?
Мероприятие, на котором можно стать частью нашей команды: без лишних собесов, тестовых заданий и бюрократии

Кто может участвовать?
Мы ищем java-разработчиков middle+ и senior уровня. Неважно, в каком городе ты живешь, главное находиться на территории России

Когда и где?
9 ноября в онлайне

Как попасть?

1. Оставь заявку на участие

2. Пройди предварительное онлайн-интервью и получи приглашение на ивент

3. Подключайся на ивент, чтобы познакомиться с проектами поближе, пройти финальное собеседование и получить свой оффер

Узнать подробности и подать заявку по ссылке
❗️Последний день подачи заявки на участие — 7 ноября

Компания Postgres Professional выпустила несколько пакетов обновлений для системы управления базами данных Postgres Pro Enterprise версий 11, 12, 13, 14 и 15. СУБД Postgres Pro Enterprise построена на PostgreSQL и распространяется на коммерческой основе. По словам создателей СУБД Postgres Pro Enterprise, их платформа предназначена для высоконагруженных систем и содержит ряд усовершенствований по сравнению с базовыми возможностями PostgreSQL.

В новых версиях:

— исправлена проблема в вычислении базы страницы для шестидесятичетырёхбитных идентификаторов транзакций;

— исправлена некорректная работа функции pg_database_size для механизма сжатия CFS;

— устранена ошибка в pgpro_scheduler, когда руководящий планировщик удалял данные о процессе database manager до его инициализации;

— прекращена поддержки ОС Debian 9;

— доработаны модули pg_probackup, pg_hint_plan, PTRACK;

 — расширены возможности хранения типов данных mchar, mvarchar, и теперь корректно хранится до 1 Гбайт данных этих типов.

Более подробно о новых обновлениях можно прочитать на сайте Postgres Pro Enterprise.

Элегантное решение проблемы увеличения инкремента при вставке дубликатов.

Множество раз в различных форумах всплывает вопрос: Как избежать увеличения автоинкремента при вставке дубликатов в таблицу?

Хотя я сам на тех же форумах отвечал что это не критично и не стоит из-за этого волноваться, а если волнуетесь то используйте bigserial и тогда точно хватит! Но душа тем не менее требовала гармонии. И так классика:

create table tbl (
    id serial, 
    val text,
    primary key (id),
    unique (val)
);
-- Вставляем Раз и Два
insert into tbl (val) values ('One'), ('Two');

-- Попробуем так
insert into tbl (val) values ('One'), ('Three') on conflict (val) do nothing;

select * from tbl;

+====+=======+
| id | val   |
+====+=======+
| 1  | One   |
| 2  | Two   |
| 4  | Three |
+----+-------+

Видно, автоинкремент растет даже если мы используем UPSERT Конечно есть вариант с проверкой NOT EXISTS, но это не для слабонервных:

insert into target2 (val) 
select val from source
where not exists (select true from target2 where target2.val = source.val);

И вот наконец то в PostgreSQL 15 завозят оператор MERGE:

merge into target3 t
using source s on t.val = s.val
when not matched then insert (val) values (val);

здесь тестируем SQL запросы

здесь читаем про Применение оператора MERGE в PostgreSQL

FETCH FIRST … ROWS WITH TIES

В этой статье я хочу показать новще решение распостраненной задачи. Найти в таблице записи с максимальным значением в одной из колонок.Для примера мы будем искать сотрудников с максимальной зарплатой.

Первое что приходит в голову - это ORDER BY salary DESC LIMIT 1 Логично но не правильно потому что максимальную зарплату мщгут получать несколько сотрудников.

Стандартное решение - сравнить зарплату с результатом подзапроса:

select * 
from employees
where salary = (select max(salary) from employees);

стандартно - не значит эффективно. Если наша СУБД поддерживает оконные функции:

with ranked as (
    select 
        employees.*,
        dense_rank() over (order by salary desc) r
    from employees
) select * from ranked where r = 1;

немного эффективней, но слишком многословно :(

Есть еще один вариант о котором знают не все. Стандарт SQL:2008 предлагает новое решение данной задачи: FETCH FIRST … ROWS WITH TIES :

select * 
from employees
order by salary desc
fetch first 1 rows with ties;

Компактно и элегантно. Проверить эти у другие запросы можно на SQLize.online