pgcli и mycli — отличные апгрейды по сравнению со стандартными клиентами psql и mysql.

Есть автодополнение, подсветка синтаксиса и в целом куда приятнее пользоваться.

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

👉 @SQLPortal

Кое-что полезное: учебное пособие по SQL для анализа данных

ЗАБИРАЕМ ЗДЕСЬ

👉 @SQLPortal

Это лучшая статья про MVCC в MySQL, что я читал.

MySQL и Postgres используют разные инженерные подходы для решения одной и той же задачи: undo log против нескольких версий кортежей.

Ещё одна отличная работа от Jeremy Cole.

👉 @SQLPortal

4,112,928 rows affected

👉 @SQLPortal

Нужна opеn-source альтернатива Airtable?

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

Подключаешь MySQL, PostgreSQL, SQL Server, SQLite и т.д., а дальше работаешь с данными через вьюхи, фильтры, формы и связи между таблицами.

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

https://github.com/nocodb/nocodb

👉 @SQLPortal

Полная и актуальная документация по PostgreSQL

SQL-синтаксис, индексы, транзакции, планировщик запросов, репликация, расширения и внутренняя архитектура. Здесь подробно описано не только как писать запросы, но и как база данных работает.

Забираем здесь!

👉 @SQLPortal

Булевы значения и целые числа в базах данных: понимание компромиссов

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

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

Булевы значения: простой двоичный вариант

Исходная схема:

Булево поле подходит идеально, когда значение может иметь только два состояния: true или false. Например, поля типа isActive или isAdmin - подходящий вариант. Вот типичный пример:

CREATE TABLE users (
    id INT PRIMARY KEY,
    isActive BOOLEAN
);

Требования к хранилищу:

Для большинства баз данных (например, PostgreSQL и MySQL) булево поле обычно занимает 1 байт на хранение. Это полезно для слабо масштабируемых вариантов использования, особенно когда число таких полей ограничено.

Проблемы с булевыми значениями:

Хотя булевы значения просты и интуитивно понятны, они могут быстро стать проблематичными по мере эволюции приложения:

» Булево значение может представлять только два состояния. Если ваши требования приводят к увеличению числа состояний (например, active, pending, suspended), булевы значения перестанут отвечать этим требованиям.

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

Про целые числа в следующем посте ❤️

👉 @SQLPortal

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

👉 @SQLPortal

Целые числа: масштабируемая альтернатива

Исходная схема

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

CREATE TABLE users (
    id INT PRIMARY KEY,
    status INT NOT NULL
);

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

const Status = {
    ACTIVE: 1,
    PENDING: 2,
    SUSPENDED: 3,
    DEACTIVATED: 4,
};

Требования к хранилищу

Обычно поле integer занимает в хранилище 4 байта, независимо от того, сколько состояний оно представляет. Это делает его эквивалентным четырем булевым полям, если говорить о хранении, - но при этом значительно более выразительным.

Ставь реакцию если делать пост про "сравнение хранения: булевы значения или целые числа" 🕺

👉 @SQLPortal

Полностью бесплатная веб-книга про индексы в SQL: https://use-the-index-luke.com/

Охватывает сразу несколько СУБД: MySQL, PostgreSQL, Oracle и другие.

👉 @SQLPortal

SQL-агент с самокоррекцией и визуализацией

Этот API на базе Flask предоставляет расширенные сервисы анализа и визуализации SQL-запросов с использованием LangChain и LangGraph.

⚡️ Ссылка

👉 @SQLPortal

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

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