Важные индексы в Postgres

🔸B-tree
Числа или текст, которые можно упорядочить или сравнить
Обычно используются с < > = или ORDER BY

🔸Multi-column B-tree
Числа или текст, которые часто запрашивают вместе
Также называется составной индекс

🔸Partial index
WHERE в определении индекса, чтобы индексировать только часть строк
Часто используется для NULL или часто встречающегося значения

🔸Covering
В индексе хранится весь результат запроса
Часто B-tree, но могут быть и другие типы

🔸Expression
Результаты функций или вычислений по данным столбца сохраняются в индексе
Обычно B-tree, но возможны и другие типы

🔸GIN
Для составных или мультизначных типов данных вроде JSONB
Также подходит для вхождений или частичного поиска по тексту

🔸GiST
Геоданные для PostGIS
Также подойдет для диапазонов, поиска ближайшего соседа и полнотекстового поиска

🔸HNSW
Поиск похожести и соседей для векторных данных

🔸BRIN
Очень большие таблицы, где B-tree будет слишком тяжелым
Часто используется для данных по временным сериям

👉 @SQLPortal

Резервное копирование PostgreSQL с несколькими местами хранения

https://github.com/RostislavDugin/postgresus/

👉 @SQLPortal

Postgres soundex: искать и фильтровать слова, которые звучат одинаково на слух.

👉 @SQLPortal

Совет по Postgres:

Можно использовать SIMILAR TO вместо набора отдельных LIKE.
Пример:

SELECT email FROM users
WHERE email SIMILAR TO '%(gmail|yahoo|hotmail)%.com';

Так запрос получается чище, особенно если список доменов длинный.

👉 @SQLPortal

Совет для тех, кто активно юзает функции в Postgres. Параметр track_functions умеет собирать статистику по вызовам функций и времени их работы. Почти как pg_stat_statements, только для функций.

👉 @SQLPortal

Так что, онлайн-апдейты не обязательны

EBR позволяет обновлять Oracle-приложения онлайн, без даунтайма.

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

🔸Улучшает опыт пользователей

Мой банк недоступен каждую ночь с 00:00 до 04:00 - это их окно обслуживания.
Мне чаще всего всё равно, но было бы приятнее, если бы сервис работал всегда.
Меньше даунтайма — лучше впечатление от сервиса.

🔸Улучшает жизнь разработчиков

С EBR апдейты можно делать когда удобно, а не по ночам и не по выходным.
Если что-то пошло не так > коллеги на месте, не надо никого будить.
Плюс фичи и фиксы выкатываются сразу, без ожидания редких окон обслуживания.

🔸Апдейты могут длиться сколько нужно

Не надо укладываться в пару часов и переживать что не успеем.
Можно начать утром, отвлечься на срочное, вернуться позже и закончить.
Промежуточные “кривые” объекты никому не мешают, пока они в новой (неэкспонированной) edition.

🔸Независимость от серверной части

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

🔸Гибкое включение

Не обязательно переключать всех сразу:
можно начать с тестового сервера, реальные пользователи остаются на старой версии.
Даже разные типы серверов могут использовать разные edition, если не все готовы обновиться.

EBR это не только про снижение даунтайма.

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

👉 @SQLPortal

Postgres хранит данные кусками по 8 кБ для внутреннего управления хранилищем и памятью.

Page: так называется единица данных в области памяти Postgres (примерно соответствует RAM).

Block: так называется единица данных в файлах на диске (примерно соответствует Storage).

👉 @SQLPortal

Запускайте SQL-запросы прямо на сайте! 🤙 SQL Workbench Embedded — лёгкая (<10kb) и хорошо настраиваемая обёртка над duckdb :

https://embedded.sql-workbench.com/

👉 @SQLPortal

Для тех, кто работает с JSON в Postgres: JSONB_OBJECT_KEYS позволяет заглянуть в структуру внутри неструктурированных данных.

Пример запроса:

SELECT DISTINCT key_name
FROM 
   (SELECT JSONB_OBJECT_KEYS(data) AS key_name FROM app_settings) 
AS all_keys;

👉 @SQLPortal

От таймстампов к пониманию продуктивности: как посчитать рабочие часы за день для нескольких сотрудников с помощью SQL

Для аналитика одна из стандартных задач — посчитать ежедневные рабочие часы сотрудников на основе логов событий.

Как это работает:

1️⃣Сопоставляем Login и Logout события

Используем CTE (WITH paired) вместе с LEAD(), чтобы получить время следующего события для каждого логина.
Разбиваем данные по employee_id и дню, чтобы считать длительность отдельно по каждому сотруднику за каждый день.

2️⃣Фильтруем только логины

Берем только Login, чтобы посчитать время до следующего события (обычно Logout).

3️⃣Считаем длительность в часах

EXTRACT(EPOCH FROM (logout_time - login_time)) / 3600 переводит разницу во времени из секунд в часы.

