С какими интересными задачами сталкивался в PostgreSQL

С какими интересными задачами сталкивался в PostgreSQL

Этот вопрос раскрывает практический опыт работы с реляционными БД, оптимизацией запросов и решением реальных проблем. Вот примеры интересных задач, с которыми может столкнуться Java-разработчик:

1. Оптимизация медленных запросов (Query Performance)

Задача: Запрос на получение всех заказов пользователя работал 30+ секунд

-- Медленный запрос (N+1 problem)
SELECT o.* FROM orders o 
WHERE o.user_id = $1;

-- Для каждого заказа в приложении вызывается отдельный запрос
SELECT * FROM order_items WHERE order_id = $1;
SELECT * FROM products WHERE id = $1;

Решение: Используем JOIN и индексы

-- Оптимизированный запрос с одной загрузкой
SELECT o.*, oi.*, p.* 
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.user_id = $1
ORDER BY o.created_at DESC;

-- Добавляем индексы
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

В Java с Hibernate:

// До оптимизации (N+1)
@Query("SELECT o FROM Order o WHERE o.userId = :userId")
List<Order> findByUserId(@Param("userId") String userId);

// После оптимизации (fetch join)
@Query("SELECT DISTINCT o FROM Order o " +
       "LEFT JOIN FETCH o.items i " +
       "LEFT JOIN FETCH i.product " +
       "WHERE o.userId = :userId " +
       "ORDER BY o.createdAt DESC")
List<Order> findByUserId(@Param("userId") String userId);

2. Работа с JSON и JSONB (PostgreSQL特性)

Задача: Хранение динамических атрибутов продукта без создания отдельной таблицы

-- Таблица с JSONB полем
CREATE TABLE products (
    id UUID PRIMARY KEY,
    name VARCHAR(255),
    attributes JSONB,  -- Динамические атрибуты
    created_at TIMESTAMP WITH TIME ZONE
);

INSERT INTO products VALUES (
    'prod-1',
    'Laptop',
    '{"color": "silver", "ram": "16GB", "cpu": "Intel i7"}'
);

-- Запрос по JSON полю
SELECT * FROM products 
WHERE attributes->>'color' = 'silver';

-- Индекс на JSON поле
CREATE INDEX idx_products_attributes 
ON products USING GIN(attributes);

-- Сложный запрос с JSON
SELECT name, attributes->>'ram' as ram
FROM products
WHERE (attributes->'specs'->>'price')::numeric > 1000;

В Java с JPA:

@Entity
public class Product {
    @Id
    private UUID id;
    
    @Column(columnDefinition = "jsonb")
    private Map<String, Object> attributes;
}

// Native query для работы с JSON
@Query(value = "SELECT * FROM products WHERE attributes->>'color' = ?1", 
       nativeQuery = true)
List<Product> findByColor(String color);

3. Window Functions для аналитики

Задача: Вычислить ранжирование продавцов по продажам с нарастающей суммой

SELECT 
    seller_id,
    SUM(amount) as total_sales,
    ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) as rank,
    SUM(SUM(amount)) OVER (ORDER BY SUM(amount) DESC) as running_total
FROM sales
GROUP BY seller_id
ORDER BY rank;

Результат:

seller_id | total_sales | rank | running_total
----------|-------------|------|---------------
   5      |   50000     |  1   |    50000
   3      |   30000     |  2   |    80000
   8      |   20000     |  3   |   100000

4. Common Table Expressions (CTE) для сложной логики

Задача: Найти всех друзей друзей пользователя (рекурсивный запрос)

WITH RECURSIVE friends_tree AS (
    -- Base case: прямые друзья
    SELECT friend_id, 1 as depth
    FROM friendships
    WHERE user_id = $1 AND depth = 1
    
    UNION
    
    -- Recursive case: друзья друзей
    SELECT f.friend_id, ft.depth + 1
    FROM friendships f
    INNER JOIN friends_tree ft ON f.user_id = ft.friend_id
    WHERE ft.depth < 3  -- Ограничиваем глубину
)
SELECT DISTINCT friend_id, depth FROM friends_tree
WHERE depth > 1;

5. Транзакции и уровни изоляции (Isolation Levels)

Задача: Race condition при переводе денег между счетами

-- Проблема: грязное чтение, фантомные строки
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  -- Другой процесс может прочитать промежуточное значение
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Решение: SERIALIZABLE или SELECT FOR UPDATE
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  
  SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

В Java с Spring:

@Service
public class TransferService {
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void transferMoney(UUID fromId, UUID toId, BigDecimal amount) {
        Account from = accountRepository.findByIdForUpdate(fromId);
        Account to = accountRepository.findByIdForUpdate(toId);
        
        if (from.getBalance().compareTo(amount) < 0) {
            throw new InsufficientFundsException();
        }
        
        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));
    }
}

@Repository
public interface AccountRepository extends JpaRepository<Account, UUID> {
    @Query("SELECT a FROM Account a WHERE a.id = ?1")
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Account findByIdForUpdate(UUID id);
}

6. Partitioning для больших таблиц

Задача: Таблица логов событий растет на 1GB в день, запросы становятся медленными

-- Partitioning по дате
CREATE TABLE events (
    id BIGSERIAL,
    user_id UUID,
    event_type VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE,
    data JSONB
) PARTITION BY RANGE (DATE_TRUNC('month', created_at));

-- Создаем партиции
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Теперь запросы будут выполняться только на нужной партиции
SELECT * FROM events WHERE created_at BETWEEN '2024-02-01' AND '2024-03-01';
-- PostgreSQL автоматически обращается только к events_2024_02

7. Full-Text Search

Задача: Поиск по полнотекстовому индексу в документах

-- Создание полнотекстового индекса
CREATE TABLE documents (
    id UUID PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    search_vector tsvector
);

-- Индекс для быстрого поиска
CREATE INDEX idx_documents_search ON documents USING GIN(search_vector);

-- Заполнение вектора при вставке
UPDATE documents SET search_vector = 
    to_tsvector('russian', coalesce(title, '') || ' ' || coalesce(content, ''));

-- Быстрый поиск
SELECT * FROM documents 
WHERE search_vector @@ to_tsquery('russian', 'PostgreSQL & оптимизация')
ORDER BY ts_rank(search_vector, to_tsquery('russian', 'PostgreSQL & оптимизация')) DESC;

8. Реакция на срабатывание Triggers

Задача: Автоматическое обновление счетчика комментариев при добавлении нового комментария

CREATE FUNCTION update_post_comment_count()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE posts SET comment_count = comment_count + 1
    WHERE id = NEW.post_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_comment_insert
AFTER INSERT ON comments
FOR EACH ROW
EXECUTE FUNCTION update_post_comment_count();

Интересные особенности PostgreSQL

UUID типы:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL
);

LISTEN/NOTIFY для real-time уведомлений:

LISTEN order_created;

NOTIFY order_created, 'New order #123';

Полезные встроенные функции:

SELECT 
    gen_random_uuid(),           -- UUID
    CURRENT_TIMESTAMP,           -- Текущее время
    DATE_TRUNC('month', now()),  -- Округление даты
    COALESCE(value, 'default'),  -- Значение по умолчанию
    ARRAY_AGG(item),             -- Агрегация в массив
    STRING_AGG(item, ',')        -- Конкатенация строк
FROM data;

Вывод

ПостgreSQL предоставляет мощные инструменты для работы с данными: от оптимизации запросов до сложной аналитики. Опытный разработчик должен знать основные техники для работы с большими объемами данных, оптимизации производительности и обеспечения data consistency.