Перейти к основному содержимому

Шпаргалка с типичными задачами по SQL

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Это шпаргалка рецептов: одна учебная схема и типовые задачи — от SELECT до администрирования PostgreSQL. С нуля — по дорожной карте; здесь удобно искать "как сделать X", когда основы пройдены. Те же сценарии магазина с разбором каждой строки и проверкой результата — SQL — реальные кейсы.

Для сравнения одного и того же SQL на SQLite, Oracle, MySQL и PostgreSQL (схема TABLE1–3, ALTER, JOIN) — отдельная компактная страница Шпаргалка SQL — четыре СУБД на одной схеме.

По умолчанию PostgreSQL, если не указано иное.

Пример базы данных​

Таблица — categories​

column_namedata_typeconstraintsdescription
idintegerPRIMARY KEYУникальный идентификатор категории
namevarchar(100)NOT NULL UNIQUEНазвание категории
parent_idintegerFOREIGN KEY REFERENCES categories(id)Ссылка на родительскую категорию
created_attimestampDEFAULT CURRENT_TIMESTAMPДата создания

Таблица — products​

column_namedata_typeconstraintsdescription
idintegerPRIMARY KEYУникальный идентификатор товара
namevarchar(200)NOT NULLНазвание товара
descriptiontextОписание товара
pricenumeric(10,2)NOT NULL CHECK (price >= 0)Цена товара
category_idintegerFOREIGN KEY REFERENCES categories(id) ON DELETE SET NULLСвязь с категорией
is_activebooleanDEFAULT trueСтатус доступности
created_attimestampDEFAULT CURRENT_TIMESTAMPДата создания
updated_attimestampDEFAULT CURRENT_TIMESTAMPДата обновления

Таблица — users​

column_namedata_typeconstraintsdescription
idintegerPRIMARY KEYУникальный идентификатор пользователя
emailvarchar(255)NOT NULL UNIQUEEmail пользователя
phonevarchar(20)Номер телефона
full_namevarchar(200)NOT NULLПолное имя
registration_datetimestampDEFAULT CURRENT_TIMESTAMPДата регистрации
is_verifiedbooleanDEFAULT falseСтатус подтверждения

Таблица — orders​

column_namedata_typeconstraintsdescription
idintegerPRIMARY KEYУникальный идентификатор заказа
user_idintegerNOT NULL FOREIGN KEY REFERENCES users(id) ON DELETE CASCADEСвязь с пользователем
order_numbervarchar(50)NOT NULL UNIQUEНомер заказа
statusvarchar(20)DEFAULT 'new' CHECK (status IN ('new','processing','shipped','delivered','cancelled'))Статус заказа
total_amountnumeric(12,2)Итоговая сумма заказа
created_attimestampDEFAULT CURRENT_TIMESTAMPДата создания заказа
updated_attimestampDEFAULT CURRENT_TIMESTAMPДата обновления

Таблица — order_items​

column_namedata_typeconstraintsdescription
idintegerPRIMARY KEYУникальный идентификатор позиции
order_idintegerNOT NULL FOREIGN KEY REFERENCES orders(id) ON DELETE CASCADEСвязь с заказом
product_idintegerNOT NULL FOREIGN KEY REFERENCES products(id)Связь с товаром
quantityintegerNOT NULL CHECK (quantity > 0)Количество единиц товара
unit_pricenumeric(10,2)NOT NULLЦена на момент покупки
subtotalnumeric(12,2)GENERATED ALWAYS AS (quantity * unit_price) STOREDПодытог позиции

Связи между таблицами:

  • categories → categories: иерархическая связь через parent_id (1 ко многим)
  • categories → products: одна категория содержит много товаров (1 ко многим)
  • users → orders: один пользователь оформляет много заказов (1 ко многим)
  • orders → order_items: один заказ содержит много позиций (1 ко многим)
  • products → order_items: один товар встречается во многих позициях заказов (1 ко многим)

Шаблон SELECT-запроса​

SELECT столбец1, столбец2, агрегатная_функция(столбец)
FROM основная_таблица
JOIN связанная_таблица ON условие_соединения
WHERE фильтрация_строк
GROUP BY группировка_по_столбцам
HAVING фильтрация_сгруппированных_данных
ORDER BY сортировка_результата
LIMIT количество_записей OFFSET пропуск_записей;

Проектирование связей между таблицами​

Связь один-к-одному (1:1)​

