rPman

Если нужны сложные индексы, то в EAV модели появляется дополнительная таблица объектов с выделенными полями под нужные атрибуты, наполняемая тригерами, и уже на них настраиваются индексы.

Скажи, зачем ты выбрал EAV модель? Зачем создал себе проблем?

В чем проблема каждому атрибуту создать колонку в таблице, сотни колонок никаких особых проблем (главная рекомендация - не делать default value, если в будущем понадобится удалять добавлять колонки), структура отлично кодом модифицируется и анализируется средствами базы данных, и главное гибко организуются многоуровневые индексы, что сложно реализовать на EAV

Это худшая задача для реляционных баз данных, что там будет происходить с foreign_key я вообще молчу, все решаемо но скорости будут не очень

Если нет связанных таблиц (что логично, твой id на самом деле не идентификатор, не вводи народ в заблуждение и назови по другому) в твоем случае скорее всего можно схитрить, можно key_id считать на лету, т.е. ключевое поле с autoincrement, но назови его по другому, например key_id_orig
1+key_id_orig-min(key_id_orig)

но это сработает только если удаление возможно только самой старой записи, если же будут удаления из середины, то порядок придется пересчитывать по другому, через сортировку всей таблицы по key_id_orig и подсчету колонок функцией row_number

В комментариях тебе уже ответили вариант с order by. Запрос order by и limit очень тяжелый для sql сервера, его очень сложно оптимизировать.

Есть еще min max но тебе нужны id, значит вложенный запрос по timestamp, не уверен что это будет быстрее (лучше поэкспериментируй)

select * from xxx where
   timestamp=(select min(timestamp) from xxx where timestamp > ...)
or timestamp=(select max(timestamp) from xxx where timestamp < ...)

p.s. я заметил что ничего не сказано про одинаковые значения timestamp в разных записях, если брать limit 1 то будет выбираться случайная запись (точнее 100500 раз одна но в какой то момент другая - undefined behavior), этот запрос выдаст все записи с одинаковыми timestamp

Вообще то sql базы данных не очень подходят к задачам, где есть относительный порядок, будут накладные расходы, причем сильно заметные при больших размерах и больших количествах запросов.

В каких то ситуациях можно очень СИЛЬНО оптимизировать задачу, если есть какое то знание о том как данные записываются в таблицу. Например если известно гарантированное окно времени, в течении которого поступают данные, и их не сильно много можно просто сделать

select * from xxx
where timestamp > date - const_interval
  and timestamp < date + const_interval

а затем уже среди этих данных выбирать по выше определенным алгоритмам или даже на бакэнде

Если изменений данных значительно меньше запросов на чтение (особенно если модификации не предполагаются и есть только добавления), то можно складывать искомые значения сразу в момент создания записи (добавлять id новой записи в предыдущую, и тут же ее id в новую), делать это можно как тригерами так и на бакэнде, накладных расходов на такую запись мало зато на чтение данные доступны за константное время, максимально быстро

select p2.*,p3.* from xxx p1
  left join xxx p2 on p1.id=p2.previous_id
  left join xxx p3 on p1.id=p3.next_id
where p1.timestamp=...

в данном случае строчка будет одна, данные раскиданы по колонкам p2.* и p3.*

В каких то ситуациях можно даже завести отдельный сервис, службу, дающую нужный ответ на порядок быстрее чем любой sql (например за счет того что данные хранятся в оперативной памяти), очень часто этим сервисом может стать тот же бакэнд (если это не cgi приложение), дабы не множить сущности, но если речь идет не об одной физической машине а о кластере, то там есть варианты и сложности.

скорее всего php устанавливался вручную готовым пакетом, все .so идут в поставке по умолчанию
настраивается в php.ini

вот пример (пути могут другие быть)

varchar/text/bytea в postgres используют одну и ту же технологию хранения и производительность будет напрямую зависеть от реального размера строк и от оптимизаций
https://habr.com/ru/company/tensor/blog/498292/

я делал (в качестве эксперимента) с помощью создания копии базы через репликацию и опционально снапшоты виртуальной машины.

Боевая база данных в режиме master, резервная копия (запускаемая в виртуальной машине) настроена в репликацию к базовой в режиме slave, в необходимый момент резервная останавливается, берется копия файлов базы либо создается снапшот файловой системы или средствами виртуальной машины, база данных для резервной копии снова запускается (вместо остановки можно приостанавливать репликацию но это не так красиво) и паралельно запускается снапшот для тестовых задач, по окончанию этих тестов снапшот удаляется.

В этой схеме база slave работает как оперативная резервная копия, в случае проблем с мастером ее безопасно будет сделать рабочей, с почти моментальным вводом в строй, а возможность ее приостановить и снять копию, боевая master база продолжает при этом работать. Снапшоты виртуальной машины или файловой системы позволяют получить моментально рабочую копию файлов базы данных, вне зависимости от ее размеров, если ее делать когда эта база остановлена, файлы будут находиться в консистентном состоянии. Само собой версии софта должны быть одинаковыми.

Кстати снапшоты виртуальной машины позволяют делать копию запущенного приложения (на основе этого реализована миграция, вручную это можно сделать на kvm), правда запускать эту копию придется в отдельной сети, так как ip адрес у нее будет совпадать с оригиналом.