Efficient way to keep a count of entries within a timeframe? e.g. How many actions did user do in the last 7 days I track user events - say, clicks - and would like to show them how often they've clicked in the last week, last 30 days, etc. So of course, as time progresses, that number needs to be updated.
Here is my naive attempt at just computing the number in a query:
Table format:
table_name
timestamp | timestamp with time zone
user_id | integer

Each row in the table represents another instance of the user event being logged.
The query I have right now:
SELECT count(*) FROM table_name WHERE user_id = 1 
  AND timestamp &gt;= NOW() - '7 days'::interval;

This works fine, especially with a bit of caching thrown in, but as someone who doesn't understand much about database internals, I have to wonder if there isn't a more efficient way than having to do a SELECT with a date comparison filter every time. Neither column here is indexed. So I'm curious, is there a better way? Like some special data type or something. Are there any problems with running a query like this frequently as the number of entries increases dramatically? Or is this actually the most common way to do it?
That's a pretty common way of doing things, and PostgreSQL is designed to handle aggregative queries for large datasets very well. Though adding an index on the user_id and timestamp columns would improve performance, if you have that ability.