14500
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
psql-лайфхак, который экономит кучу времени: \i (include) для запуска локального файла прямо из psql.
Пример:
\i ~/Documents/query.sql
psql откроет файл, выполнит все SQL-команды внутри, и выведет результат сразу в текущую сессию (как будто ты вручную вставил содержимое). Удобно для повторяемых запросов, миграций, быстрых дебаг-скриптов и репортов.
Хочешь хранить текст? Не используй char(n) или varchar(n).
В других базах это встречается постоянно, но для Postgres — не лучшая идея.
• фиксированная ширина работает медленнее
• TEXT — переменной длины и без лимита
• если нужен размер, ставь constraint и ограничивай полем, а не типом
В итоге TEXT почти всегда выигрывает по практичности и производительности.
👉 @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)
postgres=# SELECT * FROM user_events_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
203590 | 23 | t
(1 row)
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
Совет по 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;
Руководство по SQL
Это руководство охватывает различные аспекты, начиная с основ SQL и до более сложных тем
Материал дополнен практическими примерами, которые способствуют лучшему усвоению информации
Сохраняйте, чтобы не потерять
👉 @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
Функции и процедуры на самом деле очень похожи, и многие называют их как попало, но в Postgres это разные вещи.
Functions:
- Считают и возвращают значения
- Не управляют транзакциями
- Можно использовать внутри SELECT или WHERE
- SELECT function()
Procedures:
- Действия и батчи
- COMMIT и ROLLBACK
- Нельзя вызывать внутри SQL
- CALL procedure()
👉 @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
В 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);
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;
Нужно посчитать все строки в здоровенной таблице в Postgres?
SELECT count(*) FROM table;
Это может быть медленно. Можно спросить внутренние таблицы.
SELECT reltuples AS estimate FROM pg_class WHERE relname = 'table';
Это оценка, но обычно довольно точная.
👉 @SQLPortal
Повышение производительности PostgreSQL: пошаговое руководство по использованию pg_hint_plan
Планировщик запросов PostgreSQL - это сложный инженерный механизм, обычно принимающий блестящие решения относительно того, как выполнять ваши запросы. Однако в сложных сценариях или при необычных распределениях данных вы можете знать лучший способ. Именно в таких ситуациях на помощь приходит pg_hint_plan - мощное расширение, которое позволяет вам руководить, или "советовать", планировщиком для выбора специфичного пути выполнения.
Это руководство проведет вас через весь процесс, начиная с установки pg_hint_plan из источника до использования его, чтобы принудительно выполнить сканирование индекса на большом наборе данных, демонстрируя возможности непосредственного управления производительностью вашего запроса.
👉 @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;
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
Копался во внутренностях 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