> Как работать со сложными запросами в PostgreSQL (JavaScript)

Короткий ответ

Работа со сложными запросами в PostgreSQL включает оптимизацию через индексы, использование CTE, оконных функций, подзапросов и EXPLAIN ANALYZE. Для Node.js применяем библиотеки типа pg, пул соединений и избегаем N+1 через JOIN. Ключевое - понимать execution plan и писать запросы, которые эффективно используют индексы и минимизируют сканирование таблиц.

Подробное объяснение

Сложные запросы в PostgreSQL требуют системного подхода. Основные техники: использование индексов (B-tree, GIN, GiST), материализованных представлений для агрегаций, и партиционирования больших таблиц. Важно различать типы JOIN: hash join для больших несортированных данных, merge join для отсортированных, nested loop для малых выборок. CTE (WITH) позволяют разбить сложную логику, но могут быть материализованы неоптимально - в PostgreSQL 12+ есть MATERIALIZED/NOT MATERIALIZED. Оконные функции (ROW_NUMBER, LAG, LEAD) эффективнее self-join для ранжирования и сравнения строк. Подзапросы часто заменяются на LATERAL JOIN для производительности. EXPLAIN ANALYZE показывает реальное время выполнения, а не только план.

На практике

В Node.js с библиотекой pg используем пул соединений (pg.Pool) для избежания утечек. Для сложных запросов применяем параметризованные запросы ($1, $2) против SQL injection. Пагинацию делаем через keyset pagination (WHERE id > $1 ORDER BY id LIMIT 10) вместо OFFSET для больших таблиц. Агрегации с GROUP BY ускоряем через частичные индексы. Для full-text search используем GIN индексы и tsvector/tsquery. Мониторим slow queries через pg_stat_statements и логируем их в приложении. В транзакциях используем BEGIN/COMMIT с правильным уровнем изоляции (READ COMMITTED по умолчанию).

Пример кода

JAVASCRIPT
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// Сложный запрос с CTE, оконными функциями и параметризацией
async function getTopUsersByRegion(region, limit = 10) {
  const query = `
    WITH user_stats AS (
      SELECT 
        u.id,
        u.name,
        u.region,
        COUNT(o.id) as order_count,
        SUM(o.amount) as total_spent,
        ROW_NUMBER() OVER (PARTITION BY u.region ORDER BY SUM(o.amount) DESC) as rank
      FROM users u
      JOIN orders o ON o.user_id = u.id
      WHERE u.region = $1
        AND o.created_at > NOW() - INTERVAL '30 days'
      GROUP BY u.id, u.name, u.region
    )
    SELECT id, name, order_count, total_spent
    FROM user_stats
    WHERE rank <= $2
    ORDER BY total_spent DESC;
  `;
  
  const result = await pool.query(query, [region, limit]);
  return result.rows;
}

// Keyset pagination для больших таблиц
async function getUsersAfter(lastId, limit = 20) {
  const query = `
    SELECT id, name, email
    FROM users
    WHERE id > $1
    ORDER BY id
    LIMIT $2
  `;
  return (await pool.query(query, [lastId, limit])).rows;
}

Как отвечать на собеседовании

Начни с практического опыта: какие сложные запросы решал, с какими проблемами сталкивался (медленные запросы, блокировки). Покажи понимание execution plan - расскажи, как читать EXPLAIN ANALYZE и что искать (seq scan, nested loop, hash join). Упомяни конкретные кейсы: оптимизация через индексы, рефакторинг подзапросов в JOIN, использование материализованных представлений для отчетов. Для Node.js подчеркни важность пула соединений, транзакций и обработки ошибок. Приведи пример из реального проекта: как ускорил запрос с 10 секунд до 100 мс через добавление составного индекса и замену подзапроса на LATERAL JOIN.

Что проверяет интервьюер

• Понимание внутреннего устройства PostgreSQL: типы индексов, execution plan, memory management
• Умение проектировать схему под нагрузку: нормализация vs денормализация, партиционирование
• Практический опыт с Node.js: пул соединений, транзакции, обработка ошибок
• Навыки оптимизации: чтение EXPLAIN, выявление узких мест, рефакторинг запросов
• Знание продвинутых фич: оконные функции, CTE, LATERAL JOIN, full-text search

Типичные ошибки

• Игнорирование EXPLAIN ANALYZE и оптимизация "на глаз"
• Использование OFFSET для пагинации на больших таблицах (сканирует все предыдущие строки)
• N+1 запросы в Node.js - забывают про JOIN или batch loading
• Неправильное использование CTE - материализация больших результатов без необходимости
• Отсутствие индексов на внешних ключах и колонках в WHERE/ORDER BY
• Слишком много индексов - замедляют INSERT/UPDATE
• Игнорирование connection pool в Node.js - создание нового соединения на каждый запрос
• Неправильный уровень изоляции транзакций - phantom reads или deadlocks