👩‍💻 PostgreSQL 16 внёс довольно много улучшений в планировщик запросов и делает многие SQL-запросы быстрее, чем они выполнялись на предыдущих версиях PostgreSQL.

👀 Если ты посмотришь release notes 16-й версии, то увидишь некоторые из этих улучшений планировщика. Но с учётом объема изменений, вносимых в каждой версии PostgreSQL, невозможно достаточно подробно рассказать о каждом из них. Поэтому, возможно, тебе потребуется немного больше деталей, чтобы понять, в чём суть изменения, прежде чем ты сможешь понять, актуально ли оно для тебя.

Десять основных улучшений планировщика PostgreSQL в PG16:

0⃣ Инкрементальная сортировка для запросов DISTINCT

1️⃣ Более быстрые агрегаты ORDER BY / DISTINCT

2️⃣ memoize для запросов UNION ALL

3️⃣ Поддержка right anti-join

4️⃣ Параллельное хэширование FULL и RIGHT OUTER джойнов

5️⃣ Оптимизация режима ROWS оконных функций

6️⃣ Оптимизация оконных функций: ntile(), cume_dist() и percent_rank()

7️⃣ Удаление left join и unique joins в секционированных таблицах

8️⃣ LIMIT вместо UNIQUE для реализации DISTINCT, когда это возможно

9️⃣ Инкрементальная сортировка после Merge Join, в бОльшем количестве случаев

〰️〰️〰️〰️〰️〰️〰️

Подробнее в статье 🔜 ссыль

5️⃣-й пост из цикла.

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

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


🟡 Временные таблицы для промежуточных результатов

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

🟡 Создание и наполнение временных таблиц

Временные таблицы создаются и наполняются данными в рамках сеанса. Они живут в течение сеанса или до тех пор, пока не будут явно удалены. Например:

-- Создание временной таблицы для отфильтрованных заказов
CREATE TEMPORARY TABLE tmp_filtered_orders AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-06-30';

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

🟡 Временные таблицы и агрегации

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

-- Создание временной таблицы с агрегированными данными о заказах
CREATE TEMPORARY TABLE tmp_order_summary AS
SELECT customer_id, COUNT(order_id) AS order_count, SUM(total_amount) AS total_spent
FROM tmp_filtered_orders
GROUP BY customer_id;

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

❤️ datacontract CLI - это инструмент командной строки с открытым исходным кодом для работы с контрактами данных. Он использует файлы YAML контракта данных для привязки контракта данных, подключения к источникам данных и выполнения тестов схемы и качества, обнаружения критических изменений и экспорта в различные форматы.

👩‍💻 Инструмент написан на Python. Его можно использовать как автономный инструмент CLI, в пайплайне CI/CD или непосредственно как библиотеку Python.

PEP 723

# /// noscript
# requires-python = ">=3.11"
# dependencies = [
#   "requests<3",
#   "rich",
# ]
# ///

import requests
from rich.pretty import pprint

resp = requests.get("https://peps.python.org/api/peps.json")
data = resp.json()
pprint([(k, v["noscript"]) for k, v in data.items()][:10])

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

⭐️ Т.е. по сути ты сможешь описать зависимости скрипта в метадате без их установки через pip.

Если (когда) pep будет принят

👩‍💻 Вмёржили кастомизацию использования GIL в Python.

6️⃣-й пост из цикла.

Оптимизация подзапросов и агрегаций

Подзапросы и агрегации часто встречаются в SQL-запросах, но могут влиять на производительность. Давай рассмотрим дополнительные методы оптимизации.


🔵 Оптимизация подзапросов

Помимо преобразования подзапросов в JOIN или использования EXISTS, рассмотри следующие советы по оптимизации подзапросов:

🔘 Используй коррелированные подзапросы с умом: Коррелированные подзапросы могут быть медленными, поскольку они выполняются один раз для каждой строки внешнего запроса. Если возможно, пересмотри логику, чтобы минимизировать использование коррелированных подзапросов.
🔘 Избегай подзапросов в списке SELECT: Подзапросы в списке SELECT могут привести к снижению производительности. Если нужно получить данные из связанных таблиц, используйте вместо этого JOIN.
🔘 Ограничение результатов подзапроса: Если подзапрос возвращает большой набор результатов, это может повлиять на производительность. При необходимости ограничь набор результатов с помощью выражений TOP (SQL Server) или LIMIT (MySQL, PostgreSQL, SQLite).

