Postgres Guru | Базы данных

⚡Открытая трансляция главного зала Saint HighLoad++ 2025!🖐️Подключайтесь и слушайте доклады от спикеров Garage Eight, Яндекса, Сбера, Ozon и других компаний.Saint HighLoad++ 2025 — это конференция, которая определяет будущее высоконагруженных систем. 23 и 24 июня все желающие могут бесплатно посмотреть онлайн-трансляцию главного зала. Открытую трансляцию организовали совместно с генеральным партнером конференции — Garage Eight. Garage Eight — международная продуктовая IT-компания. С 2011 года развивает экосистему высоконагруженных инвестиционных продуктов, у которых сотни тысяч пользователей в 183 странах. Продукты отмечены наградами от Global Banking and Finance Review, Global Business Magazine и World Business Stars.Как всегда, в главном зале — топовые эксперты и самые актуальные темы.✅ Подключайтесь! 

🤖 Использованте FILTER в PostgreSQL. FILTER в PostgreSQL позволяет применять агрегатные функции (такие как SUM, COUNT, AVG и др.) только к строкам, удовлетворяющим определенному условию. Это мощная альтернатива использованию CASE WHEN внутри агрегатных функций. Синтаксис будет таким: АГРЕГАТНАЯ_ФУНКЦИЯ(столбец) FILTER (WHERE условие) Где: АГРЕГАТНАЯ_ФУНКЦИЯ - любая агрегатная функция (`COUNT`, `SUM`, `AVG`, `MAX`, `MIN` и др.); условие - логическое выражение, определяющее, какие строки включаются в расчет. Примеры использования: Подсчет строк по условию SELECT COUNT(*) FILTER (WHERE status = 'active') AS active_users, COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_users FROM users; Суммирование значений по условию SELECT SUM(amount) FILTER (WHERE category = 'Electronics') AS electronics_revenue, SUM(amount) FILTER (WHERE category = 'Clothing') AS clothing_revenue FROM sales; Без FILTER аналогичный результат можно получить с помощью CASE WHEN, но синтаксис будет сложнее: SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_users, SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_users FROM users; Почему FILTER лучше? ✅ Читаемость - код становится чище и понятнее; ✅ Производительность - PostgreSQL оптимизирует FILTER эффективнее, чем вложенные CASE; ✅ Гибкость - можно комбинировать с другими агрегатными функциями. FILTER особенно полезен в сочетании с GROUP BY: SELECT department, COUNT(*) FILTER (WHERE salary > 5000) AS high_earners, AVG(salary) FILTER (WHERE experience_years >= 5) AS avg_senior_salary FROM employees GROUP BY department; Ограничения: ❌ Не поддерживается во всех СУБД - FILTER является частью стандарта SQL:2003, но работает не везде; ❌ Нельзя использовать в WHERE или HAVING - только в SELECT с агрегатными функциями; ❌ Условие должно быть детерминированным - нельзя использовать функции с побочными эффектами (например, random()). В итоге FILTER в PostgreSQL - это мощный инструмент для условной агрегации данных, который делает код: ✅ Чище (избегаем сложных CASE WHEN); ✅ Быстрее (лучше оптимизируется); ✅ Гибче (работает с любыми агрегатными функциями) . На этом все! До связи! #queries

