Подходит ли платформа для начинающих без опыта работы?

Да, курсы разбиты по уровням: Junior, Middle, Senior. Начинающие могут стартовать с базовых тем Python, Docker и алгоритмов и постепенно двигаться к более сложным темам.

Как быстро можно подготовиться к собеседованию на позицию Junior разработчика?

При занятиях 1–2 часа в день — от 2 до 4 недель на основные темы. Платформа анализирует слабые места по результатам квизов и строит персональный план подготовки.

Какие технологии охватывает платформа?

Python, FastAPI, Django, Docker, алгоритмы и структуры данных, Agile/Scrum, SQL, CI/CD, системный дизайн, код-ревью и более 50 других тем для разработчиков.

Платформа бесплатная?

Большинство учебных материалов и квизов доступны бесплатно после регистрации. Регистрация занимает менее минуты.

Как платформа помогает найти работу программистом?

Платформа даёт фундаментальные знания, которые проверяют на технических собеседованиях: алгоритмы, архитектура, фреймворки. Мок-интервью имитирует реальное собеседование. Система прогресса показывает, какие темы нужно подтянуть перед собеседованием.

dml_operations

DML: Вставка, обновление, удаление

INSERT, UPDATE, DELETE, RETURNING, массовые операции

DML: Вставка, обновление, удаление

DML (Data Manipulation Language) — язык манипулирования данными, набор команд для работы с содержимым таблиц базы данных.

Зачем это нужно? DML — это основа любой работы с данными. Вы будете использовать INSERT, UPDATE и DELETE в каждом проекте. Понимание нюансов этих операций поможет писать эффективный код, избегать блокировок и предотвращать потерю данных.

INSERT — вставка данных

Вставка одной строки

Базовый синтаксис вставки одной записи:

INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 25);

Важные моменты:

Столбцы можно указывать в любом порядке, но значения должны соответствовать
Если столбец не указан, используется значение по умолчанию или NULL
Строковые литералы заключаются в одинарные кавычки 'Alice'

-- Порядок столбцов можно менять
INSERT INTO users (email, name, age)
VALUES ('bob@example.com', 'Bob', 30);

-- Если указаны не все столбцы, остальные будут NULL или DEFAULT
INSERT INTO users (name, email)
VALUES ('Charlie', 'charlie@example.com');
-- age будет NULL (если нет DEFAULT)

Вставка нескольких строк

PostgreSQL позволяет вставлять множество строк одним запросом — это намного эффективнее, чем множество отдельных INSERT:

INSERT INTO users (name, email, age) VALUES
    ('Alice', 'alice@example.com', 25),
    ('Bob', 'bob@example.com', 30),
    ('Charlie', 'charlie@example.com', 35),
    ('Diana', 'diana@example.com', 28);

Производительность:

Способ	Строк в секунду	Когда использовать
Отдельные INSERT	~1,000	1-10 строк
Множественные VALUES	~50,000	10-1000 строк
COPY	~500,000+	1000+ строк

Оптимальный размер: 100-1000 строк в одном INSERT. Больше — может вызвать блокировки и проблемы с памятью.

Вставка со значениями по умолчанию

-- Использовать DEFAULT для всех столбцов
-- (все столбцы должны иметь DEFAULT или быть SERIAL/IDENTITY)
INSERT INTO users DEFAULT VALUES;

-- Использовать DEFAULT для конкретного столбца
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', DEFAULT);

-- Явное указание DEFAULT для нескольких столбцов
INSERT INTO users (name, email, age, status)
VALUES ('Alice', 'alice@example.com', DEFAULT, DEFAULT);

Пример таблицы с DEFAULT:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER DEFAULT 0,
    status TEXT DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Вставка с использованием DEFAULT
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');
-- age=0, status='active', created_at=NOW()

RETURNING — получение данных после вставки

RETURNING позволяет получить данные вставленной строки без дополнительного SELECT:

-- Получить сгенерированный ID
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id;
-- Результат: id = 1

-- Получить несколько столбцов
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, created_at;

-- Получить все столбцы
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING *;

-- Получить вычисляемое значение
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 25)
RETURNING id, age + 1 AS next_age;

Зачем это нужно?