🔵 Оптимизация агрегаций

Агрегации, такие как SUM, COUNT, AVG и другие, необходимы, но могут быть ресурсоёмкими. Вот как их оптимизировать:

🔘 Материализованные представления агрегаций: Если ты часто используешь агрегации, подумай о создании материализованных представлений (если это поддерживается твой БД). Материализованные представления хранят предварительно вычисленные агрегации, что уменьшает необходимость их перерасчёта во время запросов.
🔘 Используй INDEX для столбцов агрегации: Если запрос включает агрегации по отдельным столбцам, убедись, что эти столбцы соответствующим образом проиндексированы. Это может ускорить операции агрегации.
🔘 Пакетная обработка: Если в приложении используется пакетная обработка или расписание задач, выполняющих агрегирование, рассмотри возможность запуска этих задач в непиковые часы, чтобы минимизировать влияние на запросы в реальном времени.

👩‍💻 PostgreSQL

▶️Документация
▶️Как не надо делать

▶️Книги
▶️В каком порядке читать книги
▶️Курсы

▶️Чат @pgsql
▶️Вакансии @pgsqljobs

7️⃣-й пост из цикла.

Избегай курсоров и циклов для повышения производительности

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


🟣 Операции на основе множеств

Вместо использования курсоров или циклов для манипулирования данными строка за строкой используй операции на основе множеств, предоставляемые SQL. Например, ты можешь использовать операторы UPDATE или DELETE с предложениями WHERE для изменения или удаления нескольких строк на основе определённых условий.

🟣 Пакетная обработка

Если нужно обработать много строк, рассмотри возможность использования пакетной обработки. Разбей операцию на управляемые фрагменты, используя LIMIT (MySQL, PostgreSQL, SQLite), FETCH FIRST (IBM Db2) или аналогичные выражения. В каждой итерации обрабатывай подмножество строк, снижая нагрузку на ресурсы сервера.

🟣 Оптимизация циклов с помощью операций на основе множеств

Если ты обнаружишь, что циклы необходимы для решения конкретной задачи, постарайся оптимизировать их, используя операции на основе множеств внутри цикла. Минимизируй взаимодействие с БД, получая и манипулируя большими наборами данных на каждой итерации, сокращая количество взаимодействий с базой данных.

Заключительный пост цикла.

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

🙃 Понимание планов выполнения запросов, стратегий индексирования, переписывания запросов, денормализации данных, временных таблиц, оптимизации подзапросов и агрегирования. А также отказа от таких неэффективных методов, как курсоры и циклы, позволит оптимизировать запросы к БД для достижения максимальной производительности.

📎 Postman и паттерны проектирования API

Команда Postman Open Technologies собирает информацию о стандартах, отраслевых форматах и спецификациях, открытых API.

Каталог практик и паттернов оформлен как рабочее пространство Postman

На текущий момент там описаны следующие паттерны:
🟡 Форматы данных:
🔵Коды стран (ISO 3166)
🔵Коды валют (ISO 4217)
🔵Дата, время и временные промежутки (ISO 8601)
🔵Числа с десятичными дробями
🔵Кастомные заголовки HTTP
🔵Расширенное описание ошибки (RFC 9457 - кстати, очень хороший формат для передачи смысла ошибки HTTP)

🟡Управление кэшированием:
🔵Параметр Cache-control
🔵Параметр Etag
🔵Параметр Expires

🟡Фильтрация:
🔵Параметры поискового запроса
🔵Точное соответствие
🔵Диапазон значений поля
🔵Выбор полей для ответа

🟡Пагинация:
🔵Заголовки page and per_page (rfc 5988)
🔵Курсор / NextRecordKey
🔵Параметры Limit и Offset

🟡Сортировка:
🔵По одному полю - параметры sort_by, sort_order
🔵По нескольким полям

🟡Версионирование:
🔵На уровне URL API
🔵На уровне отдельного ресурса
🔵Через заголовок Accept-Version
🔵Через заголовок Accept

🟡Информация:
🔵Контакты разработчиков
🔵Лицензия
🔵Условия использования
🔵Заголовок Sunset

👩‍💻 SQL CheatSheet

Шпаргалка по SQL

JOIN, IN, LIKE, BETWEEN, ORDER BY и другое, ботай!

⚡️ Выкатили новую версию 🖼️ Airflow 2.8.3


