14500
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
50 практических вопросов по SQL для отработки навыков запросов, часто появляющихся на собеседованиях
> SQL‑скрипты для создания тестовых данных (таблицы, вставка строк)
> Вопросы типа: «Напишите запрос, чтобы вывести уникальные значения», «Найдите N‑ю максимальную зарплату» и т.д.
> Готовые решения и объяснения
читать
👉 @SQLPortal
eBPF-трейсинг спинлоков PostgreSQL
PostgreSQL использует процессную архитектуру: каждое подключение обслуживается отдельным процессом. Часть структур данных шарится между этими процессами, например shared buffer cache или write-ahead log (WAL). Чтобы координировать доступ к этим общим ресурсам, PostgreSQL использует несколько механизмов блокировок, в том числе спинлоки.
Спинлоки рассчитаны на очень короткую защиту общих структур: вместо того чтобы сразу усыплять процесс, который ждёт, он крутится в busy-wait и снова и снова проверяет, освободился ли лок. При контеншене PostgreSQL ещё применяет adaptive backoff, который может включать короткие sleep.
В статье объясняется, что такое спинлоки и как они реализованы в PostgreSQL. Также описывается, как их мониторить, и показывается, как использовать мой новый инструмент pg_spinlock_tracer, чтобы трейсить внутренности спинлоков через eBPF.
👉 @SQLPortal
SQL-хак, о котором мало кто знает
Знал, что можно юзать FILTER как альтернативу CASE в агрегатах?
Самое приятное, что условия отделяются от самой агрегации, и это проще дебажить.
Если твой SQL-движок поддерживает FILTER (PostgreSQL, SQLite, DuckDB), попробуй.
CASE или FILTER? Что тебе больше нравится? 😆
👉 @SQLPortal
У индексов есть встроенный порядок сортировки. По умолчанию B-tree индекс идет по возрастанию с NULLS LAST.
Postgres умеет сканировать индекс как вперед, так и назад, поэтому во многих случаях это не имеет значения.
Но если порядок NULLS FIRST или NULLS LAST не совпадает с тем, что требуется в запросе, Postgres уже не сможет использовать этот индекс.
👉 @SQLPortal
Снижение ошибок оценки количества строк в PostgreSQL: читать
Планировщик запросов PostgreSQL опирается на статистику таблиц, чтобы оценить, сколько строк (estimated rows) будет обработано на каждом этапе выполнения, и на основе этих оценок выбрать оптимальный план выполнения. Когда оценка сильно расходится с реальным количеством строк, планировщик может выбрать неудачный план, что приводит к серьёзному падению производительности запросов.
В этой статье разбираются четыре подхода, которые использовали для снижения ошибок оценки количества строк, в порядке от наименее инвазивных к наиболее инвазивным. Из-за ограничений по конфиденциальности я не могу показать реальный SQL или планы выполнения, поэтому акцент сделан на диагностике, ходе рассуждений и применённых техниках.
👉 @SQLPortal
В Postgres есть несколько способов делать ранжирование с ORDER BY. С выбором нужно быть аккуратным, особенно если есть дубликаты значений.
RANK может пропускать номера, а DENSE_RANK выдает все ранги подряд, но при равных значениях присваивает одинаковый ранг.
👉 @SQLPortal
Как получить топ-5 продавцов в SQL (с учётом ничьих)
❌Первый вариант выглядит корректно, но он не учитывает ничьи. Если несколько продавцов делят 5-е место, SQL просто молча отбросит лишних.
✅Если база данных поддерживает FETCH FIRST … WITH TIES (например, PostgreSQL), второй вариант будет работать правильно и вернёт всех, кто делит место.
✅Для остальных СУБД выручит оконная функция — через RANK() или DENSE_RANK(). Она корректно обработает равные значения и ничего не потеряет.
👉 @SQLPortal
Postgres 18 теперь поддерживает OAUTH 2.0 для аутентификации пользователей.
Также в Postgres 18 log_line_prefix теперь может включать %L для вывода IP-адреса клиента.
👉 @SQLPortal
warda bibi: Раскрываем высокую производительность PostgreSQL: ключевые оптимизации памяти
👉 @SQLPortal
Быстрый sanity check: не забыли ли вы критичные индексы?
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;
Postgres продолжает развиваться в сторону gen AI. Тут наткнулся на два новых расширения.
VectorChord (vchord) это расширение для более дешевого и дисково-эффективного поиска похожих векторов. Обещают, что можно хранить 400 000 векторов всего за $1, и за счет этого получается ощутимая экономия: в 6 раз больше векторов по сравнению с оптимизированным хранилищем Pinecone и в 26 раз больше, чем у pgvector.
pgpu это еще одно расширение, которое умеет использовать NVIDIA GPU через CUDA, чтобы ускорять отдельные операции в базе и/или переносить их с CPU на GPU. Сейчас основной кейс это ускорение времени сборки индексного типа vchord с помощью GPU.
На диаграмме показано, как pgpu считает центроиды для индекса vchord с использованием GPU:
1. читаем данные из базы
2. считаем центроиды на GPU
3. пишем центроиды в таблицу Postgres
4. создаем индекс vchord, передавая таблицу с заранее посчитанными центроидами
👉 @SQLPortal
Правило SQL про GROUP BY, которое должен знать каждый
Если ты выбираешь в SELECT неагрегированные колонки, добавь их в GROUP BY. Это не просто хорошая практика, в некоторых СУБД без этого будет ошибка.
Почему это важно? Потому что GROUP BY определяет, что именно значит одна строка результата. Базе нужно понимать, какое единственное значение положить в каждую ячейку этой строки.
В примере выше одна строка = должность (job_title) внутри отдела (department).
Поэтому и department, и job_title обязаны быть в GROUP BY.
Если неагрегированную колонку не добавить:
❌часть баз сразу упадет с ошибкой
❌другие молча вернут случайные значения
❌запрос может "работать" сегодня и врать завтра
Общее правило: Каждая колонка в SELECT должна быть функционально зависима от GROUP BY. Если внутри группы у колонки может быть несколько значений, она не может появляться в SELECT, пока:
- ты не добавил ее в GROUP BY, или
- не завернул в агрегатную функцию.
👉 @SQLPortal
То, что Postgres в одиночку не тянет, он может закрывать вместе с DuckDB.
Расширение pg_duckdb показывает, как две базы могут работать в связке: аналитические данные крутятся в DuckDB, а потом джойнятся с транзакционными данными в Postgres. Фишка в том, что расширение встраивает DuckDB прямо внутрь Postgres. DuckDB буквально запускается внутри процесса Postgres.
Как это работает (см. диаграмму):
1. Приложение отправляет запрос в Postgres, который джойнит данные портфеля (Postgres) с историческими рыночными данными (lakehouse)
2. pg_duckdb видит доступ к lakehouse и делегирует выполнение DuckDB
3. DuckDB подтягивает нужные рыночные данные из lakehouse, читает таблицы Postgres и делает join
4. Postgres возвращает приложению финальный результат
👉 @SQLPortal
В Postgres 18 появилась новая фича для ограничений по временным диапазонам: WITHOUT OVERLAPS. Это прям то, что нужно для систем бронирований, и в целом заметно проще, чем прошлые решения.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE staff_schedule (
staff_id int,
shift_duration tstzrange,
task_description text,
PRIMARY KEY (staff_id, shift_duration WITHOUT OVERLAPS)
);
PostgreSQL по умолчанию не страхует тебя от забытых транзакций, idle-сессий и улетевших в бесконечность запросов: все это тихо накапливается, пока база не начинает казаться "залипшей".
Если мигрируешь на PostgreSQL, эти 5 параметров таймаутов критично настроить
👉 @SQLPortal
💨 Тормозят SQL-запросы и дашборды? Освободите своё время и нервы!
Устали каждый раз пить кофе, пока выполняется запрос? Раздражает, когда дашборд висит на последнем проценте загрузки? Пора это прекратить!
Приглашаем вас на практический вебинар «Аналитика без тормозов» 11 февраля в 19:00.
Мы разберем, как радикально ускорить вашу работу.
На вебинаре вы:
🔸 Узнаете об эффективных подходах — от тактических SQL-приёмов до стратегических архитектурных решений.
🔸 Разберёте конкретные методы, применимые к любой СУБД, и тонкие нюансы оптимизации.
🔸 Получите готовый набор фишек для ускорения запросов и витрин уже на следующий день.
Table bloat это одна из самых больших слабых сторон Postgres.
Каждый UPDATE строки создает новую копию строки, а старая остается лежать на месте. DELETE помечает строку как удаленную, а освобождение места откладывается на потом.
В базах с большой нагрузкой по UPDATE и DELETE в таблицах накапливаются тонны мертвых строк (tombstones), разбросанных по файлу таблицы. В итоге размер таблицы на диске выглядит гораздо больше, чем реально нужно.
Это и есть table bloat.
Мертвые строки могут переиспользоваться новыми строками, если те по размеру влезают, но иногда, чтобы реально вернуть все доступное мертвое место, нужен VACUUM FULL или похожая пересборка таблицы через расширения.
И это не случайный косяк дизайна: хранение старых версий строк полезно для MVCC и производительности. Просто за это платишь усложнением управления хранилищем :/
👉 @SQLPortal
Elasticsearch отлично работает в разработке. В продакшене все совсем иначе.
За годы эксплуатации ES-кластеров команды снова и снова натыкаются на одни и те же проблемы. Они всплывают в инцидент-репортах и вопросах на Stack Overflow. В статье собраны 10 самых частых:
» Паузы сборщика мусора JVM, которые запускают каскадные отказы
» Взрыв маппингов из-за полуструктурированных данных
» Математика с шардами, которую нужно угадать заранее (и потом уже не поменять)
» Глубокая пагинация, упирающаяся в жесткий лимит в 10 000 результатов
» Split-brain сценарии при сетевых разделениях
» Сюрпризы eventual consistency («я только что сохранил, но не могу найти»)
» Ошибки в настройке безопасности, которые приводили к массовым утечкам
» Сотни метрик, где понять, какие реально важны, можно только с глубокой экспертизой
» Пайплайны синхронизации данных между Postgres и ES, которые ломаются тихо
» Инфраструктурные затраты, растущие с каждым новым узлом
У всех этих проблем один корень: Elasticsearch — это отдельная система.
Отдельная инфраструктура, отдельная экспертиза, отдельные режимы отказа, отдельные пайплайны.
Теперь, когда BM25 доступен в Postgres через pg_textsearch, поиск можно оставить в основной базе. Без JVM, без шардов, без джобов синхронизации. Одна база, один источник истины.
В блоге разбирается каждая из проблем со ссылками на реальные инциденты и объясняется, как Postgres их избегает
👉 @SQLPortal
Рекурсивные CTE в Postgres позволяют проходить по вложенным структурам или иерархиям и находить связи шаг за шагом. Ты задаёшь рекурсию, добавляешь anchor-запрос для старта, и дальше CTE будет итерироваться по данным, пока не дойдёт до конца.
👉 @SQLPortal
Позволь Postgres владеть Iceberg-каталогом, а аналитику делегировать DuckDB. В итоге получаешь транзакционные обновления lakehouse плюс быстрые аналитические запросы.
Это не концепт. Именно так pg_lake работает уже сегодня.
pg_lake объединяет набор расширений и компонентов, которые позволяют напрямую из Postgres читать и изменять Iceberg-таблицы (и другие lakehouse-форматы). Для ускорения аналитических запросов используется DuckDB, запущенный в сайдкар-процессе pgduck_server, который общается с Postgres во время выполнения запроса.
Как это работает:
1. Приложение отправляет запрос в Postgres, чтобы посчитать нереализованный PnL (profit and loss) по тикеру Disney.
2. Postgres парсит запрос и определяет часть, где считается средняя цена на основе исторических данных из lakehouse.
3. Эта часть запроса проксируется в pgduck_server для ускоренного выполнения.
4. pgduck_server передает выполнение в DuckDB, который читает данные из lakehouse (переиспользуя кэш, если он есть).
5. DuckDB считает среднюю цену и возвращает результат в Postgres.
6. Postgres джойнит этот результат с локальными данными портфеля и считает нереализованный PnL.
7. Финальный результат возвращается приложению.
👉 @SQLPortal
Гайд: материализованные представления в PostgreSQL — когда кэширование результатов запросов имеет смысл (и когда нет…)
👉 @SQLPortal
Строки в PostgreSQL неизменяемые и хранятся в heap. У каждой строки есть скрытый идентификатор CTID, который указывает на ее физическое местоположение на диске.
CTID это идентификатор-кортеж из двух частей: номер страницы и индекс (позиция) внутри этой страницы. Посмотреть его можно простым запросом:
SELECT ctid, * FROM your_table;
(0,1), (0,2), (1,1), где первое число это страница, а второе это позиция.
БОЛЬШОЙ SQL-грех
Использовать NOT IN или IN, когда в данных (или в списке) могут быть NULL.
В SQL оператор IN это сокращение для набора условий через OR, а сравнение value = NULL всегда даёт UNKNOWN. Из-за этого запрос чаще всего вернёт пустой результат.
Если нужно учитывать NULL, обрабатывайте их явно: добавляйте условие IS NULL вместе с IN, или используйте другой подход, который корректно учитывает NULL.
Всегда тестируйте запросы на данных с NULL, чтобы убедиться, что они реально ведут себя так, как вы ожидаете.
👉 @SQLPortal
Не пиши в SQL = NULL или != NULL
Для NULL всегда используй IS NULL / IS NOT NULL
NULL это “нет значения / неизвестно”, и обычные операторы сравнения (=, !=, <, >) с ним не работают, потому что NULL не равен, не больше и не меньше вообще ничего.
= / != сравнивают значения. А NULL это не значение, а отсутствие значения. Поэтому IS / IS NOT проверяют именно наличие или отсутствие значения.
👉 @SQLPortal
БОЛЬШОЙ SQL грех
Никогда не пихай ORDER BY внутрь CTE (или подзапроса), если там же рядом нет LIMIT.
Если LIMIT забыть, база обычно будет сортировать всю таблицу вообще без смысла.
👉 @SQLPortal
Совет по Postgres для SSD: поставь random_page_cost ниже. Значение по умолчанию 4.0 больше подходит для HDD (вращающихся дисков). На SSD можно опустить до 1.1 или 1.5, потому что случайные и последовательные чтения почти одинаковы по стоимости. Это помогает оптимизатору выбирать более удачные планы выполнения.
👉 @SQLPortal
Postgres бывает реально коварным, когда в тексте намешан разный регистр. Для поиска и индексации такого текста есть расширение citext, оно в некоторых случаях помогает. И это поле/тип тоже можно индексировать обычным b-tree.
- запросы могут находить результаты в любом регистре и в любой комбинации
- обычные text и varchar нельзя проиндексировать как регистронезависимые, а citext можно
- также помогает с UNIQUE-ограничениями на текст, когда на вход прилетает разный регистр
-- Подключаем расширение
CREATE EXTENSION IF NOT EXISTS citext;
-- Создаем таблицу с типом citext
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name CITEXT UNIQUE NOT NULL,
description TEXT
);
``` :contentReference[oaicite:0]{index=0}
::contentReference[oaicite:1]{index=1}
Неиспользуемые индексы в PostgreSQL: риски, обнаружение и безопасное удаление
Индексы нужны, чтобы ускорять доступ к данным. Они позволяют PostgreSQL избегать full scan по таблице и заметно сокращают время выполнения запросов в read-heavy нагрузках.
По реальному прод-опыту мы видели, что грамотно спроектированные, точечные индексы могут ускорять запросы в 5 раз и больше, особенно на больших транзакционных таблицах.
Но индексы не бесплатны.
В этом посте разберем, какие проблемы могут создавать неиспользуемые индексы и как удалять их в проде безопасно, с планом отката.
1. Почему большие неиспользуемые индексы со временем становятся проблемой
Со временем такие индексы могут незаметно ухудшать производительность базы. Вот типовые эффекты, которые встречаются в проде.
1.1. Медленнее INSERT, UPDATE и DELETE
Каждая запись должна обновить все индексы таблицы, включая те, которыми запросы никогда не пользуются.
1.2. Больше нагрузки на VACUUM и autovacuum
В индексах так же копятся мертвые tuple, как и в таблицах. Их тоже нужно вакуумить, из-за чего растут I/O и время работы vacuum.
1.3. Дольше окна обслуживания
Операции вроде VACUUM и REINDEX выполняются тем дольше, чем больше индексов и чем они крупнее.
1.4. Пустая трата диска и “засорение” кеша
Большие неиспользуемые индексы едят место на диске и могут вытеснять полезные данные из shared_buffers, снижая эффективность кеша.
Поэтому периодически находить и аккуратно удалять неиспользуемые индексы в проде реально полезно, но только через контролируемый и хорошо проверенный процесс.
2. Как безопасно удалить неиспользуемые индексы в PostgreSQL
Ниже чеклист в стиле “можно в прод”, который стоит пройти перед удалением любого индекса.
2.1. Проверь, когда в последний раз сбрасывали статистику
Если статистику недавно reset’нули, индекс может выглядеть “неиспользуемым”, хотя он нужен под нагрузкой.
SELECT
datname,
stats_reset
FROM pg_stat_database
WHERE datname = current_database();
stats_reset (или NULL, то есть статистику никогда не сбрасывали) дает больше доверия метрикам использования индексов.SELECT
i.relname AS index_name,
c.conname AS constraint_name,
c.contype AS constraint_type,
c.conrelid::regclass AS table_name
FROM pg_constraint c
JOIN pg_class i ON i.oid = c.conindid
WHERE i.relname = '<IDX_NAME>';
SELECT
s.indexrelname AS index_name,
s.relname AS table_name,
s.idx_scan,
s.idx_tup_read,
s.idx_tup_fetch
FROM pg_stat_user_indexes s
WHERE s.indexrelname = '<IDX_NAME>';
0.SELECT pg_get_indexdef('<IDX_NAME>'::regclass) AS create_index_sql;DROP INDEX CONCURRENTLY не блокирует чтение и запись по таблице, поэтому подходит для прода.DROP INDEX CONCURRENTLY <IDX_NAME>;
Индексация без простоя с CONCURRENTLY
Создание индекса может залочить таблицу, из-за чего она фактически становится недоступной и приложение/пользователи начинают получать ошибки.
Если добавить CONCURRENTLY, Postgres соберет индекс конкурентно, в фоне. Это займет чуть больше времени, зато пользователи вообще не заметят, что индекс строится.
👉 @SQLPortal
SQL tip:
Обновление значений в таблице через коррелированный подзапрос.
👉 @SQLPortal