26 KiB
26 KiB
🔍 Анализ базы данных Mnemo Cards
Дата анализа: 14 декабря 2025
Версия схемы: 1
База данных: PostgreSQL 16 + Drift ORM
📊 Общая оценка
| Категория | Оценка | Комментарий |
|---|---|---|
| Структура схемы | ⭐⭐⭐⚪⚪ | Хорошая основа, но есть проблемы с нормализацией |
| Производительность | ⭐⭐⭐⭐⚪ | Индексы созданы, но можно оптимизировать |
| Целостность данных | ⭐⭐⭐⚪⚪ | Есть deprecated поля и проблемы с NULL |
| Масштабируемость | ⭐⭐⭐⚪⚪ | Денормализация может стать проблемой |
| Безопасность | ⭐⭐⚪⚪⚪ | Отсутствует audit trail и RLS |
Общая оценка: 3.2/5 - База данных функциональна, но требует улучшений для production-ready состояния.
🚨 Критические проблемы (приоритет: ВЫСОКИЙ)
1. Использование TEXT вместо UUID для ID
Проблема:
// Текущая реализация
TextColumn get id => text()
.withDefault(const CustomExpression('gen_random_uuid()::text'))();
Почему это плохо:
- ❌ UUID хранится как TEXT (36 байт) вместо нативного UUID (16 байт) — потеря 55% места
- ❌ Медленнее индексирование и сравнение
- ❌ Нет встроенной валидации UUID формата
- ❌ Больший размер индексов
Рекомендация:
// Использовать нативный UUID тип PostgreSQL
import 'package:postgres/postgres.dart' show PgDataType;
class Users extends Table {
Column<PgUuid> get id => customType(PgTypes.uuid)
.withDefault(const CustomExpression('gen_random_uuid()'))
.clientDefault(generateUuid)();
// ...
}
Воздействие: Экономия ~40% места в индексах, ускорение JOIN на ~20-30%
2. Денормализация в UserDatas
Проблема:
// UserDatas содержит большие JSON массивы
TextColumn get words => text()
.withDefault(const Constant('[]'))
.map(const JsonListConverter())(); // Может быть огромным!
TextColumn get packProgress => text()
.withDefault(const Constant('[]'))
.map(const JsonListConverter())(); // Дублирует данные
TextColumn get achievements => text()
.withDefault(const Constant('[]'))
.map(const JsonListConverter())(); // Уже есть таблица UserAchievements!
Почему это плохо:
- ❌ Невозможно индексировать элементы внутри JSON
- ❌ Сложно делать JOIN и агрегации
- ❌ Большой размер строки → медленные UPDATE/SELECT
- ❌ Дублирование данных (achievements уже в отдельной таблице)
Рекомендация:
2.1 Создать таблицу WordStatistics
class WordStatistics extends Table {
TextColumn get id => text()
.withDefault(const CustomExpression('gen_random_uuid()::text'))();
TextColumn get userId => text()
.references(Users, #id, onDelete: KeyAction.cascade)();
TextColumn get cardId => text()
.references(GameCards, #id, onDelete: KeyAction.cascade)();
IntColumn get correctAnswers => integer().withDefault(const Constant(0))();
IntColumn get incorrectAnswers => integer().withDefault(const Constant(0))();
RealColumn get mastery => real().withDefault(const Constant(0.0))();
Column<PgDateTime> get lastReviewed => customType(PgTypes.timestampWithTimezone)
.nullable()();
Column<PgDateTime> get nextReview => customType(PgTypes.timestampWithTimezone)
.nullable()();
@override
Set<Column> get primaryKey => {id};
}
2.2 Удалить дублирующиеся JSON поля
class UserDatas extends Table {
// УДАЛИТЬ:
// TextColumn get words => ...
// TextColumn get achievements => ... // Уже есть UserAchievements таблица!
// ОСТАВИТЬ только то, что действительно нужно как JSON:
TextColumn get categoryMinutes => text() // Может быть JSON для гибкости
.withDefault(const Constant('{}'))
.map(const JsonMapConverter())();
}
Воздействие:
- Ускорение SELECT на ~50% (меньше данных)
- Возможность эффективных запросов по словам
- Правильная нормализация
3. Дублирование связей Pack ↔ Cards
Проблема:
// GameCards уже имеет packId
class GameCards extends Table {
TextColumn get packId => text()
.references(CardPacks, #id, onDelete: KeyAction.cascade)();
// ...
}
// Но также есть отдельная таблица many-to-many
class CardPackCards extends Table {
TextColumn get packId => text()
.references(CardPacks, #id, onDelete: KeyAction.cascade)();
TextColumn get cardId => text()
.references(GameCards, #id, onDelete: KeyAction.cascade)();
// ...
}
Почему это плохо:
- ❌ Избыточность данных
- ❌ Возможность рассинхронизации (packId в GameCards != packId в CardPackCards)
- ❌ Усложнение логики обновления
Рекомендация:
Вариант A: Если карточка всегда принадлежит одному паку (что похоже на правду):
// УДАЛИТЬ таблицу CardPackCards
// ОСТАВИТЬ только packId в GameCards
class GameCards extends Table {
TextColumn get packId => text()
.references(CardPacks, #id, onDelete: KeyAction.cascade)();
// ...
}
Вариант B: Если карточка может быть в нескольких паках (share):
// УДАЛИТЬ packId из GameCards
// ОСТАВИТЬ только CardPackCards
class GameCards extends Table {
// Убрать packId отсюда
// ...
}
class CardPackCards extends Table {
TextColumn get packId => text()
.references(CardPacks, #id, onDelete: KeyAction.cascade)();
TextColumn get cardId => text()
.references(GameCards, #id, onDelete: KeyAction.cascade)();
IntColumn get order => integer().withDefault(const Constant(0))();
@override
Set<Column> get primaryKey => {packId, cardId};
}
Рекомендуется Вариант A, если карточки не переиспользуются между паками.
4. Отсутствие CHECK constraints для enum полей
Проблема:
// Статус хранится как произвольный string
TextColumn get status => text()(); // PaymentStatus
// В БД может попасть что угодно: "completed", "COMPLETED", "Completed", "invalid123"
Почему это плохо:
- ❌ Нет валидации на уровне БД
- ❌ Возможны опечатки и некорректные значения
- ❌ Усложняется отладка
Рекомендация:
Вариант A: Использовать PostgreSQL ENUM (рекомендуется):
-- Создать enum типы в миграции
CREATE TYPE payment_status AS ENUM (
'created', 'pending', 'processing',
'succeeded', 'cancelled', 'failed', 'unknown'
);
CREATE TYPE payment_system AS ENUM (
'yookassa', 'google', 'rustore',
'promo_code', 'ad_view', 'unknown'
);
// В Drift:
class Payments extends Table {
// Использовать custom type
TextColumn get status => text()
.customConstraint('payment_status NOT NULL DEFAULT \'created\'')();
TextColumn get paymentSystem => text()
.customConstraint('payment_system NOT NULL')();
// ...
}
Вариант B: CHECK constraint (проще, но менее строго):
class Payments extends Table {
TextColumn get status => text()();
@override
List<String> get customConstraints => [
'CONSTRAINT valid_payment_status CHECK (status IN (\'created\', \'pending\', \'processing\', \'succeeded\', \'cancelled\', \'failed\', \'unknown\'))',
];
}
⚠️ Важные проблемы (приоритет: СРЕДНИЙ)
5. Deprecated поля в Payments
Проблема:
class Payments extends Table {
// Deprecated fields (для обратной совместимости)
TextColumn get packs => text()
.withDefault(const Constant('[]'))
.map(const StringListConverter())();
BoolColumn get subscription => boolean()
.withDefault(const Constant(false))();
}
Рекомендация:
- Создать миграцию для удаления deprecated полей
- Убедиться, что все клиенты используют поле
productsвместоpacks
-- Миграция v2
ALTER TABLE payments DROP COLUMN IF EXISTS packs;
ALTER TABLE payments DROP COLUMN IF EXISTS subscription;
6. Отсутствие партиционирования для больших таблиц
Проблема:
Таблицы StudySessions и Payments растут со временем без ограничений.
Рекомендация: Использовать партиционирование по дате для старых записей:
-- Партиционирование StudySessions по месяцам
CREATE TABLE study_sessions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
start_time TIMESTAMP WITH TIME ZONE NOT NULL,
-- ...
) PARTITION BY RANGE (start_time);
-- Создать партиции
CREATE TABLE study_sessions_2025_12 PARTITION OF study_sessions
FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
CREATE TABLE study_sessions_2026_01 PARTITION OF study_sessions
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- И так далее (можно автоматизировать)
Альтернатива: Периодическое архивирование старых данных в отдельную таблицу.
7. Недостаточно индексов для частых запросов
Проблема: Не все часто используемые запросы оптимизированы индексами.
Рекомендация:
7.1 Composite индексы для JOIN запросов
-- Для запросов "получить паки пользователя"
CREATE INDEX idx_user_packs_composite ON user_packs(user_id, pack_id);
-- Для запросов "получить карточки пака"
CREATE INDEX idx_card_pack_cards_composite ON card_pack_cards(pack_id, "order");
-- Для получения активных подписок
CREATE INDEX idx_user_subscriptions_active ON user_subscriptions(user_id, finish)
WHERE finish > NOW();
7.2 Covering индексы (INCLUDE)
-- Для запросов по email часто нужны id и name
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (id, name, admin)
WHERE email IS NOT NULL AND is_deleted = false;
-- Для токенов часто нужен userId
CREATE INDEX idx_tokens_token_covering ON tokens(token)
INCLUDE (user_id, expires);
7.3 GIN индексы для JSON полей
-- Если все же оставляем JSON в UserDatas
CREATE INDEX idx_user_datas_category_minutes ON user_datas
USING GIN (category_minutes jsonb_path_ops);
-- Для поиска по purchases
CREATE INDEX idx_users_purchases ON users
USING GIN (purchases jsonb_path_ops);
8. Отсутствие soft delete для всех таблиц
Проблема: Не все критичные таблицы поддерживают soft delete (CardPacks, GameCards есть, но Payments, StudySessions нет).
Рекомендация:
Добавить is_deleted и deleted_at во все таблицы, где важна история:
// Для всех таблиц добавить:
BoolColumn get isDeleted => boolean()
.withDefault(const Constant(false))
.customConstraint('')();
Column<PgDateTime> get deletedAt => customType(PgTypes.timestampWithTimezone)
.nullable()();
// И соответствующие индексы
CREATE INDEX idx_{table}_not_deleted ON {table}(is_deleted)
WHERE is_deleted = false;
💡 Рекомендации по улучшению (приоритет: НИЗКИЙ)
9. Добавить audit trail таблицу
Рекомендация: Создать таблицу для логирования всех изменений критичных данных:
class AuditLog extends Table {
TextColumn get id => text()
.withDefault(const CustomExpression('gen_random_uuid()::text'))();
TextColumn get tableName => text()();
TextColumn get recordId => text()();
TextColumn get action => text()(); // 'INSERT', 'UPDATE', 'DELETE'
TextColumn get userId => text().nullable()();
TextColumn get oldData => text().nullable()(); // JSON
TextColumn get newData => text().nullable()(); // JSON
TextColumn get ipAddress => text().nullable()();
TextColumn get userAgent => text().nullable()();
Column<PgDateTime> get createdAt => customType(PgTypes.timestampWithTimezone)
.withDefault(now())();
@override
Set<Column> get primaryKey => {id};
}
Затем создать PostgreSQL триггеры для автоматического логирования:
-- Пример триггера для Payments
CREATE OR REPLACE FUNCTION audit_payment_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, created_at)
VALUES ('payments', NEW.id, 'UPDATE',
row_to_json(OLD)::text,
row_to_json(NEW)::text,
NOW());
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER audit_payments_trigger
AFTER UPDATE ON payments
FOR EACH ROW EXECUTE FUNCTION audit_payment_changes();
10. Добавить Row Level Security (RLS)
Рекомендация: Использовать PostgreSQL RLS для дополнительной безопасности:
-- Включить RLS для критичных таблиц
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_subscriptions ENABLE ROW LEVEL SECURITY;
-- Политика: пользователь может видеть только свои данные
CREATE POLICY user_isolation_policy ON users
FOR ALL
USING (id = current_setting('app.current_user_id')::text OR
(SELECT admin FROM users WHERE id = current_setting('app.current_user_id')::text));
-- Политика для платежей
CREATE POLICY payment_isolation_policy ON payments
FOR ALL
USING (user_id = current_setting('app.current_user_id')::text OR
(SELECT admin FROM users WHERE id = current_setting('app.current_user_id')::text));
Затем в коде устанавливать current_user_id при каждом запросе:
await db.customStatement(
'SET LOCAL app.current_user_id = ?',
[userId],
);
11. Использовать JSONB вместо TEXT для JSON
Проблема:
// Сейчас JSON хранится как TEXT
TextColumn get settings => text().nullable()();
TextColumn get products => text()
.withDefault(const Constant('[]'))
.map(const JsonListConverter())();
Рекомендация: PostgreSQL имеет нативный тип JSONB с индексацией и операторами:
import 'package:postgres/postgres.dart' show PgDataType;
class Payments extends Table {
// Использовать JSONB
Column<Map<String, dynamic>> get products => customType(PgTypes.jsonb)
.withDefault(const Constant('[]'))();
}
Преимущества:
- ✅ Валидация JSON на уровне БД
- ✅ Возможность индексации GIN
- ✅ Операторы для работы с JSON (@>, ?, ?|, ?&)
12. Добавить материализованные представления для аналитики
Рекомендация: Создать materialized views для часто запрашиваемой аналитики:
-- Статистика по пользователям
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
u.id,
u.name,
u.email,
COUNT(DISTINCT up.pack_id) as packs_count,
COUNT(DISTINCT p.id) as payments_count,
SUM(p.amount::numeric) as total_spent,
ud.total_study_time_minutes,
ud.total_cards,
ud.total_tests
FROM users u
LEFT JOIN user_packs up ON u.id = up.user_id
LEFT JOIN payments p ON u.id = p.user_id AND p.status = 'succeeded'
LEFT JOIN user_datas ud ON u.id = ud.user_id
WHERE u.is_deleted = false
GROUP BY u.id, ud.id;
-- Индекс для быстрого поиска
CREATE INDEX idx_user_stats_id ON user_statistics(id);
-- Обновлять раз в час (или через cron job)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;
📈 Рекомендации по оптимизации производительности
13. Connection Pooling
Рекомендация: Использовать PgBouncer для connection pooling:
# docker-compose.yml
pgbouncer:
image: pgbouncer/pgbouncer:latest
environment:
DATABASES_HOST: postgres
DATABASES_PORT: 5432
DATABASES_DBNAME: mnemo_cards
DATABASES_USER: mnemo_user
PGBOUNCER_POOL_MODE: transaction
PGBOUNCER_MAX_CLIENT_CONN: 1000
PGBOUNCER_DEFAULT_POOL_SIZE: 25
ports:
- "6432:5432"
Воздействие: Уменьшение overhead создания подключений на ~70%
14. Настройка PostgreSQL параметров
Рекомендация: Оптимизировать настройки PostgreSQL для вашей нагрузки:
# postgresql.conf
# Memory
shared_buffers = 256MB # 25% от RAM
effective_cache_size = 1GB # 50-75% от RAM
work_mem = 4MB # Для сортировок
maintenance_work_mem = 64MB # Для VACUUM, CREATE INDEX
# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 16MB
max_wal_size = 1GB
min_wal_size = 80MB
# Query Planning
random_page_cost = 1.1 # Для SSD
effective_io_concurrency = 200 # Для SSD
# Logging
log_min_duration_statement = 500 # Логировать медленные запросы (>500ms)
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
15. Регулярный VACUUM и ANALYZE
Рекомендация: Настроить автоматический vacuum:
-- Включить autovacuum (должен быть включен по умолчанию)
ALTER TABLE payments SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE study_sessions SET (autovacuum_vacuum_scale_factor = 0.05);
-- Ручной VACUUM для больших таблиц раз в неделю (в cron job)
VACUUM ANALYZE payments;
VACUUM ANALYZE study_sessions;
VACUUM ANALYZE user_datas;
🔧 План реализации улучшений
Этап 1: Критичные исправления (1-2 недели)
-
✅ Исправить NULL в is_blacklisted (уже есть SQL скрипт)
psql -h localhost -U mnemo_user -d mnemo_cards -f fix_is_blacklisted_nulls.sql -
🔄 Удалить deprecated поля из Payments
- Создать миграцию v2
- Убедиться, что все клиенты используют
products - Применить миграцию
-
🔄 Исправить дублирование Pack ↔ Cards
- Определить: нужна ли many-to-many связь?
- Если нет → удалить CardPackCards
- Обновить DAO и бизнес-логику
-
🔄 Добавить CHECK constraints для enum
- Создать PostgreSQL ENUM типы
- Обновить таблицы
- Обновить Drift схемы
Этап 2: Улучшение структуры (2-3 недели)
-
🔄 Денормализация UserDatas
- Создать таблицу WordStatistics
- Мигрировать данные из JSON
- Удалить старые JSON поля
- Обновить DAO и бизнес-логику
-
🔄 Миграция на UUID тип
- Создать новые таблицы с UUID
- Мигрировать данные
- Переключить код на новые таблицы
- Удалить старые таблицы
-
🔄 Добавить композитные индексы
- Создать covering индексы
- Создать GIN индексы для JSON
- Замерить производительность
Этап 3: Безопасность и мониторинг (1-2 недели)
-
🔄 Добавить audit trail
- Создать таблицу AuditLog
- Создать триггеры для критичных таблиц
- Настроить ротацию логов
-
🔄 Настроить RLS
- Включить RLS для пользовательских данных
- Создать политики
- Обновить код для установки current_user_id
-
🔄 Настроить мониторинг
- Включить pg_stat_statements
- Настроить алерты на медленные запросы
- Dashboard для метрик БД
Этап 4: Оптимизация (1 неделя)
-
🔄 Connection pooling
- Развернуть PgBouncer
- Обновить connection string
-
🔄 Оптимизация PostgreSQL
- Применить рекомендованные настройки
- Настроить autovacuum
- Создать cron jobs для обслуживания
📊 Ожидаемые результаты
После реализации всех улучшений:
| Метрика | Сейчас | После | Улучшение |
|---|---|---|---|
| Размер индексов | 100% | ~60% | -40% |
| Скорость JOIN | 100% | ~130% | +30% |
| Размер UserDatas | 100% | ~30% | -70% |
| SELECT по userId | 100% | ~200% | +100% |
| Безопасность | ⭐⭐⚪⚪⚪ | ⭐⭐⭐⭐⚪ | +2 |
Общее улучшение производительности: ~40-60%
🧪 Скрипты для тестирования
Проверка размера таблиц
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
Проверка медленных запросов
SELECT
query,
mean_exec_time,
calls,
total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100 -- запросы медленнее 100ms
ORDER BY mean_exec_time DESC
LIMIT 20;
Проверка неиспользуемых индексов
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
Проверка bloat (раздутых таблиц)
SELECT
current_database(),
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;
📝 Заключение
База данных Mnemo Cards имеет хорошую основу, но требует серьезных улучшений для production-ready состояния. Основные проблемы:
- Денормализация - JSON поля вместо нормализованных таблиц
- Неоптимальные типы - TEXT вместо UUID
- Отсутствие безопасности - нет audit trail и RLS
- Недостаточная оптимизация - можно добавить больше индексов
Рекомендуется реализовать улучшения поэтапно, начиная с критичных проблем.
Приоритет реализации:
- 🔴 Критичные (1-4) - немедленно
- 🟡 Важные (5-8) - в течение месяца
- 🟢 Улучшения (9-12) - опционально
📚 Полезные ресурсы
- PostgreSQL Performance Tuning
- Drift Documentation
- PostgreSQL Indexing Best Practices
- Database Normalization
Подготовлено: AI Code Analyzer
Дата: 14 декабря 2025