CREATE TABLE users (
    id integer PRIMARY KEY,
    email varchar(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id integer PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    avatar_url varchar(500),
    bio text
);

Связь один-ко-многим (1:M)​

CREATE TABLE categories (
    id integer PRIMARY KEY,
    name varchar(100) NOT NULL
);

CREATE TABLE products (
    id integer PRIMARY KEY,
    name varchar(200) NOT NULL,
    category_id integer REFERENCES categories(id)
);

Связь многие-ко-многим (M:N)​

Код ITЗагрузка примера кода…

Иерархическая связь (самоссылка)​

CREATE TABLE categories (
    id integer PRIMARY KEY,
    name varchar(100) NOT NULL,
    parent_id integer REFERENCES categories(id) ON DELETE SET NULL
);

Базовые операции выборки данных​

Выбор всех записей и всех столбцов​

SELECT * FROM products;

Выбор конкретных столбцов​

SELECT id, name, price FROM products;

Выбор с псевдонимами столбцов​

SELECT 
    id AS product_id,
    name AS product_name,
    price AS current_price
FROM products;

Фильтрация по одному условию​

SELECT name, price 
FROM products 
WHERE price > 1000;

Фильтрация по строковому значению​

SELECT * FROM users WHERE email = 'user@example.com';

Фильтрация с LIKE для поиска по шаблону​

SELECT name FROM products WHERE name LIKE '%смартфон%';
SELECT name FROM products WHERE name ILIKE 'Apple%'; -- регистронезависимый поиск

Фильтрация с IN для включения значений​

SELECT * FROM orders WHERE status IN ('new', 'processing', 'shipped');

Фильтрация с NOT IN для исключения значений​

SELECT * FROM products WHERE category_id NOT IN (5, 10, 15);

Фильтрация с AND для множественных условий​

SELECT * FROM products 
WHERE price >= 500 
  AND price <= 5000 
  AND is_active = true;

Фильтрация с OR для альтернативных условий​

SELECT * FROM orders 
WHERE status = 'cancelled' OR total_amount > 50000;

Комбинация AND и OR с группировкой условий​

SELECT * FROM products 
WHERE category_id = 3 
  AND (price < 1000 OR is_active = false);

Фильтрация по диапазону значений с BETWEEN​

SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

Фильтрация по NULL и NOT NULL​

SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;

Агрегация и группировка данных​

Подсчет общего количества записей​

SELECT COUNT(*) AS total_products FROM products;

Подсчет записей по условию​

SELECT COUNT(*) AS active_products 
FROM products 
WHERE is_active = true;

Подсчет уникальных значений​

SELECT COUNT(DISTINCT category_id) AS categories_with_products 
FROM products;

Сумма, среднее, минимум и максимум​

SELECT 
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS average_order,
    MIN(total_amount) AS min_order,
    MAX(total_amount) AS max_order
FROM orders
WHERE status = 'delivered';

Группировка по одному столбцу​

SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS average_price
FROM products
GROUP BY category_id;

Группировка по нескольким столбцам​

SELECT 
    DATE(created_at) AS order_date,
    status,
    COUNT(*) AS orders_count,
    SUM(total_amount) AS daily_revenue
FROM orders
GROUP BY DATE(created_at), status;

Фильтрация сгруппированных данных с HAVING​

SELECT 
    category_id,
    COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 10;

Комбинация WHERE и HAVING​

SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
WHERE status != 'cancelled'
GROUP BY user_id
HAVING SUM(total_amount) > 10000;

Сортировка и ограничение результатов​

Сортировка по одному столбцу по возрастанию​

SELECT name, price FROM products ORDER BY price ASC;

Сортировка по убыванию​

SELECT name, created_at FROM products ORDER BY created_at DESC;

Сортировка по нескольким столбцам​

SELECT * FROM products 
ORDER BY category_id ASC, price DESC, name ASC;

Сортировка с NULL значениями​

SELECT name, price FROM products ORDER BY price DESC NULLS LAST;
SELECT name, price FROM products ORDER BY price ASC NULLS FIRST;

Ограничение количества записей с LIMIT​

SELECT name, price FROM products ORDER BY price DESC LIMIT 10;

Пропуск записей с OFFSET для пагинации​

-- Страница 1
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 0;
-- Страница 2
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 20;
-- Страница 3
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 40;

Получение последних записей по дате​

SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 100;

Операции соединения таблиц JOIN​

INNER JOIN — пересечение таблиц​

SELECT 
    o.order_number,
    u.full_name,
    o.total_amount,
    o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'delivered';

LEFT JOIN — все записи из левой таблицы​

SELECT 
    c.name AS category_name,
    p.name AS product_name,
    p.price
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
ORDER BY c.name, p.price DESC;

RIGHT JOIN — все записи из правой таблицы​

SELECT 
    p.name AS product_name,
    oi.order_id
FROM products p
RIGHT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.order_id IS NOT NULL;

FULL OUTER JOIN — объединение всех записей​

SELECT 
    c.name AS category_name,
    p.name AS product_name
FROM categories c
FULL OUTER JOIN products p ON c.id = p.category_id
WHERE c.parent_id IS NULL OR p.category_id IS NULL;

Множественные JOIN в одном запросе​

SELECT 
    o.order_number,
    u.full_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    oi.subtotal
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status IN ('processing', 'shipped')
ORDER BY o.created_at DESC;

JOIN с агрегацией по связанным таблицам​

SELECT 
    u.id,
    u.full_name,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.full_name
ORDER BY total_spent DESC;

Подзапросы в различных контекстах​

Подзапрос в SELECT для вычисляемого столбца​

SELECT 
    p.name,
    p.price,
    (SELECT AVG(price) FROM products WHERE category_id = p.category_id) AS category_avg_price
FROM products p;

Подзапрос в FROM как временная таблица​

Код ITЗагрузка примера кода…

Подзапрос в WHERE с оператором IN​

SELECT name, price 
FROM products 
WHERE category_id IN (
    SELECT id FROM categories WHERE parent_id = 1
);

Подзапрос в WHERE с оператором EXISTS​

SELECT u.full_name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.total_amount > 50000
);

Подзапрос в WHERE с оператором сравнения​

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Подзапрос в HAVING для фильтрации групп​

SELECT 
    category_id,
    COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > (
    SELECT AVG(product_count)
    FROM (
        SELECT COUNT(*) AS product_count
        FROM products
        GROUP BY category_id
    ) AS category_counts
);

Подзапрос в JOIN для виртуальной таблицы​

Код ITЗагрузка примера кода…

Подзапрос в INSERT для вставки данных из другого запроса​

INSERT INTO order_archive (order_id, user_id, total_amount, archived_at)
SELECT id, user_id, total_amount, CURRENT_TIMESTAMP
FROM orders
WHERE status = 'cancelled' AND created_at < CURRENT_DATE - INTERVAL '1 year';

Подзапрос в UPDATE для изменения на основе результатов запроса​

UPDATE products p
SET price = price * 1.1
WHERE category_id IN (
    SELECT id FROM categories WHERE name LIKE '%премиум%'
);

Подзапрос в DELETE для удаления по условию из другого запроса​

DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders 
    WHERE status = 'cancelled' AND created_at < CURRENT_DATE - INTERVAL '6 months'
);

Операции модификации данных DML​

Вставка одной записи с возвратом идентификатора​

INSERT INTO users (email, full_name, phone)
VALUES ('newuser@example.com', 'Иван Петров', '+79991234567')
RETURNING id, created_at;

Вставка нескольких записей одним запросом​

INSERT INTO products (name, price, category_id, is_active)
VALUES 
    ('Товар 1', 1500.00, 3, true),
    ('Товар 2', 2300.50, 3, true),
    ('Товар 3', 890.00, 5, false);

Вставка с игнорированием дубликатов​

INSERT INTO users (email, full_name)
VALUES ('user@example.com', 'Тест Пользователь')
ON CONFLICT (email) DO NOTHING;

Вставка с обновлением при конфликте UPSERT​

INSERT INTO products (id, name, price, updated_at)
VALUES (123, 'Обновлённый товар', 2999.99, CURRENT_TIMESTAMP)
ON CONFLICT (id) DO UPDATE
SET 
    price = EXCLUDED.price,
    updated_at = CURRENT_TIMESTAMP
WHERE products.updated_at < EXCLUDED.updated_at;

Обновление одной записи по условию​

UPDATE products
SET price = 1999.99, updated_at = CURRENT_TIMESTAMP
WHERE id = 456 AND is_active = true;

Обновление нескольких записей с возвратом изменённых данных​

UPDATE orders
SET status = 'shipped', updated_at = CURRENT_TIMESTAMP
WHERE status = 'processing' AND created_at < CURRENT_DATE - INTERVAL '3 days'
RETURNING id, order_number, status;

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

UPDATE order_items oi
SET unit_price = p.price
FROM products p
WHERE oi.product_id = p.id 
  AND oi.order_id IN (SELECT id FROM orders WHERE status = 'new');

Удаление записей по условию с возвратом данных​

DELETE FROM order_items
WHERE order_id = 789
RETURNING product_id, quantity;

Каскадное удаление через внешние ключи​

-- При удалении пользователя удалятся все его заказы и позиции заказов
DELETE FROM users WHERE id = 100;

Массовое удаление с пакетной обработкой​

-- Удаление большими порциями для избежания блокировок
WITH deleted AS (
    DELETE FROM orders
    WHERE id IN (
        SELECT id FROM orders 
        WHERE status = 'cancelled' 
        LIMIT 1000
    )
    RETURNING id
)
SELECT COUNT(*) FROM deleted;
-- Повторять запрос пока не будут удалены все нужные записи

Временные таблицы для сложных вычислений​

