Миграция PostgreSQL объёмом 1 ТБ из AWS в Azure с помощью pgcopydb

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

В Meltwater мы недавно выполнили миграцию базы данных PostgreSQL объёмом 1 ТБ из AWS в Azure Flexible PostgreSQL в рамках более широкой задачи по консолидации инфраструктуры. Наша цель заключалась в том, чтобы безопасно перенести данные, минимизировав операционные риски и время простоя.

Для выполнения миграции мы использовали pgcopydb — open-source инструмент для миграции PostgreSQL, который автоматизирует многие сложные шаги, необходимые для копирования схемы, данных и индексов между инстансами PostgreSQL.

В этом посте мы рассмотрим:

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

👉 @SQLPortal

Некоторые из наших любимых возможностей Postgres используют логику подзапросов, поэтому мы составили небольшую матрицу по этим инструментам: когда их применять и простые примеры SQL.

Мы понимаем, что, скорее всего, теперь вы будете генерировать SQL с помощью Claude (или другой LLM), но вам как минимум стоит понимать, что именно запрашивать.

Матрица подзапросов в Postgres

✔️ Что: subselect (подзапрос)

Когда: select внутри select
Пример:

SELECT
  sum(qty) AS total_qty, sku
FROM product_orders
WHERE
  sku IN (
    SELECT sku FROM products WHERE sale_price <= price * .75
  )
GROUP BY sku;

✔️Что: CTE

Когда: подзапросы с именованными частями
Пример:

WITH huge_savings AS (
  SELECT sku
  FROM products
  WHERE sale_price <= price * .75
)
SELECT sum(qty) AS total_qty, sku
FROM product_orders
JOIN huge_savings USING (sku)
GROUP BY sku;

✔️ Что: materialized view (материализованное представление)

Когда: сохранённый запрос в виде таблицы
Пример:

CREATE MATERIALIZED VIEW recent_product_sales AS
SELECT p.sku, SUM(po.qty) AS total_quantity
FROM products p
JOIN product_orders po ON p.sku = po.sku
JOIN orders o ON po.order_id = o.order_id
WHERE o.status = 'Shipped'
GROUP BY p.sku
ORDER BY 2 DESC;

✔️ Что: window functions (оконные функции)

Когда: агрегации по подмножествам данных
Пример:

SELECT
  sku,
  SUM(qty) OVER (PARTITION BY sku)
FROM product_orders
LIMIT 10;

✔️ Что: lateral join

Когда: коррелированный подзапрос
Пример:

SELECT accounts.id, last_purchase.*
FROM accounts
INNER JOIN LATERAL (
  SELECT *
  FROM purchases
  WHERE account_id = accounts.id
  ORDER BY created_at DESC
  LIMIT 1
) AS last_purchase ON true;

👉 @SQLPortal

На официальной вики PostgreSQL опубликована статья “Don’t Do This”, где собраны типичные ошибки при работе с базой данных и объясняется, почему их стоит избегать

👉 @SQLPortal

Бесплатный курс по SQL от Baraa Khatib Salkini, известного как DataWithBaraa. Курс охватывает всё от основ до сложных запросов и реальных проектов. 🤑

В GitHub-репозитории есть datasets/ с реальными данными из ERP и CRM, scripts/ с готовыми SQL-скриптами для практики и docs/ с документацией и материалами курса.

Ссылка на курс

👉 @SQLPortal

Сокращение в Postgres: USING можно использовать вместо JOIN ... ON a.id = b.id

SELECT DISTINCT
    t.amount AS transaction_amount
FROM
    accounts a
JOIN
    transactions t USING (account_id) 
WHERE
    a.account_id = 1;

👉 @SQLPortal

Перестаньте гадать, какие запросы тормозят вашу БД. В этом разборе разработчик показывает, как с помощью pg_stat_statements определить, какие запросы потребляют больше всего суммарного CPU-времени — и как отличить действительно медленный запрос от того, который просто слишком часто вызывается.

{ автор: Labeeb Ahmad }

👉 @SQLPortal

5 мифов о SQL. Что часто неправильно понимают новички

SQL — это мощный инструмент, но некоторые мифы о нем могут сбить с толку новичков.

