14500
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
Пользователи часто хотят получать отчёты из базы данных, адаптированные под их нужды; запросы на такую кастомизацию могут со временем превратить систему в нечто вроде
SQL-инъекции как сервис
Ещё один отличный тренажёр для изучения SQL — SQL Teaching
Интерактивный сайт, где вы учитесь писать SQL-запросы в игровой форме. Всё происходит прямо в браузере: пишете — сразу видите результат
Бесплатно и опенсорс. Забираем здесь 😊
👉 @SQLPortal
Нашёл штуку, которая делает PostgreSQL понятным даже для чайников 🙂
Это визуальная надстройка над Postgres, которая превращает его в нечто среднее между Airtable и Excel. Таблицы, связи, формы — всё редактируется прямо в браузере, без SQL.
👉 @SQLPortal
Узнайте, как и зачем использовать транзакции в Postgres, в этои интерактивном браузерном туториале:
https://www.crunchydata.com/developers/playground/transactions
👉 @SQLPortal
Теперь в Oracle Database появился сервер Model Context Protocol (MCP), доступный через Oracle SQLcl.
С его помощью ты можешь безопасно подключаться к базе данных и взаимодействовать с ней через любой клиент с поддержкой MCP — включая твоего любимого AI-ассистента и LLM.
> подробнее
👉 @SQLPortal
Практикуй SQL уже сегодня
Используя таблицы на изображении в качестве ориентира, что должен вернуть следующий запрос?
SELECT name
FROM students
WHERE student_id NOT IN (
SELECT student_id
FROM grades
);
Работа с Foreign Data Wrappers в Postgres и производительность
⚡️Используй CTE , чтобы передавать более информативные запросы и избегать полноскановых операций на удалённом источнике
⚡️Аналогично — применяй подзапросы, чтобы ограничить объём передаваемых данных
⚡️Если на стороне foreign-сервера много данных — по возможности кэшируй
⚡️Материализованные представления (materialized views) хорошо подходят для создания локальных копий данных
⚡️Кэширующая таблица с MERGE может стать основой собственного мини-ETL внутри БД
Подробнее: ссылка
👉 @SQLPortal
При оптимизации SQL-запроса один из ключевых вопросов:
Какие колонки проиндексированы?
В Oracle Database это можно проверить через представления *_IND_COLUMNS
Коллега написал скрипт, который проверяет это и подтягивает другую полезную информацию для тюнинга по таблице.
https://dfitzjarrell.wordpress.com/2025/07/02/is-that-column-indexed/
👉 @SQLPortal
Вот отличное руководство по началу работы с индексами в Postgres
Если вы новичок в управлении базами данных, я уверен, что вы узнаете что-то новое, просто взглянув на него.
> cсылка
👉 @SQLPortal
Этот PDF охватывает SQL от начального до продвинутого уровня. В нём 101 страница — всё, что нужно для подготовки и повторения SQL перед собеседованиями на позиции, связанные с данными.
Дополнительно прорешай задачи на LeetCode — этого будет достаточно.
> cсылка на PDF
👉 @SQLPortal
Ускорение производительности Postgres: HOT-обновления и Fill Factor
🔸HOT-обновления происходят, когда обновлённая строка помещается на ту же страницу, что и старая версия строки.
🔸HOT-обновления улучшают производительность, так как снижают нагрузку на I/O, объём WAL, количество обновлений индексов и другие ресурсы.
🔸Обратите внимание на стратегию индексирования, если хотите чаще использовать HOT-обновления. Обычно они невозможны, если обновляемое поле проиндексировано — за исключением случая с индексами BRIN. Поддержка HOT-обновлений с BRIN — это новое улучшение в Postgres 16.
🔸Чтобы повысить вероятность HOT-обновлений, можно уменьшить fill factor страницы. Но с этим могут быть компромиссы по производительности, так что лучше действовать по принципу «измеряй и смотри».
https://crunchydata.com/blog/postgres-performance-boost-hot-updates-and-fill-factor
👉 @SQLPortal
При администрировании баз данных приходится либо использовать громоздкие графические интерфейсы, либо вручную писать длинные SQL-запросы в терминале — процесс зачастую утомительный и неудобный.
Если тебе ближе терминальный способ работы, то стоит обратить внимание на LazySQL — опенсорс-проект, который переносит удобное управление базами данных прямо в терминал.
Проект вдохновлён знаменитым Lazygit и предлагает интуитивный TUI-интерфейс для работы с MySQL, PostgreSQL, SQLite и другими СУБД.
👉 @SQLPortal
Как спроектировать тестовый набор, который будет и быстрым, и надёжным
- Не используйте моки для баз данных. Небольшой прирост скорости не стоит резкого снижения достоверности тестов.
- Упростите работу с базой данных в тестах — используйте фреймворк фикстур, который автоматизирует большую часть рутины. Он может быть и самописным, главное — удобство использования и строгие соглашения.
- Компенсируйте возможную потерю в скорости — применяйте техники вроде тестовых транзакций, чтобы максимально распараллелить выполнение. СУБД отлично справляются с такой нагрузкой.
- Отказавшись от моков, используйте преимущества реляционных СУБД — строгие схемы, типы данных, ограничения (check constraints), внешние ключи. Каждое такое ограничение, срабатывающее во время тестов — это на один баг меньше в продакшене.
> подробнее
👉 @SQLPortal
ETL мёртв. Да здравствует ELT.
Помните, когда ETL означало написание скриптов на Python, планирование cron-задач и трансформацию данных до того, как они попадали в базу данных?
Теперь это в прошлом.
В современном дата-стеке происходит следующее:
🔸Extract: Извлекаем сырые данные из API, баз данных, SaaS-инструментов
🔸Load: Загружаем их прямо в хранилище (Snowflake, BigQuery, Redshift)
🔸Transform: Используем SQL или dbt для очистки, объединения и моделирования данных внутри хранилища
Этот сдвиг — не просто семантический, а архитектурный:
Больше нет хрупкой логики трансформации за пределами хранилища
Полный контроль версий и тестирование с dbt
Легче отслеживание, наблюдаемость и повторное использование
Огромная масштабируемость благодаря вычислениям в хранилище
Будь то Fivetran, Airbyte, Stitch или кастомная загрузка данных — pipeline заканчивается на ELT.
Если вы до сих пор выполняете сложные манипуляции с данными до того, как они попадут в хранилище, скорее всего, вы:
> Медленнее
> Менее масштабируемы
> Сложнее для отладки
Теперь хранилище — это ваш движок трансформации.
ETL существовал в течение последнего десятилетия. ELT — это для этого.
👉 @SQLPortal
Топ опенсорс-инструментов, которые должен знать каждый дата-инженер в 2025
В мире data engineering именно open-source инструменты — настоящие MVP.
Опенсорс кардинально изменил жизнь дата-инженеров: будь то Hadoop, Spark, Airflow и многие другие 🥲
Вот ключевые open-source проекты, которые должен знать каждый дата-инженер:
> Apache Airflow — построение и оркестрация data pipeline'ов
> Apache Spark — распределённый движок обработки данных, перевернувший игру
> Apache Kafka — стриминг событий в реальном времени на масштаб
> dbt (Data Build Tool) — трансформация данных в хранилище с помощью SQL + контроля версий
> Apache Hudi / Delta Lake / Iceberg — современные форматы для data lake'ов с поддержкой ACID, upsert'ов и time travel
> Apache Superset — легковесный BI-инструмент для быстрой визуализации данных
> DuckDB — быстрая встроенная OLAP-база данных (подходит для аналитических нагрузок)
> Airbyte / Singer / Meltano — open-source коннекторы для ELT, легко расширяемые
> Prefect / Dagster — современные альтернативы Airflow для оркестрации
> Trino (PrestoSQL) — распределённый SQL-движок для интерактивной аналитики
> Redpanda — Kafka-совместимый стриминг-движок с низкой задержкой
✅ Стоит также упомянуть:
Polars — ультрабыстрая библиотека для работы с датафреймами
OpenMetadata — next-gen управление метаданными
Metaflow — инструмент от Netflix, отлично подходит для ML-пайплайнов
Если ты уже работаешь в data engineering, только собираешься войти в эту сферу или строишь современную data-платформу — освоение этих инструментов даст серьёзное преимущество в 2025 и дальше.
👉 @SQLPortal
Когда стоит сбрасывать pg_stat_statements?
🔸В начале нового периода мониторинга
Если ты анализируешь производительность на ежедневной, недельной или месячной основе, имеет смысл сбрасывать pg_stat_statements в начале каждого периода. Это позволяет собирать метрики с «чистого листа» и получать более точную аналитику за конкретный интервал.
🔸После внесения значимых изменений в запросы
Если в приложении были переработаны запросы, оптимизирована логика или изменены индексы, сброс pg_stat_statements помогает точнее отследить влияние этих изменений. Это также исключает устаревшие запросы, которые могут исказить результаты.
🔸Во время бенчмарков и тестирования производительности
Если ты запускаешь тесты производительности или сравниваешь эффективность разных вариантов запросов, сброс pg_stat_statements гарантирует, что собираемая статистика будет относиться только к выполненным в рамках теста запросам.
🔸После серьёзных операций по обслуживанию базы
После таких операций, как pg_repack, переиндексация или изменение структуры таблиц, сброс pg_stat_statements позволяет объективно оценить, как они повлияли на производительность.
👉 @SQLPortal
В обновляемые представления можно вставлять строки
...но при этом возможно добавить данные, противоречащие условию WHERE
Из-за этого кажется, что значения просто исчезают!
Чтобы этого избежать, добавь:
CREATE VIEW ... AS SELECT ... WHERE ...
WITH CHECK OPTION
WHERE
Уровни изоляции транзакций в Postgres
Уровни изоляции транзакций в Postgres определяют, как база данных обрабатывает одновременное выполнение нескольких транзакций.
В Postgres доступны четыре стандартных SQL-уровня изоляции:
🔸Read Uncommitted
Read Uncommitted позволяет запросам видеть изменения, сделанные в транзакциях, которые ещё не были зафиксированы (committed).
Хотя этот уровень присутствует в синтаксисе Postgres ради соответствия SQL-стандарту, фактически он не реализован.
В Postgres нельзя выполнить грязное чтение (dirty read).
🔸Read Committed
Это уровень изоляции по умолчанию в Postgres.
Транзакция видит только те данные, которые были зафиксированы до её начала.
Это стандарт для веб-приложений, CMS и типичных OLTP-нагрузок (Online Transaction Processing), где допустимы незначительные расхождения
(например, когда данные изменяются между двумя SELECT-запросами внутри одной и той же транзакции).
Пример:
Пользователь загружает дашборд — запросы, наполняющие разные виджеты на странице, не обязаны отображать строго одну и ту же "точку во времени".
Этот уровень даёт хороший баланс между производительностью и согласованностью данных в большинстве сценариев.
🔸Repeatable Read
Этот уровень гарантирует, что повторный запуск одного и того же SELECT-запроса в рамках одной транзакции всегда вернёт один и тот же результат.
Он исключает *неповторяемые чтения* (non-repeatable reads).
Подходит для многошаговых транзакций, где важна согласованность в рамках всей транзакции.
Пример:
Генерация финансового отчета — сначала запрашиваются балансы счетов, затем идут запросы по транзакционной истории тех же счетов.
Все данные должны быть согласованы с тем состоянием, в котором считались балансы, даже если в это время в системе происходят другие изменения.
🔸Serializable
Самый строгий и безопасный уровень изоляции.
Обеспечивает поведение транзакций как если бы они выполнялись последовательно, а не параллельно.
Подходит для критичных операций, где целостность данных важнее всего и необходимо исключить любые race conditions.
Примеры:
- Системы бронирования (например, исключение двойного бронирования места в самолете или гостиничного номера)
- Сложные финансовые операции, где перевод средств между счетами требует согласованности всех промежуточных шагов
Приложение должно быть готово к повторной попытке транзакций, завершившихся ошибкой сериализации.
Как задать уровень изоляции
Уровень изоляции можно задать на следующих уровнях:
1. По умолчанию для всей базы данных
2. Для отдельной сессии
3. Для конкретной транзакции (в момент BEGIN)
Во многих приложениях принято устанавливать дефолтный уровень изоляции для базы,
а для отдельных операций (например, финансовых транзакций) — использовать повышенный уровень изоляции.
👉 @SQLPortal
Сгенерированные столбцы в Postgres — это специальные столбцы в таблице, которые автоматически рассчитывают свои значения на основе других столбцов той же строки.
Напрямую вставлять данные в такие столбцы через INSERT или изменять через UPDATE нельзя — Postgres сам отвечает за генерацию значений. Они задаются с помощью синтаксиса GENERATED ALWAYS AS (...). Ключевое слово STORED означает, что значение вычисляется при записи строки и сохраняется на диск — так же, как и обычные столбцы.
А зачем вообще нужны сгенерированные столбцы?
➜ Удобная агрегация данных: можно автоматически формировать, например, full_name, объединяя first_name и last_name. Больше не нужно каждый раз писать конкатенацию в запросах!
full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
Задача:
При работе с базой данных ритейл-клиента Accenture ты замечаешь, что в столбце category таблицы products есть NULL-значения.
Твоя задача — написать запрос, который вернёт таблицу products с заполненными значениями category.
Предполагается, что у первого продукта каждой категории всегда указана категория.
Таблица products:
🔸product_id
🔸category
🔸name
Объяснение:
Используем рекурсивный CTE с именем filled — это удобно, когда значение каждой строки зависит от предыдущей.
-- Базовый случай:
-- выбираем первую строку (product_id = 1), у которой уже есть категория (например, 'Shoes')
-- это стартовая точка рекурсии
-- Рекурсивный шаг:
-- для каждой следующей строки (product_id + 1):
-- если p.category НЕ NULL → берём её
-- иначе → подхватываем f.category из предыдущей строки
-- Всё это реализуется через COALESCE() — он возвращает первое непустое значение
В рекурсивном CTE базовый случай исполняется один раз,
а рекурсивная часть повторяется до тех пор, пока не перестают возвращаться новые строки.
Q) Почему ты написал ON p.product_id = f.product_id + 1?
A) Я хотел симулировать поведение forward fill — проход по строкам в порядке ID, строка за строкой.
👉 @SQLPortal
SQLZap — это прям находка
sqlzap.com — это онлайн-платформа для тренировок SQL-запросов в браузере. Что-то вроде интерактивного тренажёра
Идеально чтобы натаскать руку — как Leetcode, только для SQL
👉 @SQLPortal
psql совет: \h
Знали ли вы, что в psql есть большая справка по SQL-командам?\h + нужная команда покажет подробности по синтаксису и ссылку на документацию.
Пример:
postgres=# \h CREATE USER
Команда: CREATE USER
Описание: создать новую роль базы данных
Синтаксис:
CREATE USER имя [ [ WITH ] параметр [ ... ] ]
где параметр может быть:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT макс_подключений
| [ ENCRYPTED ] PASSWORD 'пароль' | PASSWORD NULL
| VALID UNTIL 'временная_метка'
| IN ROLE имя_роли [, ...]
| IN GROUP имя_роли [, ...]
| ROLE имя_роли [, ...]
| ADMIN имя_роли [, ...]
| USER имя_роли [, ...]
| SYSID uid
Преобразуйте GeoJSON в объекты Oracle Spatial и обратно с помощью:
SDO_UTIL.FROM_GEOJSON
SDO_UTIL.TO_GEOJSON
Часто используете JSON в Postgres?
Работать с JSON сложно — особенно когда дело касается индексации.
—> GIN-индексы отлично подходят для ускорения общих поисков или поиска по множеству ключей.
Однако часто возникают запросы, где нужно регулярно фильтровать или сортировать данные внутри JSON по конкретным значениям.
—> В таких случаях можно использовать B-tree индексы по отдельным значениям из массива, применяя механизм индексов на выражения в Postgres.
Обратите внимание: индекс на выражение должен точно соответствовать тексту запроса — это особенно полезно для повторяющихся запросов.
CREATE INDEX idx_orders_total ON orders (((details->>'order_total')::numeric));
Используешь Postgres \copy, но хочешь видеть индикатор прогресса?
Существует pg_stat_progress_copy — но это представление (view), которое нужно опрашивать вручную.
Если тебе нужен прогресс прямо в терминале, пользователи macOS и Linux могут использовать утилиту pv (pipe viewer). Ее нужно установить отдельно.
С помощью \copy FROM PROGRAM можно вызвать pv прямо из команды \copy:
\copy obrc(station_name, measurement) FROM PROGRAM 'pv "/1br/measurements.txt"' WITH (FORMAT CSV, DELIMITER ';');
Разница между двумя датами в Oracle Database — это количество дней между ними.
Чтобы увидеть разбивку по единицам времени, используйте выражения с интервалами:(d1 - d2) day to second — дни, часы, минуты, секунды(d1 - d2) year to month — годы, месяцы
👉 @SQLPortal
Доступ к внутренним и внешним данным с помощью гибридных партиционированных таблиц в Oracle Database 19c
Внутренние партиции (internal partitions) используют обычные строки, хранящиеся непосредственно в базе данных.
Внешние партиции (external partitions) ссылаются на файлы, расположенные на сервере базы данных или в объектных хранилищах (object stores). Они поддерживают широкий спектр форматов, включая:
🔸CSV
🔸Parquet
🔸ORC
Гибридный подход позволяет объединить локальные и внешние данные в одной партиционированной таблице, упрощая доступ, обработку и аналитические операции без необходимости предварительной загрузки внешних данных в базу.
👉 @SQLPortal
Нужно узнать размер таблицы в Postgres?SELECT pg_relation_size('table_name'); — покажет размер основного файла таблицы (HEAP).
Но pg_relation_size() учитывает только саму таблицу, без индексов, TOAST-таблиц, карт свободного пространства и других вспомогательных структур.
Если нужен полный размер всего, что связано с таблицей, используй:
SELECT pg_total_relation_size('table_name');pg_total_relation_size() для более точной оценки занимаемого места таблицей на диске.
Для продакшн-бэкапов PostgreSQL мы не рекомендуем использовать pg_dump.
Однако pg_dump и его вспомогательная утилита pg_dumpall всё же могут быть очень полезны для ряда задач.
Быстрое напоминание о pg_dump и pg_dumpall
➠ pg_dump
Создаёт дамп одной базы данных в рамках кластера Postgres.
- Может быть дамп только схемы (без данных)
- Поддерживает дамп отдельной таблицы, если нужно сохранить что-то конкретное
- Не включает роли и другие глобальные объекты
- Гранты (grants) на таблицы будут включены в дамп
➠ pg_dumpall
Создаёт дамп всех баз данных, данных и ролей во всём инстансе Postgres.
- Поддерживает режим дампа только схемы
- Поддерживает флаг -g для выгрузки всех глобальных объектов — пользователей и ролей
👉 @SQLPortal
В PostgreSQL больше нет разделения на пользователей и группы — теперь есть только роли.
Индивидуальный пользователь — это просто роль с правом входа (LOGIN), которой можно назначить одну или несколько других ролей — таким образом и создаются группы пользователей.
Как создать нового пользователя в Postgres:
1️⃣Создать роль с правом входа (пользователя):
CREATE ROLE simon;
\password simon
21654641seeswf!2@
21654641seeswf!2@
\password — это специальная команда Postgres, которая устанавливает пароль в виде уже хэшированного значения.CREATE ROLE accounting_ro NOLOGIN;
GRANT CONNECT ON DATABASE finance TO accounting_ro;
GRANT USAGE ON SCHEMA accounting TO accounting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO accounting_ro;
GRANT accounting_ro TO simon;