Создание временной таблицы с данными​

CREATE TEMP TABLE temp_order_summary (
    user_id integer,
    order_count integer,
    total_spent numeric(12,2),
    last_order_date timestamp
) ON COMMIT DROP;

Наполнение временной таблицы данными​

INSERT INTO temp_order_summary
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent,
    MAX(created_at) AS last_order_date
FROM orders
WHERE status = 'delivered'
GROUP BY user_id;

Обновление данных во временной таблице​

UPDATE temp_order_summary tos
SET total_spent = total_spent * 1.1
WHERE total_spent > 100000;

Использование временной таблицы в основном запросе​

SELECT 
    u.full_name,
    u.email,
    tos.order_count,
    tos.total_spent,
    tos.last_order_date
FROM users u
JOIN temp_order_summary tos ON u.id = tos.user_id
ORDER BY tos.total_spent DESC;

Удаление временной таблицы после использования​

DROP TABLE IF EXISTS temp_order_summary;

Автоматическое удаление временной таблицы при завершении транзакции​

CREATE TEMP TABLE session_data (
    key varchar(100),
    value text,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
) ON COMMIT DROP;

Common Table Expressions (CTE) с WITH​

Простой CTE для улучшения читаемости​

WITH active_products AS (
    SELECT id, name, price, category_id
    FROM products
    WHERE is_active = true
)
SELECT 
    ap.name,
    ap.price,
    c.name AS category_name
FROM active_products ap
JOIN categories c ON ap.category_id = c.id
ORDER BY ap.price DESC;

Множественные CTE в одном запросе​

Код ITЗагрузка примера кода…

Рекурсивный CTE для иерархических данных​

Код ITЗагрузка примера кода…

Рекурсивный CTE для поиска всех предков узла​

Код ITЗагрузка примера кода…

CTE с модификацией данных и возвратом результата​

WITH moved_orders AS (
    UPDATE orders
    SET status = 'archived'
    WHERE created_at < CURRENT_DATE - INTERVAL '2 years'
      AND status IN ('delivered', 'cancelled')
    RETURNING id, user_id, total_amount
)
INSERT INTO orders_archive (order_id, user_id, amount, archived_at)
SELECT id, user_id, total_amount, CURRENT_TIMESTAMP
FROM moved_orders;

Оконные функции для аналитических запросов​

Нумерация строк в рамках группы​

SELECT 
    category_id,
    name,
    price,
    ROW_NUMBER() OVER (
        PARTITION BY category_id 
        ORDER BY price DESC
    ) AS rank_in_category
FROM products
WHERE is_active = true;

Ранжирование с пропуском номеров при равенстве​

SELECT 
    user_id,
    total_amount,
    RANK() OVER (ORDER BY total_amount DESC) AS price_rank,
    DENSE_RANK() OVER (ORDER BY total_amount DESC) AS dense_rank
FROM orders
WHERE status = 'delivered';

Доступ к предыдущему и следующему значению​

SELECT 
    DATE(created_at) AS order_date,
    SUM(total_amount) AS daily_revenue,
    LAG(SUM(total_amount)) OVER (ORDER BY DATE(created_at)) AS prev_day_revenue,
    LEAD(SUM(total_amount)) OVER (ORDER BY DATE(created_at)) AS next_day_revenue
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date;

Скользящее среднее значение​

SELECT 
    DATE(created_at) AS order_date,
    AVG(total_amount) AS daily_avg,
    AVG(AVG(total_amount)) OVER (
        ORDER BY DATE(created_at)
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_moving_avg
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date;

Кумулятивная сумма​

SELECT 
    DATE(created_at) AS order_date,
    SUM(total_amount) AS daily_revenue,
    SUM(SUM(total_amount)) OVER (
        ORDER BY DATE(created_at)
    ) AS cumulative_revenue
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date;

Процент от общей суммы в группе​

SELECT 
    category_id,
    name,
    price,
    SUM(price) OVER (PARTITION BY category_id) AS category_total,
    ROUND(
        price * 100.0 / SUM(price) OVER (PARTITION BY category_id),
        2
    ) AS percent_of_category
FROM products
WHERE is_active = true;

Первый и последний элемент в окне​

Код ITЗагрузка примера кода…

Хранимые функции и процедуры​

Скалярная функция с возвратом значения​

CREATE OR REPLACE FUNCTION calculate_age(birth_date date)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN DATE_PART('year', AGE(CURRENT_DATE, birth_date));
END;
$$;

-- Использование
SELECT full_name, calculate_age(registration_date) AS account_age_years
FROM users;

Функция с табличным возвратом​

Код ITЗагрузка примера кода…

Процедура с параметрами и транзакцией​

Код ITЗагрузка примера кода…

Функция с обработкой исключений​

Код ITЗагрузка примера кода…

Функция для массовой обработки записей​

Код ITЗагрузка примера кода…

Управление схемой данных DDL​

Добавление колонки без блокировки таблицы​

-- Добавление колонки с значением по умолчанию NULL (быстро)
ALTER TABLE products ADD COLUMN discount_percent numeric(5,2);

-- Заполнение данных в фоновом режиме
UPDATE products SET discount_percent = 0 WHERE discount_percent IS NULL;

-- Установка NOT NULL после заполнения
ALTER TABLE products ALTER COLUMN discount_percent SET NOT NULL;

-- Установка значения по умолчанию
ALTER TABLE products ALTER COLUMN discount_percent SET DEFAULT 0;

Изменение типа колонки с минимальным простоем​

-- Создание новой колонки нужного типа
ALTER TABLE orders ADD COLUMN total_amount_big numeric(15,2);

-- Копирование данных
UPDATE orders SET total_amount_big = total_amount;

-- Переименование колонок
ALTER TABLE orders RENAME COLUMN total_amount TO total_amount_old;
ALTER TABLE orders RENAME COLUMN total_amount_big TO total_amount;

-- Удаление старой колонки
ALTER TABLE orders DROP COLUMN total_amount_old;

Добавление ограничения с проверкой существующих данных​

-- Проверка наличия ограничения перед добавлением
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'check_price_positive'
    ) THEN
        ALTER TABLE products 
        ADD CONSTRAINT check_price_positive 
        CHECK (price >= 0);
    END IF;
END $$;

Условное создание индекса​

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_category_active 
ON products (category_id, is_active) 
WHERE is_active = true;

Изменение внешнего ключа без блокировки​

-- Добавление нового FK без валидации существующих данных
ALTER TABLE order_items 
ADD CONSTRAINT fk_order_items_product_new 
FOREIGN KEY (product_id) REFERENCES products(id) 
NOT VALID;

-- Валидация в отдельной транзакции
ALTER TABLE order_items 
VALIDATE CONSTRAINT fk_order_items_product_new;

-- Удаление старого FK
ALTER TABLE order_items 
DROP CONSTRAINT IF EXISTS fk_order_items_product_old;

Проверка заполнения N полей из M​

-- Ограничение на уровне таблицы
ALTER TABLE users ADD CONSTRAINT check_contact_info 
CHECK (
    CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN address IS NOT NULL THEN 1 ELSE 0 END >= 1
);

