14137
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
Копался во внутренностях MySQL и наткнулся на интересную штуку — doublewrite buffer. Это аккуратный хак против порчи данных. Коротко о том, как он работает.
Когда в MySQL (InnoDB) происходит запись, движок не пишет страницу сразу на диск в её финальное место. Вместо этого он делает так:
- сначала пишет страницу в doublewrite buffer на диске
- fsync — чтобы гарантировать, что данные легли
- потом копирует страницу в её финальное место в data file
Зачем этот лишний шаг? Он даёт атомарность и защиту от крэшей.
Представь, что сервер падает ровно в момент записи страницы в data file. Мы переписываем старые байты новыми, и если запись порвётся посередине — файл данных может оказаться повреждённым. Восстановиться будет уже некуда.
Но так как InnoDB сначала пишет в отдельный буфер, у нас есть прочная копия страницы с последними изменениями. После рестарта движку остаётся просто скопировать её на место.
Если порча произошла на финальном шаге, InnoDB просто восстанавливает страницу из doublewrite buffer.
Цена — небольшой дополнительный I/O, но выигрыш — высокая надёжность, crash recovery и защита от повреждений. Очень крутое решение с инженерной точки зрения.
👉 @SQLPortal
SQL Studio вышел в публичную бету
Современный кроссплатформенный SQL-клиент, сделанный с упором на скорость и простоту.
SQLite уже поддерживается. Остальные диалекты в пути.
Баги прилагаются 🪲
[https://sql.studio]
👉 @SQLPortal
в Postgres 18 появился новый возвращаемый полеcет для OLD и NEW данных. куча прикладного кода зависит от того чтобы вставить данные и сразу знать старое и новое состояние. теперь можно сразу подтверждать INSERT и UPDATE без лишних костылей.
👉 @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';
Сравнение хранения: булевы значения или целые числа
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: https://use-the-index-luke.com/
Охватывает сразу несколько СУБД: MySQL, PostgreSQL, Oracle и другие.
👉 @SQLPortal
Простая демонстрация того, насколько мощными могут быть вложенные выражения CASE для анализа данных.
👉 @SQLPortal
Полная и актуальная документация по PostgreSQL
SQL-синтаксис, индексы, транзакции, планировщик запросов, репликация, расширения и внутренняя архитектура. Здесь подробно описано не только как писать запросы, но и как база данных работает.
Забираем здесь!
👉 @SQLPortal
4,112,928 rows affected
👉 @SQLPortal
Кое-что полезное: учебное пособие по SQL для анализа данных
ЗАБИРАЕМ ЗДЕСЬ
👉 @SQLPortal
Как планировщик оценивает план выполнения запросов
Раз уж разобрались с cost’ами и статистикой, посмотрим, как PostgreSQL реально собирает и выбирает план.
Планировщик разбивает запрос на части и считает стоимость каждой отдельно.
Для каждой таблицы в запросе PostgreSQL оценивает разные варианты доступа. Пройтись по всей таблице seq scan? Использовать индекс? Задействовать несколько индексов вместе? Каждый вариант получает свою оценку стоимости на основании параметров cost’ов и статистики, о которых говорили в пред постах.
Для join’ов между таблицами PostgreSQL перебирает разные алгоритмы: nested loop, hash join, merge join. Каждый вариант также получает оценку.
Итоговая стоимость плана — это сумма всех операций: сканирование таблиц, join’ы, сортировки, фильтры. PostgreSQL генерирует несколько полноценных планов, считает итоговую стоимость каждого.
Побеждает тот, у которого минимальная оценка. Именно его PostgreSQL и исполнит.
Подход снизу вверх: оцениваем отдельные операции, собираем их в полный план, выбираем самый дешёвый. Так PostgreSQL ориентируется в огромном пространстве вариантов. Дальше посмотрим, как он оценивает варианты доступа к таблицам.
👉 @SQLPortal
Просмотр статистики
PostgreSQL держит всю эту инфу в системных каталогах, которые можно дернуть обычным запросом.
-- Базовая статистика по таблице
SELECT schemaname, relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'film';
-- Результат:
-- schemaname | relname | n_live_tup | n_dead_tup
-- -----------+---------+------------+------------
-- public | film | 1000 | 0
-- Статистика по колонкам
SELECT tablename, attname, n_distinct, null_frac,
most_common_vals, most_common_freqs,
correlation
FROM pg_stats
WHERE tablename = 'film' AND attname = 'rental_duration';
-- Результат:
-- tablename | attname | n_distinct | null_frac | most_common_vals | most_common_freqs | correlation
-- ----------+-----------------+------------+-----------+-------------------+----------------------------------------+-------------
-- film | rental_duration | 5 | 0 | {6,3,4,5,7} | {0.212,0.203,0.203,0.191,0.191} | 0.163
Статистика: понимание своих данных
Формулы стоимости ничего не стоят без точного представления о данных. Сколько строк пройдёт по WHERE? Сколько уникальных значений у колонки? Какие значения встречаются чаще всего? Ответы на эти вопросы дают именно статистики.
Команда ANALYZE в PostgreSQL берёт выборку из таблиц и строит статистические сводки. Эти данные участвуют во всех решениях планировщика. Именно они отделяют нормальный план выполнения от полного провала.
Что отслеживает PostgreSQL
Для каждой колонки PostgreSQL хранит набор статистик:
Row Counts: количество живых строк в каждой таблице. Это база для всех оценок - если планировщик не знает примерный размер таблицы, он не сможет адекватно прикинуть стоимость запроса.
Null Fraction: доля NULL-значений. Когда вы пишете WHERE column IS NOT NULL, именно эта метрика помогает планировщику понять, насколько фильтр будет выборочным.
Most Common Values (MCVs): самые частые значения и их частоты. PostgreSQL хранит до ~100 таких значений на колонку.
Почему MCV важны. Представим таблицу:
-- У большинства фильмов rental_duration = 6 (21.2%), остальные распределены ровнее
SELECT * FROM film WHERE rental_duration = 6;
-- MCV показывает 21.2% → вероятнее всего последовательное сканирование
SELECT * FROM film WHERE rental_duration = 7;
-- MCV показывает 19.1% → всё ещё относительно выборочно
SELECT * FROM payment WHERE amount BETWEEN 5.00 AND 10.00;
Шаги SQL-запроса
Когда выполняется SQL-запрос:
SELECT owner, count(*)
FROM cats
WHERE owner != 3
GROUP BY owner
HAVING count(*) = 2
ORDER BY owner DESC;
PostgreSQL позволяет клонировать базу на 6 ГБ за 212 миллисекунд вместо 67 секунд. Вот как это работает.
Клонирование баз данных полезно в нескольких сценариях:
* тестирование миграций без трогания продакшн-данных
* поднятие свежих копий под каждый прогон тестов
* сброс sandbox-окружения между сессиями
* воспроизводимые снапшоты для отладки
Когда база весит несколько мегабайт, pg_dump вполне справляется. Но когда речь идёт о сотнях гигабайт, «просто сделать копию» превращается в серьёзный узкий момент.
В PostgreSQL система шаблонов была всегда. Каждый CREATE DATABASE тихо клонирует template1 под капотом, и вместо template1 можно использовать любую базу.
В версии 15 появился параметр STRATEGY, и по умолчанию включили WAL_LOG — поблочное копирование через журнал предзаписи (WAL). I/O становится ровнее, но для больших баз это медленнее.
В PostgreSQL 18 появилась опция file_copy_method = clone. На современных файловых системах вроде XFS, ZFS или APFS она использует операцию FICLONE. Вместо копирования байтов файловая система создаёт новые метаданные, которые указывают на те же физические блоки. Обе базы используют одно и то же хранилище, пока вы ничего не меняете.
Всю магию здесь делает файловая система, создавая copy-on-write (CoW) клон файлов.
Когда вы обновляете строку, файловая система запускает copy-on-write только для затронутых страниц. Всё остальное остаётся общим. Клон на 6 ГБ изначально не занимает дополнительного места и растёт только по мере расхождения данных.
Важно помнить один момент: у исходной базы не должно быть активных подключений во время клонирования. Это ограничение PostgreSQL, а не файловой системы.
Довольно круто.
👉 @SQLPortal
Почему SQL считается общим языком для работы с данными
SQL настолько важен, что даже внутри Python у тебя всё равно появляется SQL.
Да, даже в pandas — по сути тот же подход.
Да, даже в PySpark — тоже SQL.
Pandas просто заимствовал базовую логику: SELECT, WHERE, GROUP BY, JOIN и завернул её в методы и функции. Синтаксис другой, мышление то же.
Если начать с SQL, потом быстрее въезжаешь в pandas и PySpark — порог входа заметно ниже.
Если уже работаешь с pandas, то выучить SQL тоже будет довольно просто.
Вывод: если ты занимаешься данными, SQL обязателен.
👉 @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;
Операционные накладные расходы: почему целые числа лучше
1. Эволюция схемы
Добавление новых состояний является обычным требованием в развивающихся приложениях. Рассмотрим оперативные этапы для каждого подхода:
Булевы значения:
- Добавление нового состояния требует добавление в схему нового булева поля, что влечет за собой изменение базы данных, а также тестирование в различных средах в целях безопасности. Пример:
ALTER
-- Множество булевых полей
SELECT * FROM users WHERE isActive = TRUE AND isSuspended = FALSE;
-- Единственное поле status
SELECT * FROM users WHERE status = 1; -- Активный пользователь
Популярный 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;
SQL-агент с самокоррекцией и визуализацией
Этот API на базе Flask предоставляет расширенные сервисы анализа и визуализации SQL-запросов с использованием LangChain и LangGraph.
⚡️ Ссылка
👉 @SQLPortal
Целые числа: масштабируемая альтернатива
Исходная схема
Целочисленное поле обладает большей гибкостью, поскольку оно может представлять множество состояний. Вместо многочисленных булевых полей единственное поле status может кодировать все возможные состояния, например:
CREATE TABLE users (
id INT PRIMARY KEY,
status INT NOT NULL
);
const Status = {
ACTIVE: 1,
PENDING: 2,
SUSPENDED: 3,
DEACTIVATED: 4,
};
Булевы значения и целые числа в базах данных: понимание компромиссов
Булевы значения просты и рациональны, но они плохо масштабируются, когда ваша модель данных эволюционирует. Целые числа могу элегантно обрабатывать множество состояний, уменьшая сложность схемы.
При проектировании базы данных разработчики часто сталкиваются с выбором казалось бы простого решения: следует ли использовать булевы значения или целые для представления поля? Хотя на первый взгляд разница может показаться тривиальной, выбор может оказать существенное влияние на хранилище, поддержку и масштабируемость. Давайте исследуем "за" и "против" каждого подхода, сосредоточив внимание на размере хранилища, накладных операционных расходах и обслуживании в долгосрочной перспективе.
Булевы значения: простой двоичный вариант
Исходная схема:
Булево поле подходит идеально, когда значение может иметь только два состояния: true или false. Например, поля типа isActive или isAdmin - подходящий вариант. Вот типичный пример:
CREATE TABLE users (
id INT PRIMARY KEY,
isActive BOOLEAN
);
Нужна opеn-source альтернатива Airtable?
NocoDB позволяет поверх существующей базы сделать интерфейс как у таблиц, без написания бэкенда.
Подключаешь MySQL, PostgreSQL, SQL Server, SQLite и т.д., а дальше работаешь с данными через вьюхи, фильтры, формы и связи между таблицами.
Можно развернуть у себя бесплатно или взять их хостинг, если не хочется заморачиваться с инфраструктурой.
https://github.com/nocodb/nocodb
👉 @SQLPortal
Это лучшая статья про MVCC в MySQL, что я читал.
MySQL и Postgres используют разные инженерные подходы для решения одной и той же задачи: undo log против нескольких версий кортежей.
Ещё одна отличная работа от Jeremy Cole.
👉 @SQLPortal
pgcli и mycli — отличные апгрейды по сравнению со стандартными клиентами psql и mysql.
Есть автодополнение, подсветка синтаксиса и в целом куда приятнее пользоваться.
Если заходишь к базе прямо из терминала, ставь их как можно раньше.
👉 @SQLPortal
Вставки в БД всегда про компромисс по производительности: либо пишем быстро и раскладываем всё по полкам позже, либо сразу платим за организацию данных.
Хорошо видно на примере MySQL и Postgres. В MySQL с InnoDB новые строки сразу падают в кластерный индекс. Это даёт быстрые последующие выборки, но вставка сама по себе дороже из-за обхода дерева, вставки в нужное место и возможного page split.
Postgres просто пишет новые строки в файл страниц/кортежей, из-за чего вставка получается попроще, но оптимизация места (VACUUM) откладывается на потом. Плюс индексы обновляются отдельно, потому что они хранятся вне основного файла таблицы.
На картину влияет и формат хранения: B-деревья против LSM-деревьев.
В B-деревьях вставка требует больше работы сразу. LSM разнесён по уровням, и система регулярно делает фоновые проходы и слияния. Запись выглядит «быстрее» (вкинули данные в in-memory слой), но потом движок постоянно занимается оптимизацией.
Отдельная история — журнал (binlog, WAL и так далее). Логи позволяют ускорить запись, перенося обновление реальных структур на диск до чекпоинта или вытеснения страницы.
Даже с этим механизмом заметно, насколько падает или растёт производительность в зависимости от того, сколько работы откладывается «на потом».
👉 @SQLPortal
MongoDB выкатывает внезапный праздничный фичерелиз
К РАСШИРЕННОМУ РЕЛИЗУ
Пало-Альто, Калифорния — MongoDB с радостью объявляет о MongoBleed™, инновационной фиче, которая проактивно делится содержимым вашей базы данных со всем интернетом.
«Годы нам говорили: как сделать наши чувствительные данные доступнее?» — говорит вымышленный спикер. — «MongoBleed отвечает на этот запрос. Никакой авторизации. Никакого согласия. Просто свободный, беспрепятственный поток данных наружу».
Ключевые возможности:
- Zero-Click Sharing: пароли сами уходят в интернет
- Десятилетие доверия: тихая бета с 2015 года
- Релиз под праздники: ничто так не говорит «С Рождеством», как продакшен-секреты на GitHub
- Elastic Integration: писали явно люди, которые «поняли задачу» по-своему
Отзывы пользователей:
«Я ел рождественский ужин, когда прилетел пейдж. База начала делиться логинами по всему миру. Семья реально сплотилась — вокруг моего ноутбука, пока я плакал». — вполне настоящий IT-админ
Что дальше?
Планы на 2026:
- Автоматическая рассылка паролей на Shodan
- AI-добыча секретов (они тоже в AI теперь, а как же)
- Интеграция со Slack, которая шлёт ваш .env прямо в #general
О MongoDB: MongoDB — база, которая верит, что данные хотят быть свободными. Очень свободными.
👉 @SQLPortal
Startup vs Total Cost
PostgreSQL считает для каждой операции два показателя:
Startup Cost — работа до первой строки результата. Для seq scan почти ноль, потому что строки можно отдавать сразу. Для sort - сначала надо отсортировать весь набор, и только потом появятся строки на вывод.
Total Cost — startup плюс выполнение до конца, то есть стоимость получения всех строк.
Пример на базе pagila:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM film WHERE rental_duration = 5;
Seq Scan on film (cost=0.00..76.50 rows=191 width=390) (actual time=0.013..0.252 rows=191 loops=1)
Filter: (rental_duration = 5)
Rows Removed by Filter: 809
Buffers: shared hit=64
Planning Time: 0.063 ms
Execution Time: 0.274 ms
cost=0.00..76.50 означает:Buffers: shared hit=64 говорит, что запрос прошёл по 64 страницам буферного кеша. На этих страницах и строится стоимость:Чтение страниц: 1.0 × 64 страниц = 64.0
Обработка строк: 0.01 × 1000 строк = 10.0
Проверка фильтра: 0.0025 × 1000 строк = 2.5
Итого: ~76.5
Свежак: Elasticsearch не обязателен. BM25 уже есть в Postgres.
Теперь поиск можно закрывать прямо в базе. Postgres для всего.
Читать
👉 @SQLPortal
sqlit — это TUI в стиле lazygit для работы с SQL в терминале.
Поддерживает больше 10 баз (Postgres, SQLite, Supabase, ClickHouse, DuckDB и другие), есть история запросов, автодополнение и прочие удобства.
https://terminaltrove.com/sqlit/
👉 @SQLPortal