📦 PyPI

📚 Docs

🛠 Release Notes

🐳 Docker Image:
docker pull apache/airflow:2.8.3

#airflow

👩‍💻 Распространённые ошибки составления SQL запросов

Использование SELECT *

❤️‍🩹 Проблема: Выбор всех колонок с помощью SELECT * приводит к передаче ненужных данных, увеличению использования памяти и снижению производительности запросов.
✔️ Решение: Укажи в операторе SELECT только необходимые колонки.

〰️ Пример проблемы

SELECT * FROM employees;

〰️ Улучшенный запрос

SELECT employee_id, first_name, last_name FROM employees;

Отсутствие индексов

❤️‍🩹 Проблема: Отсутствие индексов может привести к полному сканированию таблицы и снижению производительности запросов.
✔️ Решение: Создай и используй индексы для часто используемых в выражениях WHERE колонок.

〰️ Создание индекса

CREATE INDEX idx_last_name ON employees(last_name);

〰️ Использования индекса в запросе

SELECT * FROM employees WHERE last_name = 'Smith';

Чрезмерное использование подзапросов

💔 Проблема: Подзапросы могут работать медленнее, чем JOIN, особенно при работе с большими наборами данных.
✔️ Решение: Используй JOIN, когда это возможно, а подзапросы оставь для ситуаций, в которых они более эффективны.

〰️ Пример проблемы (подзапрос)

SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees);

〰️ Улучшенный запрос (JOIN)

SELECT DISTINCT d.department_name FROM departments d JOIN employees e ON d.department_id = e.department_id;

Неэффективные JOIN

❤️‍🩹 Проблема: Выбор неправильного типа JOIN (например, Cartesian JOIN) или неправильное указание условий соединения может привести к неправильным результатам или замедлению запросов.
✔️ Решение: Разберись в различных типах JOIN (INNER, LEFT, RIGHT, FULL) и используй их по назначению.

〰️ Пример проблемы (Cartesian JOIN)

SELECT * FROM employees, departments;

〰️ Улучшенный запрос (INNER JOIN)

SELECT e.employee_name, d.department_name FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Неиспользование выражений WHERE

❤️‍🩹 Проблема: Отсутствие фильтрации данных с помощью выражений WHERE может привести к запросу ненужных данных.
✔️ Решение: Всегда включайте выражения WHERE, ограничивающие набор результатов.

〰️ Пример проблемы (без выражения WHERE)

SELECT * FROM orders;

〰️ Улучшенный запрос (с выражением WHERE)

SELECT * FROM orders WHERE order_date >= '2023-01-01';

Игнорирование планов выполнения запросов

💔 Проблема: Игнорирование планов выполнения запросов может привести к упущенным возможностям оптимизации.
✔️ Решение: Используй такие инструменты, как EXPLAIN, для анализа планов выполнения и внесения необходимых оптимизаций.

〰️ Просмотр плана выполнения

EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

Отсутствие оптимизации больших наборов данных

❤️‍🩹 Проблема: Запросы, хорошо работающие с небольшими наборами данных, могут плохо работать с большими объёмами данных.
✔️ Решение: Реализуй такие стратегии, как пагинация, разбиение данных на разделы и оптимизация индексов для больших наборов данных.

〰️ реализация пагинации

SELECT * FROM products LIMIT 10 OFFSET 20;

Повторяющиеся агрегации

❤️‍🩹 Проблема: Повторение одних и тех же агрегаций в нескольких частях запроса может быть неэффективным.
✔️ Решение: Используй CTE (Общие табличные выражения) для хранения промежуточных результатов и избегай лишних вычислений.

〰️ Пример проблемы (повторяющаяся агрегация)

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department;

〰️ Улучшенный запрос (с CTE)

WITH DepartmentSalaries AS (
    SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department
)
SELECT * FROM DepartmentSalaries;

Неадекватная обработка ошибок

💔 Проблема: Неправильная обработка ошибок может привести к сбоям в работе приложения или неправильным результатам.
✔️ Решение: Реализуйте надлежащую обработку ошибок в SQL запросах или в коде приложения.

〰️ Пример обработки ошибок в SQL (MySQL)

BEGIN;
-- SQL выражение
IF some_condition THEN
    ROLLBACK; -- Откат транзакции при ошибке
ELSE
    COMMIT; -- Коммит транзакции при успешном выполнении всех выражений
END IF;