Новость. Выпущено расширение pg_ai_query для PostgreSQL, которое позволяет генерировать SQL-запросы на естественном языке и анализировать производительность запросов с помощью ИИ.

https://github.com/benodiwal/pg_ai_query

👉 @SQLPortal

Postgres поддерживает point-in-time recovery — возможность откатить базу к состоянию в прошлом.

Это работает за счет комбинации восстановления из бэкапа и проигрывания WAL.

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

Это приближает нас к нужному времени, но чаще всего остается разрыв в несколько часов. Далее Postgres начинает проигрывать write-ahead log (WAL). Он применяет все вставки, обновления и удаления, которые произошли между временем бэкапа и точкой восстановления.

Основной инстанс Postgres можно настроить на непрерывный архив WAL, например, в s3 — именно оттуда он получает логи для PITR.

Пример: сегодня 24 ноября. Нужно восстановиться на 23 ноября в 5 утра. Есть ежедневные бэкапы в 1 утра и архив WAL с ретеншеном 48 часов. Для PITR выполняем:

- создаем новый Postgres-сервер
- восстанавливаем бэкап от 23 ноября 1:00
- проигрываем WAL за промежуток 1:00 -> 5:00

После этого можно просматривать базу в состоянии на этот момент времени.

👉 @SQLPortal

Lateral join отлично подходят, когда нужно выбрать top-N из нескольких таблиц.

LATERAL JOIN позволяет подзапросу в секции FROM обращаться к колонкам таблицы, которая стоит перед ним.

Обычный join рассматривает таблицы и подзапросы как независимые наборы данных. Подзапрос в FROM вычисляется один раз до выполнения основного запроса и не может видеть строки таблицы, к которой присоединяется.

Lateral join работает как коррелированный подзапрос, но может возвращать несколько строк. Он выполняет подзапрос для каждой строки внешней (левой) таблицы.

Смотри пример в видео.

👉 @SQLPortal

Получите SQL базу в облаке.

Без регистрации, оплаты, ожидания и настройки.

Запустите в терминале → npx get-db

👉 @SQLPortal

Aurora DSQL теперь показывает стоимость SQL-запросов

В Aurora DSQL появилась функция EXPLAIN ANALYZE VERBOSE, которая позволяет узнать точную стоимость выполнения SQL-запроса. Это даёт возможность напрямую оценивать влияние оптимизации и индексов на финансовые затраты.

В регионе us-east-1 запрос без индекса стоит $0.00000208, что примерно соответствует $2.10 за миллион выполнений. После добавления индекса стоимость снижается почти в 12 раз — до $0.00000018, или около 18 центов за миллион запросов.

Повторное выполнение ещё быстрее: при кэшированном плане стоимость падает примерно в 10 раз по сравнению с первым запуском.

Кроме того, индексированный вариант значительно лучше масштабируется = стоимость полного сканирования растет линейно с увеличением размера таблицы, тогда как индексированный подход увеличивает расходы примерно логарифмически.

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

Подробнее: https://docs.aws.amazon.com/aurora-dsql/latest/userguide/understanding-dpus-explain-analyze.html

👉 @SQLPortal

Ищешь самое популярное значение в Postgres? Не обязательно городить GROUP BY + ORDER BY + LIMIT 1. Можно просто использовать mode.

Пример:

SELECT
  mode() WITHIN GROUP (ORDER BY color) AS most_popular_color
FROM
  products_sold;

Коротко и удобно.

👉 @SQLPortal

CTE против подзапросов: когда использовать CTE

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

✅ Когда нужно переиспользовать подзапрос несколько раз в одном выражении. Если ловишь себя на повторе одного и того же подзапроса, то CTE будет более правильным вариантом.

👉 @SQLPortal

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

✓ Более 5 часов в 39 видео.
✓ Запросы, соединения, JSON, индексы и создание таблиц.
✓ Все объясняется на практике и постепенно.
✓ Бесплатно → https://databaseschool.com/series/intro-to-postgres

👉 @SQLPortal

Разделить колонку, в которой встречаются нули, в Postgres можно через NULLIF, чтобы избежать деления на 0.

SELECT
  total_sales / NULLIF(total_customers, 0) AS avg_sale_per_customer
FROM
  sales_data;

👉 @SQLPortal

Уменьшаем время выборки из БД с 2–3 секунд до ~100 мс:

SELECT * FROM transactions
WHERE user_id = 40
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

На первый взгляд запрос нормальный?
ДА, вроде ок.

Но:

