Looking for an efficient way to build some kind of "FIFO" table
I would store day as a DATE. You can easily query the next seven days from that and generate the &quot;day number&quot; using a window function:
select id, row_number() over (order by day) as day_nr, value
from the_table
where day &gt;= current_date 
  and day &lt;= current_date + 7

To get rid of old rows, you simply run a DELETE statement with the necessary WHERE condition. To make that even more efficient, you can define the table with a range partition. Dropping a partition is a lot faster then running a DELETE on a large table.