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

Напрямую вставлять данные в такие столбцы через INSERT или изменять через UPDATE нельзя — Postgres сам отвечает за генерацию значений. Они задаются с помощью синтаксиса GENERATED ALWAYS AS (...). Ключевое слово STORED означает, что значение вычисляется при записи строки и сохраняется на диск — так же, как и обычные столбцы.

А зачем вообще нужны сгенерированные столбцы?

➜ Удобная агрегация данных: можно автоматически формировать, например, full_name, объединяя first_name и last_name. Больше не нужно каждый раз писать конкатенацию в запросах!

full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED

➜ Упрощение запросов: можно заранее посчитать сложные выражения. Например, сохранить цену с учётом налога (price * 1.07), чтобы не дублировать вычисления в каждом SELECT. Чисто и лаконично!

➜ Нормализация данных: можно хранить нормализованную версию данных для удобства поиска и индексации. Частый кейс — сохранение email'а в нижнем регистре для регистронезависимого поиска.

👉 @SQLPortal

Узнайте, как и зачем использовать транзакции в Postgres, в этои интерактивном браузерном туториале:

https://www.crunchydata.com/developers/playground/transactions

👉 @SQLPortal

Уровни изоляции транзакций в Postgres

Уровни изоляции транзакций в Postgres определяют, как база данных обрабатывает одновременное выполнение нескольких транзакций.

В Postgres доступны четыре стандартных SQL-уровня изоляции:

🔸Read Uncommitted
Read Uncommitted позволяет запросам видеть изменения, сделанные в транзакциях, которые ещё не были зафиксированы (committed).
Хотя этот уровень присутствует в синтаксисе Postgres ради соответствия SQL-стандарту, фактически он не реализован.
В Postgres нельзя выполнить грязное чтение (dirty read).

🔸Read Committed
Это уровень изоляции по умолчанию в Postgres.
Транзакция видит только те данные, которые были зафиксированы до её начала.
Это стандарт для веб-приложений, CMS и типичных OLTP-нагрузок (Online Transaction Processing), где допустимы незначительные расхождения
(например, когда данные изменяются между двумя SELECT-запросами внутри одной и той же транзакции).

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

Этот уровень даёт хороший баланс между производительностью и согласованностью данных в большинстве сценариев.

🔸Repeatable Read
Этот уровень гарантирует, что повторный запуск одного и того же SELECT-запроса в рамках одной транзакции всегда вернёт один и тот же результат.
Он исключает *неповторяемые чтения* (non-repeatable reads).

Подходит для многошаговых транзакций, где важна согласованность в рамках всей транзакции.

Пример:
Генерация финансового отчета — сначала запрашиваются балансы счетов, затем идут запросы по транзакционной истории тех же счетов.
Все данные должны быть согласованы с тем состоянием, в котором считались балансы, даже если в это время в системе происходят другие изменения.

🔸Serializable
Самый строгий и безопасный уровень изоляции.
Обеспечивает поведение транзакций как если бы они выполнялись последовательно, а не параллельно.

Подходит для критичных операций, где целостность данных важнее всего и необходимо исключить любые race conditions.

Примеры:
- Системы бронирования (например, исключение двойного бронирования места в самолете или гостиничного номера)
- Сложные финансовые операции, где перевод средств между счетами требует согласованности всех промежуточных шагов

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

Как задать уровень изоляции

Уровень изоляции можно задать на следующих уровнях:

1. По умолчанию для всей базы данных
2. Для отдельной сессии
3. Для конкретной транзакции (в момент BEGIN)

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

👉 @SQLPortal

Нашёл штуку, которая делает PostgreSQL понятным даже для чайников 🙂

Это визуальная надстройка над Postgres, которая превращает его в нечто среднее между Airtable и Excel. Таблицы, связи, формы — всё редактируется прямо в браузере, без SQL.

👉 @SQLPortal

В обновляемые представления можно вставлять строки

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

Из-за этого кажется, что значения просто исчезают!

Чтобы этого избежать, добавь:

CREATE VIEW ... AS SELECT ... WHERE ...
WITH CHECK OPTION

Тогда вставлять можно будет только те строки, которые соответствуют условию WHERE

👉 @SQLPortal

Ещё один отличный тренажёр для изучения SQL — SQL Teaching

Интерактивный сайт, где вы учитесь писать SQL-запросы в игровой форме. Всё происходит прямо в браузере: пишете — сразу видите результат

Бесплатно и опенсорс. Забираем здесь 😊

👉 @SQLPortal

Когда стоит сбрасывать pg_stat_statements?

🔸В начале нового периода мониторинга
Если ты анализируешь производительность на ежедневной, недельной или месячной основе, имеет смысл сбрасывать pg_stat_statements в начале каждого периода. Это позволяет собирать метрики с «чистого листа» и получать более точную аналитику за конкретный интервал.

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

🔸Во время бенчмарков и тестирования производительности
Если ты запускаешь тесты производительности или сравниваешь эффективность разных вариантов запросов, сброс pg_stat_statements гарантирует, что собираемая статистика будет относиться только к выполненным в рамках теста запросам.

🔸После серьёзных операций по обслуживанию базы
После таких операций, как pg_repack, переиндексация или изменение структуры таблиц, сброс pg_stat_statements позволяет объективно оценить, как они повлияли на производительность.

👉 @SQLPortal

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

SQL-инъекции как сервис

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

https://idiallo.com/blog/sql-injection-as-a-feature

Берегите свои данные, коллеги! 🐒

👉 @SQLPortal

Подготовленные выражения / параметризованные запросы в Postgres

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

Полноценный SQL-запрос в Postgres проходит через следующие этапы:

