title: Efficiently calculate number of currently active users
body: <p>I'm currently stuck on figuring out how to efficiently calculate the number of active users on any given "page" on an app. I'm using PostgreSQL and have a table like this:</p>
  
  <pre><code>CREATE TABLE user_is_viewing_page (
    user_id BIGINT,
    page_id BIGINT,
    timestamp TIMESTAMP
  );
  </code></pre>
  
  <p>Each user is going to POST to a server every 10 seconds or so with the id of the page they are viewing and a new row will be inserted into the database.</p>
  
  <p>The current query I have to count the number of "active" users on any given page is:</p>
  
  <pre><code>SELECT COUNT(DISTINCT user_id)
  FROM user_is_viewing_page WHERE page_id = 1 
                            AND timestamp &gt; CURRENT_TIMESTAMP - INTERVAL '10 seconds';
  </code></pre>
  
  <p>I'm wondering what the most efficient way to use this query is. Keep in mind that I need to access this count fairly often (every 5-10 seconds per page).</p>
  
  <ul>
  <li>Should I create a materialized view using the query? (Knowing that I have to access it often)</li>
  <li>Should I just run the query by itself every time I need it?</li>
  <li>What is the best way to index my table to allow quick lookups?</li>
  </ul>