Всем привет!

Если кто не знает - у PostgreSQL есть такая интересная фича, как механизм NOTIFY/LISTEN https://www.postgresql.org/docs/current/sql-notify.html
А вот пример его использования: https://eax.me/postgresql-notify-listen/
По сути мы получаем очередь средствами СУБД. Чем она лучше той же Kafka - тем, что выполняется в контексте транзакции СУБД, и т.об. событие не будет отправлено потребителям до тех пор, пока транзакция не закоммитится. Это логично, более того, для событий, возникающих внутри БД, может быть критично. Что интересно: если LISTEN также находится внутри транзакции - событие тоже будет доставлено только после ее фиксации. Это не так очевидно, но причина аналогична NOTIFY - если транзакция с LISTEN откатится, а в коде клиентского приложения, подписанного на события, будут выполнены какие-то действия вне контура БД - откатить их не получится.
Самое простое применение NOTIFY/LISTEN - инвалидация кэша при изменении таблицы в БД.

Но такую "очередь" в БД можно использовать для произвольных событий, не связанных с БД. Вот подробный пример как интегрировать Spring Integration и NOTIFY\LISTEN https://www.baeldung.com/spring-receiving-postresql-push-notifications
Зачем? Ну например если Kafka разворачивать не хочется, PostgreSQL уже есть, нужна персистентность, и есть множество подписчиков в разных процессах\на разных серверах.

Какие у данного механизма ограничения:
1) производительность PostgreSQL на порядки меньше Kafka
2) есть ограничения на размер сообщения и общий размер очереди
3) все сообщения хранятся в одной очереди pg_notify на диске, которая может стать узким местом.
4) очередь pg_notify в отличие от данных в таблицах не устойчива к падениям СУБД, при перезапуске она очищается, WAL не используется
5) каждая подписка на события забирает один коннект к СУБД из пула

Использовать ли события PostgreSQL? Пилот или небольшая нагрузка + допустимость потерь сообщений - почему бы нет. Остальные случаи надо смотреть детальнее и конечно же проводить НТ

#postgresql #messaging

Вдогонку про storage движки PostgreSQL

Во-первых - если говорить о классических СУБД, то storage движки - тема не новая, можно вспомнить MySQL, который исторически имел несколько движков. InnoDB - самый известный, а вот полный список из официальной поставки:
https://dev.mysql.com/doc/refman/8.4/en/storage-engines.html
Из интересного:
blackhole - ничего не сохраняет
csv - сохраняет понятно куда)
memory - аналогично)
federated - удаленные сервера, горизонтальное масштабирование, но похоже с рядом ограничений (не копал глубоко).
Плюс есть реализации от внешних поставщиков.

Во-вторых, возможность создания storage движков в PostgreSQL дает следующая фича - Table Access Method (TAM) Interface https://www.postgresql.org/docs/current/tableam.html
Это слой абстракции между storage и compute при работе с таблицами. И расширение PostgreSQL может переопределить метод. Например, для реализации принципиально другого способа хранения записей таблицы и конкурентной модификации данных (MVCC). Или реализации шардирования. Или сжатия данных.

Тут стоит упомянуть, что есть похожая технология Foreign Data Wrapper (FDW).
Это реализация стандарта SQL/MED, позволяющая подключать к PostgreSQL внешние удаленные хранилища и, соответственно, выполнять к ним SQL запросы. Т.е. дать унифицированный SQL API для внутренних и внешних таблиц.
Тут детали https://www.percona.com/blog/foreign-data-wrappers-postgresql-postgres_fdw/
В отличие от Table Access Method Interface данная технология сделана для подключения внешних источников данных, в то время как для TAM подразумевает внутреннее хранение. Или локально, или в случае cloud native - использование пусть и облачного, но своего хранилища.

Соответственно, полноценные storage движки - это движки, меняющие ТАМ.
Вот какие бы я выделил:

