В PostgreSQL больше нет разделения на пользователей и группы — теперь есть только роли.

Индивидуальный пользователь — это просто роль с правом входа (LOGIN), которой можно назначить одну или несколько других ролей — таким образом и создаются группы пользователей.

Как создать нового пользователя в Postgres:

1️⃣Создать роль с правом входа (пользователя):

CREATE ROLE simon;

2️⃣Установить пароль:

\password simon

21654641seeswf!2@
21654641seeswf!2@

\password — это специальная команда Postgres, которая устанавливает пароль в виде уже хэшированного значения.

3️⃣Чтобы создать роль-группу (без возможности входа), используйте NOLOGIN. Затем выдайте нужные права:

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;

4️⃣Привязать пользователя к роли-группе:

GRANT accounting_ro TO simon;

👉 @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

Для продакшн-бэкапов PostgreSQL мы не рекомендуем использовать pg_dump.
Однако pg_dump и его вспомогательная утилита pg_dumpall всё же могут быть очень полезны для ряда задач.

Быстрое напоминание о pg_dump и pg_dumpall

➠ pg_dump

Создаёт дамп одной базы данных в рамках кластера Postgres.

- Может быть дамп только схемы (без данных)
- Поддерживает дамп отдельной таблицы, если нужно сохранить что-то конкретное
- Не включает роли и другие глобальные объекты
- Гранты (grants) на таблицы будут включены в дамп

➠ pg_dumpall

Создаёт дамп всех баз данных, данных и ролей во всём инстансе Postgres.

- Поддерживает режим дампа только схемы
- Поддерживает флаг -g для выгрузки всех глобальных объектов — пользователей и ролей

👉 @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

Нужно узнать размер таблицы в Postgres?

SELECT pg_relation_size('table_name'); — покажет размер основного файла таблицы (HEAP).
Но pg_relation_size() учитывает только саму таблицу, без индексов, TOAST-таблиц, карт свободного пространства и других вспомогательных структур.

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

SELECT pg_total_relation_size('table_name');

Он вернёт суммарный размер таблицы, включая:

> данные самой таблицы (heap),
> данные индексов,
> TOAST-данные (если есть),
> карту свободного пространства (FSM),
> карту видимости (VM).

Используй pg_total_relation_size() для более точной оценки занимаемого места таблицей на диске.

👉 @SQLPortal

Как спроектировать тестовый набор, который будет и быстрым, и надёжным

- Не используйте моки для баз данных. Небольшой прирост скорости не стоит резкого снижения достоверности тестов.

- Упростите работу с базой данных в тестах — используйте фреймворк фикстур, который автоматизирует большую часть рутины. Он может быть и самописным, главное — удобство использования и строгие соглашения.

- Компенсируйте возможную потерю в скорости — применяйте техники вроде тестовых транзакций, чтобы максимально распараллелить выполнение. СУБД отлично справляются с такой нагрузкой.

- Отказавшись от моков, используйте преимущества реляционных СУБД — строгие схемы, типы данных, ограничения (check constraints), внешние ключи. Каждое такое ограничение, срабатывающее во время тестов — это на один баг меньше в продакшене.

> подробнее

👉 @SQLPortal

Доступ к внутренним и внешним данным с помощью гибридных партиционированных таблиц в Oracle Database 19c

Внутренние партиции (internal partitions) используют обычные строки, хранящиеся непосредственно в базе данных.

Внешние партиции (external partitions) ссылаются на файлы, расположенные на сервере базы данных или в объектных хранилищах (object stores). Они поддерживают широкий спектр форматов, включая:

🔸CSV
🔸Parquet
🔸ORC

Гибридный подход позволяет объединить локальные и внешние данные в одной партиционированной таблице, упрощая доступ, обработку и аналитические операции без необходимости предварительной загрузки внешних данных в базу.

👉 @SQLPortal

