PrepBro
← Назад к вопросам

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

2.0 Middle🔥 161 комментариев
#Другое

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI22 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

# Оптимизация таблицы с нагрузкой 100 тысяч записей в день для ускорения недельных выборок

При нагрузке 100K записей/день таблица растёт очень быстро (3М+ записей в месяц). Чтобы недельные выборки оставались быстрыми, нужна стратегическая оптимизация на уровне индексов, партиционирования, архивирования и кэширования.

Проблема

Без оптимизации недельный запрос будет сканировать миллионы строк:

-- Медленно: сканирует ВСЁ из таблицы
SELECT COUNT(*) FROM events
WHERE created_at >= NOW() - INTERVAL '7 days';
-- Time: 5-30 секунд

Решение 1: Индекс на timestamp

Основной инструмент оптимизации — индекс на колонку с датой:

-- Простой индекс
CREATE INDEX idx_events_created_at ON events(created_at DESC);

-- Или составной индекс (если часто фильтруешь по user_id)
CREATE INDEX idx_events_user_date ON events(user_id, created_at DESC);

Теперь запрос будет использовать индекс:

SELECT COUNT(*) FROM events
WHERE created_at >= NOW() - INTERVAL '7 days';
-- Time: 10-100ms (в зависимости от выборки за неделю)

Решение 2: Партиционирование таблицы

Разбиваем таблицу на меньшие части по дате. Postgres автоматически сканирует только нужные партиции:

-- Создание партиционированной таблицы
CREATE TABLE events (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    action VARCHAR(100),
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (EXTRACT(EPOCH FROM created_at));

-- Партиция за день
CREATE TABLE events_2024_03_20 PARTITION OF events
FOR VALUES FROM (1710950400) TO (1711036800); -- 2024-03-20

CREATE TABLE events_2024_03_21 PARTITION OF events
FOR VALUES FROM (1711036800) TO (1711123200); -- 2024-03-21

-- Индекс на каждой партиции
CREATE INDEX idx_events_2024_03_20_user ON events_2024_03_20(user_id);

Преимущества партиционирования:

  • При недельном запросе сканируются только 7 партиций вместо всей таблицы
  • DELETE старых партиций очень быстрый (DROP PARTITION)
  • Автоматическое распределение нагрузки
-- Быстро благодаря партиционированию
SELECT COUNT(*) FROM events
WHERE created_at >= NOW() - INTERVAL '7 days';
-- Time: 50-200ms

Решение 3: Архивирование старых данных

Основная таблица хранит только последние 30 дней. Старые данные архивируются:

@Configuration
public class DataArchivingConfig {
    
    @Scheduled(cron = "0 2 * * *") // Каждый день в 02:00
    public void archiveOldData() {
        // Переносим данные старше 30 дней в архив
        String sql = "INSERT INTO events_archive SELECT * FROM events " +
                    "WHERE created_at < NOW() - INTERVAL '30 days'";
        jdbcTemplate.update(sql);
        
        // Удаляем из основной таблицы
        String deleteSql = "DELETE FROM events " +
                          "WHERE created_at < NOW() - INTERVAL '30 days'";
        jdbcTemplate.update(deleteSql);
    }
}

Исходная таблица остаётся компактной:

  • events: 3-10M записей (30 дней × 100-300K/день)
  • events_archive: всё старое
  • Недельные запросы работают с 700K-2M записей

Решение 4: Материализованное представление (Materialized View)

Предвычисляем агрегированные данные:

-- Материализованное представление с агрегированными данными по дню
CREATE MATERIALIZED VIEW events_daily_summary AS
SELECT
    DATE(created_at) as event_date,
    user_id,
    action,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM events
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at), user_id, action;

-- Индекс на представлении
CREATE INDEX idx_events_daily_user ON events_daily_summary(user_id, event_date);

-- Обновляем каждый час
REFRESH MATERIALIZED VIEW CONCURRENTLY events_daily_summary;

Теперь запросы намного быстрее:

-- Вместо сканирования млн строк, сканируем тысячи предвычисленных строк
SELECT event_date, COUNT(*) FROM events_daily_summary
WHERE user_id = ?
AND event_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY event_date;
-- Time: 1-10ms

Решение 5: Read Replica и кэширование

Для высоконагруженных систем используем отдельный Read Replica:

@Service
public class EventReportService {
    
    @Autowired
    @Qualifier("readReplicaJdbcTemplate")
    private JdbcTemplate readJdbcTemplate;
    
    // Недельные выборки идут на Replica
    public List<EventSummary> getWeeklySummary(String userId) {
        String sql = "SELECT DATE(created_at) as event_date, " +
                    "COUNT(*) as count, action " +
                    "FROM events " +
                    "WHERE user_id = ? AND created_at >= NOW() - INTERVAL '7 days' " +
                    "GROUP BY DATE(created_at), action";
        return readJdbcTemplate.query(sql, new Object[]{userId}, rowMapper);
    }
}

