Postgres MERGE это давно ожидаемая фича, которая появилась в Postgres 15. MERGE используется для реализации upsert-логики (то есть insert или update).

Многие, кто работает с Postgres, хорошо знакомы с конструкцией INSERT ... ON CONFLICT. Но INSERT ... ON CONFLICT не подходит для сценариев с условной логикой, где нужно использовать WHERE, чтобы определить, что именно обновлять.

Читать: тык

👉 @SQLPortal

Ой... CPU забито на 100%... Что случилось? Это аналитик написал рекурсивный CTE и забыл лимит поставить? Жесть… что-то крутится, и это нужно срочно остановить. Давайте зайдём в Postgres, найдём и убьём runaway-транзакцию.

Для начала посмотрим на системную таблицу активности Postgres — pg_stat_activity. В ней отображаются все работающие backend-процессы (каждый со своим pid):

SELECT * FROM pg_stat_activity;

Важно: при запросе к pg_stat_activity нужно отфильтровать свою собственную сессию. Её PID можно получить через pg_backend_pid().
Так что запрос, который покажет все активные транзакции, кроме вашей:

SELECT * FROM pg_stat_activity WHERE pid <> pg_backend_pid();

Нашли "виновника"? Окей, теперь можно прибить процесс:

SELECT pg_terminate_backend(pid);

Но имей в виду: если ты вызываешь pg_terminate_backend, это может быть транзакция с незакоммиченными изменениями.
В таком случае произойдёт откат (rollback), и в зависимости от конфигурации системы это может привести к потере данных.

После того как ты спас базу от перегрева, застрахуйся от runaway-запросов в будущем, выставив statement_timeout

👉 @SQLPortal

SQL-вопрос на собеседовании для Data Engineer'ов (сможешь решить?)

Social Media PII

Социальная медиа-компания ведёт таблицу с информацией о пользователях, включая email-адреса и номера телефонов. Твоя задача написать функцию, которая обрабатывает эти данные для целей приватности и аналитики.

Твоя задача

—> Напиши функцию, которая выполняет следующие преобразования над входным DataFrame:

✅Извлечение домена из email: Из поля email извлеки доменное имя (текст после символа @).
✅Анонимизация номера телефона: Замаскируй первые 6 цифр номера телефона с помощью символов *, оставив только последние 4 цифры.

Результат должен включать следующие колонки:
anon_phone: Анонимизированный номер телефона (например, ******1234)
email_domain: Извлечённый домен (например, example.com)
user_id: Исходный user_id

Отсортируй результат по номеру телефона по возрастанию (до маскировки)

Схема входных данных

Имя DataFrame: input_df

| Название | Тип |
|------------------|------------|
| user_id | String |
| email | String |
| phone | Integer |

Схема выходных данных

| Название колонки | Тип |
|---------------------------|------------|
| anon_phone | String |
| email_domain | String |
| user_id | Integer |

Реши задачу здесь

👉 @SQLPortal

Data-engineer-handbook

Это репозиторий сo ссылками на всё, что вы когда-либо захотите изучить по data engineering

Github : https://github.com/DataExpert-io/data-engineer-handbook

😳😳😳

👉 @SQLPortal

Новичкам в Postgres — разберитесь с основами создания таблиц: первичные ключи, внешние ключи и типы данных, всё это в oбучающем гайде

-- https://www.crunchydata.com/developers/playground/postgres-tutorial-create-tables

👉 @SQLPortal

Бомбический инструмент для работы с SQL-базами данных

Исследуй, работай с таблицами и получай аналитику прямо из них.

Поддерживает MySQL, PostgreSQL, SQLite и другие.

Бесплатный и с открытым исходным кодом:

→ http://github.com/frectonz/sql-studio

👉 @SQLPortal

Совет по работе с JSON в Postgres: используйте jsonb_pretty для форматирования JSON-поля в читаемый и структурированный вид с отступами.

SELECT
    id,
    username,
    jsonb_pretty(profile) AS pretty_profile
FROM
    users;

Результат запроса будет выглядеть так:

 id | username | pretty_profile
----+----------+------------------------------------------------
  1 | jdoe     | {
    |          |     "age": 30,
    |          |     "name": "John Doe",
    |          |     "address": {
    |          |         "city": "Anytown",
    |          |         "street": "123 Main St"
    |          |     },
    |          |     "courses": [
    |          |         {
    |          |             "title": "History",
    |          |             "credits": 3
    |          |         },
    |          |         {
    |          |             "title": "Math",
    |          |             "credits": 4
    |          |         }
    |          |     ],
    |          |     "isStudent": false
    |          | }

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

👉 @SQLPortal

Когда-нибудь случайно удалял не те данные?

и закоммитил?

в проде? 😱

С помощью Oracle Flashback Query можно посмотреть, как данные выглядели в прошлом:

SELECT ... FROM ... AS OF TIMESTAMP <время>

Это позволяет найти строки, которых больше нет в таблице, и восстановить их обратно.

👉 @SQLPortal

ХОСТИНГИ ДЛЯ БАЗ ДАННЫХ — БЕСПЛАТНО
Актуальные варианты на сегодня ↓

🔸NEON TECH
PostgreSQL, 500 МБ, 190 часов вычислений
→ neon.tech

🔸MONGODB ATLAS
MongoDB, 512 МБ, автоматические бэкапы
→ mongodb.com

🔸TURSO
SQLite, 5 ГБ, 1 миллиард чтений
→ turso.com

🔸XATA
PostgreSQL, 15 ГБ, неограниченный трафик
→ lite.xata.io

🔸SUPABASE
PostgreSQL, 500 МБ, 5 ГБ трафика
→ supabase.com

