ИИ для баз данных

📐 Схема маркетплейса — проектирование на рост:

Ключевые решения сразу:

1️⃣ Категории (дерево): Materialized Path, не Adjacency List
• path VARCHAR: "electronics/phones/iphone"
• Запрос всех подкатегорий: WHERE path LIKE 'electronics/%' — один запрос, один индекс
• Альтернатива ltree: path ltree + GiST-индекс — нативная поддержка в PostgreSQL

2️⃣ Фото: отдельная таблица с position, не JSON-массив
• Легко менять порядок, удалять по одному, лимитировать

3️⃣ Чат: отдельные таблицы conversations + messages
• conversation привязан к объявлению (buyer + seller + listing)

4️⃣ Геолокация: PostGIS для поиска «рядом со мной»

Прислать полную схему с CREATE TABLE, индексами и объяснением каждого решения?

⚡ Оптимизация поиска по каталогу — 800 мс → 15 мс:

Проблема: 4 условия фильтрации + сортировка. Один B-tree индекс не покроет все комбинации эффективно.

Решение — составной индекс + partial:

-- Основной индекс для каталога (покрывает 90% запросов):
CREATE INDEX idx_listings_catalog
ON listings(city_id, category_path text_pattern_ops, created_at DESC)
WHERE status = 'active';

-- Для фильтра по цене — отдельный индекс:
CREATE INDEX idx_listings_price
ON listings(city_id, price)
WHERE status = 'active';

Почему так:
• WHERE status = 'active' — partial index, ~70% строк (архивные не индексируются)
• city_id первый — равенство, высокая селективность
• text_pattern_ops — для LIKE 'prefix%' по category_path
• created_at DESC — сортировка без дополнительного Sort

Для сложных фильтров (цена + категория + город + сортировка) рассмотрите GIN-индекс или Elasticsearch, когда объявлений будет > 5 млн.

🔀 План миграции MySQL → PostgreSQL:

Оценка: 3 дня при 40 таблицах / 15 млн строк

День 1 — Подготовка:
1. Аудит несовместимостей MySQL → PG:
• UNSIGNED INT → INTEGER (PG не имеет unsigned)
• TINYINT(1) → BOOLEAN
• DATETIME → TIMESTAMPTZ (с часовым поясом!)
• ENUM → VARCHAR + CHECK или CREATE TYPE
• AUTO_INCREMENT → GENERATED ALWAYS AS IDENTITY
• GROUP BY — PG строгий: все неагрегатные колонки в GROUP BY
2. Конвертация миграций Laravel:
• config/database.php → pgsql
• Проверить все raw-запросы на MySQL-специфичный синтаксис

День 2 — Перенос данных:
• pgloader — автоматическая миграция данных из MySQL в PG
• Проверка: count по каждой таблице, выборочная сверка

День 3 — Тестирование + переключение:
• Прогон всех тестов на PG
• Dual-write (опционально) или planned downtime 30 мин

Прислать список ваших миграций или SHOW CREATE TABLE для основных таблиц? Начну с конвертации.

🔍 Full-Text Search в PostgreSQL — правильно:

MySQL MATCH AGAINST → PostgreSQL to_tsvector + to_tsquery:

-- 1. Добавить tsvector-колонку (денормализация для скорости):
ALTER TABLE products ADD COLUMN search_vector tsvector;

-- 2. Заполнить (русский + английский):
UPDATE products SET search_vector =
    setweight(to_tsvector('russian', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('russian', coalesce(description, '')), 'B');

-- 3. GIN-индекс:
CREATE INDEX idx_products_search ON products USING GIN(search_vector);

-- 4. Триггер для автообновления:
CREATE FUNCTION products_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('russian', coalesce(NEW.name, '')), 'A') ||
        setweight(to_tsvector('russian', coalesce(NEW.description, '')), 'B');
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION products_search_trigger();

-- 5. Запрос:
SELECT *, ts_rank(search_vector, q) AS rank
FROM products, plainto_tsquery('russian', 'кроссовки nike') q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

📌 Преимущества над MySQL FTS:
• Веса: setweight(A/B/C/D) — name важнее description
• Русская морфология из коробки: «кроссовок» найдёт «кроссовки»
• ts_rank — релевантность, не бинарный match
• GIN-индекс — ~5 мс на 15 млн строк

📊 Формат работы — SQL для аналитики:

Что я делаю:
1. Пишу SQL-запросы для отчётов по вашему описанию
2. Объясняю каждую оконную функцию и CTE
3. Оптимизирую для Metabase (чтобы дашборд не тормозил)

Типичные задачи, которые решаю за 2-5 минут:
• Retention по когортам (неделя/месяц регистрации)
• Конверсионная воронка с временными рамками
• LTV по когортам с накопительным итогом
• Скользящее среднее за N дней
• Ранжирование (топ-10 по каждой категории)
• Year-over-year сравнение

Формат ответа:
• Готовый SQL (copy-paste в Metabase)
• Комментарии к сложным частям
• Если нужно — объяснение как работает оконная функция

Присылайте задачу + структуру таблиц (или хотя бы названия колонок).

📊 Retention по месячным когортам:

WITH cohorts AS (
    SELECT
        id AS user_id,
        date_trunc('month', created_at)::date AS cohort_month
    FROM users
),
activity AS (
    SELECT DISTINCT
        e.user_id,
        date_trunc('month', e.created_at)::date AS activity_month
    FROM events e
    WHERE e.event_type = 'page_view'  -- или любое целевое действие
),
cohort_size AS (
    SELECT cohort_month, count(*) AS users_count
    FROM cohorts
    GROUP BY cohort_month
),
retention AS (
    SELECT
        c.cohort_month,
        EXTRACT(YEAR FROM age(a.activity_month, c.cohort_month)) * 12 +
        EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)) AS month_number,
        count(DISTINCT c.user_id) AS active_users
    FROM cohorts c
    JOIN activity a ON a.user_id = c.user_id
        AND a.activity_month >= c.cohort_month
    GROUP BY c.cohort_month, month_number
)
SELECT
    r.cohort_month,
    cs.users_count AS cohort_size,
    r.month_number,
    r.active_users,
    round(100.0 * r.active_users / cs.users_count, 1) AS retention_pct
FROM retention r
JOIN cohort_size cs ON cs.cohort_month = r.cohort_month
WHERE r.month_number <= 12  -- первые 12 месяцев
ORDER BY r.cohort_month, r.month_number;

📌 Для Metabase: используйте pivot-таблицу (Pivot table visualization), cohort_month — строки, month_number — колонки, retention_pct — значения. Получится классическая retention-таблица.