Управление enum-типами​

Код ITЗагрузка примера кода…

Поиск упоминаний объекта в базе данных​

Код ITЗагрузка примера кода…

Восстановление последовательности​

Код ITЗагрузка примера кода…

Работа с индексами​

Создание индекса без блокировки таблицы​

CREATE INDEX CONCURRENTLY idx_orders_user_created 
ON orders (user_id, created_at DESC);

Составной уникальный индекс с NULL-значениями​

-- Уникальность только для не-NULL значений
CREATE UNIQUE INDEX idx_products_sku_active 
ON products (sku) 
WHERE is_active = true;

-- Или с частичным уникальным ограничением
CREATE UNIQUE INDEX idx_users_email_verified 
ON users (email) 
WHERE is_verified = true;

Частичный индекс для оптимизации запросов​

-- Индекс только для активных товаров
CREATE INDEX idx_products_active_category 
ON products (category_id, price) 
WHERE is_active = true;

-- Индекс для недавних заказов
CREATE INDEX idx_orders_recent 
ON orders (created_at, status) 
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days';

Индекс на выражении​

-- Индекс для регистронезависимого поиска
CREATE INDEX idx_users_email_lower 
ON users (LOWER(email));

-- Использование
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Индекс на дате без времени
CREATE INDEX idx_orders_date_only 
ON orders ((DATE(created_at)));

Временное отключение индекса​

-- Отключение индекса для массовой загрузки данных
ALTER INDEX idx_products_category DISABLE;

-- Массовая вставка
INSERT INTO products SELECT * FROM staging_products;

-- Включение индекса обратно
ALTER INDEX idx_products_category ENABLE;

-- Перестроение индекса после массовой операции
REINDEX INDEX CONCURRENTLY idx_products_category;

Анализ использования индексов​

Код ITЗагрузка примера кода…

Администрирование и мониторинг​

Просмотр активных запросов​

SELECT 
    pid,
    usename AS username,
    client_addr,
    application_name,
    state,
    query_start,
    NOW() - query_start AS duration,
    LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

Завершение зависших процессов​

-- Завершение конкретного процесса
SELECT pg_terminate_backend(12345);

-- Завершение долгих запросов от определённого пользователя
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user'
  AND state = 'active'
  AND NOW() - query_start > INTERVAL '30 minutes'
  AND query NOT ILIKE '%pg_stat_activity%';

Блокировки и ожидания​

Код ITЗагрузка примера кода…

Статистика размера таблиц​

SELECT 
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size,
    n_live_tup AS row_estimate
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Быстрый подсчёт записей в большой таблице​

-- Приблизительный быстрый подсчёт
SELECT reltuples::bigint AS estimate 
FROM pg_class 
WHERE relname = 'products';

-- Точный подсчёт с анализом
SELECT COUNT(*) FROM products;

-- Подсчёт с условием и использованием индекса
SELECT COUNT(*) FROM products WHERE category_id = 5 AND is_active = true;

Ресурсоёмкие запросы​

Код ITЗагрузка примера кода…

Параметры конфигурации​

Код ITЗагрузка примера кода…

Автовакуум и его мониторинг​

Код ITЗагрузка примера кода…

Управление WAL-файлами​

-- Просмотр информации о WAL
SELECT 
    pg_current_wal_lsn() AS current_wal,
    pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;

-- Принудительное переключение WAL-файла
SELECT pg_switch_wal();

-- Просмотр архивных WAL-файлов
-- (требует доступа к файловой системе или настройки archive_command)

Репликация и отставание​

Код ITЗагрузка примера кода…

Управление ролями и объектами​

Код ITЗагрузка примера кода…

Сравнение конфигураций двух баз данных​

Код ITЗагрузка примера кода…

Валидация данных и целостность​

Проверка формата email через регулярное выражение​

SELECT email
FROM users
WHERE email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

Поиск записей с некорректным email​

SELECT id, email
FROM users
WHERE email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
  AND email IS NOT NULL;

Очистка таблицы от записей с некорректными email​

DELETE FROM users
WHERE email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
  AND email IS NOT NULL;

Валидация цвета в формате CSS (HEX)​

SELECT color_code
FROM design_assets
WHERE color_code ~* '^#([A-F0-9]{6}|[A-F0-9]{3})$';

Проверка ИНН организации (10 цифр)​

SELECT inn
FROM companies
WHERE inn ~ '^\d{10}$'
  AND LENGTH(inn) = 10;

Проверка ИНН физического лица (12 цифр)​

SELECT inn
FROM contractors
WHERE inn ~ '^\d{12}$'
  AND LENGTH(inn) = 12;

Валидация номера телефона через маску​

SELECT phone
FROM users
WHERE phone ~ '^\+7\(\d{3}\)\d{3}-\d{2}-\d{2}$';

Обновление поля только при явной передаче значения​

UPDATE users
SET 
    phone = COALESCE(NULLIF(:new_phone, ''), phone),
    updated_at = CURRENT_TIMESTAMP
WHERE id = :user_id;

Использование доменов для типизации данных​

CREATE DOMAIN email_address AS VARCHAR(255)
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE contacts (
    id integer PRIMARY KEY,
    user_email email_address NOT NULL
);

Работа с JSON и JSONB​

Теория типа и отличия json / jsonb — JSONB. Пошаговый практикум с таблицей, 10 большими JSON-записями, JOIN, CROSS JOIN, HAVING, RETURNING, индексами и EXPLAIN — Практикум PostgreSQL по JSONB.

Извлечение значения по ключу​

SELECT 
    id,
    Данные->>'name' AS user_name,
    Данные->'address'->>'city' AS city
FROM users
WHERE Data IS NOT NULL;

Поиск записей по наличию ключа в JSON​

SELECT *
FROM events
WHERE payload ? 'error_code';

Поиск записей по значению внутри JSON массива​

SELECT *
FROM orders
WHERE items @> '[{"product_id": 123}]';

Сравнение двух JSON объектов и поиск отличий​

SELECT 
    jsonb_each_text(old_data) AS old_val,
    jsonb_each_text(new_data) AS new_val
FROM audit_log
WHERE old_data IS DISTINCT FROM new_data;

Обновление конкретного ключа в JSONB поле​

UPDATE users
SET data = jsonb_set(Данные, '{last_login}', to_jsonb(CURRENT_TIMESTAMP))
WHERE id = 42;

Удаление ключа из JSONB объекта​

UPDATE users
SET data = Данные - 'temporary_token'
WHERE id = 42;

Индексация JSONB полей для ускорения поиска​

CREATE INDEX idx_users_data_gin ON users USING GIN (Данные);
CREATE INDEX idx_users_data_path ON users USING GIN (Данные jsonb_path_ops);

Агрегация данных в JSON массив​

SELECT 
    order_id,
    jsonb_agg(jsonb_build_object('product', product_name, 'qty', quantity)) AS items
FROM order_items
GROUP BY order_id;

Операции с массивами​

Объединение массивов в одну строку​