🤖 PREPARE TRANSACTION в PostgreSQL: двухфазная фиксация транзакций. PREPARE TRANSACTION - это команда PostgreSQL, которая подготавливает текущую транзакцию к двухфазной фиксации. После подготовки транзакция переходит в особое состояние и может быть завершена (зафиксирована или отменена) позже, возможно, из другого сеанса или даже с другого сервера. Состояние транзакции будет записано на диск и она может завершиться удачно, даже после неожиданного отключения сервера. Про двухфазную фиксацию транзакций в канале давно уже выходил пост. Так что, можете ознакомиться, если не знаете что это такое. Синтаксис будет таким: PREPARE TRANSACTION id_транзакции; Эта команда должна выполняться внутри блока транзакции, который начинается с команды BEGIN. Пример: BEGIN; INSERT INTO accounts (user_id, amount) VALUES (1, 100); UPDATE balances SET total = total - 100 WHERE user_id = 2; PREPARE TRANSACTION 'transfer_123'; Здесь у нас 'transfer_123' - id, который мы назначили нашей подготовленной транзакции. Фиксация подготовленной транзакции происходит с помощью следующей команды: COMMIT PREPARED 'transfer_123'; Откат подготовленной транзакции можно сделать так: ROLLBACK PREPARED 'transfer_123'; Фактически команда PREPARED TRANSACTION нам, простым DBA, может ни когда и не пригодиться, если конечно мы не разрабатываем свой собственный менеджер транзакций для какого-то сложного распределенного приложения. Но знать о распределенных транзакциях все же полезно, так как они могут натворить больших дел в базе данных. Все дело в том, что если в нашей базе есть какие-то подготовленные и не завершенные транзакции, о которых мы не знаем, то мы можем довести базу данных до зацикливания ID транзакций, так как подготовленные транзакции препятствуют VACUUM потому что будут удерживать все необходимые им ресурсы пока незавершатся. Убедиться, что у нас в базе нет незавершённых подготовленных транзакций нам поможет представление pg_prepared_xacts и вот такой запрос: SELECT gid, prepared, owner, database FROM pg_prepared_xacts; А чтобы обезопасить себя на будущее, если вам действительно не нужны подготовленные транзакции, можно выставить параметр PostgreSQL max_prepared_transactions в ноль. Ограничения и особенности: 📌 Идентификаторы транзакций должны быть уникальными в пределах кластера; 📌 Временные объекты (temporary tables) не поддерживаются в подготовленных транзакциях; 📌 Ресурсы: подготовленные транзакции удерживают блокировки и ресурсы до завершения; 📌 Таймауты: нет встроенного механизма автоматического отката по таймауту PREPARE TRANSACTION- мощный инструмент для сложных распределенных сценариев, но он требует глубокого понимания и аккуратного использования. На этом все! До связи! #pgbase

🛠️ PG-OSC: Инструмент для онлайн-изменения схемы PostgreSQL. PG-OSC (PostgreSQL Online Schema Change) - это инструмент для выполнения изменений схемы базы данных PostgreSQL с минимальным временем простоя или блокировками. Он позволяет вносить изменения в структуру таблиц без длительной блокировки, что критически важно для высоконагруженных приложений. Т. е. мы можем добавлять новые колонки в таблицы, переименовывать существующие и т.д. с минимальными блокировками. Официальный GitHub утилиты: ➡️ https://github.com/shayonj/pg-osc Утилита поддерживает версии PostgreSQL 9.6 и выше. Основные возможности: 📌 Минимальные блокировки: Изменения выполняются без длительных эксклюзивных блокировок; 📌 Работа без остановки: Приложение может продолжать читать и писать данные во время миграции; 📌 Технология shadow-таблиц: Создается новая версия таблицы с синхронизацией изменений; 📌 Поддержка различных операций: Добавление/удаление столбцов, изменение типов данных, добавление/удаление ограничений Типичные сценарии использования: ✅ Добавление или удаление столбцов в больших таблицах; ✅ Изменение типов данных столбцов; ✅ Добавление или удаление ограничений (constraints); ✅ Переименование столбцов или таблиц. Сама технология pg-osc достаточна проста и похожа на такие утилиты как pg_repack, например. Т.е. pg-osc создает временную таблицу, делает с ней все необходимые манипуляции, переносит в нее все данные из исходной таблицы и, наконец удаляет исходную таблицу. Установка: pg-osc написано на Ruby, поэтому устанавливается через Ruby-gem: gem install pg-osc Также утилиту можно установить с помощью Docker: docker pull shayonj/pg-osc:latest Синтаксис использования утилиты будет следующим: # pg-online-schema-change perform -a, --alter-statement=ALTER_STATEMENT -d, --dbname=DBNAME -h, --host=HOST -p, --port=N -s, --schema=SCHEMA -u, --username=USERNAME Здесь ALTER_STATEMENT и будет тем самым выражением ALTER, которое что-то меняет в структуре вашей базы данных. Остальные ключи , я думаю, понятны по контексту. Пример использования из официальной документации (переименовываем колонку таблицы): export PGPASSWORD="" pg-online-schema-change perform \ --alter-statement 'ALTER TABLE books RENAME COLUMN email TO new_email' \ --dbname "postgres" \ --host "localhost" \ --username "jamesbond" \ Преимущества перед стандартными подходами: ✅ Избегает длительных блокировок ALTER TABLE; ✅ Позволяет приложению оставаться доступным; ✅ Контролируемая нагрузка на БД за счет пакетного копирования; ✅ Возможность отката на ранних этапах процесса. Более подробно обо всех возможностях утилиты читаем в документации. На этом все! До связи! #pgutils