Добавляем Redis кэш для ещё большей скорости:

@Service
@CacheConfig(cacheNames = "weeklySummary")
public class EventReportService {
    
    @Cacheable(key = "#userId")
    public List<EventSummary> getWeeklySummary(String userId) {
        // Первый вызов: идёт в БД
        // Второй вызов: из кэша (Redis)
        return jdbcTemplate.query(
            "SELECT ... WHERE user_id = ? AND created_at >= ...",
            new Object[]{userId}
        );
    }
    
    @CacheEvict(key = "#userId")
    public void invalidateCache(String userId) {
        // Инвалидируем кэш после новых данных
    }
}

Решение 6: Полная стратегия оптимизации

-- 1. Создание партиционированной таблицы
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    action VARCHAR(50) NOT NULL,
    data JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- 2. Суточные партиции
CREATE TABLE events_2024_03_20 PARTITION OF events
FOR VALUES FROM ('2024-03-20') TO ('2024-03-21');
CREATE TABLE events_2024_03_21 PARTITION OF events
FOR VALUES FROM ('2024-03-21') TO ('2024-03-22');

-- 3. Индексы
CREATE INDEX idx_events_2024_03_20_user_date ON events_2024_03_20(user_id, created_at DESC);
CREATE INDEX idx_events_2024_03_20_action ON events_2024_03_20(action) WHERE action IN ('click', 'view');

-- 4. Материализованное представление
CREATE MATERIALIZED VIEW events_daily_stats AS
SELECT
    DATE(created_at) as event_date,
    action,
    user_id,
    COUNT(*) as count
FROM events
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at), action, user_id;

CREATE INDEX idx_daily_stats_date ON events_daily_stats(event_date DESC);

-- 5. Функция автоматического создания партиций
CREATE OR REPLACE FUNCTION create_partition_for_date(partition_date DATE)
RETURNS void AS $$
BEGIN
    EXECUTE format('CREATE TABLE IF NOT EXISTS events_%s PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
        to_char(partition_date, 'YYYY_MM_DD'),
        partition_date,
        partition_date + INTERVAL '1 day'
    );
END;
$$ LANGUAGE plpgsql;

Java код для оптимизированных запросов

@Repository
public class EventRepository {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Autowired
    @Qualifier("cacheTemplate")
    private CacheTemplate cacheTemplate;
    
    // Недельная статистика с кэшем
    @Cacheable(cacheNames = "weeklyStats", key = "#userId + ':' + #instant.toString()")
    public WeeklyStats getWeeklyStats(String userId, Instant instant) {
        String sql = "SELECT action, COUNT(*) as count " +
                    "FROM events " +
                    "WHERE user_id = ? AND created_at >= ? " +
                    "GROUP BY action";
        
        Instant weekAgo = instant.minus(Duration.ofDays(7));
        
        return jdbcTemplate.queryForObject(sql, 
            new Object[]{userId, weekAgo},
            (rs, rowNum) -> new WeeklyStats(
                rs.getString("action"),
                rs.getLong("count")
            )
        );
    }
    
    // Использование материализованного представления
    public List<EventSummary> getSummaryFromMaterialized(String userId) {
        String sql = "SELECT event_date, action, count FROM events_daily_stats " +
                    "WHERE user_id = ? AND event_date >= CURRENT_DATE - INTERVAL '7 days' " +
                    "ORDER BY event_date DESC";
        
        return jdbcTemplate.query(sql, new Object[]{userId}, 
            (rs, rowNum) -> new EventSummary(
                rs.getDate("event_date"),
                rs.getString("action"),
                rs.getLong("count")
            )
        );
    }
}

Мониторинг и профилирование

-- Объём данных за день
SELECT pg_size_pretty(sum(pg_total_relation_size(tablename)))
FROM pg_tables
WHERE tablename LIKE 'events%';

-- Медленные запросы
SELECT query, calls, mean_time
FROM pg_stat_statements
WHERE query LIKE '%events%'
ORDER BY mean_time DESC;

-- Использование индексов
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'events'
ORDER BY idx_scan DESC;

Чеклист оптимизации

  1. Индексы — idx_created_at, idx_user_created_at
  2. Партиционирование — по дате (суточные партиции)
  3. Архивирование — старые данные за пределы 30 дней
  4. Материализованные представления — предвычисленная статистика
  5. Read Replica — для недельных отчётов
  6. Кэширование — Redis для часто запрашиваемых данных
  7. Мониторинг — pg_stat_statements для выявления узких мест

Результат

  • До: недельный запрос — 5-30 секунд
  • После: недельный запрос — 10-100ms
  • Ускорение: в 50-300 раз

Ключ к успеху — комбинация индексов, партиционирования и кэширования.