Looking for an efficient way to build some kind of "FIFO" table I need to store measured data on a daily basis for a given period in the future, lets say 7 days. The current day should be always day 1.
Basic Example:

| id | day    | value |
|----|--------|-------|
| 1  | 1      | 10    |
| 2  | 2      | 20    |        
| 3  | 3      | 30    |
| 4  | 4      | 40    |
| 5  | 5      | 50    |
| 6  | 6      | 60    |
| 7  | 7      | 70    |


So at night the next day (day 2) becomes day one and all data will move up one day, and new data can be added.

Basic Example:

| id | day    | value |
|----|--------|-------|
| 1  | 1      | 20    |
| 2  | 2      | 30    |        
| 3  | 3      | 40    |
| 4  | 4      | 50    |
| 5  | 5      | 60    |
| 6  | 6      | 70    |
| 7  | 7      | 80    |


So the principle is kind of FIFO. I always look at 7 days in the future every night the perspective changes because tomorrow becomes today and day 7 can hold a new value.

What would be an efficient way to build something like this in postgres.
As it is now I would have to update all rows at night to move the values.
Is there a better way?
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.