- это OFFSET-пагинация
- и это прям ПЛОХО
- БД вытягивает 10020 строк и выкидывает 10000, чтобы показать 20, лол
- чем больше OFFSET, тем больше нагрузка на БД
- со временем запрос начинает занимать больше 2 секунд, пока растет объем данных

Решение:

KEYSET (seek) пагинация: добавляем условие вида created_at < last_seen_timestamp

SELECT * FROM transactions
WHERE user_id = 40
  AND created_at < '2024-05-01 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

- так БД может сразу прыгнуть по индексу
- по сути это "дай следующие 20 записей после этого timestamp", где timestamp используется как ключ
- время реально падает с секунд до примерно 100–200 мс

Что если timestamp не уникален, есть дубли:

Добавляем tie-breaker: (created_at, id) и в WHERE, и в ORDER BY:

WHERE (created_at, id) < ('2024-05-01 10:00:00', 98765)
ORDER BY created_at DESC, id DESC

Так пагинация остается быстрой и детерминированной, даже при одинаковых created_at.

👉 @SQLPortal

Частичное извлечение данных в Postgres с помощью SQL:

Все эти запросы возвращают один и тот же результат: 78901

SELECT substring('USER_ACCT-78901-NYC-ACTIVE' FROM 11 FOR 5);

SELECT split_part('USER_ACCT-78901-NYC-ACTIVE', '-', 2);

SELECT (regexp_matches('USER_ACCT-78901-NYC-ACTIVE', 'ACCT-(\d+)-'))[1];

👉 @SQLPortal

Когда лучше использовать подзапрос вместо CTE

1. Обновление таблицы: если ты обновляешь данные в таблице, можно использовать подзапрос, например коррелированный подзапрос.

2. Вычисление скалярного значения: когда нужно добавить одно агрегированное значение из другой таблицы как новый столбец для каждой строки в результирующем наборе.

3. Использование EXISTS или NOT EXISTS: когда нужно просто проверить, существуют ли связанные строки, а не извлекать сами данные.

👉 @SQLPortal

Postgres CONCAT_WS удобно собирает строку даже если есть NULL:

SELECT CONCAT_WS(' ', first_name, middle_initial, last_name);

Если middle_initial = 'T', то результат будет:

John T Smith

Если middle_initial = NULL, то получится:

John Smith

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

👉 @SQLPortal

Нашёл полезную статью, которая рассказывает, как автоматизировать резервное копирование удалённой PostgreSQL-базы по расписанию, не храня дампы на том же сервере.

Автор показывает пример на Python и Docker: pg_dump, cron, настройка переменных окружения и отправка уведомлений в Telegram. Решение подходит тем, кому нужно простое и дешевое резервирование без отдельного сервера под бэкапы.

👉 @SQLPortal

Неделя Postgres contrib. Поговорим о фичах, которые уже поставляются вместе с Postgres, но по умолчанию выключены.

Contrib-расширения входят в библиотеку contrib внутри PostgreSQL. То есть код расширения уже лежит в системе, если ты ставил Postgres из официальных пакетов. Все, что нужно — выполнить CREATE EXTENSION, чтобы включить его.

Расширения, которые не входят в contrib, надо собирать или компилировать вместе с Postgres перед тем, как их можно будет подключить через CREATE EXTENSION.

👉 @SQLPortal

😏😏😏

👉 @SQLPortal

SQL-совет:

Простая демонстрация вложенных выражений CASE.

👉 @SQLPortal

Postgres pgstattuple — расширение из contrib, которое показывает физическое состояние таблицы или индекса.

Пример:

SELECT * FROM pgstattuple('accounts');

Что там видно:
• tuples — количество строк
• dead tuples — строки, которые удалены или изменены
• free space и free percent — свободное место и его процент

Если dead tuples или free percent слишком высокие, стоит проверить настройки vacuum.

👉 @SQLPortal

В DBeaver можно спокойно работать с большими SQL-скриптами через панель Outline.

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

Функция доступна в десктопных версиях, начиная с 24.0.

👉 @SQLPortal

Postgres расширение из набора contrib под названием amcheck позволяет проверить таблицы и индексы на повреждение.

Проверка индекса:

SELECT * FROM bt_index_check('accounts_pkey'::regclass::oid);

Проверка таблицы:

SELECT * FROM verify_heapam('accounts');

Если вернулось 0 строк — все ок. Если что-то вернулось — значит есть повреждение.

Если ты собираешься решать Advent of Code в SQL или Postgres в этом году, у Грега Сабино Маллейна есть серия постов, где он пару лет назад проходил AoC на Postgres и разобрал кучу полезных приемов и примеров кода.

https://crunchydata.com/blog/topic/advent-of-code

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

👉 @SQLPortal