Получить сгенерированный первичный ключ для использования в связанных таблицах
Получить значение created_at без дополнительного запроса
Атомарно получить данные (избегаем race conditions)

Пример на Python:

# Без RETURNING (два запроса, race condition!)
cur.execute("INSERT INTO users (name) VALUES (%s)", ('Alice',))
cur.execute("SELECT id FROM users ORDER BY id DESC LIMIT 1")
user_id = cur.fetchone()[0]

# С RETURNING (один запрос, атомарно)
cur.execute("""
    INSERT INTO users (name) VALUES (%s)
    RETURNING id
""", ('Alice',))
user_id = cur.fetchone()[0]

INSERT ... SELECT

Вставка данных из другой таблицы или результата запроса:

-- Копирование всех данных
INSERT INTO users_backup
SELECT * FROM users;

-- Копирование с фильтрацией
INSERT INTO users_archive
SELECT id, name, email, created_at
FROM users
WHERE created_at < '2025-01-01';

-- С преобразованием данных
INSERT INTO users_summary (user_id, total_orders)
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id;

-- С объединением таблиц
INSERT INTO user_reports (user_id, name, total_spent)
SELECT u.id, u.name, COALESCE(SUM(o.amount), 0)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Важно: Столбцы в SELECT должны соответствовать столбцам в INSERT по порядку и типу данных.

INSERT из VALUES с генерацией данных

-- Генерация тестовых данных
INSERT INTO users (name, email, age)
SELECT 
    'User_' || i,
    'user' || i || '@example.com',
    18 + (i % 50)
FROM generate_series(1, 1000) AS i;

-- Вставка с вычислениями
INSERT INTO products (name, price, discounted_price)
SELECT 
    name,
    price,
    price * 0.9  -- Скидка 10%
FROM raw_products;

UPDATE — обновление данных

Обновление по условию

-- Обновление одного столбца
UPDATE users
SET age = 26
WHERE id = 1;

-- Обновление нескольких столбцов
UPDATE users
SET age = 26,
    email = 'new@example.com',
    updated_at = NOW()
WHERE id = 1;

-- Обновление по нескольким условиям
UPDATE users
SET status = 'premium'
WHERE age >= 18 
  AND country = 'RU'
  AND created_at < '2024-01-01';

⚠️ Критически важно: Всегда используйте WHERE, иначе обновятся все строки в таблице!

-- ОШИБКА: Обновит ВСЕХ пользователей!
UPDATE users SET status = 'inactive';

-- Правильно: Обновит только нужных
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

Обновление с вычислениями

-- Увеличить возраст на 1
UPDATE users SET age = age + 1;

-- Увеличить цену на 10%
UPDATE products SET price = price * 1.1;

-- Применить функцию
UPDATE users SET email = LOWER(email);

-- Конкатенация строк
UPDATE users SET full_name = first_name || ' ' || last_name;

-- Использовать CASE для условного обновления
UPDATE users
SET status = CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
END;

-- CASE с несколькими условиями
UPDATE orders
SET priority = CASE
    WHEN amount > 10000 THEN 'high'
    WHEN amount > 1000 THEN 'medium'
    ELSE 'low'
END;

UPDATE ... FROM

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

-- Простое соединение
UPDATE users u
SET total_spent = o.total
FROM orders o
WHERE u.id = o.user_id;

-- С несколькими таблицами
UPDATE orders o
SET status = 'shipped'
FROM shipments s
WHERE o.id = s.order_id
  AND s.tracking_number IS NOT NULL;

-- С агрегацией (подзапрос)
UPDATE users u
SET order_count = stats.count
FROM (
    SELECT user_id, COUNT(*) as count
    FROM orders
    GROUP BY user_id
) stats
WHERE u.id = stats.user_id;

-- С сложными условиями
UPDATE products p
SET
    last_sale_date = latest.sale_date,
    last_sale_price = latest.price
FROM (
    SELECT product_id, MAX(sale_date) as sale_date, price
    FROM sales
    GROUP BY product_id, price
) latest
WHERE p.id = latest.product_id;

UPDATE с кортежным присваиванием

PostgreSQL позволяет обновлять несколько столбцов одновременно:

