Как писал запросы в PostgreSQL?

Подход к написанию запросов в PostgreSQL

При написании запросов в PostgreSQL я придерживаюсь стратегированного подхода, сочетающего понимание предметной области, знания о структуре данных и особенности самой СУБД. Вот ключевые аспекты моего процесса:

1. Анализ требований и структуры данных

Перед написанием запроса я изучаю: / Структуру таблиц (\d table_name в psql или через информационные схемы) / Индексы и ограничения (первичные/внешние ключи, уникальность) / Объем данных и статистику (используя EXPLAIN ANALYZE на тестовых данных)

-- Пример анализа структуры
SELECT 
    table_name, 
    column_name, 
    data_type,
    is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

2. Построение запросов поэтапно

Я начинаю с простых селектов, постепенно добавляя сложности:

-- 1. Базовый SELECT
SELECT id, name, created_at FROM users;

-- 2. Добавляю фильтрацию
SELECT id, name FROM users 
WHERE active = true AND created_at > '2024-01-01';

-- 3. Включаю агрегацию при необходимости
SELECT 
    department_id,
    COUNT(*) as emp_count,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) >":

3. Оптимизация и использование специфичных возможностей PostgreSQL

PostgreSQL предоставляет уникальные возможности, которые я активно использую:

CTE (Common Table Expressions) для сложных запросов

WITH active_users AS (
    SELECT * FROM users WHERE active = true
),
user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders 
    WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT 
    au.name,
    uo.order_count,
    RANK() OVER (ORDER BY uo.order_count DESC) as rank
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

Оконные функции для аналитики

SELECT 
    department_id,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department_id) as diff_from_avg
FROM employees;

4. Работа с JSON и специальными типами данных

-- Использование JSONB для гибких данных
SELECT 
    id,
    metadata->>'email' as email,
    (metadata->>'score')::numeric as score
FROM documents
WHERE metadata @> '{"category": "premium"}';

-- Работа с массивами
SELECT 
    id,
    tags,
    array_length(tags, 1) as tag_count
FROM products
WHERE 'electronics' = ANY(tags);

5. Производительность и индексы

При написании запросов я всегда учитываю: / Использование подходящих индексов (B-tree для диапазонов, GIN для JSONB/массивов, GiST для геоданных) / Избегание N+1 проблемы через правильные JOIN / Оптимизация подзапросов через преобразование в JOIN

-- Пример с оптимизированными JOIN
-- Вместо подзапросов в WHERE использую JOIN:
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_id = 123
  AND o.created_at > NOW() - INTERVAL '7 days';

6. Безопасность и предотвращение SQL-code injection

Я всегда использую параметризованные запросы или подготовленные выражения:

// Пример на Go с использованием pq
rows, err := db.Query(
    "SELECT * FROM users WHERE email = $1 AND active = $2",
    email,
    true
)

7. Работа с транзакциями и конкурентностью

Для сложных операций обязательно использую транзакции с соответствующими уровнями изоляции:

BEGIN;
-- Использую FOR UPDATE для пессимистической блокировки при необходимости
SELECT * FROM accounts 
WHERE id = 123 
FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 123;
INSERT INTO transactions (account_id, amount) VALUES (123, -100);
COMMIT;

8. Документирование и форматирование

Я строго следую стилю кодирования: / Ключевые слова в верхнем регистре / Четкие отступы для читаемости / Комментарии для сложных логических частей

/*
 * Получение ежемесячной статистики по активным пользователям
 * с количеством заказов более 3 за последний месяц
 */
SELECT 
    DATE_TRUNC('month', u.created_at) AS month,
    COUNT(DISTINCT u.id) AS new_users,
    COUNT(DISTINCT o.user_id) AS ordering_users
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
  AND o.created_at >= DATE_TRUNC('month', NOW()) - INTERVAL '1 month'
GROUP BY DATE_TRUNC('month', u.created_at)
HAVING COUNT(DISTINCT o.id) > 3;

9. Тестирование и валидация

Каждый нетривиальный запрос я проверяю:

1. На реалистичных объемах данных
2. С EXPLAIN ANALYZE для анализа плана выполнения
3. На корректность результатов в пограничных случаях

Такой подход позволяет мне писать эффективные, безопасные и поддерживаемые запросы, которые хорошо работают даже на больших объемах данных и легко модифицируются при изменении требований.