💻Немного о недавнем обновлении PostgreSQL 16

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

Вот некоторые из изменений:
⏩Разрешена инкрементальная сортировка в большем количестве случаев, включая DISTINCT

⏩Добавлена возможность для агрегатов, имеющих ORDER BY или DISTINCT, использовать предварительно отсортированные данные

⏩Разрешена мемоизация в UNION ALL

⏩Разрешено выполнять anti-join с ненулевым входом в качестве внутреннего отношения

⏩Разрешено распараллеливание FULL и RIGHT OUTER хэш-соединения

⏩Разрешено оконным функциям использовать более быстрый режим ROWS, когда режим RANGE активен, но не нужен

⏩Оптимизация постоянно увеличивающихся оконных функций ntile(), cume_dist() и percent_rank()

⏩Разрешено удаление left join и unique joins в секционированных таблицах

⏩Можно использовать Limit вместо Unique для реализации DISTINCT, когда это возможно

⏩Ослаблены слишком строгие правила в select_outer_pathkeys_for_merge()

📎 Статья с подробным описанием улучшений

@sqlhub

💻BYTE и CHAR в Oracle: разница, хранение, кодировка

Если в двух словах
Выбор между BYTE и CHAR зависит от кодировки и объема данных, которые вы собираетесь представлять в байтах.
⏩BYTE фиксирует объем данных в байтах и идеален для однобайтовых кодировок, таких как ASCII где каждому символу соответствует один байт. Так, VARCHAR2(10 BYTE) вмещает любую комбинацию из десяти байтов.
⏩CHAR же определяет количество символов, не учитывая их объем в байтах; выбрав VARCHAR2(10 CHAR), вы гарантируете пространство на 10 символов, независимо от их размера в байтах. Для иллюстрации вот простой SQL-пример:

-- BYTE: сохраняет байты
CREATE TABLE bytewise (name VARCHAR2(10 BYTE));

-- CHAR: распределяет пространство с учетом количества символов
CREATE TABLE charwise (name VARCHAR2(10 CHAR));

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


Детали при работе с UTF-8
Важно понимать последствия выбора типа данных при работе с многобайтовыми символами кодировки, такой как UTF-8, где один символ может занимать до 4 байтов.
⏩Если вы опираетесь на BYTE, это может привести к проблемам в системах, поддерживающих много языков.
⏩Для предотвращения потери или обрезки данных CHAR будет более надежной опцией, так как он обеспечивает достаточное пространство для любого символа, независимо от его веса.


Критерии для выбора между BYTE и CHAR:
⏩Однородность данных: Если данные подчиняются стандартному размеру в байтах, особенно в случаях, когда важно экономить место, BYTE будет наилучшим выбором.
⏩Многоязычная поддержка: CHAR выигрывает, когда важна поддержка множества языков, это помогает предотвратить проблемы с несоответствием размера хранения.
⏩Производительность: BYTE может быть быстрее по сравнению с CHAR из-за обработки меньшего объема данных, что делает его подходящим для приложений с высокими требованиями к производительности.

📎 Подробнее про BYTE и CHAR можно прочитать тут

@sqlhub

💻Склонение месяцев в SQL

Вроде мелочь, а вполне-таки реальный кейс
В общем, необходимо вносить в договор дату в формате: "30 мая 2099 г."
Эта дата хранится в БД, ее нужно просто подгрузить и нормально отформатировать.
Для этого можно обращаться к месяцу с помощью: LOWER(DATENAME(month, @Tdate)), однако в таком случае получается месяц в именительном падеже - "май".

И тут полезно вспомнить, что форматирование даты с правильными склонениями месяца работает из коробки:

SELECT FORMAT(GETDATE(), 'dd MMMM yyyy', 'ru-RU')

-- 30 мая 2099

@sqlhub

💻SQL в качестве API