SELECT array_agg(product_name) AS product_list
FROM order_items
WHERE order_id = 100;

Пересечение двух массивов​

SELECT ARRAY[1, 2, 3] &< ARRAY[3, 4, 5];
-- Результат: true (если есть общие элементы)

Получение уникальных элементов массива​

SELECT ARRAY(SELECT DISTINCT unnest(ARRAY[1, 2, 2, 3, 3]));

Сортировка элементов массива​

SELECT ARRAY(SELECT unnest(ARRAY[3, 1, 2]) ORDER BY 1);

Разница между двумя массивами​

SELECT ARRAY[1, 2, 3] - ARRAY[2];
-- Результат: {1,3}

Поиск элемента внутри массива​

SELECT * FROM products
WHERE tags @> ARRAY['new'];

Развертывание массива в набор строк​

SELECT unnest(ARRAY['apple', 'banana', 'cherry']) AS fruit;

Проверка наличия любого элемента из массива​

SELECT * FROM products
WHERE tags && ARRAY['sale', 'clearance'];

Полнотекстовый поиск​

Создание вектора поиска​

SELECT to_tsvector('russian', 'быстрая коричневая лиса');

Создание запроса поиска​

SELECT to_tsquery('russian', 'быстрая & лиса');

Выполнение поиска по тексту​

SELECT title, body
FROM articles
WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'база & данных');

Поиск с учетом морфологии и ранжирование​

SELECT 
    title,
    ts_rank(to_tsvector('russian', body), query) AS rank
FROM articles, to_tsquery('russian', 'оптимизация') query
WHERE to_tsvector('russian', body) @@ query
ORDER BY rank DESC;

Подсветка найденных слов в тексте​

SELECT 
    ts_headline('russian', body, to_tsquery('russian', 'поиск'), 'StartSel=<b>, StopSel=</b>') AS snippet
FROM articles
WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'поиск');

Создание материалализованного представления для поиска​

CREATE MATERIALIZED VIEW articles_search AS
SELECT 
    id,
    to_tsvector('russian', title || ' ' || body) AS search_vector
FROM articles;

CREATE INDEX idx_articles_search ON articles_search USING GIN (search_vector);

Обновление материалализованного представления​

REFRESH MATERIALIZED VIEW CONCURRENTLY articles_search;

Поиск с исправлением опечаток (trigram)​

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT word, similarity(word, 'постгрес') AS sim
FROM words
WHERE word % 'постгрес'
ORDER BY sim DESC
LIMIT 5;

Гео-запросы и работа с координатами​

Установка расширения PostGIS​

CREATE EXTENSION IF NOT EXISTS postgis;

Вычисление расстояния между точками​

SELECT 
    ST_Distance(
        ST_MakePoint(37.6176, 55.7558)::geography,
        ST_MakePoint(30.3158, 59.9391)::geography
    ) AS distance_meters;

Поиск ближайших объектов​

SELECT 
    name,
    ST_Distance(location::geography, ST_MakePoint(37.6176, 55.7558)::geography) AS dist
FROM shops
ORDER BY dist
LIMIT 10;

Поиск объектов внутри радиуса​

SELECT *
FROM shops
WHERE ST_DWithin(
    location::geography,
    ST_MakePoint(37.6176, 55.7558)::geography,
    5000 -- радиус в метрах
);

Получение площади полигона​

SELECT ST_Area(polygon::geography) AS area_sq_meters
FROM regions
WHERE id = 1;

Проверка пересечения геометрий​

SELECT a.name, b.name
FROM regions a, regions b
WHERE ST_Intersects(a.polygon, b.polygon)
  AND a.id != b.id;

Оптимизация выполнения запросов​

Анализ плана выполнения запроса​

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123;

Ускорение запросов с большим списком в IN​

-- Использование временной таблицы вместо большого IN
CREATE TEMP TABLE target_ids (id integer);
INSERT INTO target_ids VALUES (1), (2), (3); -- массовая вставка

SELECT * FROM orders o
JOIN target_ids t ON o.user_id = t.id;

Использование существующих индексов​

-- Проверка использования индекса
EXPLAIN SELECT * FROM products WHERE category_id = 5;
-- Убедиться в наличии Index Scan вместо Seq Scan

Оптимизация COUNT(*) для больших таблиц​

-- Использование системных каталогов для приблизительного подсчета
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';

Разбиение большого запроса на части​

Код ITЗагрузка примера кода…

Измерение времени выполнения функции​

Код ITЗагрузка примера кода…

Поиск дубликатов записей​

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Добавление времени выполнения в результат запроса​

SELECT 
    *,
    clock_timestamp() - statement_timestamp() AS query_duration
FROM orders
LIMIT 100;

Оценка объема данных запроса​

SELECT 
    pg_relation_size('orders') AS table_size,
    pg_total_relation_size('orders') AS total_size_with_indexes;

Рекурсивный запрос для обхода дерева​

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, t.level + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY level, name;

Модификация данных (DML) продвинутые техники​

UPSERT — Вставка или Обновление​

INSERT INTO users (id, email, name)
VALUES (1, 'test@example.com', 'Test')
ON CONFLICT (id) DO UPDATE
SET 
    email = EXCLUDED.email,
    name = EXCLUDED.name,
    updated_at = CURRENT_TIMESTAMP;

Вставка без увеличения последовательности при конфликте​

INSERT INTO users (id, email)
VALUES (1, 'test@example.com')
ON CONFLICT (id) DO NOTHING;
-- Последовательность не расходуется при игнорировании

Массовая вставка через VALUES​

INSERT INTO products (name, price)
VALUES 
    ('Item 1', 100),
    ('Item 2', 200),
    ('Item 3', 300);
-- Использование COPY для еще большей скорости при загрузке из файла

Возврат ID затронутых записей в связанных таблицах​

WITH inserted_user AS (
    INSERT INTO users (email) VALUES ('new@test.com') RETURNING id
),
inserted_profile AS (
    INSERT INTO profiles (user_id, bio)
    SELECT id, 'Default bio' FROM inserted_user
    RETURNING user_id
)
SELECT * FROM inserted_profile;

Обновление данных из другой таблицы​

UPDATE products p
SET price = src.new_price
FROM price_source src
WHERE p.id = src.product_id
  AND p.price IS DISTINCT FROM src.new_price;

Сохранение сгенерированного ID в другом поле​

INSERT INTO documents (id, reference_code)
VALUES (DEFAULT, 'DOC-' || NEXTVAL('documents_id_seq'))
RETURNING id, reference_code;

Последовательные запросы с использованием ID​

-- В рамках одной транзакции или функции
INSERT INTO orders (user_id) VALUES (1) RETURNING id INTO v_order_id;
INSERT INTO order_items (order_id, product_id) VALUES (v_order_id, 5);

Обновление только при изменении данных​

UPDATE users
SET name = 'New Name'
WHERE id = 1
  AND name IS DISTINCT FROM 'New Name';

Защита от перезаписи чужих изменений (Optimistic Locking)​

UPDATE documents
SET content = 'New content', version = version + 1
WHERE id = 1
  AND version = 5; -- Проверка версии

