Какой бы использовал индекс для работы с JSON?

Индексы для работы с JSON в базе данных

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

Для оптимальной работы с JSON в PostgreSQL использую GIN индекс (Generalized Inverted Index). Это специализированный индекс, созданный именно для JSON документов и полнотекстового поиска.

Типы индексов для JSON

1. GIN индекс (Generalized Inverted Index) — РЕКОМЕНДУЕМЫЙ

-- Создаём таблицу с JSON
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    data JSONB,
    created_at TIMESTAMP
);

-- Создаём GIN индекс
CREATE INDEX idx_users_data_gin ON users USING gin(data);

-- Или для конкретного пути в JSON
CREATE INDEX idx_users_email_gin ON users USING gin((data -> 'email'));

Как это работает:

• GIN индекс разбирает JSON на отдельные ключи и значения
• Создаёт обратный индекс для быстрого поиска
• Идеален для операций ->, ->>, @>, ?

Пример запроса с GIN:

-- Очень быстро благодаря GIN индексу
SELECT * FROM users 
WHERE data @> '{"status": "active"}';

-- Проверка наличия ключа
SELECT * FROM users 
WHERE data ? 'email';

-- Проверка значения в массиве
SELECT * FROM users 
WHERE data -> 'tags' ? 'premium';

Плюсы GIN:

• Очень быстро для поиска по содержимому
• Хорошо для @> операций (contains)
• Стандарт для JSON

Минусы GIN:

• Медленнее создаётся
• Занимает больше памяти
• Медлительнее при вставке/обновлении

2. BTREE индекс (B-Tree) — для конкретных полей

-- Если часто сортируешь по полю JSON
CREATE INDEX idx_users_name_btree ON users USING btree((data ->> 'name'));

-- Или преобразуя в конкретный тип
CREATE INDEX idx_users_age_btree ON users USING btree((data ->> 'age')::int);

Когда использовать:

• Сортировка (ORDER BY)
• Диапазонные запросы (> < BETWEEN)
• Точный поиск по одному полю

Пример:

SELECT * FROM users 
WHERE (data ->> 'age')::int > 18
ORDER BY (data ->> 'name');

3. BRIN индекс (Block Range Index) — для больших таблиц

-- Для очень больших таблиц (миллионы строк)
CREATE INDEX idx_users_data_brin ON users USING brin(data);

Когда использовать:

• Таблицы > 1 млн строк
• Когда JSON коррелирует с физическим расположением
• Экономия памяти (BRIN намного меньше, чем GIN)

Java код для работы с JSON индексами

// Spring Data JPA с Hibernate

@Entity
@Table(name = "users")
public class User {
    @Id
    private Long id;
    
    @Column(columnDefinition = "jsonb")
    @Type(JsonType.class) // Hibernate 6+
    private Map<String, Object> data;
    
    @CreationTimestamp
    private LocalDateTime createdAt;
}

// Custom repository для JSON запросов
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // Query с JSON оператором contains (@>)
    @Query(value = "SELECT * FROM users WHERE data @> :filter", nativeQuery = true)
    List<User> findByDataContains(@Param("filter") String filter);
    
    // Пример использования:
    // userRepository.findByDataContains("{\"status\": \"active\"}");
}

// Альтернатива с QueryDSL
@Repository
public class UserRepositoryCustomImpl implements UserRepositoryCustom {
    @PersistenceContext
    private EntityManager em;
    
    public List<User> findActiveUsers() {
        return em.createNativeQuery(
            "SELECT * FROM users WHERE data @> :status",
            User.class
        )
        .setParameter("status", "{\"status\": \"active\"}")
        .getResultList();
    }
}

Сравнение типов индексов

Оптимизация производительности

1. Многоуровневый индекс

-- Если часто ищешь по нескольким полям
CREATE INDEX idx_users_composite ON users USING gin(data) 
INCLUDE (created_at, id);

2. Partial индекс

-- Индексируем только активные записи
CREATE INDEX idx_users_active ON users USING gin(data)
WHERE (data ->> 'status') = 'active';

3. Параллельный индекс

# Создание индекса без блокировки таблицы
CREATE INDEX CONCURRENTLY idx_users_data_gin ON users USING gin(data);

Мониторинг индексов

-- Размер индекса
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Статистика использования индекса
SELECT 
    schemaname,
    tablename,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Реальный пример: система с JSON профилями

@Entity
@Table(name = "user_profiles")
public class UserProfile {
    @Id
    private UUID id;
    
    @Column(columnDefinition = "jsonb")
    @Type(JsonType.class)
    private UserData data; // { name, email, age, preferences, ... }
    
    @CreationTimestamp
    private LocalDateTime createdAt;
}

// SQL миграция
-- CREATE INDEX idx_profiles_gin ON user_profiles USING gin(data);
-- CREATE INDEX idx_profiles_created_btree ON user_profiles USING btree(created_at DESC);
-- CREATE INDEX idx_profiles_email ON user_profiles USING btree((data ->> 'email'));

@Service
public class UserProfileService {
    @Autowired
    private EntityManager em;
    
    public List<UserProfile> findByPreference(String key, String value) {
        String query = "SELECT * FROM user_profiles " +
                      "WHERE data -> 'preferences' @> :pref";
        
        return em.createNativeQuery(query, UserProfile.class)
                 .setParameter("pref", String.format("{\"%s\": \"%s\"}", key, value))
                 .getResultList();
    }
}

Выводы

1. Для JSON используй GIN индекс — это стандарт для JSONB в PostgreSQL
2. GIN идеален для:
   • Проверки содержимого (@>)
   • Поиска по ключам (?)
   • Полнотекстового поиска
3. BTREE для:
   • Сортировки (ORDER BY)
   • Диапазонных запросов
   • Конкретных полей JSON
4. BRIN для:
   • Очень больших таблиц
   • Когда место критично
5. Всегда мониторь использование индексов — они должны активно использоваться