Что такое OLTP

OLTP — Online Transaction Processing. Это базы данных, обслуживающие живые бизнес-операции в реальном времени. Каждая web-форма, каждый клик в банковском приложении, каждый заказ на маркетплейсе порождает несколько OLTP-операций: вставить запись, обновить остаток, проверить статус. Миллионы пользователей, тысячи операций в секунду, каждая операция — маленькая.

Канонические примеры OLTP-баз:

• PostgreSQL — open-source, индустриальный стандарт для новых проектов.
• MySQL / MariaDB — historic giant, всё ещё massive install base.
• Oracle Database — enterprise legacy, банки и государство.
• Microsoft SQL Server — корпоративный сегмент Microsoft-стэка.
• SQLite — embedded, на устройствах и в мелких приложениях.

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

ACID: четыре буквы, на которых держится OLTP

ACID — аббревиатура свойств транзакции. Это контракт, который OLTP-база даёт приложению.

A — Atomicity (атомарность). Транзакция либо выполняется целиком, либо никак. Если в середине что-то пошло не так — все изменения откатываются. Перевод денег между счетами: либо “списали с A + зачислили на B” целиком, либо вообще ничего. Не может быть промежуточного состояния “списали, но не зачислили”.

C — Consistency (согласованность). После транзакции данные удовлетворяют всем ограничениям (foreign keys, unique constraints, check constraints, бизнес-инварианты). База не позволит привести данные в нелегальное состояние.

I — Isolation (изоляция). Параллельные транзакции не “видят” промежуточных состояний друг друга. Если 100 пользователей одновременно покупают последний товар, только один получит его, остальные увидят “out of stock”. Никто не увидит “товар уже куплен, но ещё в наличии”.

D — Durability (долговечность). После того как транзакция подтверждена (COMMIT), данные сохраняются — даже если через секунду упадёт сервер. Реализуется через write-ahead log (WAL), который сначала пишется на диск, потом изменения применяются в основные структуры.

-- Классический пример: перевод между счетами
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

Если между двумя UPDATE сервер упадёт — транзакция не закоммитится, обе строки останутся как были. Это атомарность. Если в момент commit-а другой пользователь делает SELECT — он увидит либо обе строки до изменения, либо обе после. Это изоляция.

Уровни изоляции

Изоляция — самое сложное из ACID, потому что строгая изоляция жертвует параллелизмом. Поэтому SQL-стандарт определяет четыре уровня:

Dirty read — увидел незакоммиченные изменения другой транзакции. Большинство баз даже на минимальном уровне это не допускают.

Non-repeatable read — два раза прочитал одну строку, получил разные значения (другая транзакция в это время её изменила и закоммитила).

Phantom read — два раза выполнил SELECT ... WHERE, в результате появились новые строки (другая транзакция вставила).

В реальной разработке: 95% времени достаточно READ COMMITTED. SERIALIZABLE нужен в финансовых операциях, где даже редкие аномалии недопустимы. Углубление в sql-fundamentals и sql-internals.

Нормализация

OLTP-схемы обычно нормализованы. Это значит: данные разложены по таблицам так, чтобы каждый факт хранился в одном месте. Никакой дубликации.

Пример: таблица заказов в денормализованном виде:

orders:
  order_id | customer_name | customer_email | item_name | item_price | qty
  1001     | John Smith    | [email protected]     | iPhone    | 1000       | 1
  1002     | John Smith    | [email protected]     | iPad      | 800        | 1

Если John изменит email, нужно обновить все его заказы. Если iPhone подорожает — обновить везде. Это анти-паттерн.

Нормализованная версия:

customers:
  customer_id | name       | email
  42          | John Smith | [email protected]

products:
  product_id | name   | price
  1          | iPhone | 1000
  2          | iPad   | 800

orders:
  order_id | customer_id | product_id | qty
  1001     | 42          | 1          | 1
  1002     | 42          | 2          | 1

Каждый факт — в одном месте. Email John-а — только в customers. Цена iPhone — только в products. Изменение требует UPDATE одной строки.

Это 3NF (Third Normal Form) — третья нормальная форма. Существуют 4NF, 5NF, BCNF — более строгие формы. Для OLTP обычно достаточно 3NF.

Цена нормализации: запросы становятся сложнее, требуют JOIN-ов:

SELECT o.order_id, c.name, p.name, p.price, o.qty
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_id = 1001;

JOIN — это дорогая операция, но OLTP-движки оптимизированы под неё через индексы.

B-tree индексы

Индекс — это вспомогательная структура, ускоряющая поиск. В OLTP стандарт — B-tree (Balanced Tree). Это сбалансированное дерево, где каждый узел хранит ключи и указатели на следующие узлы или строки.