При администрировании баз данных приходится либо использовать громоздкие графические интерфейсы, либо вручную писать длинные SQL-запросы в терминале — процесс зачастую утомительный и неудобный.

Если тебе ближе терминальный способ работы, то стоит обратить внимание на LazySQL — опенсорс-проект, который переносит удобное управление базами данных прямо в терминал.

Проект вдохновлён знаменитым Lazygit и предлагает интуитивный TUI-интерфейс для работы с MySQL, PostgreSQL, SQLite и другими СУБД.

👉 @SQLPortal

Разница между двумя датами в Oracle Database — это количество дней между ними.

Чтобы увидеть разбивку по единицам времени, используйте выражения с интервалами:

(d1 - d2) day to second — дни, часы, минуты, секунды
(d1 - d2) year to month — годы, месяцы

👉 @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

Используешь 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 ';');

👉 @SQLPortal

Этот PDF охватывает SQL от начального до продвинутого уровня. В нём 101 страница — всё, что нужно для подготовки и повторения SQL перед собеседованиями на позиции, связанные с данными.

Дополнительно прорешай задачи на LeetCode — этого будет достаточно.

> cсылка на PDF

👉 @SQLPortal

Часто используете JSON в Postgres?

Работать с JSON сложно — особенно когда дело касается индексации.

—> GIN-индексы отлично подходят для ускорения общих поисков или поиска по множеству ключей.

Однако часто возникают запросы, где нужно регулярно фильтровать или сортировать данные внутри JSON по конкретным значениям.

—> В таких случаях можно использовать B-tree индексы по отдельным значениям из массива, применяя механизм индексов на выражения в Postgres.

Обратите внимание: индекс на выражение должен точно соответствовать тексту запроса — это особенно полезно для повторяющихся запросов.

CREATE INDEX idx_orders_total ON orders (((details->>'order_total')::numeric));

👉 @SQLPortal

Вот отличное руководство по началу работы с индексами в Postgres

Если вы новичок в управлении базами данных, я уверен, что вы узнаете что-то новое, просто взглянув на него.

> cсылка

👉 @SQLPortal

Преобразуйте GeoJSON в объекты Oracle Spatial и обратно с помощью:

SDO_UTIL.FROM_GEOJSON
SDO_UTIL.TO_GEOJSON

Это позволяет применять пространственные функции к данным в формате JSON,

например, для вычисления расстояния между двумя точками.

👉 @SQLPortal

При оптимизации SQL-запроса один из ключевых вопросов:

Какие колонки проиндексированы?

В Oracle Database это можно проверить через представления *_IND_COLUMNS

Коллега написал скрипт, который проверяет это и подтягивает другую полезную информацию для тюнинга по таблице.

https://dfitzjarrell.wordpress.com/2025/07/02/is-that-column-indexed/

👉 @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

Документация: https://postgresql.org/docs/16/sql-createuser.html

👉 @SQLPortal

Работа с Foreign Data Wrappers в Postgres и производительность

⚡️Используй CTE , чтобы передавать более информативные запросы и избегать полноскановых операций на удалённом источнике
⚡️Аналогично — применяй подзапросы, чтобы ограничить объём передаваемых данных
⚡️Если на стороне foreign-сервера много данных — по возможности кэшируй
⚡️Материализованные представления (materialized views) хорошо подходят для создания локальных копий данных
⚡️Кэширующая таблица с MERGE может стать основой собственного мини-ETL внутри БД

Подробнее: ссылка

👉 @SQLPortal

SQLZap — это прям находка

sqlzap.com — это онлайн-платформа для тренировок SQL-запросов в браузере. Что-то вроде интерактивного тренажёра

Идеально чтобы натаскать руку — как Leetcode, только для SQL

👉 @SQLPortal

Практикуй SQL уже сегодня

Используя таблицы на изображении в качестве ориентира, что должен вернуть следующий запрос?

SELECT name
FROM students
WHERE student_id NOT IN (
  SELECT student_id
  FROM grades
);

👉 @SQLPortal