Совет по Postgres на сегодня: используйте lock_timeout

Устанавливайте lock_timeout в сессиях приложения — так блокировки автоматически снимаются, если держатся слишком долго. Это поможет избежать ситуаций, когда таблица остаётся залоченной и мешает другим операциям.

ALTER SYSTEM SET lock_timeout = '10s';

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

👉 @SQLPortal

Команда Pgpool выпустила срочные обновления для всех актуальных веток:

> 4.6.1
> 4.5.7
> 4.4.12
> 4.3.15
> 4.2.22

Этот релиз содержит исправление уязвимости безопасности.

В механизме аутентификации клиентов Pgpool-II обнаружена уязвимость, позволяющая обойти аутентификацию. В результате злоумышленник может войти под любым пользователем, что может привести к утечке данных, их подмене или даже полной остановке базы данных.
(CVE-2025-46801)

Уязвимость затрагивает:

> Все версии Pgpool-II 4.0 и 4.1
> Версии с 4.2.0 по 4.2.21
> С 4.3.0 по 4.3.14
> С 4.4.0 по 4.4.11
> С 4.5.0 по 4.5.6
> Версию 4.6.0

Рекомендуется срочно обновиться до: 4.6.1, 4.5.7, 4.4.12, 4.3.15 или 4.2.22 — или изменить конфигурации, чтобы они не попадали под уязвимые шаблоны 😡

> Подробности

👉 @SQLPortal

Хочешь узнать, когда та или иная фича появилась в Oracle Database?

Зацени Oracle Database Features & Licensing:

> https://apex.oracle.com/pls/apex/r/features/dbfeatures/home

Там можно посмотреть, в каких версиях доступна нужная функциональность.

А во вкладке Licensing — в каких редакциях Oracle её можно легально использовать 🔗

👉 @SQLPortal

Нашёл крутейший сайт для тех, кто хочет с нуля вкатиться в SQL. Там всё по полочкам: SELECT, JOIN, WHERE и даже про подзапросы есть. Прям интерактивно учишься — пишешь код, сразу видишь результат.

Если хочешь разобраться в базах — w3schools.com/sql 😡

👉 @SQLPortal

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

Пример:

CREATE INDEX CONCURRENTLY foobar ON foo (bar);

👉 @SQLPortal

Вышло pg_ivm 1.1.1 — расширение для инкрементального обновления материализованных представлений в PostgreSQL!

Теперь можно обновлять только изменённые данные, не пересоздавая всё представление целиком

Команда: REFRESH MATERIALIZED VIEW больше не будет тормозить всё на свете

Инкрементальное обновление происходит автоматически при изменении базовых таблиц.

Подробнее: https://www.postgresql.org/about/news/pg_ivm-111-released-3079/ 😎

👉 @SQLPortal

Когда вы используете столбцы в формулах, например:

WHERE quantity * unit_price > ...

оптимизатору может быть сложно точно оценить, сколько строк будет возвращено.

Чтобы помочь Oracle-оптимизатору в таких случаях, включите динамическую статистику на уровне auto:

ALTER SESSION SET optimizer_dynamic_sampling = 11;

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

👉 @SQLPortal

Теперь можно скопировать схему таблицы прямо из редактора Supabase и использовать её как контекст для вашего любимого ИИ-инструмента

Просто нажмите на три точки рядом с названием таблицы и выберите Copy — в буфер попадут типы данных, ограничения и значения по умолчанию 😡

👉 @SQLPortal

Удобная фишка в psql:

Получение CSV из SQL-запроса:

SELECT * FROM customers \g (format=csv) ~/Downloads/clients.csv

Команда \g (format=...) сохраняет результаты запроса в указанном формате — в данном случае CSV.

Также поддерживаются форматы html и (для любителей математики) latex:

SELECT * FROM customers \g (format=html) ~/Downloads/clients.html

👉 @SQLPortal

Практика SQL

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

Можно сделать так: (смотри после того как сделал сам) 😘

SELECT u.user_id, username, training_id, training_date, count( user_training_id ) AS count
FROM users u JOIN training_details t ON t.user_id = u.user_id
GROUP BY u.user_id, username, training_id, training_date
HAVING count( user_training_id ) > 1
ORDER BY training_date DESC;

👉 @SQLPortal

Совет по Postgres на сегодня: Покрывающие индексы

Индексы позволяют создавать как ключевые (индексируемые) столбцы, так и дополнительные столбцы с помощью INCLUDE. Эти INCLUDE-столбцы не входят в ключ индекса, но их данные сохраняются вместе с индексом, благодаря чему некоторые запросы могут быть обработаны исключительно за счёт индекса.

Ключевые столбцы индекса используются для сортировки и поиска. INCLUDE-столбцы «покрывают» дополнительные запросы, поэтому такие индексы часто называют покрывающими (covering indexes). Покрывающие индексы позволяют оптимизировать запросы, чтобы использовать только сканирование индекса (index-only scan) вместо полного сканирования таблицы.

Пример покрывающего индекса:

CREATE INDEX idx_orders_customer_date_include_total
ON orders (customer_id, order_date)
INCLUDE (total_amount);

