Дизайн базы данных для временных рядов

Я бы выбрал подход с одной таблицей, возможно, разделенный по годам, чтобы было легко избавиться от старых данных.

Создайте индекс, например

CREATE INDEX ON a (date_trunc('hour', t + INTERVAL '30 minutes'));

Затем используйте свой запрос, как вы его написали, но добавьте

AND date_trunc('hour', t + INTERVAL '30 minutes')
  = date_trunc('hour', asked_time + INTERVAL '30 minutes')

Дополнительное условие действует как фильтр и может использовать индекс.

Вы можете использовать UNION двух запросов, чтобы найти все временные метки, наиболее близкие к заданной:

(
  select t
  from a
  where t >= timestamp '2019-03-01 17:00:00'
  order by t
  limit 1
)
union all
(
  select t
  from a
  where t <= timestamp '2019-03-01 17:00:00'
  order by t desc
  limit 1
)

Это позволит эффективно использовать индекс на t. В таблице с 10 миллионами строк (~ 3 года данных) я получаю следующий план выполнения:

Append  (cost=0.57..1.16 rows=2 width=8) (actual time=0.381..0.407 rows=2 loops=1)
  Buffers: shared hit=6 read=4
  I/O Timings: read=0.050
  ->  Limit  (cost=0.57..0.58 rows=1 width=8) (actual time=0.380..0.381 rows=1 loops=1)
        Output: a.t
        Buffers: shared hit=1 read=4
        I/O Timings: read=0.050
        ->  Index Only Scan using a_t_idx on stuff.a  (cost=0.57..253023.35 rows=30699415 width=8) (actual time=0.380..0.380 rows=1 loops=1)
              Output: a.t
              Index Cond: (a.t >= '2019-03-01 17:00:00'::timestamp without time zone)
              Heap Fetches: 0
              Buffers: shared hit=1 read=4
              I/O Timings: read=0.050
  ->  Limit  (cost=0.57..0.58 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)
        Output: a_1.t
        Buffers: shared hit=5
        ->  Index Only Scan Backward using a_t_idx on stuff.a a_1  (cost=0.57..649469.88 rows=78800603 width=8) (actual time=0.024..0.024 rows=1 loops=1)
              Output: a_1.t
              Index Cond: (a_1.t <= '2019-03-01 17:00:00'::timestamp without time zone)
              Heap Fetches: 0
              Buffers: shared hit=5
Planning Time: 1.823 ms
Execution Time: 0.425 ms

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

Вышеприведенное можно использовать для состояния IN:

select *
from a
where t in ( 
  (select t
   from a
   where t >= timestamp '2019-03-01 17:00:00'
   order by t
   limit 1)
  union all
  (select t
   from a
   where t <= timestamp '2019-03-01 17:00:00'
   order by t desc
   limit 1)
);

Если вы знаете, что у вас никогда не будет более 100 значений, близких к запрошенной метке времени, вы можете полностью удалить запрос IN и просто использовать limit 100 в обеих частях объединения. Это делает запрос немного более эффективным, так как нет второго шага для оценки условия IN, но может возвращаться больше строк, чем нужно.

Если вы всегда ищете метки времени в одном и том же году, то разбиение по годам действительно поможет в этом.

Вы можете поместить это в функцию, если это слишком сложно для запроса:

create or replace function get_closest(p_tocheck timestamp)
  returns timestamp
as
$$
  select *
  from (
     (select t
     from a
     where t >= p_tocheck
     order by t
     limit 1)
    union all
    (select t
     from a
     where t <= p_tocheck
     order by t desc
     limit 1)
  ) x
  order by greatest(t - p_tocheck, p_tocheck - t)
  limit 1;
$$
language sql stable;

Запрос становится таким же простым, как:

select *
from a
where t = get_closest(timestamp '2019-03-01 17:00:00');

Другое решение — использовать расширение btree_gist, которое предоставляет оператор «расстояния» <->

Затем вы можете создать индекс GiST по метке времени:

create index on a using gist (t) ;

и используйте следующий запрос:

select *
from a where t in (select t
                  from a
                  order by t <-> timestamp '2019-03-01 17:00:00'
                  limit 1);