-- Обновление нескольких столбцов из подзапроса
UPDATE users u
SET (first_name, last_name, email) = (
    SELECT fn, ln, em
    FROM temp_users t
    WHERE t.id = u.id
)
WHERE EXISTS (
    SELECT 1 FROM temp_users t WHERE t.id = u.id
);

-- Обновление из VALUES
UPDATE users u
SET (age, status) = (v.new_age, v.new_status)
FROM (VALUES 
    (1, 26, 'active'),
    (2, 30, 'inactive'),
    (3, 35, 'active')
) AS v(id, new_age, new_status)
WHERE u.id = v.id;

RETURNING при обновлении

-- Получить обновлённые данные
UPDATE users
SET age = age + 1
WHERE id = 1
RETURNING id, name, age;

-- Получить старые и новые значения
UPDATE users
SET balance = balance - 100
WHERE id = 1 AND balance >= 100
RETURNING 
    id,
    balance + 100 AS old_balance,  -- Значение ДО обновления
    balance AS new_balance;        -- Значение ПОСЛЕ

-- Получить количество обновлённых строк
UPDATE users SET status = 'active' WHERE status = 'pending'
RETURNING COUNT(*) OVER() AS total_updated;

UPDATE с блокировкой строк

-- Блокировка строк при обновлении (предотвращает concurrent updates)
UPDATE accounts
SET balance = balance - 100
WHERE id = 1
RETURNING balance;

-- С явной блокировкой
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

DELETE — удаление данных

Удаление по условию

-- Удаление одной строки
DELETE FROM users WHERE id = 1;

-- Удаление нескольких строк
DELETE FROM users WHERE age < 18;

-- Удаление с несколькими условиями
DELETE FROM orders 
WHERE status = 'cancelled' 
  AND created_at < '2024-01-01';

-- Удаление с возвратом данных
DELETE FROM users
WHERE created_at < '2024-01-01'
RETURNING id, name, email;

-- Удаление с подсчётом удалённых строк
DELETE FROM logs WHERE created_at < '2024-01-01'
RETURNING COUNT(*) OVER() AS deleted_count;

⚠️ Осторожно: DELETE FROM users без WHERE удалит все строки!

-- Удалить ВСЕХ пользователей (часто ошибка!)
DELETE FROM users;

-- Удалить конкретных пользователей
DELETE FROM users WHERE status = 'deleted';

Удаление с использованием USING

USING позволяет использовать данные из других таблиц для определения удаляемых строк:

-- Удаление пользователей с отменёнными заказами
DELETE FROM users u
USING orders o
WHERE u.id = o.user_id
  AND o.status = 'cancelled';

-- Удаление пользователей без заказов (за последний год)
DELETE FROM users u
USING (
    SELECT id FROM users
    EXCEPT
    SELECT DISTINCT user_id FROM orders
    WHERE created_at > NOW() - INTERVAL '1 year'
) inactive
WHERE u.id = inactive.id;

-- Удаление дубликатов (оставить строку с минимальным ID)
DELETE FROM users u1
USING users u2
WHERE u1.id > u2.id
  AND u1.email = u2.email;

-- Удаление старых записей из связанной таблицы
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id
  AND o.status = 'cancelled'
  AND o.created_at < '2023-01-01';

Удаление дубликатов

Различные стратегии удаления дубликатов:

-- Стратегия 1: Оставить строку с минимальным ID
DELETE FROM users u1
USING users u2
WHERE u1.id > u2.id 
  AND u1.email = u2.email;

-- Стратегия 2: Оставить строку с максимальным ID
DELETE FROM users u1
USING users u2
WHERE u1.id < u2.id 
  AND u1.email = u2.email;

-- Стратегия 3: Оставить последнюю по дате
DELETE FROM users u1
USING users u2
WHERE u1.created_at < u2.created_at 
  AND u1.email = u2.email;

-- Стратегия 4: Использовать оконные функции
WITH duplicates AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);

ON CONFLICT — обработка дубликатов (UPSERT)

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

ON CONFLICT позволяет обработать нарушение уникального ограничения:

-- Обновить при конфликте
INSERT INTO users (email, name, created_at)
VALUES ('alice@example.com', 'Alice', NOW())
ON CONFLICT (email)              -- Уникальное ограничение
DO UPDATE SET
    name = EXCLUDED.name,        -- EXCLUDED — данные из INSERT
    updated_at = NOW()
