SQL Ready | Базы Данных

Временные таблицы: различия между TEMP, CTE и материализацией данных!

В SQL существует несколько способов работать с промежуточными результатами. Основные варианты — временные таблицы, CTE через WITH и обычные подзапросы. Выбор между ними влияет на читаемость запроса, возможность повторного использования данных и работу оптимизатора.

Временная таблица создаётся внутри текущей сессии базы данных и существует до её завершения или до явного удаления. Она подходит для многоэтапной обработки данных, когда результат нужно использовать в нескольких следующих запросах.
CREATE TEMP TABLE monthly_sales AS
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id;

После создания временная таблица становится отдельным объектом базы данных. К ней можно обращаться как к обычной таблице, создавать индексы и выполнять дополнительные операции.
SELECT *
FROM monthly_sales
WHERE total_amount > 10000;

Временные таблицы особенно полезны при сложных процессах обработки данных, где нужно разделить вычисления на несколько этапов и повторно использовать промежуточный результат.
CREATE INDEX idx_monthly_sales_customer_id
ON monthly_sales(customer_id);

CTE (Common Table Expression) создаётся с помощью конструкции WITH и существует только во время выполнения одного SQL-запроса. Он помогает сделать сложную логику более читаемой и структурированной.
WITH monthly_sales AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT *
FROM monthly_sales
WHERE total_amount > 10000;

В PostgreSQL начиная с версии 12 CTE может быть автоматически встроен оптимизатором в основной запрос. Это называется CTE inlining. В таком случае отдельное промежуточное хранилище данных не создаётся.
WITH active_users AS MATERIALIZED (
SELECT *
FROM users
WHERE status = 'active'
)
SELECT *
FROM active_users;

Основное отличие MATERIALIZED заключается в том, что PostgreSQL принудительно сохраняет результат CTE перед дальнейшей обработкой. Это может быть полезно, если один и тот же результат используется несколько раз или нужно избежать повторного выполнения тяжёлого вычисления.
WITH user_stats AS MATERIALIZED (
SELECT
user_id,
COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
)
SELECT
a.user_id,
a.orders_count
FROM user_stats a
JOIN user_stats b
ON a.user_id = b.user_id;

Однако использование CTE не всегда означает материализацию. PostgreSQL самостоятельно выбирает оптимальный способ выполнения запроса, если не указано MATERIALIZED или NOT MATERIALIZED.
WITH user_stats AS NOT MATERIALIZED (
SELECT
user_id,
COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
)
SELECT *
FROM user_stats;

Если промежуточный результат большой и используется несколько раз в рамках сложного процесса, временная таблица часто подходит лучше. Она позволяет создать индексы, выполнять дополнительные запросы и управлять этапами обработки отдельно.
CREATE TEMP TABLE user_stats AS
SELECT
user_id,
COUNT(*) AS orders_count
FROM orders
GROUP BY user_id;

CREATE INDEX idx_user_stats_user_id
ON user_stats(user_id);

ANALYZE user_stats;

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

Обычный подзапрос существует только внутри конкретного SQL-выражения. Он подходит для локальных вычислений, когда результат нужен только в одном месте и не требуется повторное использование.
SELECT *
FROM (
SELECT
user_id,
COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
) s
WHERE orders_count > 50;

🔥 Выбор подходящего варианта зависит от задачи. CTE обычно используют для повышения читаемости и разделения сложной логики внутри одного запроса. Временные таблицы подходят для многошаговой обработки, больших промежуточных результатов и случаев, когда нужны индексы. Подзапросы удобны для простых локальных вычислений внутри одного SQL-выражения.

➡️ SQL Ready | #практика

Разбираем почему REINDEX не заменяет VACUUM в PostgreSQL!

После большого количества операций UPDATE и DELETE в PostgreSQL часто используют команды VACUUM и REINDEX. Несмотря на то что обе относятся к обслуживанию базы данных, они решают разные задачи и не являются взаимозаменяемыми.

Создадим таблицу:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT
);

Добавим данные:
INSERT INTO users (email)
SELECT 'user' || g || '@mail.com'
FROM generate_series(1, 100000) AS g;

Удалим половину строк:
DELETE
FROM users
WHERE id