Простыми словами: вместо того чтобы сканить всю таблицу в поисках WHERE customer_id = 42, движок идёт по дереву от корня к листу, делая O(log n) сравнений. Для миллиарда строк это ~30 шагов вместо миллиарда — выигрыш на много порядков.

CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Без индекса: full table scan, O(n)
-- С индексом: B-tree lookup, O(log n)
SELECT * FROM orders WHERE customer_id = 42;

B-tree хорош для:

• Точечных запросов: WHERE id = X
• Диапазонов: WHERE created_at BETWEEN ... AND ...
• Сортировки: ORDER BY ... (если индекс совпадает с сортировкой — sort пропускается)

Плох для:

• Полнотекстового поиска (нужен GIN/GiST/full-text)
• Геопоиска (нужен GiST/SP-GiST)
• LIKE ‘%substring%’ (не использует индекс)

Размер запросов: маленькие и быстрые

OLTP-запросы характерны:

• Читают/обновляют малое число строк (1-100).
• Используют точечные индексные lookup-ы.
• Возвращают результат за миллисекунды.
• Идут сотнями-тысячами в секунду.

Типичные запросы:

-- Авторизация пользователя
SELECT id, password_hash FROM users WHERE email = '[email protected]';

-- Получить заказ
SELECT * FROM orders WHERE order_id = 1001;

-- Обновить статус
UPDATE orders SET status = 'shipped' WHERE order_id = 1001;

-- Добавить заказ
INSERT INTO orders (customer_id, amount, status) VALUES (42, 1000, 'pending');

Если запрос сканит миллион строк или джойнит десятки таблиц — это не OLTP, это аналитика. Аналитика разрушает OLTP-производительность: один тяжёлый SELECT может заблокировать таблицу для writes.

Что внутри: shared buffers, WAL, MVCC

Postgres (и большинство OLTP-баз) устроен так:

• Shared buffers — кэш страниц в RAM. Read-heavy workload работает быстро, потому что hot data в памяти.
• WAL (Write-Ahead Log) — последовательный лог изменений. Сначала пишется на диск (sequential write — быстро), потом изменения применяются в основные таблицы. Это даёт durability и crash recovery.
• MVCC (Multi-Version Concurrency Control) — каждое изменение создаёт новую версию строки, старая остаётся до тех пор, пока её “видят” активные транзакции. Это позволяет читать без блокировок (readers не блокируют writers).
• B-tree, GIN, BRIN индексы — структуры для быстрого поиска.

Почему OLTP плох для аналитики

Простой пример: на OLTP-базе с 100 GB заказов запросить “сумма продаж по странам за всю историю” — это full table scan, миллионы JOIN-ов, минуты выполнения, и пока он идёт — деградация всех остальных запросов. OLTP-движки не оптимизированы для:

1. Полных сканов больших таблиц (нет колоночного хранения).
2. Агрегации миллионов строк (B-tree не помогает).
3. JOIN-ов огромных таблиц (нет MPP).

Поэтому в любой нетривиальной системе аналитика выносится на отдельную OLAP-систему — Snowflake, BigQuery, ClickHouse, Redshift. OLTP-база остаётся “тонкой”: быстрая, малые операции, никакой аналитики. Это разделение — основа современной data architecture.

Реальные числа

Хорошо настроенная OLTP-база на современном железе:

• Postgres: 50K-100K TPS (транзакций в секунду) с правильной конфигурацией.
• MySQL InnoDB: похоже, 30K-80K TPS.
• Oracle: 100K-200K TPS на enterprise hardware.

Это для простых транзакций (одна строка, одна таблица). Сложные транзакции с несколькими JOIN-ами — десятки TPS, не больше.

Объём данных: OLTP комфортно работает до 1-10 TB. Дальше начинаются проблемы с VACUUM, размером индексов, временем восстановления. Один из паттернов — архивирование старых данных в отдельную OLAP-систему, оставление в OLTP только последних 6-12 месяцев.

Попробуй сам

1. Установи Postgres локально. Создай таблицу orders с 1 млн строк. Замерь время SELECT * WHERE order_id = X (B-tree lookup) и сравни с WHERE comments LIKE '%test%' (full scan).
2. Открой две psql-сессии. В одной начни транзакцию BEGIN; UPDATE accounts SET balance = ... WHERE id = 1;. В другой попробуй прочитать ту же строку. Понаблюдай поведение в разных isolation levels (SET TRANSACTION ISOLATION LEVEL ...).
3. Создай неиндексированную таблицу с 10 млн строк, выполни запрос с WHERE — замерь время. Создай индекс — замерь повторно. Объясни разницу.
4. Изучи EXPLAIN ANALYZE для своих запросов. Найди запрос с Seq Scan и оптимизируй индексом до Index Scan.