Ускорение производительности Postgres: HOT-обновления и Fill Factor

🔸HOT-обновления происходят, когда обновлённая строка помещается на ту же страницу, что и старая версия строки.
🔸HOT-обновления улучшают производительность, так как снижают нагрузку на I/O, объём WAL, количество обновлений индексов и другие ресурсы.
🔸Обратите внимание на стратегию индексирования, если хотите чаще использовать HOT-обновления. Обычно они невозможны, если обновляемое поле проиндексировано — за исключением случая с индексами BRIN. Поддержка HOT-обновлений с BRIN — это новое улучшение в Postgres 16.
🔸Чтобы повысить вероятность HOT-обновлений, можно уменьшить fill factor страницы. Но с этим могут быть компромиссы по производительности, так что лучше действовать по принципу «измеряй и смотри».

https://crunchydata.com/blog/postgres-performance-boost-hot-updates-and-fill-factor

👉 @SQLPortal

Используешь Postgres \copy, но хочешь видеть индикатор прогресса?

Существует pg_stat_progress_copy — но это представление (view), которое нужно опрашивать вручную.

Если тебе нужен прогресс прямо в терминале, пользователи macOS и Linux могут использовать утилиту pv (pipe viewer). Ее нужно установить отдельно.

С помощью \copy FROM PROGRAM можно вызвать pv прямо из команды \copy:

\copy obrc(station_name, measurement) FROM PROGRAM 'pv "/1br/measurements.txt"' WITH (FORMAT CSV, DELIMITER ';');

👉 @SQLPortal

Этот PDF охватывает SQL от начального до продвинутого уровня. В нём 101 страница — всё, что нужно для подготовки и повторения SQL перед собеседованиями на позиции, связанные с данными.

Дополнительно прорешай задачи на LeetCode — этого будет достаточно.

> cсылка на PDF

👉 @SQLPortal

Часто используете JSON в Postgres?

Работать с JSON сложно — особенно когда дело касается индексации.

—> GIN-индексы отлично подходят для ускорения общих поисков или поиска по множеству ключей.

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

—> В таких случаях можно использовать B-tree индексы по отдельным значениям из массива, применяя механизм индексов на выражения в Postgres.

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

CREATE INDEX idx_orders_total ON orders (((details->>'order_total')::numeric));

👉 @SQLPortal

Вот отличное руководство по началу работы с индексами в Postgres

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

> cсылка

👉 @SQLPortal

Преобразуйте GeoJSON в объекты Oracle Spatial и обратно с помощью:

SDO_UTIL.FROM_GEOJSON
SDO_UTIL.TO_GEOJSON

Это позволяет применять пространственные функции к данным в формате JSON,

например, для вычисления расстояния между двумя точками.

👉 @SQLPortal

При оптимизации SQL-запроса один из ключевых вопросов:

Какие колонки проиндексированы?

В Oracle Database это можно проверить через представления *_IND_COLUMNS

Коллега написал скрипт, который проверяет это и подтягивает другую полезную информацию для тюнинга по таблице.

https://dfitzjarrell.wordpress.com/2025/07/02/is-that-column-indexed/

👉 @SQLPortal

psql совет: \h

Знали ли вы, что в psql есть большая справка по SQL-командам?

\h + нужная команда покажет подробности по синтаксису и ссылку на документацию.

Пример:

postgres=# \h CREATE USER
Команда:     CREATE USER  
Описание:   создать новую роль базы данных  
Синтаксис:  
CREATE USER имя [ [ WITH ] параметр [ ... ] ]

где параметр может быть:

      SUPERUSER | NOSUPERUSER  
    | CREATEDB | NOCREATEDB  
    | CREATEROLE | NOCREATEROLE  
    | INHERIT | NOINHERIT  
    | LOGIN | NOLOGIN  
    | REPLICATION | NOREPLICATION  
    | BYPASSRLS | NOBYPASSRLS  
    | CONNECTION LIMIT макс_подключений  
    | [ ENCRYPTED ] PASSWORD 'пароль' | PASSWORD NULL  
    | VALID UNTIL 'временная_метка'  
    | IN ROLE имя_роли [, ...]  
    | IN GROUP имя_роли [, ...]  
    | ROLE имя_роли [, ...]  
    | ADMIN имя_роли [, ...]  
    | USER имя_роли [, ...]  
    | SYSID uid

Документация: https://postgresql.org/docs/16/sql-createuser.html

👉 @SQLPortal

Работа с Foreign Data Wrappers в Postgres и производительность

⚡️Используй CTE , чтобы передавать более информативные запросы и избегать полноскановых операций на удалённом источнике
⚡️Аналогично — применяй подзапросы, чтобы ограничить объём передаваемых данных
⚡️Если на стороне foreign-сервера много данных — по возможности кэшируй
⚡️Материализованные представления (materialized views) хорошо подходят для создания локальных копий данных
⚡️Кэширующая таблица с MERGE может стать основой собственного мини-ETL внутри БД

Подробнее: ссылка

👉 @SQLPortal

SQLZap — это прям находка

sqlzap.com — это онлайн-платформа для тренировок SQL-запросов в браузере. Что-то вроде интерактивного тренажёра

Идеально чтобы натаскать руку — как Leetcode, только для SQL

👉 @SQLPortal

Практикуй SQL уже сегодня

Используя таблицы на изображении в качестве ориентира, что должен вернуть следующий запрос?

SELECT name
FROM students
WHERE student_id NOT IN (
  SELECT student_id
  FROM grades
);

👉 @SQLPortal

Задача:
При работе с базой данных ритейл-клиента Accenture ты замечаешь, что в столбце category таблицы products есть NULL-значения.

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

Таблица products:

🔸product_id

🔸category

🔸name

Объяснение:
Используем рекурсивный CTE с именем filled — это удобно, когда значение каждой строки зависит от предыдущей.

-- Базовый случай:
-- выбираем первую строку (product_id = 1), у которой уже есть категория (например, 'Shoes')
-- это стартовая точка рекурсии

-- Рекурсивный шаг:
-- для каждой следующей строки (product_id + 1):
-- если p.category НЕ NULL → берём её
-- иначе → подхватываем f.category из предыдущей строки

-- Всё это реализуется через COALESCE() — он возвращает первое непустое значение

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

Q) Почему ты написал ON p.product_id = f.product_id + 1?
A) Я хотел симулировать поведение forward fill — проход по строкам в порядке ID, строка за строкой.

👉 @SQLPortal

Теперь в Oracle Database появился сервер Model Context Protocol (MCP), доступный через Oracle SQLcl.

С его помощью ты можешь безопасно подключаться к базе данных и взаимодействовать с ней через любой клиент с поддержкой MCP — включая твоего любимого AI-ассистента и LLM.

> подробнее

👉 @SQLPortal

Сгенерированные столбцы в 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

SELECT "Программирование — В С Ё!";

В 2025 году на кодинге уже не вывезешь, перспектива года - Кибербезопасность.

Ловите полезные каналы, которые помогут ворваться в новое направление:

👍 ZeroDay — Подробные уроки по безопасности с нуля, эксплуатации уязвимостей, инструментам и свежие новости.

👨‍💻 Серверная Админа — Большое количество уроков, статей, книг и гайдов по устройству и настройке компьютерных сетей. База которую должен знать каждый

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

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

👉 @SQLPortal

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

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

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

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

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

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

👉 @SQLPortal

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

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

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

👉 @SQLPortal