📌Интересная статья, которая позволит посмотреть на SQL с другой стороны
Статья обсуждает использование SQL в API для обработки сложных запросов, в результате автор приходит к выводу, что применение SQL в API может быть безопасным и удобным.

📎 Статья

@sqlhub

💻 Зачем нужен SELECT ... FOR UPDATE?

▶️Итак, ответ. Пока у вас транзакции следуют строго одна за другой - у вас всё хорошо и нет проблем. Кроме производительности. А чтобы улучшить производительность — необходимо разрешить транзакции выполнять параллельно. И вот тут начинается богатый и поразительный мир concurrently control. При том, не только в БД, а везде где хоть что-то выполняется параллельно.

Допустим есть пользователь, у него есть 100 денег на счету. Пользователь может их тратить, вы проверяете баланс SELECT balance ..., затем обновляете баланс при покупке UPDATE ... SET balance = ? WHERE .... И вот в счастливый день как-то так вышло, что приходят сразу 2 запроса на покупки для этого пользователя. Одна на 50 денег, вторая на 70. Одна из них должна быть отклонена, т.к. денег недостаточно. Но в результате получается что обе покупки прошли и у вас проблема, вы продали то, что не надо было. И это даже не видно по балансу пользователя. Как же?

Это типичный race condition, обе транзакции сначала данные читают, потом локально что-то делают, потом что-то пишут.
⏩читать им никто не мешает, потому обе транзакции прочитали что у пользователя 100 денег
⏩обе транзакции закономерно решили что денег достаточно
⏩обе транзакции обновили баланс пользователя
При конкурентном доступе к ресурсу подрались только на последнем шаге, транзакция которая начала обновлять данные позже сначала подождала завершение первой транзакции. А затем банально перезаписала баланс на тот который считала правильным сама — lost update аномалия.

▶️Вот как раз для того чтобы предупредить СУБД о том, что мы планируем с данными что-то делать, а потому нам надо сериализовать транзакции иначе, и существует FOR SHARE, FOR UPDATE дополнения. Потому они кстати и задокументированы в разделе Explicit Locking

📎 Подробнее прочитать можно тут

@sqlhub

💻 Cheatsheet SQL

Шпаргалок по SQL много не бывает, так что держите, полезно освежить перед собеседованием

Здесь освещаются самые основные операторы, такие как JOIN, IN, LIKE, BETWEEN, ORDER BY, а также много всего ещё
Пользуйтесь)

@sqlhub

💻AlPort опубликовал наиболее полный рейтинг генеративных технологий, который учитывает локальные особенности продуктов

В рейтинг попали самые перспективные разработки в сфере генеративного искусственного интеллекта по ключевым категориям. Туда попали сразу две нейросетки от Яндекса — текстовая YandexGPT и мультимодальная YandexART. Также Яндекс вместе с Stability AI, Open AI, Google, Microsoft, Tencent и Baidu стал одной из 11 компаний со всего мира, которые разрабатывают более одного типа GenAI-моделей.

📎 Статья

@sqlhub

💻 Vitess 19 для кластеризации БД

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

Изменения и дополнения в Vitess 19:
⏩прекращение поддержки MySQL 5.7. Разработчики советуют пользователям выполнить обновление до MySQL 8.0, используя Vitess 18, прежде чем переходить на Vitess 19. Однако Vitess 19 по-прежнему будет поддерживать импорт из MySQL 5.7;
⏩добавлены новые метрики для консолидации потоков и версия сборки в /debug/vars, чтобы обеспечить более глубокое понимание и отслеживаемость;
⏩улучшена совместимость запросов, реализована поддержка операций удаления из нескольких таблиц, новый запрос SHOW VSCHEMA KEYSPACES и несколько других улучшений синтаксиса SQL, которые расширяют совместимость Vitess с MySQL;
⏩поддержка отсрочки попыток переключения в случае блокировки. Поддержка принудительного отключения;
⏩улучшение процесса инкрементного резервного копирования: поддержка имён резервных копий и пустых резервных копий.