1) Orioledb, уже упомянутый ранее - ускорение сохранения данных за счет другой реализации MVCC. Еще умеют сжимать данные и работают над cloud ready storage с разделением compute и storage. В последнем случае позиционируют себя как конкурент Neon, но еще неготовый к production https://www.orioledb.com/blog/orioledb-neon-differences#orioledb-1.
Кандидат на замену основного движка PostgreSQL, но команда PostgreSQL пока сопротивляется)
Если нужно больше информации - лучше, чем описано тут я описать не смогу) https://t.me/rybakalexey/240
Из важных особенностей - требует патча ядра PostgreSQL, что нарушает идею бесшовной замены движков

2) TimescaleDB - оптимизация под хранение временных рядов. Если обычные данные PostgreSQL хранятся построчно (row storage), то новый движок добавляет column storage. Конкурент для InfluxDB и Prometheus. Тут можно найти больше деталей https://deepwiki.com/timescale/timescaledb/12-hypercore-and-columnar-storage

3) Citus - горизонтальное масштабирование, но без разделения compute и storage, а путем возможности создания shared-nothing шард и распределенного выполнения запросов. Это дает возможность безопасно хранить данных для разных потребителей на разных шардах, но при этом иметь возможность выполнять аналитические запросы по всему объему данных. Подходит для SaaS систем.

Для справки - есть достаточно много реализаций Foreign Data Wrapper, предоставляющие доступ к файлам:
0) PostgreSQL - единственный FDW, входящий в поставку PostgreSQL. Эталонная реализация.
1) Oracle
2) MSSQL
3) MySQL
4) parquet (parquet - стандартный формат для Data Lake - аналитических БД, Hadoop, spark и вот это все)
...

Маленькое замечание - в отличие от технологии dblink в Oracle, FDW хранит метаданные удаленной таблицы внутри, что позволяет оптимизировать план выполнения запроса. Например, выполнять какую-то агрегацию на удаленном сервере. Или вытаскивать с удаленного сервера только необходимые данные. Это плюс. А минус тот же, что у dblink - ходить в чужую БД плохо, и противоречит микросервисной архитектуре.
Поэтому видится, что основной способ применения FDW - прототипирование, какие-то временные решения или миграции. Вот пример использования для миграции данных из Oracle в PostgreSQL https://habr.com/ru/companies/vtb/articles/819133/

#db #cloud #postgresql

Как нам улучшить PostgreSQL?

Исходя из предудущих моих постов ответ напрашивается такой - установкой расширений (extension).

Вот так это примерно делается:

CREATE EXTENSION IF NOT EXISTS extension_name
WITH SCHEMA schema_name
VERSION version
CASCADE

Вот так обновление:
ALTER EXTENSION extension_name
UPDATE TO version

И удаление:
DROP EXTENSION IF EXISTS extension_name
CASCADE | RESTRICT

Из важного - расширения тоже имеют транзитивные зависимости, их установка/удаление решается опцией CASCADE.
Проблема несовместимых версий видимо решается установкой совместимых версий)
Расширение ставится в конкретную схему.
Всегда можно сделать условное обновление\удаление - с проверкой на существование.

Что меняют расширения:
1) механизм хранения данных в таблицах (Table Access Method Interface)
2) аналогично для индексов, т.е. новые типы индексов (Index Access Method Interface)
3) новые типы данных
4) новые функции
5) внешние источники данных (Foreign Data Wrapper)
6) SQL синтаксис

И к делу.
Вот что интересного я нашел (orioledb, timescaledb, citus и прочие Foreign Data Wrapper уже описал ранее, исключаем):

1) pg_stat_statements - статистика по времени и ресурсам, затраченным на выполнение запросов (где-то год назад уже писал о нем)

Выборка 10 "тяжелых" запросов по общему времени выполнения:
SELECT query, total_time, calls, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

2) PostGIS - новые типы данных в столбцах, индексы и функции для работы с геоданными (point, line, polygon)

