Операционные накладные расходы: почему целые числа лучше

1. Эволюция схемы

Добавление новых состояний является обычным требованием в развивающихся приложениях. Рассмотрим оперативные этапы для каждого подхода:

Булевы значения:

- Добавление нового состояния требует добавление в схему нового булева поля, что влечет за собой изменение базы данных, а также тестирование в различных средах в целях безопасности. Пример:

ALTER

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

Целые значения (status):

- Добавление нового состояния может потребовать только изменения логики приложения (например, добавление новой константы в перечисление Status).
- Никаких изменений схемы не требуется.

2. Согласованность данных

При наличии большого числа булевых полей поддержка согласованности становится проблематичной:

Пример: Для пользователя не должно одновременно выполняться isActive = TRUE и isSuspended = TRUE.

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

С единственным полем status пользователь сможет иметь в каждый момент времени только одно состояние, что естественным образом препятствует ошибочным сочетаниям.

3. Простые запросы

Запросы по множеству булевых полей сложны и подвержены ошибкам:

-- Множество булевых полей
SELECT * FROM users WHERE isActive = TRUE AND isSuspended = FALSE;

При единственном поле status запросы яснее:

-- Единственное поле status
SELECT * FROM users WHERE status = 1; -- Активный пользователь

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

👉 @SQLPortal

в Postgres 18 появился новый возвращаемый полеcет для OLD и NEW данных. куча прикладного кода зависит от того чтобы вставить данные и сразу знать старое и новое состояние. теперь можно сразу подтверждать INSERT и UPDATE без лишних костылей.

👉 @SQLPortal

Postgres умеет очень просто агрегировать данные по датам через date_trunc — просто передай day, week, month, quarter и сделай GROUP BY по этому же полю.

SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS monthly_total
FROM
    orders
GROUP BY
    date_trunc('month', order_date)
ORDER BY
    month;

👉 @SQLPortal

SQL Studio вышел в публичную бету

Современный кроссплатформенный SQL-клиент, сделанный с упором на скорость и простоту.

SQLite уже поддерживается. Остальные диалекты в пути.

Баги прилагаются 🪲