🤖 Функция JSON_TABLE() в PostgreSQL 17. Одним из очень полезных нововведений в PostgreSQL 17 стала функция JSON_TABLE(), которая предназначена для преобразования данных в формате JSON в табличный вид, что позволяет использовать их в SQL-запросах как обычную таблицу. Эта функция поддерживает сложные структуры JSON и позволяет извлекать данные из вложенных уровней, а также формировать реляционные представления. Основные особенности JSON_TABLE(): 📌 Использование в FROM-предложении: JSON_TABLE() можно использовать в FROM-предложении запроса SELECT, UPDATE, DELETE, а также как источник данных в MERGE-запросе; 📌 Извлечение данных: Функция использует JSON-путь (JSON path expression) для извлечения нужных данных из JSON-структуры; 📌 Формирование таблицы: С помощью COLUMNS-предложения можно задать структуру результирующей таблицы, указав, какие данные из JSON-объекта должны быть извлечены и каким образом. Синтаксис будет таким: JSON_TABLE ( context_item, path_expression [AS json_path_name] [PASSING {value AS varname} [, ...]] COLUMNS (json_table_column [, ...]) [ {ERROR | EMPTY} ON ERROR ] [PLAN (json_table_plan) | PLAN DEFAULT ({INNER | OUTER} [, {CROSS | UNION} ] | {CROSS | UNION} [, {INNER | OUTER} ])] ) На первый взгляд кажется сложным. Давайте с этим разбираться. context_item - это источник наших данных JSON/JSONB. Это может быть таблица, содержащая JSON данные; path_expression - SQL или JSON выражение, определяющее запрос; COLUMNS - определяет схему таблицы, в которую мы загружаем JSON. Т.е. здесь мы определяем в какие колонки какие JSON данные грузить. Со всеми остальными параметрами функции более подробно можно ознакомиться в документации: ➡️ https://postgrespro.ru/docs/postgrespro/17/functions-json#FUNCTIONS-SQLJSON-TABLE Примеры использования (из документации): Предположим, у нас есть таблица my_films, где хранится JSON-данные: CREATE TABLE my_films (js jsonb); INSERT INTO my_films VALUES ('{"favorites": [{"kind": "comedy", "films": [{"title": "Bananas", "director": "Woody Allen"}, {"title": "The Dinner Game", "director": "Francis Veber"}]}, {"kind": "horror", "films": [{"title": "Psycho", "director": "Alfred Hitchcock"}]}, {"kind": "thriller", "films": [{"title": "Vertigo", "director": "Alfred Hitchcock"}]}, {"kind": "drama", "films": [{"title": "Yojimbo", "director": "Akira Kurosawa"}]}]}'); Используем JSON_TABLE() для извлечения жанра, названия и режиссера: SELECT jt.* FROM my_films, JSON_TABLE ( js, '$.favorites[*]' COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', NESTED PATH '$.films[*]' COLUMNS ( title text PATH '$.title', director text PATH '$.director') ) ) AS jt; Здесь у нас ещё присутствует параметр FOR ORDINALITY, который добавляет столбец с порядковым номером строки, а также NESTED PATH (вложенные пути) - позволяет извлекать данные из вложенных JSON-объектов или массивов. С помощью параметров PASSING и FORMAT JSON мы можем извлечь JSON-данные и преобразовать их в строку: SELECT jt.* FROM my_films, JSON_TABLE ( js, '$.favorites[*]' PASSING 'Alfred Hitchcock' AS filter COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', title text FORMAT JSON PATH '$.films[*].title' OMIT QUOTES, director text PATH '$.films[*].director' KEEP QUOTES ) ) AS jt; Функция JSON_TABLE() в PostgreSQL 17 - мощный инструмент для преобразования JSON-данных в реляционный формат. Она позволяет работать с вложенными структурами, извлекать нужные поля и формировать таблицы, которые можно использовать в SQL - запросах. На этом все! До связи! #pgjson

✅ Как настроить реакцию на изменения в таблицах Postgres? Как передать эти изменения в микросервисы, в Kafka и в другие СУБД, например в Clickhouse? Расскажем на открытом уроке «Событийная интеграция Postgres» посвященный курсу «PostgreSQL для администраторов баз данных и разработчиков» ✅ Научитесь выбирать правильный способ событийной интеграции ✅ Посмотрите, как и что можно реализовать для надежной передачи данных из Postgres во внешние системы 👉Узнаете про опыт других предприятий и протестируйте обучение на открытом уроке https://tglink.io/a905c3cf23a1?erid=2W5zFJhYbnG #реклама О рекламодателе