Обновление нескольких записей разными данными​

UPDATE products p
SET price = v.price
FROM (VALUES 
    (1, 100.00),
    (2, 200.00),
    (3, 300.00)
) AS v(id, price)
WHERE p.id = v.id;

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

-- Запускать циклически до завершения
DELETE FROM logs
WHERE ctid IN (
    SELECT ctid FROM logs
    WHERE created_at < '2023-01-01'
    LIMIT 5000
);

Ограничение количества зависимых записей​

Код ITЗагрузка примера кода…

Журналирование изменений таблицы​

Код ITЗагрузка примера кода…

Автоматическое обновление поля updated_at​

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Модификация схемы (DDL) безопасные методы​

Добавление колонки с значением по умолчанию​

-- Быстрое добавление (без пересчета таблицы)
ALTER TABLE products ADD COLUMN is_new boolean DEFAULT false;

Проверка существования ограничения перед созданием​

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'unique_email'
    ) THEN
        ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
    END IF;
END $$;

Изменение типа колонки с минимальным влиянием​

-- Шаг 1: Добавить новую колонку
ALTER TABLE orders ADD COLUMN total_big numeric(15,2);

-- Шаг 2: Заполнить данными
UPDATE orders SET total_big = total_amount;

-- Шаг 3: Переключить приложение на новую колонку (логически)

-- Шаг 4: Удалить старую колонку
ALTER TABLE orders DROP COLUMN total_amount;

-- Шаг 5: Переименовать новую
ALTER TABLE orders RENAME COLUMN total_big TO total_amount;

Удаление значения из ENUM типа​

-- Создание нового типа
CREATE TYPE order_status_new AS ENUM ('new', 'processing', 'shipped');

-- Конвертация данных
ALTER TABLE orders 
ALTER COLUMN status TYPE order_status_new 
USING status::text::order_status_new;

-- Замена типа
DROP TYPE order_status;
ALTER TYPE order_status_new RENAME TO order_status;

Поиск зависимостей объекта​

SELECT 
    dependent_ns.nspname AS dependent_schema,
    dependent.relname AS dependent_table,
    source_ns.nspname AS source_schema,
    source.relname AS source_table
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent ON pg_rewrite.ev_class = dependent.oid
JOIN pg_class AS source ON pg_depend.refobjid = source.oid
JOIN pg_namespace AS dependent_ns ON dependent.relnamespace = dependent_ns.oid
JOIN pg_namespace AS source_ns ON source.relnamespace = source_ns.oid
WHERE source.relname = 'users';

Администрирование и обслуживание​

Просмотр активных процессов​

SELECT 
    pid,
    usename,
    datname,
    client_addr,
    application_name,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle';

Завершение процесса​

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;

Список всех функций и процедур​

SELECT 
    routine_schema,
    routine_name,
    routine_type
FROM information_schema.routines
WHERE routine_schema = 'public';

Список внешних ключей​

SELECT 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Статистика использования индексов​

SELECT 
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Список расширений​

SELECT 
    extname,
    extversion,
    nspname AS schema_name
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace;

Размер таблиц и индексов​

SELECT 
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Тяжелые запросы​

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Запросы с временными файлами​

SELECT 
    query,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC;

Параметры конфигурации​

SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('work_mem', 'shared_buffers', 'effective_cache_size');

Процессы автовакуума​

SELECT 
    pid,
    query,
    NOW() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

Отставание реплик​

SELECT 
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Использование последовательностей​

SELECT 
    sequencename,
    last_value,
    max_value,
    ROUND(100.0 * last_value / NULLIF(max_value, 0), 2) AS usage_percent
FROM pg_sequences;

Очистка WAL файлов​

-- Настройка archive_cleanup_command в postgresql.conf
-- Или ручной вызов на реплике
SELECT pg_walfile_name(pg_last_wal_receive_lsn());

Удаление роли с объектами​

REASSIGN OWNED BY old_role TO new_role;
DROP OWNED BY old_role;
DROP ROLE old_role;

Сравнение конфигураций​

SELECT name, setting, source
FROM pg_settings
WHERE source != 'default'
ORDER BY name;

Копирование базы данных​

-- Через утилиту командной строки
-- pg_dump -U user source_db | psql -U user target_db

Проверка синтаксиса SQL​

-- Использование EXPLAIN без выполнения
EXPLAIN SELECT * FROM users WHERE id = 1;

Откат части транзакции​

BEGIN;
SAVEPOINT my_savepoint;
-- Операции
ROLLBACK TO my_savepoint;
-- Продолжение работы
COMMIT;

Завершение простаивающих подключений​

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND NOW() - state_change > INTERVAL '1 hour';

Блокировки и очереди​

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.GRANTED;

Журналирование DDL команд​

-- Настройка log_statement = 'ddl' в postgresql.conf
-- Или использование событийных триггеров
CREATE EVENT TRIGGER log_ddl_commands
ON ddl_command_end
EXECUTE FUNCTION log_ddl_function();

Статистика действий в таблице​

SELECT 
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
ORDER BY relname;

Транзакции и управление блокировками​

Начало и завершение транзакции​

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Откат транзакции при ошибке​

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Ошибка выполнения
ROLLBACK;

Использование точек сохранения (Savepoints)​

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT sp_order_items;

INSERT INTO order_items (order_id, product_id) VALUES (1, 10);
-- Ошибка вставки позиции
ROLLBACK TO sp_order_items;

INSERT INTO order_items (order_id, product_id) VALUES (1, 11);

COMMIT;

Установка уровня изоляции транзакции​

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Блокировка строк для обновления​

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

Параллельный захват пакета задач без ожидания (несколько воркеров, одна таблица jobs):

BEGIN;