Давайте разберем пять распространенных заблуждений и развеем их: 👇

Миф 1: SQL — это только для извлечения данных.

✦ Реальность: Нет, это не так! SQL также может создавать, изменять и управлять базами данных, контролировать доступ и поддерживать консистентность данных.

✦ Как исправить: Изучите все возможности SQL, такие как DDL (для проектирования баз данных), DCL (для управления доступом) и TCL (для транзакций). SQL — это не только SELECT!

Миф 2: Использование SELECT * — это нормально.

✦ Реальность: Это может быть легко, но неэффективно. Извлечение всех колонок расходует память и замедляет производительность.

✦ Как исправить: Выбирайте только те колонки, которые вам действительно нужны. Это быстрее и чище. Не очень хорошо - SELECT * FROM employees; Лучше - SELECT employee_id, name, department FROM employees;

Миф 3: SQL не может обрабатывать сложный анализ данных.

✦ Реальность: SQL может делать гораздо больше, чем простые запросы! С такими концепциями, как оконные функции и CTE, вы можете обрабатывать действительно сложный анализ данных.

✦ Как исправить: Изучите продвинутые возможности SQL, такие как оконные функции (ROW_NUMBER(), RANK()) и CTE, чтобы улучшить свои навыки. Пример - Ранжирование сотрудников по зарплате в пределах их отдела

WITH ranked_salaries AS (SELECT employee_id, salary, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees) SELECT * FROM ranked_salaries WHERE rank = 1;

Миф 4: Медленные запросы — всегда вина базы данных.

✦ Реальность: Обычно замедление происходит из-за неэффективных запросов. Причиной могут быть, например, отсутствующие индексы или неоптимизированный код.

✦ Как исправить: Правильно используйте индексы, избегайте сложных вычислений в условиях WHERE и проверяйте план выполнения запроса для выявления узких мест.

Миф 5: SQL устарел и скоро будет заменен.

✦ Реальность: SQL останется! Несмотря на рост популярности NoSQL, SQL по-прежнему является основой для структурированных данных.

✦ Как исправить: Следите за новыми тенденциями и исследуйте, как SQL интегрируется с платформами для больших данных и облачными базами данных. SQL актуален как никогда.

Не позволяйте этим мифам останавливать вас. SQL — это мощный инструмент, и когда вы освоите его в полной мере, вы сможете делать потрясающие вещи с вашими данными.❤️

👉 @SQLPortal

Практически каждая операция, даже базовые запросы в Postgres, использует блокировки для управления доступом к ресурсам. Существует несколько типов блокировок, многие из которых не блокируют чтение или запись, но могут блокировать DDL-операции, изменяющие схему. Некоторые основные моменты.

👉 @SQLPortal

Сегодня мы изучим хранимые процедуры и функции:

Хранимые процедуры и функции — это предварительно скомпилированные объекты, хранящиеся в базе данных, которые позволяют инкапсулировать и повторно использовать логику на серверной стороне.
Хранимые процедуры:

Хранимая процедура — это набор SQL-операторов, которые могут быть выполнены как единое целое.

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL-операторы
END;

Выполнение хранимой процедуры:

EXEC procedure_name;

Функции:

Функция возвращает значение на основе входных параметров. Существуют два типа: скалярные функции и функции, возвращающие таблицы.

CREATE FUNCTION function_name (@param1 INT, @param2 VARCHAR(50))
RETURNS INT
AS
BEGIN
    -- SQL-операторы
    RETURN some_value;
END;

Вызов функции:

SELECT dbo.function_name(param1, param2);

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

👉 @SQLPortal

Совет по Postgres: настройте префикс строки лога (log_line_prefix), чтобы при последующей фильтрации логов было больше контекста. По умолчанию там есть таймстамп и ID процесса, но можно добавить гораздо больше.

Например: user=%u, db=%d.

Новое в Postgres 18: %L — локальный IP-адрес сервера.

👉 @SQLPortal

Существует множество способов выполнить один и тот же запрос в Postgres.

- Делать поиск через последовательное сканирование (sequential scan) или по индексу?
- Какие индексы использовать?
- В каком порядке лучше джойнить таблицы?
- Какой алгоритм сортировки выбрать?