RETURNING id, name, updated_at;

-- Игнорировать дубликат
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;
-- Ничего не возвращает, если был конфликт

-- Получить информацию о конфликте
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING
RETURNING *;
-- Вернёт строку только если вставка успешна

EXCLUDED — это псевдотаблица, содержащая строки, которые пытались вставить.

Частичный UPSERT

Обновление только при выполнении условия:

-- Обновить только если пользователь не активен
INSERT INTO users (email, is_active, name)
VALUES ('alice@example.com', TRUE, 'Alice')
ON CONFLICT (email)
DO UPDATE SET
    is_active = EXCLUDED.is_active,
    name = EXCLUDED.name,
    updated_at = NOW()
WHERE users.is_active = FALSE;  -- Условие на существующую строку

-- Обновить только если новое значение "новее"
INSERT INTO sensor_data (sensor_id, value, recorded_at)
VALUES (1, 25.5, NOW())
ON CONFLICT (sensor_id)
DO UPDATE SET
    value = EXCLUDED.value,
    recorded_at = EXCLUDED.recorded_at
WHERE EXCLUDED.recorded_at > sensor_data.recorded_at;

Конфликт по уникальному индексу

-- По имени ограничения
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT CONSTRAINT users_email_key
DO UPDATE SET name = EXCLUDED.name;

-- По столбцам (PostgreSQL найдёт ограничение)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- По выражению (частичный уникальный индекс)
-- Индекс: CREATE UNIQUE INDEX idx_active_email ON users (email) WHERE is_active = true;
INSERT INTO users (email, is_active)
VALUES ('alice@example.com', true)
ON CONFLICT (email) WHERE (is_active = true)
DO UPDATE SET is_active = EXCLUDED.is_active;

Практические примеры UPSERT

-- Счётчик посещений страниц
INSERT INTO page_visits (page_url, visit_count, last_visited)
VALUES ('/products/123', 1, NOW())
ON CONFLICT (page_url)
DO UPDATE SET
    visit_count = page_visits.visit_count + 1,
    last_visited = NOW()
RETURNING visit_count;

-- Кэш результатов
INSERT INTO query_cache (query_hash, result, expires_at)
VALUES ('abc123', '{"data": [...]}', NOW() + INTERVAL '1 hour')
ON CONFLICT (query_hash)
DO UPDATE SET
    result = EXCLUDED.result,
    expires_at = EXCLUDED.expires_at
WHERE EXCLUDED.expires_at > query_cache.expires_at;

-- Синхронизация данных из внешнего источника
INSERT INTO products (external_id, name, price, synced_at)
VALUES (42, 'Widget', 99.99, NOW())
ON CONFLICT (external_id)
DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    synced_at = NOW()
WHERE products.price IS DISTINCT FROM EXCLUDED.price
   OR products.name IS DISTINCT FROM EXCLUDED.name;

Массовые операции

Массовая вставка

-- Множественные VALUES (до 65535 строк за раз)
INSERT INTO users (name, email) VALUES
    ('User 1', 'user1@example.com'),
    ('User 2', 'user2@example.com'),
    ('User 3', 'user3@example.com'),
    -- ...
    ('User N', 'userN@example.com');

-- Из массива (PostgreSQL)
INSERT INTO users (name)
SELECT unnest(ARRAY['Alice', 'Bob', 'Charlie']);

-- Генерация данных
INSERT INTO test_data (id, value)
SELECT i, random() * 100
FROM generate_series(1, 10000) AS i;

COPY для очень больших объёмов:

-- Импорт из CSV
COPY users (name, email, age)
FROM '/path/to/users.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- Экспорт в CSV
COPY (SELECT * FROM users WHERE active = true)
TO '/path/to/active_users.csv'
WITH (FORMAT csv, HEADER true);

Массовое обновление

-- Обновление по CASE
UPDATE users
SET status = CASE id
    WHEN 1 THEN 'active'
    WHEN 2 THEN 'inactive'
    WHEN 3 THEN 'pending'
    ELSE status  -- Оставить без изменений
END
WHERE id IN (1, 2, 3);