WITH batch AS (
  SELECT id
  FROM jobs
  WHERE processed = false
  ORDER BY created_at
  LIMIT 100
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET processed = true,
    processed_at = now()
FROM batch
WHERE j.id = batch.id
RETURNING j.*;

COMMIT;

Подробнее — очередь на одну строку, индексы, статусы processing и восстановление "зависших" задач — Блокировки в PostgreSQL.

Блокировка строк для чтения​

SELECT * FROM accounts WHERE id = 1 FOR SHARE;

Определение текущих блокировок​

Код ITЗагрузка примера кода…

Завершение транзакции с указанием цепи​

COMMIT AND CHAIN;
ROLLBACK AND CHAIN;

Секционирование таблиц​

Создание секционированной таблицы по диапазону​

CREATE TABLE measurements (
    logdate date NOT NULL,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (logdate);

Создание секций для таблицы​

CREATE TABLE measurements_y2023 PARTITION OF measurements
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE measurements_y2024 PARTITION OF measurements
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Создание секционированной таблицы по списку​

CREATE TABLE cities (
    city_id int NOT NULL,
    name text,
    region text
) PARTITION BY LIST (region);

CREATE TABLE cities_europe PARTITION OF cities
    FOR VALUES IN ('EUROPE', 'EUR');

CREATE TABLE cities_asia PARTITION OF cities
    FOR VALUES IN ('ASIA', 'ASA');

Создание секционированной таблицы по хешу​

CREATE TABLE users_hash (
    user_id int NOT NULL,
    username text
) PARTITION BY HASH (user_id);

CREATE TABLE users_hash_0 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_hash_1 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Добавление новой секции без блокировки основной таблицы​

CREATE TABLE measurements_y2025 PARTITION OF measurements
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Отсоединение секции для архивации​

ALTER TABLE measurements DETACH PARTITION measurements_y2023;

Присоединение существующей таблицы как секции​

ALTER TABLE measurements ATTACH PARTITION measurements_y2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Запрос к секционированной таблице с указанием секции​

SELECT * FROM measurements_y2024 WHERE logdate = '2024-05-01';

Автоматическое создание секций через функцию​

Код ITЗагрузка примера кода…

Безопасность на уровне строк (RLS)​

Включение RLS для таблицы​

ALTER TABLE accounts ENABLE ROW LEVEL БЕЗОПАСНОСТЬ;

Создание политики для владельца записи​

CREATE POLICY user_is_owner ON accounts
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::int);

Создание политики только для чтения​

CREATE POLICY users_can_view ON accounts
    FOR SELECT
    USING (true);

Создание политики для модераторов​

CREATE POLICY moderators_can_edit ON accounts
    FOR UPDATE
    USING (current_setting('app.user_role') = 'moderator');

Принудительное применение RLS для владельца таблицы​

ALTER TABLE accounts FORCE ROW LEVEL БЕЗОПАСНОСТЬ;

Просмотр активных политик​

SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
WHERE tablename = 'accounts';

Отключение RLS для таблицы​

ALTER TABLE accounts DISABLE ROW LEVEL БЕЗОПАСНОСТЬ;

Работа с датами и временными интервалами​

Получение текущей даты и времени​

SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT CURRENT_TIMESTAMP;
SELECT NOW();
SELECT clock_timestamp();

Извлечение частей даты​

SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(DOW FROM CURRENT_TIMESTAMP); -- День недели
SELECT EXTRACT(ISOYEAR FROM CURRENT_TIMESTAMP);

Сложение и вычитание интервалов​

SELECT CURRENT_DATE + INTERVAL '1 day';
SELECT CURRENT_DATE - INTERVAL '1 month';
SELECT CURRENT_TIMESTAMP + INTERVAL '2 hours 30 minutes';
SELECT CURRENT_DATE + INTERVAL '1 year 2 months 3 days';

Вычисление разницы между датами​

SELECT AGE(CURRENT_TIMESTAMP, created_at) AS age FROM orders;
SELECT CURRENT_DATE - DATE '2023-01-01' AS days_diff;

Обрезка времени до начала периода​

SELECT DATE_TRUNC('hour', CURRENT_TIMESTAMP);
SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP);
SELECT DATE_TRUNC('week', CURRENT_TIMESTAMP);
SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP);

Генерация серии дат​

SELECT generate_series(
    '2024-01-01'::date,
    '2024-01-31'::date,
    '1 day'::interval
) AS date_value;

Конвертация временных зон​

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Europe/Moscow';
SELECT TIMESTAMP '2024-01-01 12:00:00' AT TIME ZONE 'America/New_York';

Проверка високосного года​

SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS year,
       CASE 
           WHEN EXTRACT(DAY FROM (DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year - 1 day')) = 366 
           THEN 'Leap' 
           ELSE 'Common' 
       END AS year_type;

Получение первого и последнего дня месяца​

SELECT DATE_TRUNC('month', CURRENT_DATE) AS first_day;
SELECT (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month - 1 day')::date AS last_day;

Работа с временными диапазонами (tsrange)​

SELECT tsrange('2024-01-01', '2024-02-01') @> '2024-01-15';
SELECT tsrange('2024-01-01', '2024-02-01') && tsrange('2024-01-25', '2024-03-01');

Паттерны обслуживания и миграции​

Расширение таблицы без блокировки (Add Column)​

ALTER TABLE large_table ADD COLUMN new_column varchar(100);
-- Заполнение в фоне
UPDATE large_table SET new_column = 'default' WHERE new_column IS NULL;
ALTER TABLE large_table ALTER COLUMN new_column SET NOT NULL;

Переименование таблицы с минимальным простоем​

ALTER TABLE old_name RENAME TO new_name;
-- Обновление зависимостей требуется вручную или через скрипт

Изменение типа данных колонки через новую колонку​

ALTER TABLE users ADD COLUMN phone_new varchar(20);
UPDATE users SET phone_new = phone;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN phone_new TO phone;

Пересборка таблицы для освобождения места​

VACUUM FULL VERBOSE users;
-- Или использование CLUSTER для физического упорядочивания
CLUSTER users USING idx_users_created_at;

Обновление статистики планировщика​

ANALYZE VERBOSE orders;
ANALYZE VERBOSE products;

Проверка целостности данных​

"Битые" ссылки — заказы без пользователя, позиции без заказа. NOT IN опасен при NULL в подзапросе (Фильтрация и трёхзначная логика). Надёжнее NOT EXISTS:

SELECT o.id, o.order_number
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM users u WHERE u.id = o.user_id
);

SELECT oi.id, oi.order_id
FROM order_items oi
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.id = oi.order_id
);

Если нужен NOT IN: WHERE user_id NOT IN (SELECT id FROM users WHERE id IS NOT NULL).

Архивация старых данных​

INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

DELETE FROM orders
WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

Сжатие данных через TOAST​

-- Автоматическое сжатие больших текстовых полей
-- Настройка уровня сжатия
ALTER TABLE documents ALTER COLUMN content SET STORAGE EXTENDED;

Мониторинг производительности​

Поиск запросов с полным сканированием таблицы​

SELECT 
    query,
    calls,
    total_exec_time
FROM pg_stat_statements
WHERE query LIKE '%Seq Scan%'
ORDER BY total_exec_time DESC
LIMIT 10;

Анализ кэш-хитов​

SELECT 
    relname,
    heap_blks_read,
    heap_blks_hit,
    ROUND(100.0 * heap_blks_hit / NULLIF(heap_blks_read + heap_blks_hit, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY hit_ratio ASC;

Отслеживание роста таблиц​

SELECT 
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||relname) DESC;

Проверка состояния индексов​

SELECT 
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Выявление долгих транзакций​

SELECT 
    pid,
    usename,
    NOW() - xact_start AS transaction_duration,
    state,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND NOW() - xact_start > INTERVAL '5 minutes';

Мониторинг подключений​

SELECT 
    datname,
    count(*) as connections
FROM pg_stat_activity
GROUP BY datname;

Проверка лимитов подключений​

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

Тестирование SQL запросов​

Создание тестовых данных​

INSERT INTO users (email, full_name)
SELECT 
    'user' || i || '@test.com',
    'User ' || i
FROM generate_series(1, 1000) AS i;

Очистка тестовых данных​

TRUNCATE TABLE users RESTART IDENTITY CASCADE;

Проверка ожидаемого результата​

SELECT COUNT(*) = 1000 AS test_passed FROM users;

Изоляция тестов в транзакции​

BEGIN;
-- Тестовые запросы
ROLLBACK;

Сравнение результатов двух запросов​

(SELECT * FROM query_a EXCEPT SELECT * FROM query_b)
UNION ALL
(SELECT * FROM query_b EXCEPT SELECT * FROM query_a);

Утилиты и вспомогательные функции​

Генерация UUID​

SELECT gen_random_uuid();
-- Требует расширения pgcrypto или uuid-ossp

Хеширование данных​

SELECT md5('secret_string');
SELECT sha256('secret_string');

Сжатие и распаковка данных​

SELECT compress('Данные');
SELECT decompress(compress('Данные'));

Работа с сетевыми адресами​

SELECT '192.168.1.1'::inet;
SELECT '192.168.1.0/24'::cidr;
SELECT host('192.168.1.1'::inet);
SELECT masklen('192.168.1.0/24'::cidr);

Работа с векторами и массивами бит​

SELECT '101'::bit(3);
SELECT '101'::varbit(5);

Обработка XML данных​

SELECT xpath('/root/element/text()', '<root><element>value</element></root>');

Обработка YAML данных (при наличии расширения)​

SELECT yaml_parse('key: value');

Расширенные возможности оконных функций​

Вычисление процента от суммы​

SELECT 
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2) AS percent_of_dept
FROM employees;

Скользящее среднее за 3 периода​

SELECT 
    date,
    value,
    AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM metrics;

Накопительный итог​

SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

Доступ к первой и последней строке окна​

SELECT 
    department,
    employee,
    salary,
    FIRST_VALUE(employee) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
    LAST_VALUE(employee) OVER (
        PARTITION BY department 
        ORDER BY salary DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_earner
FROM employees;

Нумерация строк для пагинации​

SELECT * FROM (
    SELECT 
        id,
        name,
        ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num
    FROM products
) sub
WHERE row_num BETWEEN 21 AND 40;

Распределение по группам (NTILE)​

SELECT 
    employee,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Процентиль непрерывный​

SELECT 
    employee,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank
FROM employees;

Процентиль дискретный​

SELECT 
    employee,
    salary,
    CUME_DIST() OVER (ORDER BY salary) AS cumulative_dist
FROM employees;

Оптимизация соединений​

Использование EXISTS вместо IN для больших наборов​

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Использование LATERAL для зависимых соединений​

SELECT 
    u.id,
    u.name,
    latest.order_date,
    latest.total
FROM users u
CROSS JOIN LATERAL (
    SELECT order_date, total
    FROM orders
    WHERE user_id = u.id
    ORDER BY order_date DESC
    LIMIT 1
) latest;

Соединение с условием диапазона​

SELECT 
    e.name,
    d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.hired_at BETWEEN d.start_date AND d.end_date;

Соединение по неэквивалентным условиям​

SELECT 
    a.id,
    b.id
FROM table_a a
JOIN table_b b ON a.value > b.value;

Работа с полнотекстовым поиском​

Создание конфигурации поиска​

CREATE TEXT SEARCH CONFIGURATION ru (COPY = russian);
ALTER TEXT SEARCH CONFIGURATION ru
    ALTER MAPPING FOR hword, hword_part, word
    WITH russian_stem;

Индексация текстовых полей​

CREATE INDEX idx_products_search ON products USING GIN (to_tsvector('ru', name || ' ' || description));

Поиск с ранжированием​

SELECT 
    id,
    name,
    ts_rank(to_tsvector('ru', name || ' ' || description), query) AS rank
FROM products, to_tsquery('ru', 'телефон') query
WHERE to_tsvector('ru', name || ' ' || description) @@ query
ORDER BY rank DESC;

Подсветка результатов​

SELECT 
    ts_headline('ru', description, to_tsquery('ru', 'телефон'), 'StartSel=<b>, StopSel=</b>') AS snippet
FROM products
WHERE to_tsvector('ru', description) @@ to_tsquery('ru', 'телефон');

Словарь синонимов​

CREATE TEXT SEARCH DICTIONARY syn_dict (TEMPLATE = synonym, SYNONYMS = my_synonyms);

Стоп-слова​

CREATE TEXT SEARCH DICTIONARY stop_dict (TEMPLATE = stop, STOPWORDS = russian);

Управление расширениями​

Установка расширения​

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;
CREATE EXTENSION IF NOT EXISTS hstore;

Просмотр установленных расширений​

SELECT * FROM pg_extension;

Обновление расширения​

ALTER EXTENSION pg_trgm UPDATE TO '0.5';

Удаление расширения​

DROP EXTENSION IF EXISTS pg_trgm;

Поиск объектов расширения​

SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pg_trgm');

Резервное копирование и восстановление​

Логическое резервное копирование​

pg_dump -U username database_name > backup.sql
pg_dump -U username -t table_name database_name > table_backup.sql

Восстановление из дампа​

psql -U username database_name < backup.sql

Физическое резервное копирование​

pg_basebackup -U username -D /var/lib/postgresql/backup -Fp -Xs -P

Точечное восстановление (PITR)​

# Настройка recovery_target_time в postgresql.conf
# recovery_target_time = '2024-01-01 12:00:00'

Репликация WAL-файлов​

# Настройка archive_command
# archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Конфигурация сервера​

Просмотр настроек​

SHOW ALL;
SELECT * FROM pg_settings;

Изменение настроек на лету​

SET statement_timeout TO '1min';
SET lock_timeout TO '10s';

Изменение настроек в конфигурации​

ALTER SYSTEM SET work_mem = '256MB';
SELECT pg_reload_conf();

Проверка версии сервера​

SELECT version();
SELECT current_setting('server_version_num');

Просмотр путей к файлам​

SHOW data_directory;
SHOW hba_file;
SHOW ident_file;

Диагностика проблем​

Поиск блокирующих запросов​

Код ITЗагрузка примера кода…

Поиск дубликатов индексов​

SELECT 
    pg_relation_size(i.indexrelid) AS index_size,
    i.indexrelname AS index_name,
    i.indrelname AS table_name
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE NOT indisunique
  AND EXISTS (
      SELECT 1 FROM pg_stat_user_indexes si 
      WHERE si.indrelid = i.indrelid 
        AND si.indexrelid != i.indexrelid
        AND pg_get_indexdef(si.indexrelid) = pg_get_indexdef(i.indexrelid)
  );

Поиск неиспользуемых индексов​

SELECT 
    indexrelname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey';

Анализ вакуума​

SELECT 
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE last_vacuum IS NULL OR last_autovacuum IS NULL;

Проверка целостности страниц​

SELECT * FROM pg_stat_database WHERE datname = current_database();

Мониторинг репликации​

SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

Проверка отставания последовательностей​

SELECT 
    sequencename,
    last_value,
    max_value,
    CASE 
        WHEN max_value IS NULL THEN 'unlimited'
        ELSE ROUND(100.0 * last_value / max_value, 2) || '%'
    END AS usage
FROM pg_sequences;
Содержание