category_id (PK, INT, AUTO_INCREMENT) — уникальный ID категории,
name (VARCHAR) — наименование,
parent_id (FK → categories.category_id) — родительская категория (NULL = корень),
path (VARCHAR) — путь в дереве (например, /1/5/23/) — для быстрого поиска поддерева,
level (INT) — уровень вложенности (0 = корень).
product_id (PK, INT, AUTO_INCREMENT) — уникальный ID товара,
sku (VARCHAR, UNIQUE) — артикул,
name (VARCHAR) — наименование,
quantity (INT, CHECK ≥ 0) — остаток на складе,
price (NUMERIC(12,2), CHECK ≥ 0) — цена,
category_id (FK → categories.category_id) — категория товара,
root_category_id (FK → categories.category_id) — корневая категория (для оптимизации отчётов).
client_id (PK, INT, AUTO_INCREMENT) — уникальный ID клиента,
name (VARCHAR) — наименование,
address (TEXT) — адрес,
email, phone — контактные данные.
order_statuses
status_id (PK, SMALLINT) — ID статуса,
status_name (VARCHAR, UNIQUE) — наименование (например, "новый", "подтверждён", "отменён").
order_id (PK, INT, AUTO_INCREMENT) — уникальный ID заказа,
client_id (FK → clients.client_id) — клиент,
status_id (FK → order_statuses.status_id) — статус,
order_date (TIMESTAMP) — дата создания,
total_sum (NUMERIC(14,2)) — сумма заказа.
order_item_id (PK, INT, AUTO_INCREMENT) — уникальный ID позиции,
order_id (FK → orders.order_id) — заказ,
product_id (FK → products.product_id) — товар,
quantity (INT, CHECK > 0) — количество,
price_at_order (NUMERIC(12,2), CHECK ≥ 0) — цена на момент заказа.
year_month (CHAR(7)) — месяц (например, "2024-09"),
product_id (FK → products.product_id),
root_category_id (FK → categories.category_id),
total_quantity (INT) — общее количество проданного за месяц,
last_updated (TIMESTAMP) — время обновления.
job_name (TEXT, PK) — имя задачи (например, "monthly_sales"),
last_processed_order_date (TIMESTAMP) — дата последнего обновления агрегатов.
CREATE OR REPLACE VIEW top_5_products_last_month AS
SELECT
p.name AS "Наименование товара",
root_cat.name AS "Категория 1-го уровня",
SUM(oi.quantity) AS "Общее количество проданных штук"
FROM
order_items oi
JOIN
orders o ON oi.order_id = o.order_id
JOIN
products p ON oi.product_id = p.product_id
JOIN
categories cat ON p.category_id = cat.category_id
JOIN
categories root_cat ON root_cat.category_id =
CAST(
split_part(trim(leading '/' from trim(trailing '/' from cat.path)), '/', 1)
AS INTEGER
)
WHERE
o.order_date >= date_trunc('month', CURRENT_DATE)
AND o.order_date < date_trunc('month', CURRENT_DATE) + INTERVAL '1 month'
AND o.status_id IN (2, 3, 4, 5, 6)
GROUP BY
p.product_id,
p.name,
root_cat.name
ORDER BY
"Общее количество проданных штук" DESC
LIMIT 5;- Вычисление корневой категории через split_part
Невозможно использовать индекс → full scan (categories) для каждой строки → O(N^2)
- Агрегация по сырым данным order_items.
При 300 000+ позиций в месяц → тяжёлая GROUP BY и SUM, следовательно высокая нагрузка на CPU и I/O (время выполнения от 1 до 10 сек)
- Отсутствие покрывающих индексов
Фильтр по order_date + status_id → выполняется полное сканирование orders
- Отсутствие кэширования
Каждый вызов выполняет тяжёлый запрос → не масштабируется
- Нет партиционирования
Время сканирования растёт линейно с объёмом данных
-
Добавление root_category_id в products - убирает split_part → JOIN по индексу → O(1).
-
Отдельная таблица monthly_product_sales с предварительно посчитанными суммами, обновлять через Celery, из-за этого чтение идет не миллионами строк, а 100 строк к примеру раз в час.
-
Сделать индексы для агрегации и фильтров заказов.
-
Кэширование на уровне приложения, чтобы не делать запросы к БД, и хранить данные в кэше, следовательно околонулевая нагрузка на БД и запросы идут из кэша.
-
Можно использовать партиционирование при количестве строк > 1 млн.
Предложенные оптимизации вносят умеренную денормализацию и асинхронную агрегацию
SELECT
products.name AS "Наименование товара",
categories.name AS "Категория 1-го уровня",
monthly_product_sales.total_quantity AS "Общее количество проданных штук"
FROM
monthly_product_sales
JOIN products ON monthly_product_sales.product_id = products.product_id
JOIN categories ON monthly_product_sales.root_category_id = categories.category_id
WHERE
monthly_product_sales.year_month = to_char(CURRENT_DATE, 'YYYY-MM')
ORDER BY
monthly_product_sales.total_quantity DESC
LIMIT 5;🛠️ Стек:
Python 3.11
FastAPI — фреймворк
SQLAlchemy Core — работа с БД (без ORM-моделей)
PostgreSQL — основная БД
Redis — кэширование
Celery — фоновые задачи
Docker / Docker Compose — контейнеризация
Flyway — миграции
POST /orders/{order_id}/items
{
"product_id": 1,
"quantity": 2
}Проверяет: Заказ существует и статус = "новый",
Товар существует,
Есть ли товар на складе,
Если товар уже есть в заказе → увеличивает количество,
Если нет → создаёт новую позицию.
{
"order_item_id": 5,
"order_id": 10,
"product_id": 1,
"quantity": 3,
"price_at_order": 79990.0
}GET /orders/top5
Возвращает ТОП-5 самых покупаемых товаров за текущий месяц,
Читает из предварительно посчитанных агрегатов (monthly_product_sales),
Использует Redis-кэширование (15 минут)
[
{
"Наименование товара": "Samsung Galaxy S24",
"Категория 1-го уровня": "Электроника",
"Общее количество проданных штук": 10
}
]Flyway — управляет миграциями,
Celery + Redis — фоновое обновление агрегатов (раз в час),
SQLAlchemy Core — безопасные запросы к существующей схеме,
Кэширование — уменьшает нагрузку на БД,
Docker Compose — запускает весь стек (PostgreSQL, Redis, FastAPI, Celery).
Запуск: docker-compose up -d,
Документация API: http://localhost:8000/docs,
Отчёт: http://localhost:8000/orders/top5,
Добавление товара: POST http://localhost:8000/orders/1/items,
Мониторинг Celery: http://localhost:5555.