-- Обновление из VALUES (эффективно для многих строк)
UPDATE users u
SET status = v.status
FROM (VALUES 
    (1, 'active'),
    (2, 'inactive'),
    (3, 'pending')
) AS v(id, status)
WHERE u.id = v.id;

-- Пакетное обновление с временной таблицей
CREATE TEMP TABLE temp_updates (id INTEGER, new_status TEXT);
INSERT INTO temp_updates VALUES (1, 'active'), (2, 'inactive');

UPDATE users u
SET status = t.new_status
FROM temp_updates t
WHERE u.id = t.id;

DROP TABLE temp_updates;

Массовое удаление

-- Удаление по диапазону
DELETE FROM users WHERE id BETWEEN 1 AND 1000;

-- Удаление по списку
DELETE FROM users WHERE id IN (1, 5, 10, 15, 20);

-- Удаление с LIMIT (постепенное удаление)
DELETE FROM users
WHERE id IN (
    SELECT id FROM users 
    WHERE status = 'deleted' 
    LIMIT 1000
);

-- Удаление по условию с JOIN
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id
  AND o.created_at < '2023-01-01';

Пакетная обработка больших объёмов

-- Функция для пакетного удаления
CREATE OR REPLACE FUNCTION batch_delete(table_name TEXT, condition TEXT, batch_size INTEGER)
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER := 0;
    total_deleted INTEGER := 0;
BEGIN
    LOOP
        EXECUTE format(
            'DELETE FROM %I WHERE %s AND ctid IN (SELECT ctid FROM %I WHERE %s LIMIT %L)',
            table_name, condition, table_name, condition, batch_size
        );
        
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        total_deleted := total_deleted + deleted_count;
        
        EXIT WHEN deleted_count = 0;
        
        -- Пауза для снижения нагрузки
        PERFORM pg_sleep(0.1);
    END LOOP;
    
    RETURN total_deleted;
END;
$$ LANGUAGE plpgsql;

-- Использование
SELECT batch_delete('logs', 'created_at < ''2023-01-01''', 1000);

Сравнение DELETE и TRUNCATE

Характеристика	DELETE	TRUNCATE
Тип операции	DML	DDL
WHERE	✅ Поддерживается	❌ Не поддерживается
Скорость	🐌 Медленнее (логирует каждую строку)	⚡ Быстрее (сбрасывает таблицу)
Логирование	Полное (каждая строка в WAL)	Минимальное (только факт)
Триггеры	✅ Вызывает ON DELETE	❌ Не вызывает
Возврат строк	✅ RETURNING	❌ Нельзя
Блокировка	Строк (ROW)	Таблицы (TABLE)
Место на диске	❌ Не освобождает сразу	✅ Освобождает
Foreign Keys	Работает с ON DELETE	Требует CASCADE
Permissions	DELETE privilege	DROP privilege

-- DELETE — для выборочного удаления
DELETE FROM users WHERE age < 18;
DELETE FROM logs WHERE created_at < '2023-01-01';

-- TRUNCATE — для полной очистки
TRUNCATE TABLE users;                    -- Очистить таблицу
TRUNCATE TABLE users, orders;            -- Несколько таблиц
TRUNCATE TABLE users RESTART IDENTITY;   -- Сбросить автоинкремент
TRUNCATE TABLE users CASCADE;            -- С каскадом на FK

Когда использовать:

Сценарий	Команда
Удалить часть строк	`DELETE`
Удалить всё, но нужны триггеры	`DELETE`
Удалить всё, важна скорость	`TRUNCATE`
Удалить всё и сбросить ID	`TRUNCATE ... RESTART IDENTITY`
Удалить из таблицы с FK	`DELETE` или `TRUNCATE ... CASCADE`

CTE (Common Table Expressions) в DML

CTE позволяют создавать сложные цепочки операций:

WITH для INSERT

-- Вставка с предварительной обработкой
WITH new_users AS (
    SELECT 
        LOWER(email) as email,
        INITCAP(name) as name,
        NOW() as created_at
    FROM (VALUES 
        ('ALICE@EXAMPLE.COM', 'alice'),
        ('BOB@EXAMPLE.COM', 'bob')
    ) AS raw(email, name)
)
INSERT INTO users (email, name, created_at)
SELECT email, name, created_at FROM new_users
RETURNING *;

WITH для UPDATE

