Коротко и по делу.

1) Недостатки/узкие места при запросе «посты с наибольшим числом лайков за последние сутки»

Отсутствие временной метки в Likes (нельзя отфильтровать по «последним суткам» без изменения схемы).Полный скан таблицы Likes при фильтрации по времени и агрегации по post_id → высокая I/O и CPU при больших объёмах.Большое количество мелких обновлений индексов при вставке лайков → индексная фрагментация и contention.JOIN Posts ↔ Likes и агрегирование (GROUP BY post_id) на большой выборке → соревновательный доступ к памяти/CPU.«Hot» посты (горячие ключи) → блокировки/конкуренция при инкрементах, точечные спайки нагрузки.Рост размера данных → неэффективный кэш, больше страниц на диск, ухудшение локальности данных.Отсутствие партиционирования → удаление/очистка старых лайков (скользящее окно 24 часа) дорого обходится.

2) Обязательно: добавить временную метку

Изменение schema: Likes(user_id, post_id, created_at TIMESTAMP). Без этого задача не решается корректно.

3) Индексная стратегия (Postgres-подходы, общая логика)

Индекс для быстрого выборочного сканирования по времени и последующей агрегации по посту:
варианта A (фильтрация по времени при больших объёмах): индекс B-tree на ((created_at, post_id)). Позволяет быстро сделать range scan по времени и затем группировать по post_id.варианта B (если чаще запрашивают для конкретного поста или проверяют existence): индекс на ((post_id, created_at)).Частичный/матчующий индекс для «недавних» лайков (если допустимо): индекс на ((post_id, created_at)) WHERE (created_at \ge now() - interval '7 days') — уменьшает размер индекса для горячего окна.Партиционирование по времени (range-partitioning по created_at, например ежедневные/часовые партиции) — при запросе «последние сутки» сканируются только последние партиции.Для очень больших append-only таблиц: BRIN-индекс по created_at (легкий и компактный) вместо B-tree, если данные физически упорядочены по времени.Поддерживать уникальный/PK индекс (если нужно предотвратить повторные лайки): PK или UNIQUE(user_id, post_id) — важно для логики, но усложняет запись.Покрывающий (covering) индекс: в PostgreSQL INCLUDE(post_id) или аналог, чтобы избежать чтения строк.

4) Денормализация и варианты предвычислений

Поле aggregated в Posts:
posts.total_likes (общий счетчик) — обновляется инкрементом при каждом лайке.posts.likes_last_24h — можно поддерживать разными способами.Incremental counters (режим реального времени):
При вставке лайка делать atomic INCREMENT counter в Posts.likes_last_24h (быстро, но требуется механизм удаления по истечении окна).Если нужна точность для скользящего окна, дополнительно хранить time-buckets.Time-buckets table (рекомендуемый практический подход):
Table LikesPerHour(post_id, hourts, cnt). При лайке инкрементить соответствующий часовой бакет. Для 24-часового окна агрегировать (\sum{i=0}^{23} cnt_i) по соответствующим hour_ts — быстро, требует чтения максимум 24 записей на пост.Materialized view / OLAP-агрегаты:
Материализованный запрос типа «top-N за последние сутки», refresh (incremental или on-schedule). Подходит если допустима некоторая задержка.Кэш/ин-мемори:
Redis sorted set с ключом для временного топа, где score = число лайков за 24ч, регулярное обновление через background job или стриминговый инкремент + очистка устаревших.Приблизительные методы:
Count-Min Sketch / Top-K структуры для экономии памяти, если допустимы ошибки.

5) Когда переходить на колоночное хранилище / OLAP

Показатели для перехода:
Объём данных и частота аналитических запросов такие, что OLTP-БД регулярно перегружается (aggs/joins по сотням миллионов строк).Нужны частые глобальные топ-N/агрегации по временным окнам, историческая аналитика, срезы по множеству измерений.Технические требования допускают задержку обновления (near-real-time) — OLAP обычно асинхронен.Что даёт колонночное хранилище (ClickHouse, Apache Druid, BigQuery и т.п.):
Быстрая агрегация и сжатие по колонкам, эффективные top-K и group-by на больших объемах.Параллельная и векторная обработка, менее чувствительно к чтению большого числа строк.Архитектурный паттерн:
Оставляем OLTP для оперативных транзакций и небольших запросов; стримим лайки в Kafka → преобразуем/агрегируем в стриминге (Flink/Beam) → записываем агрегаты в OLAP/колоночное хранилище и/или Redis для top-N.Примеры, когда точно стоит переходить:
Ежедневные/минутные запросы топ-N по всей базе, где каждый запрос сканирует десятки/сотни млн строк.Нужна историческая аналитика с низкой ценой на storage и высокой скоростью агрегации.

6) Практические рекомендации (сводно)

Добавить created_at в Likes.Партиционировать Likes по времени (день/час).Индексировать либо ((created_at, post_id)) для временных фильтров, либо ((post_id, created_at)) для частых точечных агрегаций; рассмотреть BRIN для больших append-only таблиц.Поддерживать денормализованные счётчики: total_likes + time-buckets для last-24h.Кэшировать/материализовывать top-N (Redis / materialized view) для быстрых ответов.Перенос на OLAP/колоночную БД при росте данных и потребности в быстрых глобальных агрегатах; использовать стриминг для синхронизации.

Если нужно — могу дать конкретные SQL-примеры индексов/партиций, схему time-buckets или архитектурную схему с Kafka/ClickHouse.