> customer_id, order_date входят в ключ индекса и участвуют в поиске и сортировке
> total_amount включён, чтобы избежать обращения к таблице, если он требуется в запросе

Этот запрос может быть полностью выполнен с использованием только индекса:

SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 123
ORDER BY order_date;

👉 @SQLPortal

В версии Postgres 15, спустя многие годы ожидания, наконец появилась команда MERGE

MERGE объединяет несколько операций с данными (INSERT, UPDATE, DELETE) в один атомарный оператор.
До этого подобное поведение приходилось реализовывать через INSERT ... ON CONFLICT или с помощью SELECT + UPDATE + INSERT в связке с CTE и функциями.

MERGE особенно полезен при работе с крупными объёмами данных — например, при объединении батчей или стримов данных.

А вот статья, где Жан-Поль Аргюдо делится полезными примерами кода и советами по внедрению MERGE в ваши Postgres-процессы: тык

👉 @SQLPortal

Оконные функции в Postgres полезны, когда нужно выполнять вычисления по нескольким строкам, не объединяя их в одну. Такой набор строк называется "frame" (окно).

Именно конструкция OVER определяет границы этого окна.

Пример: накопительный итог продаж.

В этом случае для каждой строки окно будет включать текущую дату и все предыдущие строки. То есть для каждой строки формируется своё окно, и итог считается по нему.

Результат: ты получаешь сумму продаж на каждый день с учётом всех предыдущих.
Это мощный способ анализировать тренды без подзапросов и JOIN

👉 @SQLPortal

SQL Cheatsheet — шпора, которую стоит сохранить

Она спасёт тебе интервью, проект и задницу 😃

👉 @SQLPortal

PostgreSQL позволяет задать любое строковое значение (включая Unicode-символы) для отображения NULL. Это удобно, чтобы легко отличать NULL от пустых строк.

\pset null '☘️'

Это значение можно прописать и по умолчанию — в конфигурационном файле psqlrc

👉 @SQLPortal

В копилку тренажёров: sqltutor.ru

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

👉 @SQLPortal

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

Если нужно сохранить, например, общую сумму как unit_price плюс налог, используйте:

ALTER TABLE coffee_shop_sales
  ADD COLUMN total_sale_amt numeric
  GENERATED ALWAYS AS
    ((unit_price * (1 + sales_tax_pct / 100) * transaction_qty)) STORED;

STORED означает, что значение хранится физически и автоматически пересчитывается при изменении исходных данных.

Пример нормализации телефонного номера, приходящего в разном виде:

ALTER TABLE contacts
  ADD COLUMN normalized_phone text
  GENERATED ALWAYS AS (REGEXP_REPLACE(phone, '[^0-9]', '', 'g')) STORED;

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

Преимущества генерируемых колонок:

> Производительность — ускоряют частые вычисления.
> Целостность данных — уменьшают риск ошибок при ручном написании выражений в запросах.

Виды генерируемых колонок:

> STORED — значения сохраняются в таблице и пересчитываются при вставке/обновлении строки. Улучшает производительность, так как нет вычислений при запросе.

> VIRTUAL — значения вычисляются на лету при запросе, но не хранятся. Удобно, когда хранение нецелесообразно (экономия места и др.).

Используя генерируемые колонки, вы упрощаете выражения в запросах, снижаете нагрузку при их выполнении и можете дополнительно ускорить работу с помощью индексов. ✋

👉 @SQLPortal

Разработчики на Postgres обожают CTE

Но один из способов их использования часто остаётся недооценённым — это обновление таблиц через CTE, например:

> Перемещение данных из одной таблицы в другую
> Или из одной таблицы в несколько

💡И поскольку INSERT и DELETE выполняются внутри CTE, они автоматически попадают в одну транзакцию — это удобно и безопасно.

Эта тема подробно разобрана с хорошими примерами в одной из архивных статей Дэвида Кристенсена

👉 @SQLPortal

Если ты разработчик, скорее всего, тебе приходится иметь дело с JSON-данными через большее количество API, чем хотелось бы.

А ты знал, что можно преобразовать любой JSON в реляционную таблицу с колонками SQL?

В PostgreSQL 17 есть фича — JSON_TABLE, которая позволяет на лету разворачивать массивы JSON и работать с ними, как с обычными таблицами в SQL

Ставьте лайки под постами. если они оказались полезны ✊

👉 @SQLPortal

Если вы начинаете новый проект на Postgres, какие расширения обязательно установить с первого дня?

На внутреннем обсуждении на прошлой неделе мы пришли к такому почти универсальному списку для любой базы данных Postgres:

> pg_stat_statements — для сбора и анализа статистики выполнения SQL-запросов. Помогает находить медленные и часто вызываемые запросы.
> pgAudit — для расширенного аудита действий в базе данных. Особенно важно для соответствия требованиям безопасности и контроля доступа.
> pg_cron — встроенный планировщик задач на основе cron. Удобен для регулярных бэкапов, очистки и других периодических задач прямо в базе.
> auto_explain — автоматически логирует планы выполнения запросов, особенно полезен для отладки медленных запросов.

👉 @SQLPortal