[https://sql.studio]

👉 @SQLPortal

Почему SQL считается общим языком для работы с данными

SQL настолько важен, что даже внутри Python у тебя всё равно появляется SQL.

Да, даже в pandas — по сути тот же подход.

Да, даже в PySpark — тоже SQL.

Pandas просто заимствовал базовую логику: SELECT, WHERE, GROUP BY, JOIN и завернул её в методы и функции. Синтаксис другой, мышление то же.

Если начать с SQL, потом быстрее въезжаешь в pandas и PySpark — порог входа заметно ниже.

Если уже работаешь с pandas, то выучить SQL тоже будет довольно просто.

Вывод: если ты занимаешься данными, SQL обязателен.

👉 @SQLPortal

Копался во внутренностях MySQL и наткнулся на интересную штуку — doublewrite buffer. Это аккуратный хак против порчи данных. Коротко о том, как он работает.

Когда в MySQL (InnoDB) происходит запись, движок не пишет страницу сразу на диск в её финальное место. Вместо этого он делает так:

- сначала пишет страницу в doublewrite buffer на диске
- fsync — чтобы гарантировать, что данные легли
- потом копирует страницу в её финальное место в data file

Зачем этот лишний шаг? Он даёт атомарность и защиту от крэшей.

Представь, что сервер падает ровно в момент записи страницы в data file. Мы переписываем старые байты новыми, и если запись порвётся посередине — файл данных может оказаться повреждённым. Восстановиться будет уже некуда.

Но так как InnoDB сначала пишет в отдельный буфер, у нас есть прочная копия страницы с последними изменениями. После рестарта движку остаётся просто скопировать её на место.

Если порча произошла на финальном шаге, InnoDB просто восстанавливает страницу из doublewrite buffer.

Цена — небольшой дополнительный I/O, но выигрыш — высокая надёжность, crash recovery и защита от повреждений. Очень крутое решение с инженерной точки зрения.

👉 @SQLPortal

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

Functions:

- Считают и возвращают значения
- Не управляют транзакциями
- Можно использовать внутри SELECT или WHERE
- SELECT function()

Procedures:

- Действия и батчи
- COMMIT и ROLLBACK
- Нельзя вызывать внутри SQL
- CALL procedure()

👉 @SQLPortal

Data Modeling

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

Теперь у нас есть таблица с одной строкой на каждую строку файла. Как сгруппировать такие данные? Группировку похожих строк обычно делают через window-функции. В нашем случае нужно «складывать» строки до тех пор, пока не встретится пустая строка, после чего начинать новую группу. Создадим псевдоколонку и будем увеличивать sequence только когда значение в calories_count пустое. Также вызовем setval() для задания начального значения, чтобы функция currval() работала.

CREATE SEQUENCE aoc;
SELECT setval('aoc', 1);
SELECT calories_count,
       CASE WHEN calories_count = ''  THEN nextval('aoc')
            ELSE currval('aoc') END
FROM calories LIMIT 10;

Результат:

calories_count | currval 
---------------+---------
 9686          |       1
 10178         |       1
 3375          |       1
 9638          |       1
 6318          |       1
 4978          |       1
 5988          |       1
 6712          |       1
               |       2
 10422         |       2

Как видно, currval меняется, когда sequence обнаруживает новую группу. Используем это свойство, чтобы посчитать сумму по каждой группе. Поскольку у нас текстовый столбец, придется привести его к int перед суммированием. Дополнительно пустая строка не может быть приведена к int, поэтому явно детектируем строку-разделитель и считаем ее значение равным 0.

👉 @SQLPortal

Group and Sum

SELECT SUM(calories_count) OVER(partition by currval) FROM
  (SELECT CASE WHEN calories_count = '' THEN 0
               ELSE calories_count :: int END AS calories_count,
          CASE WHEN calories_count = '' THEN nextval('aoc')
               ELSE currval('aoc') END
   FROM calories
  ) x LIMIT 10;

Результат:

sum  
-------
 56873
 56873
 56873
 56873
 56873
 56873
 56873
 56873
 43456
 43456

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

👉 @SQLPortal

Дан запрос:

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC
LIMIT 5;

Вопрос: какой реальный порядок выполнения?

Варианты:

A) FROM → WHERE → SELECT → GROUP BY → HAVING → ORDER BY → LIMIT
B) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
C) FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY → LIMIT
D) WHERE → FROM → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

👉 @SQLPortal

Руководство по SQL

Это руководство охватывает различные аспекты, начиная с основ SQL и до более сложных тем

Материал дополнен практическими примерами, которые способствуют лучшему усвоению информации

Сохраняйте, чтобы не потерять

👉 @SQLPortal

Повышение производительности PostgreSQL: пошаговое руководство по использованию pg_hint_plan

Планировщик запросов PostgreSQL - это сложный инженерный механизм, обычно принимающий блестящие решения относительно того, как выполнять ваши запросы. Однако в сложных сценариях или при необычных распределениях данных вы можете знать лучший способ. Именно в таких ситуациях на помощь приходит pg_hint_plan - мощное расширение, которое позволяет вам руководить, или "советовать", планировщиком для выбора специфичного пути выполнения.

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

👉 @SQLPortal

Совет по Postgres: используй filter вместо case when для условных агрегатов. Читается проще, выглядит более идиоматично для SQL и часто работает быстрее.

--  не делай так: громоздко и хуже читается
select
  count(case when status = 'active' then 1 end) as active_count,
  count(case when status = 'archived' then 1 end) as archived_count
from projects;

--  лучше так: чище и оптимизируется Postgres
select
  count(*) filter (where status = 'active') as active_count,
  count(*) filter (where status = 'archived') as archived_count
from projects;

👉 @SQLPortal

Нужно посчитать все строки в здоровенной таблице в Postgres?

SELECT count(*) FROM table;

Это может быть медленно. Можно спросить внутренние таблицы.

SELECT reltuples AS estimate FROM pg_class WHERE relname = 'table';

Это оценка, но обычно довольно точная.

👉 @SQLPortal

В Postgres каждая sequence хранится как отдельная таблица. Детали по sequence можно посмотреть напрямую, делая select из этой таблицы, или через команду \d в psql, чтобы получить описание.

