Сравнение хранения: булевы значения или целые числа

1. Хранилище для множества булевых полей

Скажем, мы хотим представить следующие состояния: isActive, isPending, isSuspended и isDeactivated. Использование булевых значений:

- Каждое булево значение требует 1 байт.
- Для четырех булевых полей общий объем хранилища на запись составляет 4 байта.

Если набор данных состоит из 1 миллиона пользователей, то для 4 булевых полей потребуется 4 х 1000000 = 4000000 байтов (4 Мб).

Если при развитии системе потребовалось иметь 10 состояний, представленных 10 булевыми полями, требование к хранилищу растет линейно:

10 х 1000000 = 10000000 байтов (10 Мб).

По мере роста объема данных это линейное увеличение объема памяти становится все более заметным.

2.Хранилище для единственного поля состояний

Использование целочисленного поля:

- Целое требует четырех байтов.
- Суммарный объем хранилища для 1 миллиона пользователей: 4 х 1000000 = 4000000 байтов (4 Мб).

Даже если при развитии система будет включать 10 состояний, размер хранилища на одну запись останется постоянным - 4 байта. Для 100 миллионов пользователей потребуется размер хранилища 4 х 100000000 байтов (400 Мб).

Ключевой момент

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

👉 @SQLPortal

Популярный SQL-вопрос на собеседованиях: Напишите запрос, который находит вторую по величине зарплату в таблице employees. А что делать, если второй по величине зарплаты не существует?

Зачем его задают:

Этот вопрос проверяет не знание синтаксиса, а базовое SQL-мышление. Поиск второй зарплаты заставляет учитывать дубликаты, сортировку, фильтрацию и пограничные случаи вроде «а если значения нет». Он показывает, умеешь ли ты рассуждать о данных, а не просто вытаскивать их запросом.
Интервьюеры также смотрят, понимаешь ли ты, как SQL пошагово обрабатывает результаты, и способен ли писать устойчивые запросы, а не хрупкие. С виду задача простая, но она быстро вскрывает, реально ли человек понимает основы SQL или просто заучил пару шаблонов.

Как отвечать:

Очевидно, способов несколько. Логика простая:

Сначала определить максимальную зарплату.

Затем найти максимальное значение, которое меньше этого максимума.

Для примера возьмём таблицу employees (id, name, salary). В решении ниже используется подзапрос.

Такой подход автоматически корректно работает с дубликатами. Даже если несколько сотрудников получают максимальную зарплату, подзапрос всё равно вернёт одно значение MAX, а внешний запрос найдёт следующую отличающуюся зарплату. Никаких костылей или допущений не нужно.

Самое важное:

Решение с подзапросом надёжное. Если второй по величине зарплаты не существует, запрос спокойно вернёт NULL, а не сломается и не выдаст мусор. Для интервьюера это сигнал, что ты думаешь про edge cases и пишешь защитный SQL.

Решение:

SELECT (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (SELECT MAX(salary) FROM employees)
) AS second_highest_salary;

👉 @SQLPortal

SQL-совет: понимание оператора EXCEPT в SQL

SELECT sales_person, sales_amount
FROM sales
WHERE sales_person = 'Wei Zhang'
   OR sales_person = 'Yuki Nakamura'

EXCEPT -- используем EXCEPT, чтобы получить строки из первого запроса, которых нет во втором

SELECT sales_person, sales_amount
FROM sales
WHERE sales_person = 'Wei Zhang'
   OR sales_person = 'Giovanni Rossi';

В этом запросе используется оператор EXCEPT для сравнения двух наборов результатов и возврата строк, которые есть в первом SELECT, но отсутствуют во втором.
Первый запрос выбирает записи о продажах, где sales_person равен Wei Zhang или Yuki Nakamura, а второй — записи, где sales_person равен Wei Zhang или Giovanni Rossi.

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

👉 @SQLPortal

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

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

📱 Держите 6 хороших каналов по искусственному интеллекту и программированию для любого уровня!

Выбирай направление:

📱 Нейросети — @neuro_prompt

🤖 AI-инструменты — @ai_prompt

📱 Python — @python_prompt

🤔 InfoSec & Хакинг — @infosec_prompt

👩‍💻 IT Новости — @it_news

😄 IT Мемы — @it_memes

Промпты, обучение, шпаргалки и полезные ресурсы на каждую тему!

в 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