4️⃣Агрегация часов за день

С помощью SUM() и GROUP BY получаем итоговые рабочие часы для каждого сотрудника за день.
Такой подход корректно работает с несколькими логин-логаут сессиями в течение дня, суммируя их в точное общее рабочее время.

👉 @SQLPortal

Postgres INNER JOIN и CROSS JOIN.

🚫 SELECT * FROM users, accounts; это CROSS JOIN и декартово произведение.

✅ SELECT * FROM users u JOIN accounts a ON u.user_id = a.user_id; это INNER JOIN и он связывает таблицы по совпадающим строкам.

👉 @SQLPortal

Умные мысли часто преследовали разработчика

Но он быстрее

👉 @SQLPortal

Explain это мощный инструмент в Postgres.

Если важна производительность, стоит регулярно запускать команды explain и explain analyze и разобраться, как читать их вывод.

Вот отличный вводный материал

👉 @SQLPortal

Новый опенсорс-экстеншен для Postgres — pg_lake

- Создание и запросы к Iceberg-таблицам
- Запросы и импорт данных в Iceberg, Parquet, CSV, JSON и другие файлы в объектном хранилище
- Экспорт и копирование данных в lakehouse-хранилища

👉 @SQLPortal

В DBeaver появилась удобная возможность для работы с геоданными.

Теперь можно выделять несколько областей на карте за секунды с помощью инструмента лассо. Фича уже доступна во всех редакциях DBeaver, включая бесплатную Community. Работает с PostgreSQL, MySQL и другими базами, поддерживающими Spatial-данные.

Подробности есть в документации: https://dbeaver.com/docs/dbeaver/Working-with-Spatial-GIS-data/

👉 @SQLPortal

Создать flashback-архив в Oracle AI Database для записи всех изменений таблицы

CREATE FLASHBACK ARCHIVE ... RETENTION ...
ALTER TABLE ... FLASHBACK ARCHIVE

Затем можно просматривать изменения через

SELECT * FROM ... VERSIONS BETWEEN ...

Демо от Matt_Mulvaney: читать

👉 @SQLPortal

@> проверяет: диапазон слева содержит дату справа

SELECT '[2025-11-01, 2025-11-10)'::daterange @> '2025-11-05'::date;
-- true, потому что 5-е число внутри диапазона

<@ проверяет наоборот: дата слева лежит внутри диапазона справа

SELECT '2025-12-25'::date <@ '[2025-12-01, 2026-01-01)'::daterange;
-- true, потому что 25-е попадает в интервал

Квадратная скобка - включительно, круглая - не включительно.
То есть [2025-11-01, 2025-11-10) содержит 1 ноября, но не содержит 10 ноября.

👉 @SQLPortal

По умолчанию используется timestamptz(6) — то есть с точностью до 6 знаков после запятой. Можно указать любое значение от 0 до 6. При timestamptz(0) время округляется до секунд.

Все варианты занимают по 8 байт, поэтому timestamptz(0) не экономит место на диске. Обычно рекомендуют оставлять точность 6.

👉 @SQLPortal

Что происходит, когда вы вставляете строку (INSERT) в Postgres?

Postgres должен обеспечить надёжное сохранение данных, не теряя в скорости записи и сохраняя возможность восстановиться после сбоя. Ключевую роль здесь играет журнал предзаписи (Write-Ahead Log, WAL).

1. Получение запроса

Postgres принимает запрос и определяет, на какую страницу данных поместить новую запись.
Эта страница может уже быть в памяти (в буфере), может быть подгружена с диска, а может быть создана заново.

2. Запись в память

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

3. Формирование записи в WAL

В буфер WAL в памяти добавляется новая запись, содержащая всю информацию, нужную для восстановления этой операции вставки.

4. Сброс WAL на диск

WAL синхронно сбрасывается на диск (через fsync или аналог). Это гарантирует, что данные попали в надёжное хранилище.
После успешного сброса Postgres отправляет клиенту ответ success.

Когда клиент получает подтверждение, данные уже точно записаны в WAL (а значит, безопасны с точки зрения crash recovery), но сами данные таблицы могут пока оставаться только в памяти.
Их сброс на диск произойдёт позже — при контрольных точках (checkpoint), в фоновом процессе или при вытеснении страниц из памяти.

Если сервер упадёт до того, как грязные страницы будут записаны, при старте Postgres просто воспроизведёт WAL и восстановит все зафиксированные изменения.

WAL это сердце механизма надёжности Postgres.

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

👉 @SQLPortal

DISTINCT ON в Postgres - это особый синтаксис SQL, который позволяет выбрать одну строку с максимальным, минимальным, первой или последней записью в пределах каждой группы.

Эта конструкция требует обязательного использования ORDER BY. Если две строки полностью идентичны, Postgres просто возьмёт одну из них.

👉 @SQLPortal