Indexing only for most recent entries in PostgreSQL

Question

Consider the following table:

|-----------------------------------------------------|
| raffle                                              |
|----|---------|----------|-----|---------------------|
| id | shuffle |  user_id | ... |           notify_at |
|----|---------|----------|-----|---------------------|
| 1  | 4D6G8Z1 |      542 | ... | 2019-12-01 14:00:00 |
| 2  | 64G264D |        6 | ... | 2019-12-28 14:00:00 |
| 3  | 4IPF93D |       58 | ... | 2020-01-01 14:00:00 |
| 4  | D25LF03 |       58 | ... | 2020-01-14 14:00:00 |
| 5  | G04LDWE |      684 | ... | 2020-03-02 13:00:00 |

In this table, most requests are not done to the id column, but to the user_id and notify_at, which is a 64-bit timestamp (no 2038 Bug):

SELECT * 
  FROM [raffle] 
 WHERE [user_id] = ? 
   AND [notify_at] = ?

The table grows by the minute, but that is not the problem, but rather, the records for the notify_at in the current month are most accessed than the rest. In 10.000.000 records, an index of the user_id and notify_at sums 160MB, which only 1% of these are heavily accessed.

Is there a way to optimize an index (or any other strategy) to make retrieval of the records for the current month snappier (as in, "trying to use the index instead of sweeping the whole table for records)?

Update 1: I'm asking this way because the table holds many notifications. This would grow large over time, and the SQL query would only take those in the current month:

SELECT * 
  FROM raffle 
 WHERE user_id = 542 
   AND notify_at > '2020-01-01 00:00:00' 
   AND notify_at < '2020-01-31 23:59:59'

As you can see, the index would also grow larger.

Answer 1

The index type you are looking for is Range Type

Answer 2

the other possible solution requires modifying the table. This more simple approach using simple equality method and uses composite index

create table plrange ( id serial primary key,
                       user_id int,
                       notify_start date  default now()::date,
                       notify_end date default now()::date + 30);

 insert into plrange (user_id, notify_start, notify_end) 
 select (random()*100)::int, 
         now()::date - (random()*10)::int ,
         now()::date
    from generate_series(1,1000);


  create index on plrange using btree(user_id, notify_end );  

  explain select * from plrange where
  user_id =4 and notify_end > now()::date

Answer 3

Asked to give a sample of how to use RANGE type. I do not think it possible to implement this solution without adding/modifying column to the table, during writing this i thought of another solution that would not use Range Type but still requires modifying the table will add it as another answer

There are a few gotchas with range type with indexes and the special operators,

1. the GIST index the preferred index as it has allot operators
2. Btree and hash indexes only do = or != so not very useful
3. GIST index does not support composite indexes.
4. Range type uses different operators read the manual

Range type is a very useful tool to quickly find a value so here is 1 method to do that

create table plrange ( id serial primary key,
                  user_id int,
                  notify_range daterange);

insert into plrange (user_id, notify_range) 
  select (random()*100)::int, 
      daterange(now()::date - (random()*10)::int , 
            now()::date )
    from generate_series(1,1000);

create index on plrange using gist(notify_range); 

create view Last_30days as select * from plrange 
    where notify_range @> (now()::date -30)::date

select * from last_30days where user_id = 7

This creates a view that returns all the records that are in the last 30 days which can be filtered down more .