Создание таблицы со геоданными:
CREATE TABLE spatial_data ( id SERIAL PRIMARY KEY, name VARCHAR, location GEOMETRY(Point, 4326);

Поиск объектов в радиусе 1000 метров от точки с определенными координатами:
SELECT * FROM spatial_data WHERE ST_DWithin(location, ST_GeomFromText('POINT(-73.975972 40.782865)', 4326), 1000);

3) HypoPG - создание гипотетических индексов. Т.е. индекса физически нет, но планировщик запросов думает, что он есть.

Создание гипотетического индекса:
SELECT * FROM hypopg_create_index('CREATE INDEX ON mytable (cardId)');

Проверка, подействовал ли он:
EXPLAIN SELECT * FROM mytable WHERE cardId = 1;

4) jsquery - более лучший поиск в стиле XPath по JSONB столбцам (столбцам, с бинарным оптимизированным хранением json) Детали: https://habr.com/ru/companies/selectel/articles/928922/

Поиск пользователей с именем «Иван» старше 30 лет или тех, у кого есть тег «vip»:

SELECT * FROM users WHERE data @@ '((name = "Иван" AND age > 30) OR tags.# = "vip")';

5) pgvector - работа с векторными типами данных, PostgreSQL как RAG, AI и все такое. Детали: https://habr.com/ru/companies/selectel/articles/920824/

Создание таблицы для хранения векторов:
CREATE TABLE items (
id SERIAL PRIMARY KEY,
title TEXT,
embedding VECTOR(1536)
);

Вставка данных :
INSERT INTO items (title, embedding) VALUES
('PostgreSQL embeddings', '[0.10, -0.80, 0.45]'),
('Neural image processing', '[0.42, 0.18, -0.35]'),
('Sound pattern matching', '[-0.20, 0.70, 0.60]'),
('Document clustering', '[0.09, -0.79, 0.48]');

Нахождение похожих векторов:

SELECT
a.title AS title_a,
b.title AS title_b,
a.embedding <-> b.embedding AS distance
FROM items a
JOIN items b ON a.id < b.id
ORDER BY distance;

6) pgcrypto - криптографические функции в PostgreSQL. Детали: https://habr.com/ru/companies/selectel/articles/925848/

Сохранение хэшированного пароля:
INSERT INTO users (username, password_hash) VALUES ('new_user', crypt('highly-secure-password123', gen_salt('bf', 10)));

7) hstore - key-value тип данных. На оф.сайте https://www.postgresql.org/docs/current/hstore.html

Создание таблицы с key-value столбцом для хранения атрибутов книги:
CREATE TABLE books (
id serial PRIMARY KEY,
name varchar,
attributes hstore
);

Вставка:
INSERT INTO books (name, attributes) VALUES (
'Harry Potter and the Philosophers Stone',
'author => "J. K. Rowling", pages => 223, series => "Harry Potter"'
);

и выборка по ключу атрибута:
SELECT name, attributes->'author' as author
FROM books
WHERE attributes->'series' = 'Harry Potter'

To be continued...
#PostgreSQL #db

Продолжение поста про расширения PostgreSQL.

Все что не влезло в предыдущий)

8) citext - case-insensitive хранение, а скорее сравнение строк в БД.

Создание таблицы с case-insensitive столбцом и вставка данных:

CREATE TABLE users (user_id SERIAL PRIMARY KEY, username CITEXT, email CITEXT);
INSERT INTO users (username, email) VALUES ('Иван Смирнов', 'ivan.smironoff@mail.ru');

Поиск без учета регистра:

SELECT * FROM users WHERE username = 'иван смирнов';

9) pg_stat_kcache - еще больше статистики по выполнению запросов, в особенности про работу с файловой системой - например, чтение из кэша ОС vs чтение с диска.
Что за данные собираются - можно узнать тут https://github.com/powa-team/pg_stat_kcache