«Следуя тенденции последних трёх лет, новая версия Vitess быстрее предыдущей во всех тестах, которые мы отслеживаем в Arewefastyet. Мы исправили несколько проблем с производительностью, доработали интерфейс и код», — пояснили разработчики, порекомендовав изучить документацию проекта и список исправлений.

🖥 Исходный код проекта опубликован на GitHub под лицензией Apache License 2.0.

@sqlhub

Как правильно подставлять значения из переменных / списков / словарей в SQL запрос в Python?

⏩Лучше всего использовать SQL запросы с использованием параметров:

cursor.execute("SELECT * FROM my_table WHERE id = ?", [123])
# parameter placeholder ------------------------> ^
# список/кортеж со значениями параметров -----------> ^^^^^

⏩Какие преимущества приносит использование параметров?
— Защита от SQL-инъекций

— Правильное квотирование литералов в зависимости от их типа (пример со строками, пример с датами).

— Оптимизация — сокращение времени работы SQL запроса. Благодаря использованию параметров следующие шаги не выполняются при повторном запуске (зависит от БД):
— проверка синтаксиса SQL запроса
— проверка прав доступа к объектам БД
— построение плана выполнения SQL запроса

— Защита от переполнения/вытеснения кеша SQL запросов. Например "безобидный" запрос qry = f"SELECT first_name, last_name FROM users WHERE id = {user_id}", который часто выполняется в нагруженной системе с различными значениями user_id может вытеснить из кеша запросов полезные запросы.

⏩ Пример использования параметров в SQL запросе:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table lang (name, first_appeared)")

cur.execute("insert into lang values (?, ?)", ("C", 1972))

lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009),
]
cur.executemany("insert into lang values (?, ?)", lang_list)

cur.execute("select * from lang where first_appeared=:year", {"year": 1972})
print(cur.fetchall())

con.close()

При таком подходе можно использовать cursor.executemany() - это значительно быстрее и эффективнее по сравнению с вставкой в цикле по одной строке.

📎 Читать подробнее

@sqlhub

💻 BEGIN, COMMIT, ROLLBACK (работа с транзакциями)

⏩Транзакции – это способ группировки одной или нескольких операций с базой данных в единую единицу работы. Транзакция может состоять из одной или нескольких SQL-операций и использоваться для обеспечения согласованности данных. Транзакция обеспечивает гарантированное выполнение либо всех входящих в неё SQL-операций, либо ни одной. Транзакции управляются командами BEGIN, COMMIT, ROLLBACK.

⏩Транзакции выполняются внутри сессий. Сессия — это одно соединение с базой данных, которое начинается при подключении к базе данных и завершается при её отключении. Транзакция начинается с команды BEGIN и завершается командой COMMIT (успешное завершение) или ROLLBACK (откат). Указывать BEGIN, COMMIT и ROLLBACK не обязательно, часто их использование подразумевается неявно. В случае если сессия неожиданно прерывается, тогда все транзакции, которые были начаты в текущей сесcии – автоматически откатываются.

⏩Подробнее:
— BEGIN – инициирует новую транзакцию. После выполнения этой команды все последующие операции с базой данных будут выполняться в рамках этой транзакции.
— COMMIT – завершает текущую транзакцию, применяя все её операции. Если все операции в транзакции были успешными, результаты этих операций фиксируются (становятся постоянными). Изменения становятся видны последующим транзакциям.
— ROLLBACK – откатывает текущую транзакцию, отменяя все её операции, если в процессе выполнения транзакции возникли ошибки или отмена транзакции производится приложением исходя из внутренней логики работы.

⏩Если данные, внесённые с помощью транзакции на изображении верны – нужно выполнить инструкцию подтверждения транзакции:

COMMIT;

📎 Читать подробнее

@sqlhub

💻Распространённые ошибки в составлении 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;