🔸COCKROACHDB
10 ГБ хранилища, 50 млн запросов
→ cockroachlabs.com

🔸KOYEB
PostgreSQL, 1 ГБ, 5 часов вычислений
→ koyeb.com

🔸FREEDB TECH
MySQL, 25 МБ, максимум 200 подключений
→ freedb.tech

Если знаешь ещё варианты — делись

👉 @SQLPortal

Чтобы сгруппировать данные по году/дню/часу/... в Oracle SQL, используйте TRUNC:

TRUNC(<dt>, '<fmt>')

А как быть с кратными интервалами — например, 4 часа или 2 дня?

Раньше это было непросто.

Но Laurent Schneider показал, что начиная с версии 23.7 это стало проще с помощью:

TIME_BUCKET(<dt>, <interval>, <start_date>)

https://laurentschneider.com/wordpress/2025/03/time_bucket-group-by-time-period.html

👉 @SQLPortal

Поиск по эмбеддингам в YDB

Векторный поиск в СУБД Яндекса позволяет искать данные (текст, изображения, аудио, видео) по смыслу, а не по точному совпадению.

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

Хабр: https://habr.com/ru/companies/yandex/articles/926724/

👉 @SQLPortal

Кешируй результаты PL/SQL-функций, пометив их как:

CREATE FUNCTION ... RETURN ... RESULT_CACHE

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

Killianlynchh показывает, как это использовать и мониторить — ccылка 👍

👉 @SQLPortal

Мы все когда-то пытались использовать агрегатную функцию в WHERE

SELECT department, SUM(salary)
FROM employees
WHERE SUM(salary) > 100000 
GROUP BY department;

Но SQL так не работает 😉

Для фильтрации по агрегатным значениям используется HAVING:

SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

В чём разница между WHERE, HAVING и FILTER?

WHERE фильтрует строки до группировки и агрегации:

SELECT department, SUM(salary)
FROM employees
WHERE hire_date >= DATE '2020-01-01'
GROUP BY department;

HAVING фильтрует после агрегации, работает в связке с GROUP BY (или хотя бы с агрегатным контекстом).
С помощью HAVING можно применять условия к агрегатам, таким как SUM, AVG и т.д.:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Этот запрос вернёт только тех клиентов, у которых больше 5 заказов.

FILTER применяется к конкретной агрегатной функции, не влияя на остальные данные строки или группы.
Полезно, когда нужно агрегировать только определённые значения, но при этом сохранить общую группировку.

Пример, который не сработает:

SELECT
  department,
  SUM(salary) FILTER (WHERE SUM(salary) > 100000) AS total_salary
FROM employees
GROUP BY department;

Нельзя вкладывать агрегаты внутрь FILTER.
FILTER применяется к строкам до агрегации, а не к агрегированным результатам после.

Правильный способ — использовать HAVING для фильтрации агрегатов, а FILTER — для условий внутри агрегатной функции:

SELECT
  department,
  SUM(salary) AS total_salary,
  SUM(salary) FILTER (WHERE employment_type = 'full-time') AS full_time_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

👉 @SQLPortal

Как масштабировать базы данных?

Партиционирование базы данных — это процесс разделения большой таблицы или базы на меньшие, более управляемые части, называемые партициями.

Это позволяет выполнять запросы только к нужным фрагментам данных, ускоряя загрузку.

В целом, существует два основных типа партиционирования:

1. Горизонтальное партиционирование (шардинг)

Таблицы разбиваются по строкам: каждая партиция имеет одинаковую схему, но содержит разные записи.

Это особенно полезно для multi-tenant приложений, где данные можно разделить по пользователям/клиентам,
либо когда объём данных слишком велик для одной базы данных.

Варианты шардинга:

- Шардинг по ключу (hash-based)

Шард определяется с помощью хеш-функции, применяемой к определённому ключевому столбцу записи.
Хеш-функция равномерно распределяет данные по шардам.

✅Преимущество — сбалансированная нагрузка и объём данных
Недостатки — сложные кросс-шардовые запросы и трудности при переразбиении (re-sharding)

- Шардинг по диапазонам (range-based)
Данные распределяются по диапазонам ключей.

Например: customer_id от 1 до 1000 — в одном шарде, от 1001 до 2000 — в другом.

✅Удобен для запросов с операциями над диапазонами

Может привести к перегрузке отдельных шардов (hotspots), если распределение неравномерное

- Шардинг по справочнику (directory-based)
Используется таблица соответствий (lookup table), которая мапит ключи на конкретные шарды.

✅Гибкость: легко добавлять/удалять шарды и делать переразбиение

> При шардинге все экземпляры БД должны быть структурно идентичны, а данные — равномерно распределены.

2. Вертикальное партиционирование

Таблицы разбиваются по столбцам: часто используемые колонки отделяются от редко используемых.
Это повышает эффективность доступа и кеширования.

Например, таблицы можно разнести по разным базам данных.

Почему шардинг — это сложно?

Шардинг требует значительных усилий: нужно перераспределять данные, адаптировать запросы,
обрабатывать пересекающиеся данные.

Это долго, дорого и усложняет поддержку системы.

Что делать, когда БД начинает тормозить?

1. Вертикальное масштабирование — добавить ресурсов на сервер БД (CPU, память и т.д.)
2. Репликация — создать реплику для чтения, чтобы разгрузить основной экземпляр

✅Репликация эффективна только при наличии кеширования

Когда применяют партиционирование?

- Горизонтальное партиционирование — когда нужно масштабировать большие таблицы
и повысить производительность для определённых типов запросов

- Вертикальное партиционирование — если в таблицах много колонок, но не все используются одновременно

👉 @SQLPortal

В 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