Пример запроса: 5 самых затратных по времени запросов, их частоту выполнения, объемы обращений к диску и оперативной памяти, а также количество переключений контекста ядра ОС.

SELECT
round(total_exec_time::numeric, 0) AS time,
calls,
pg_size_pretty(exec_minflts * 4096) AS reclaim, pg_size_pretty(exec_majflts * 4096) AS faults,
pg_size_pretty(exec_reads) AS reads, pg_size_pretty(exec_writes) AS writes,
round(exec_user_time::numeric, 2) AS user_time, round(exec_system_time::numeric, 2) AS sys_time,
exec_nvcsws AS virtual_switches, exec_nivcsws AS involuntary_switches,
LEFT(query, 27) AS query_text
FROM pg_stat_statements s
JOIN pg_stat_kcache() k
USING(userid, dbid, queryid)
ORDER BY total_exec_time DESC
LIMIT 5;

10) isn - добавляет новые типы данных для проверки разного рода штрих кодов EAN13, UPC, ISBN (books), ISMN (music), and ISSN (serials) на допустимые префиксы. У модуля есть минус - набор допустимых префиксов постоянном меняется, следовательно, надо поддерживать актуальность расширения и может быть гэп по времени по поступлению обновлений.

Создание и вставка данных в таблицу с ISBN столбцом (уникальный номер книги):

CREATE TABLE test (id isbn);
INSERT INTO test VALUES('9780393040029');

11) pg_hint_plan - позволяет "прибить гвоздями" кусок плана выполнения запроса

Тут мы указываем, что надо использовать HashJoin и сразу смотрим реальный план выполнения запроса:

/*+ HashJoin(pt st) */
EXPLAIN SELECT * FROM s1.t1 st
JOIN public.t1 pt ON (st.id=pt.id);

12) pg_cron - что делает - понятно. Можно подумать, что расширение нарушает принцип - никакой логики в БД. Но оно полезно для другого - для служебных задач, например, периодически делать VACUUM:

SELECT cron.schedule('59 23 * * *', 'VACUUM');

На цифре 12 пожалуй и остановлюсь.

Что еще важно.
Часть расширений уже входит в поставку.
Какие именно - можно узнать выполнив команду:

SELECT * FROM pg_available_extensions;

Набор может различаться, в официальном дистрибутиве их на данный момент 60, в PostgrePro - 48, Сбер Pangolin - 119.

#postgresql #db #plugins

UUID ключи в PostgreSQL

Я уже писал про версии UUID https://t.me/javaKotlinDevOps/264
Особенно интересной выглядит 7-я версия для использования в БД для построения индексов по двум причинам:
1) позволяет сортировать записи по времени создания
2) значение содержит метку времени, ее можно извлечь
Ну и эффект, наблюдаемый только в БД - записи ложатся последовательно в индексах и, соответственно, partition благодаря тому, что генерируются монотонно возрастающие значения.

Стандарт был принят в мае 2024 года https://datatracker.ietf.org/doc/rfc9562/

И не прошло и полгода (прошел год, но в мире БД это кажется даже быстро) и появляется PostgreSQL 18 c нативной поддержкой UUID v7 (функции uuidv7() и uuid_extract_timestamp) https://habr.com/ru/companies/spring_aio/articles/946168/

P.S. Если вчитаться в стандарт - попадаешь в кроличью нору:
1) для целей безопасности метка времени обрезается до миллисекунд
2) но чтобы получить возрастающие значения используется счетчик
3) счетчик инициализируется случайным числом, во избежание коллизий
4) есть защита от переполнения счетчика - допустимо использовать как счетчик ту часть метки времени, которую мы обнулили ранее, главное не перейти за границы миллисекунды. Если и этого не хватит - вопрос...
5) генератор должен хранить время t0, чтобы при переводе времени продолжать использовать исходное время и значения были уникальными и монотонно возрастающими
...

#postgresql #uuid