Все эти решения принимает query planner. Его задача — принять запрос, определить оптимальный план выполнения, а затем передать его в execution engine.

Postgres собирает статистику по каждой таблице (размер, оценка кардинальности колонок и т.д.), и эти данные используются планировщиком, помогая ему принимать более точные решения.

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

В таких случаях можно:

- настраивать параметры, например enable_seqscan=off
- вручную обновлять статистику
- или использовать расширения вроде pg_hint_plan, чтобы подсказать планировщику более подходящий вариант выполнения

👉 @SQLPortal

ORM обычно по умолчанию используют INSERT, но это не всегда лучший вариант — особенно если нужно загружать большие объёмы данных.

Для действительно крупных батчей стоит использовать COPY в PostgreSQL или \copy из командной строки.

Postgres: INSERT vs COPY

INSERT

- построчно (row by row), идеально для транзакций
- можно использовать ON CONFLICT
- подходит для нескольких тысяч строк, но COPY быстрее

COPY

- быстрее
- принцип «всё или ничего» (all-or-nothing)
- нужен для загрузки данных в больших объёмах
- можно запускать из psql через \copy

👉 @SQLPortal

Совет по PostgreSQL: \crosstabview

Если у вас есть результат из 3 столбцов, PostgreSQL может преобразовать его в сводную таблицу с помощью команды \crosstabview (в psql).

Для более сложных наборов данных используйте функцию crosstab()из расширения tablefunc — она требует более точной настройки с помощью SQL-запросов

👉 @SQLPortal

Нашёл коллекцию 100+ SQL-скриптов

Они могут помочь в управлении базами данных, оптимизации запросов и выполнении различных операций с данными.

Сохраняй если хочешь улучшить свои навыки работы с SQL. 👍

👉 @SQLPortal

Ищешь игры, которые помогут тебе выучить и практиковать SQL? 🤔

Попробуй SQL Noir — детективная игра по SQL

Пиши запросы, анализируй данные и раскрывай дела

Попробовать - https://www.sqlnoir.com/

👉 @SQLPortal

SQL-собеседования ОБОЖАЮТ проверять вас на оконные функции.

Вот список из 7 самых популярных оконных функций:

7 самых часто проверяемых оконных функций 👇

🟡RANK() – присваивает ранг каждой строке в разделе на основе указанного столбца или значения.
🟡DENSE_RANK() – присваивает ранг каждой строке, но не пропускает значения ранга.
🟡ROW_NUMBER() – присваивает каждой строке уникальный порядковый номер в разделе на основе порядка строк.
🟡LEAD() – извлекает значение из следующей строки в разделе на основе указанного столбца или выражения.
🟡LAG() – извлекает значение из предыдущей строки в разделе на основе указанного столбца или выражения.
🟡NTH_VALUE() – извлекает n-е значение в разделе.

👉 @SQLPortal

Изучайте SQL быстрее

Этот сайт объясняет ключевые понятия с помощью аналогий

- SQL, JavaScript, CSS, Git и многое другое

С интерактивными учебниками

→ http://codeanalogies.com

👉 @SQLPortal

Одна из N причин, почему шардинг — отличный способ масштабировать базу данных:

Скорость резервного копирования.

👉 @SQLPortal

Известная компания Postgres Professional выпустила свой обновлённый базовый курс DBA-1 по администрированию свободно распространяемой СУБД PostgreSQL.

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

Основные изменения:

- Добавлена информация про версии PostgreSQL 14, 15 и 16.
- Заменены четыре темы раздела «Управление доступом», по которым в дальнейшем появится отдельный подробный курс;
- Частично изменена структура: изложение стало более логичным и последовательным;
- Физическая и логическая репликации теперь рассматриваются в отдельных темах.
- Исправлены недочёты в изложении, ошибки в скриптах демонстраций и практических заданий.

Cам курс: тут

👉 @SQLPortal

Полный перевод документации PostgreSQL на русский язык

Оригинальная англоязычная документация PostgreSQL и документация к СУБД Postgres Pro в вариантах Standard и Enterprise.

⛓ Ссылка: тык

👉 @SQLPortal