-- Обновление с подсчётом
WITH updated AS (
    UPDATE users
    SET status = 'active'
    WHERE last_login > NOW() - INTERVAL '30 days'
    RETURNING id
)
SELECT COUNT(*) AS activated_count FROM updated;

WITH для DELETE

-- Архивация перед удалением
WITH archived AS (
    INSERT INTO users_archive (id, name, email, archived_at)
    SELECT id, name, email, NOW()
    FROM users
    WHERE created_at < '2024-01-01'
    RETURNING id
)
DELETE FROM users
WHERE id IN (SELECT id FROM archived)
RETURNING COUNT(*) AS deleted_count;

Цепочка операций

-- Комплексная операция: архивация → удаление → отчёт
WITH 
-- Шаг 1: Архивировать старые заказы
archived AS (
    INSERT INTO orders_archive
    SELECT *, NOW() as archived_at
    FROM orders
    WHERE status = 'completed' 
      AND created_at < '2023-01-01'
    RETURNING id
),
-- Шаг 2: Удалить архивированные заказы
deleted_items AS (
    DELETE FROM order_items
    WHERE order_id IN (SELECT id FROM archived)
    RETURNING order_id
),
deleted_orders AS (
    DELETE FROM orders
    WHERE id IN (SELECT id FROM archived)
    RETURNING id
)
-- Шаг 3: Вернуть статистику
SELECT 
    (SELECT COUNT(*) FROM archived) AS archived_count,
    (SELECT COUNT(DISTINCT order_id) FROM deleted_items) AS items_deleted,
    (SELECT COUNT(*) FROM deleted_orders) AS orders_deleted;

Транзакционность DML

Все DML-операции выполняются внутри транзакций:

-- Явная транзакция
BEGIN;

INSERT INTO accounts (id, balance) VALUES (1, 1000);
INSERT INTO accounts (id, balance) VALUES (2, 0);

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

COMMIT;  -- Или ROLLBACK при ошибке

-- Автоматическая транзакция (каждый запрос отдельно)
INSERT INTO users (name) VALUES ('Alice');  -- Авто-COMMIT
UPDATE users SET age = 25 WHERE name = 'Alice';  -- Авто-COMMIT

Важность транзакций:

-- Без транзакции (опасно!)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Сбой питания здесь!
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Деньги исчезли!

-- С транзакцией (безопасно)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Или ROLLBACK при любой ошибке

Производительность DML

Оптимизация INSERT

-- 1. Отключить индексы перед массовой вставкой (если возможно)
-- 2. Использовать COPY вместо INSERT для больших объёмов
COPY users (name, email) FROM '/data/users.csv' WITH (FORMAT csv);

-- 3. Пакетная вставка
INSERT INTO users (name, email) VALUES 
    (...), (...), ...  -- 100-1000 строк

-- 4. Отложить создание индексов
CREATE TABLE users_temp AS SELECT ...;
CREATE INDEX ON users_temp (email);
ALTER TABLE users_temp RENAME TO users;

-- 5. Настроить параметры для массовой вставки
SET synchronous_commit = OFF;  -- Быстрее, но риск потери данных
SET wal_level = minimal;

Оптимизация UPDATE

-- 1. Обновлять только нужные строки
UPDATE users SET status = 'active' WHERE status = 'pending';  -- Не все!

-- 2. Использовать индекс в WHERE
CREATE INDEX ON users (status);
UPDATE users SET status = 'active' WHERE status = 'pending';

-- 3. Избегать обновлений без изменений
UPDATE users SET status = 'active' WHERE status = 'active';  -- Бесполезно!

-- 4. Пакетное обновление для больших таблиц
UPDATE users u
SET last_name = v.last_name
FROM temp_updates v
WHERE u.id = v.id;

Оптимизация DELETE

-- 1. Удалять пакетами
DELETE FROM logs 
WHERE created_at < '2023-01-01'
AND ctid IN (SELECT ctid FROM logs WHERE created_at < '2023-01-01' LIMIT 1000);

-- 2. Использовать PARTITION DROP вместо DELETE
-- Создать партиции по дате, затем DROP старой партиции
DROP TABLE logs_2022;  -- Мгновенно!

-- 3. VACUUM после массового удаления
DELETE FROM large_table WHERE condition;
VACUUM large_table;  -- Освободить место