- Парсинг — преобразование в внутреннее представление;
- Переписывание / трансформация — применение правил переписывания запроса, создание внутренних представлений (например, CTE или view);
- Планирование — планировщик Postgres использует статистику по таблицам и другую информацию, чтобы выбрать оптимальный план выполнения;
- Выполнение — непосредственный запуск запроса.

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

Пример

Подготовить запрос и сохранить план выполнения

PREPARE get_user_by_email (text) AS
SELECT * FROM users WHERE email = $1;

Выполнить с одним параметром

EXECUTE get_user_by_email('alice@test.com');

Выполнить с другим параметром

EXECUTE get_user_by_email('bob@test.com');

Освободить ресурсы

DEALLOCATE get_user_by_email;

ORM'ы часто работают с подготовленными выражениями, и это может значительно повысить производительность приложений. Со стороны приложения такие запросы обычно называют параметризованными. Параметризованный запрос содержит плейсхолдеры, которые заполняются значениями позже. Такой запрос на стороне БД превращается в подготовленное выражение.

Примеры в коде

В Rails:

User.where("email = ?", "alice@test.com")

В Python:

cur.execute("SELECT * FROM users WHERE email = %s", ("alice@test.com",))

👉 @SQLPortal

Изучаем базы данных

Дорожная карта, которая поможет глубже разобраться в мире СУБД

👉 @SQLPortal

Новичок в использовании pg_stat_statements для анализа производительности запросов в Postgres?

Попробуй этот простой веб-учебник:
https://crunchydata.com/developers/playground/query-performance-analytics

💡Там есть пара отличных запросов, которые стоит сохранить для дальнейшего использования.

👉 @SQLPortal

Новая фича в PostgreSQL 17 — геймчейнджер

Эта новая возможность в PostgreSQL 17 настоящая революция.

🔸Механика страниц в PostgreSQL

PostgreSQL, как и большинство СУБД, работает с страницами фиксированного размера.
Практически всё — таблицы, индексы и т.д. хранится в виде страниц размером 8 КБ.

Каждая страница содержит:
- строки таблицы или кортежи индекса
- фиксированный заголовок

Страницы это просто байты в файлах. Они читаются и кэшируются в буферном пуле

Примеры чтения страниц:
- Страница 0: read(offset=0, size=8192)
- Страница 1: read(offset=8193, size=8192)
- Страница 7: read(offset=57345, size=8192)

Если таблица занимает 100 страниц, то для full table scan придётся выполнить 100 системных вызовов.
А каждый системный вызов имеет накладные расходы.

🔸Что изменилось в Postgres 17

Добавили возможность объединения I/O:
- Можно указать, сколько страниц читать за один системный вызов
- Теоретически можно просканировать всю таблицу за один системный вызов

Это не всегда хорошая идея, подробнее об этом ниже.

Также добавили поддержку векторизованного I/O:
- Используется системный вызов preadv
- Он принимает массив оффсетов и длин — удобно для произвольного чтения

🔸Проблема: не читать лишнего

Допустим, мы делаем seq scan, чтобы найти значение:
1. Читаем страницу 0
2. Находим нужное → выходим
3. Остальные страницы не нужны

Теперь с новой фичей:
- Мы читаем сразу 10 страниц одним I/O
- Загружаем всё в shared buffers
- А нужное было уже на первой странице

> Результат — потрачены ресурсы (диск, память) впустую

🔸Как это сбалансировать

Нужно будет находить баланс:
- Между эффективным I/O
- И избежанием избыточного чтения

🔸Примечание

PostgreSQL действительно вызывает системный вызов ядра, чтобы читать по 8 КБ за раз.
Но ядро может быть настроено на read-ahead — заранее читать больше и кэшировать это в файловом кэше ОС.

👉 @SQLPortal

Интерактивная тренировка SQL

Если хочешь прокачать SQL на практике без установки СУБД и настройки окружения заходи на sqltest.online

👉 @SQLPortal

База данных имеет право выполнять части SQL-выражения WHERE в любом порядке.

Так как же избежать ошибок, если известно, что выражение может выбросить ошибку,

например, извлечение квадратного корня из отрицательного числа?

Используй CASE, чтобы выполнять вычисление только для допустимых значений:

WHERE CASE 
          WHEN col > 0 THEN SQRT(col) 
          ELSE ... 
      END ...

👉 @SQLPortal

JSON-реляционные duality views в Oracle Database 23ai — это гибкий, производительный и согласованный способ доступа к данным как:

- документам,
- реляционным таблицам,
- и к обоим одновременно 💊

anders__swanson показывает, как создать базовые duality views для этого.

👉 @SQLPortal

6 структур данных для экономии места в базе данных

Фильтр Блума
Снижает стоимость поиска, проверяя, является ли элемент членом множества.

HyperLogLog
Оценивает количество уникальных элементов.

Cuckoo filter
Проверяет, принадлежит ли элемент множеству.

Minhash
Находит сходство между множествами.

SkipList
Эффективно ищет, вставляет и удаляет элементы из отсортированного списка.

Count-min sketch
Находит частоту элементов в большом потоке данных.

👉 @SQLPortal

Если ты хочешь изучить SQL или погрузиться в аналитику, начинай с Mode SQL Tutorial. У них всё работает прямо в браузере - никаких заморочек с установкой СУБД.

Что тебя ждёт:

— Basic SQL — SELECT, WHERE, ORDER BY, LIMIT и логические операторы;
— Intermediate SQL — агрегаты, GROUP BY, HAVING, JOINы;
— Advanced SQL — window functions, подзапросы, pivot’ы и performance-tuning;
— SQL Analytics Training — реальные кейсы аналитики, включая A/B тестирование и инсайты продукта.

👉 @SQLPortal