Как искать sequences в Postgres

1. \d таблицы, ищем sequence-колонку

postgres=# \d user_events
             Table "public.user_events"
 Column     | Type                      | Collation | Nullable | Default
------------+---------------------------+-----------+----------+--------------------------------------------------
 id         | integer                   |           | not null | nextval('user_events_id_seq'::regclass)
 data       | text                      |           |          |
 created_at | timestamp without time zone|          |          | now()

Indexes:
    "user_events_pkey" PRIMARY KEY, btree (id)

2. SELECT * из sequence-таблицы, чтобы посмотреть текущее значение

postgres=# SELECT * FROM user_events_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
     203590 |      23 | t
(1 row)

3. \d sequence-таблицы, чтобы посмотреть максимальное значение и шаг

postgres=# \d user_events_id_seq;
          Sequence "public.user_events_id_seq"
 Type    | Start | Minimum | Maximum     | Increment | Cycles? | Cache
---------+-------+---------+-------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647  |         1 | no      |     1

👉 @SQLPortal

SQL-совет:

Есть одна особенность в SQL, на которой спотыкаются многие новички (и даже часть опытных пользователей):

ORDER BY может быть чувствителен к регистру.

Большинство думает, что сортировка строк — это просто.
Алфавит. От A до Z. И всё.

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

То есть вместо:

apple
banana
cherry

Можно получить:

Apple
Banana
apple
banana

Слова те же, порядок другой.

Почему так?

Потому что некоторые базы воспринимают заглавные и строчные символы как разные значения при сортировке.

В SQLite можно отключить чувствительность к регистру через COLLATE NOCASE.

Здесь не нужно менять сами данные, можно просто сказать SQL, как их сортировать. Пример ниже.

SELECT name FROM products ORDER BY name COLLATE NOCASE;

👉 @SQLPortal

Хочешь хранить текст? Не используй char(n) или varchar(n).

В других базах это встречается постоянно, но для Postgres — не лучшая идея.

• фиксированная ширина работает медленнее
• TEXT — переменной длины и без лимита
• если нужен размер, ставь constraint и ограничивай полем, а не типом

В итоге TEXT почти всегда выигрывает по практичности и производительности.

👉 @SQLPortal

В Postgres 18 наконец-то завезли параллельную сборку GIN-индексов при CREATE INDEX. То есть индексы под full-text (tsvector) и jsonb теперь могут строиться не в один поток, а с воркерами, как это уже давно было у B-tree/BRIN.

Фишка в том, что оно автоматом упирается в ваши настройки maintenance-параллелизма: max_parallel_maintenance_workers + общий бюджет maintenance_work_mem (и от него тоже зависит, сколько воркеров вообще дадут).

Мини-чеклист, если часто пересоздаете FTS/JSON индексы:

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 8;

CREATE INDEX gin_idx ON docs USING gin (to_tsvector('simple', body));
-- или jsonb
CREATE INDEX gin_json_idx ON docs USING gin (payload);

На больших таблицах это прям приятный буст, особенно когда билд был CPU-bound, а не в потолок по диску.

👉 @SQLPortal

psql-лайфхак, который экономит кучу времени: \i (include) для запуска локального файла прямо из psql.

Пример:

\i ~/Documents/query.sql

psql откроет файл, выполнит все SQL-команды внутри, и выведет результат сразу в текущую сессию (как будто ты вручную вставил содержимое). Удобно для повторяемых запросов, миграций, быстрых дебаг-скриптов и репортов.

👉 @SQLPortal

PlanetScale показали pg_strict для Postgres.

Идея простая: это расширение, которое добавляет в Postgres “страховочную сетку” и ловит опасные запросы до того, как они реально выполнятся.

У всех бывало: хотел поправить одну строку, а всё уехало в UPDATE без WHERE. Или случайный DELETE, или “быстренько проверю миграцию” на не той базе. В итоге урон не от бага в коде, а от одной команды в psql.

Что обещает pg_strict:

- проверять запросы заранее
- блочить потенциально опасные операции
- спасать прямо на уровне базы, а не уже после инцидента

👉 @SQLPortal