Используется PostgreSQL 9.3
Таблица состоит из полей id (int), started (timestamp), ended(timestamp), task(varchar 2048), active(int), comment(varchar 1024), retries(int)
Количество новых записей в эту таблицу - примерно 2 миллиона строк в сутки.
Количество апдейтов - так же, 2 миллиона.
При этом, актуальность строки (в идеале) - примерно 1 минута. То есть будет так:
00:00. Задача(строка) добавлена со "active"
00:01. Задача обработана ("active" = 0)
При этом, выполненные задания должны быть доступны для просмотра (удалять - не вариант) и должна сохраняться хорошая скорость поиска по id+active (перед постановкой задания проводится проверка, есть ли это задание в таблице, или нет).
Я вижу 3 варианта:
1. Все записи в одной таблице, индекс по active.
2. Завести вторую таблицу (tasks_done), куда переносить выполненные задания
3. После выполнения задания удалять строку из таблицы и помещать ее в nosql лог (elasticsearch)
Какой из вариантов будет предпочтительнее в плане быстродействия поиска еще не выполненного задания и поиска уже выполненного? Допустим, через 2 месяца можно удалять задания, то есть максимальное количество записей в таблице - 60 миллионов.
Если у вас есть требование "в статусе active=1 может быть только один таск с определённым значением поля task" (т.е. нужно отсутствие дубликатов) можно создать составной уникальный частичный индекс по полям task + active с ограничением типа "active = 1". Это позволит:
1) Не делать проверку на наличие таска перед его добавлением. Вместо этого обрабатывать исключение, возникающее при инсерте дублирующихся значений. Ваш вариант с проверкой таска на существование перед добавлением организует вам хорошие проблемы, если вы вдруг задумаете обрабатывать таски хотя бы в два потока.
2) Быстрый поиск по id и active
Регулярный перенос done задач в любом случае хорошая идея - будет облегчаться основная таблица с тасками.
Средний