@sqlhub

💎 Квантизация нейросетевых моделей

Интересная статья, в которой ML-разработчик Яндекса рассказывает, что такое квантизация и как она помогает экономить ресурсы, разбирает типы данных и объясняет, что квантизовать, чтобы улучшить эффективность модели.

Автор также рассказывает, что такое выбросы, как они портят жизнь ML-инженерам, а также как с ними бороться.

📎Статья

@sqlhub

🖥 Как проверить, является ли строка продолжением предыдущей.

В статье показано, как с помощью matching в Oracle #SQL найти серию последовательных строк с начальной даты и до конечной:

MATCH_RECOGNIZE (
...
PATTERN (str e+)
DEFINE e AS start_date = PREV(end_date) + 1 --start is 1 day after previous end
)

https://how2ora-en.blogspot.com/2023/01/how-to-check-if-row-is-continuation-of.html

@sqlhub

💻Пару полезных советов при работе с PostgreSQL

⏩Используйте отдельные схемы для разных модулей
Скажем, вам необходимо реализовать систему рассылки пуш-уведомлений на android-устройства клиентов. Вы пишете сервис, который будет работать с FCM. Пишете функции в вашей БД, которые будут выдавать списки token-текст (да там много полей вообще).
Так вот, необходимо, чтобы такие ручки для разных модулей (бэкендов) лежали в отдельных схемах.

⏩Используйте группы ролей и назначайте гранты именно на роли
Предположим, что вы реализовали пункт выше, теперь ваш сервис должен подключаться к БД и крутить свои запросы. Но под каким пользователем? Не будет же он бегать под postgres или admin?)

⏩Используйте расширения
PostgreSQL — это мощная СУБД. Но и она не может быть универсальной. Не надо полагать, что всё делается штатными средствами PostgreSQL. Так-же не надо пробовать писать свои системы чего-либо.

⏩Храните дататайм в timestamptz
Возьмите за привычку в БД всегда хранить timestampTZ и когда-нибудь Вам скажут большое спасибо за это.

⏩Используйте метрики. Пользуйтесь pg_stat_statements
Так-же рекомендую сразу установить расширения для мониторинга запросов (например pg_stat_statements)

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

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

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

⏩Старайтесь не пихать всё в TOAST
Наверное сразу стоит добавить и тот пункт, что не надо хранить в TOAST ( например json/text с дефолтным параметром хранения ) аналитические данные.

📎 Статья

@sqlhub

🖥 Harlequin — SQL IDE в вашем терминале

Функции, которые вы ожидаете от IDE, работают прямо в вашем терминале". Среди этих функций:

- Каталог данных: просмотр таблиц, столбцов и их типов.
- Редактор запросов.
- Окно для отображения результатов (возможность вывода 1M+ записей).
- Экспорт результатов и история запросов.

▪Github

@sqlhub

💻Поговорим немного о автогенерации первичного ключа

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

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

⏩Автогенерацию первичного ключа часто называют автоинкрементом (autoincrement). Что переводится как автоматическое увеличение и напоминает операцию инкремента из программирования ++.

⏩До определенного момента механизм автоинкремента был реализован по-своему в каждой СУБД разными способами. Это создавало проблемы при переходе от одной СУБД к другой и усложняло реализацию программного слоя доступа к базе данных.

⏩Эта функциональность добавлена в стандарт SQL:2003, то есть очень давно. И только в 2018 году PostgreSQL в версии 10 стал его поддерживать. Такой автоинкремент известен под именем GENERATED AS IDENTITY:

CREATE TABLE colors (
  -- Одновременное использование и первичного ключа и автогенерации
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name varchar(255)
);

INSERT INTO colors (name) VALUES ('Red'), ('Blue');

SELECT * FROM colors;

-- id    name
-- 1     Red
-- 2     Blue

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

⏩Вот его структура из документации:

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

@sqlhub