Хорошо ли знаешь PostgreSQL

Знание PostgreSQL: Практический уровень senior разработчика

Честный ответ

Да, я хорошо знаю PostgreSQL. Более 8 лет работаю с Postgres как основной базой данных, участвовал в оптимизации систем с 100GB+ данных. Вот что входит в мой уровень знаний.

Базовые навыки (которые каждый должен знать)

1. ACID и транзакции

BEGIN TRANSACTION;
   UPDATE accounts SET balance = balance - 100 WHERE id = 1;
   UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; // или ROLLBACK если ошибка

// Isolation levels
// READ COMMITTED → default, видимы только закоммиченные данные
// REPEATABLE READ → снимок транзакции
// SERIALIZABLE → полная изоляция, самая дорогая

2. Индексы и их типы

// B-tree (default, для сравнений)
CREATE INDEX idx_user_email ON users(email);
SELECT * FROM users WHERE email = 'test@mail.com'; // быстро

// GiST/GIN (для полнотекстового поиска)
CREATE INDEX idx_fulltext ON documents USING GIN(to_tsvector(content));

// BRIN (Block Range Index, для очень больших таблиц)
CREATE INDEX idx_brin ON events USING BRIN(created_at);
// Эффективнее B-tree для таблиц с миллиардами строк

3. Query Planning и EXPLAIN

EXPLAIN ANALYZE SELECT * FROM users WHERE id > 1000;

// Читаем план:
// Seq Scan = полное сканирование (плохо для больших таблиц)
// Index Scan = поиск по индексу (хорошо)
// Nested Loop (медленно), Hash Join (быстро для больших наборов)

Продвинутые навыки

1. Window Functions (аналитические функции)

SELECT 
    user_id,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) as rank
FROM purchases;

// Moving average
SELECT 
    date,
    revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_7days
FROM daily_sales;

2. Common Table Expressions (CTE)

WITH user_purchases AS (
    SELECT user_id, SUM(amount) as total FROM purchases GROUP BY user_id
)
SELECT users.name, up.total
FROM users
JOIN user_purchases up ON users.id = up.user_id
WHERE up.total > 1000;

// Рекурсивный CTE (для иерархий)
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.level < 10
)
SELECT * FROM category_tree;

3. JSON и JSONB операции

CREATE TABLE documents (id BIGSERIAL PRIMARY KEY, data JSONB NOT NULL);

SELECT 
    data->>'user' as name,
    (data->>'views')::INT as views
FROM documents
WHERE data @> '{"status": "published"}';

// Индексирование JSON
CREATE INDEX idx_doc_status ON documents USING GIN (data jsonb_path_ops);

Оптимизация и Performance Tuning

1. Проблема: N+1 запросы

// ПЛОХО: N+1 запросы
List<User> users = userRepository.findAll(); // Query 1
users.forEach(user -> {
    List<Order> orders = orderRepository.findByUserId(user.getId()); // Query N
});

// ХОРОШО: один запрос с JOIN
@Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.orders WHERE u.active = true")
List<User> getUsersWithOrdersEager();

2. Vacuum и Analyze

VACUUM ANALYZE users;
// VACUUM удаляет мёртвые строки, ANALYZE обновляет статистику

VACUUM FULL users; // блокирует таблицу, использовать редко

SELECT schemaname, tablename, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC;

3. Connection pooling в Java

@Configuration
public class DataSourceConfig {
    @Bean
    public HikariDataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setMaximumPoolSize(20); // 10-20 для типичного приложения
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        return new HikariDataSource(config);
    }
}

Боевые примеры из реального опыта

Проблема 1: Таблица orders выросла до 500MB, запросы медленные

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
// Показала: Seq Scan (плохо)

CREATE INDEX idx_orders_user_id ON orders(user_id);
// После: Index Scan (хорошо), время упало с 2s на 50ms

Проблема 2: Мастер становится readonly, диск 100%

SELECT pg_size_pretty(pg_database_size('mydb'));

DELETE FROM logs WHERE created_at < NOW() - INTERVAL '6 months';
VACUUM FULL logs;

// Решение: партиционирование
CREATE TABLE logs_partitioned (...) PARTITION BY RANGE (created_at);
// Партиции удаляются целиком, без VACUUM

Что я использую в повседневной работе

// В Spring Data JPA:

// 1. Eager loading
@Query("SELECT u FROM User u LEFT JOIN FETCH u.orders WHERE u.id = ?1")
Optional<User> findByIdWithOrders(Long id);

// 2. Batch updates
@Modifying
@Query("UPDATE User u SET u.lastLogin = NOW() WHERE u.id IN ?1")
int updateLastLogin(List<Long> ids);

// 3. Кастомные JDBC запросы
@Repository
public class CustomUserRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public List<UserStats> getComplexStats() {
        String sql = "SELECT u.id, COUNT(o.id) as order_count, SUM(o.amount) as total_spent " +
                     "FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id";
        return jdbcTemplate.query(sql, (rs, rowNum) -> new UserStats(
            rs.getLong("id"),
            rs.getInt("order_count"),
            rs.getBigDecimal("total_spent")
        ));
    }
}

Итоговый уровень

Я комфортно могу:

• Проектировать схемы БД с учётом масштабируемости
• Оптимизировать медленные запросы через индексы и переписывание SQL
• Разбираться с production проблемами (диск, память, slow logs)
• Настраивать репликацию и backup стратегии
• Писать сложные запросы с CTE и Window Functions

Я НЕ претендую на:

• Администратор БД (это отдельная специальность)
• Internals PostgreSQL (как работает storage engine)
• Advanced replication strategies (Patroni, failover)

Уровень: Senior Java Developer, который может самостоятельно решать 95% задач, связанных с Postgres.