How to do a search for the nearest row by index?

Question

PostgreSQL

Good afternoon.
Suppose there is such a table that reflects different versions of the same object over time:

CREATE TABLE foo (
  id INTEGER,
  updated DATE,
  PRIMARY KEY (id, updated)
)

-- Данные:
INSERT INTO foo (id, updated) VALUES
  (1, '2000-01-01'),  -- актуальная с 01 по 09 число
  (1, '2000-01-10'),  -- актуальная с 10 по 19 число
  (1, '2000-01-20'),  -- актуальная с 20 по 24 число
  (1, '2000-01-25'),  -- актуальная с 25 по 29 число
  (1, '2000-01-30')  -- актуальная с 30 и по сей день

The task is to find the current date :date_param string with :id_param.
The following request can be considered not a very desirable implementation:

SELECT * FROM foo WHERE id = :id_param AND date <= :date_param  ORDER BY updated DESC LIMIT 1

Theoretically, this query should be executed based on the index, without extracting and sorting the preliminary result. But it still looks a little bulky. And if you need to extract the actual records for several id values, then the query becomes even more complicated: I don’t even know how (I think it can be done using unnest or union).
The question is, is it possible to make such a search more elegant? With the ability to search for several "actual" lines at once.
Thank you.

Answer 1

1. Option 1: expr BETWEEN min AND max => here
2. Option 2: Interlayer: add an additional table of time intervals (dateRanges), in this one - an additional field with the record ID from dateRanges. dateRanges - support when adding a new record: the date is out of the last interval - create the next one.
If you add both options - this is the best optimal choice.
Because with a "waterfall" descent:
[Option_2]->[Option_1]->RESULT
You will significantly reduce the time for searching / selecting / sorting, and the required calculations. resources.
If you need a specific request 1 time - use only the 1st option.
If we need to repeat a specific query many times - we can reduce the search area based on the DateRanges table through a set of time intervals.

Answer 2

If the current date is the current day, then only active days can be stored in the table, and the data can be shifted according to the scheduler.