Диагностика и отладка

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

-- GET DIAGNOSTICS в PL/pgSQL
DO $$
DECLARE
    row_count INTEGER;
BEGIN
    UPDATE users SET status = 'active';
    GET DIAGNOSTICS row_count = ROW_COUNT;
    RAISE NOTICE 'Обновлено строк: %', row_count;
END;
$$;

-- В приложении (Python/psycopg2)
cur.execute("UPDATE users SET status = 'active'")
print(f"Обновлено строк: {cur.rowcount}")

Мониторинг DML-операций

-- Текущие блокировки
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.pid != blocked_locks.pid
WHERE NOT blocked_locks.granted;

-- Долгие запросы
SELECT 
    pid,
    NOW() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND NOW() - query_start > INTERVAL '1 minute'
ORDER BY duration DESC;

Чек-лист: выбор правильного подхода

Задача	Решение
Вставить одну строку	`INSERT ... VALUES`
Вставить много строк	`INSERT ... VALUES` (пакет) или `COPY`
Вставить или обновить	`INSERT ... ON CONFLICT`
Обновить по условию	`UPDATE ... WHERE`
Обновить из другой таблицы	`UPDATE ... FROM`
Удалить выборочно	`DELETE ... WHERE`
Удалить всё	`TRUNCATE`
Удалить дубликаты	`DELETE ... USING` или оконные функции
Получить вставленные данные	`INSERT ... RETURNING`
Архивировать и удалить	CTE с `INSERT` + `DELETE`

Контрольные вопросы

В чём разница между DEFAULT VALUES и указанием DEFAULT для столбца?
- DEFAULT VALUES вставляет значения по умолчанию во все столбцы
- DEFAULT для столбца использует значение по умолчанию только для этого столбца
Что такое EXCLUDED в ON CONFLICT?
- Псевдотаблица с данными, которые пытались вставить
Когда использовать TRUNCATE вместо DELETE?
- Когда нужно удалить все строки быстро и не нужны триггеры
Зачем нужен RETURNING?
- Получить данные изменённых строк без дополнительного SELECT
Как безопасно удалить дубликаты?
- Использовать DELETE ... USING с условием на ID или оконные функции

Готовы применить знания на практике? Переходите к упражнениям!

Проверьте свои знания

Вопросы ещё не добавлены

Вопросы для этой подтемы ещё не добавлены.

Способ

Строк в секунду

Когда использовать

Отдельные INSERT

~1,000

1-10 строк

Множественные VALUES

~50,000

10-1000 строк

COPY

~500,000+

1000+ строк

Характеристика

DELETE

TRUNCATE

Тип операции

DML

DDL

WHERE

✅ Поддерживается

❌ Не поддерживается

Скорость

🐌 Медленнее (логирует каждую строку)

⚡ Быстрее (сбрасывает таблицу)

Логирование

Полное (каждая строка в WAL)

Минимальное (только факт)

Триггеры

✅ Вызывает ON DELETE

❌ Не вызывает

Возврат строк

✅ RETURNING

❌ Нельзя

Блокировка

Строк (ROW)

Таблицы (TABLE)

Место на диске

❌ Не освобождает сразу

✅ Освобождает

Foreign Keys

Работает с ON DELETE

Требует CASCADE

Permissions

DELETE privilege

DROP privilege

Сценарий

Команда

Удалить часть строк

DELETE

Удалить всё, но нужны триггеры

DELETE

Удалить всё, важна скорость

TRUNCATE

Удалить всё и сбросить ID

TRUNCATE ... RESTART IDENTITY

Удалить из таблицы с FK

DELETE или TRUNCATE ... CASCADE

Задача

Решение

Вставить одну строку

INSERT ... VALUES

Вставить много строк

INSERT ... VALUES (пакет) или COPY

Вставить или обновить

INSERT ... ON CONFLICT

Обновить по условию

UPDATE ... WHERE

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

UPDATE ... FROM

Удалить выборочно

DELETE ... WHERE

Удалить всё

TRUNCATE

Удалить дубликаты

DELETE ... USING или оконные функции

Получить вставленные данные

INSERT ... RETURNING

Архивировать и удалить

CTE с INSERT + DELETE