title: How to scale geospatial with relationships query / a Tinder like backend system
body: <p>I have a application where I search for users based on their location, last activity and a number of other filters, and have now reached a point where the performance is not good enough but must be improved due to increasing number of users. However, Im not sure what the best way forward is and would appreciate any input!</p>
  
  <p>My basic setup is two tables, lets call them users and relations. Each user has a number of properties, such as location, last_activity, and various properties. Each user can have a relation with another user (friend / enemy).</p>
  
  <p>The query I want to do (and that is heavy to do) is search for nearby users fulfilling a number of properties that the user not yet have a relation with. The user will then walk through the list and add relations to each user in the list. When done query for another list and repeat. </p>
  
  <p>Right now its implemented in PostgreSQL with PostGIS for geo index, but it is not scalable. </p>
  
  <p>Pseudo psql:</p>
  
  <pre><code>CREATE TABLE users
  (
    id serial NOT NULL,
    location geometry,
    last_active timestamp NOT NULL,
    property1 int NOT NULL
  )
  
  CREATE TABLE relations
  (
    user_id int NOT NULL,
    other_user_id int NOT NULL,
    relation_type char(1) NOT NULL
  )
  </code></pre>
  
  <p>and the query</p>
  
  <pre><code>nearby := SELECT * FROM users 
      WHERE property1 &gt; 1
      ORDER BY location &lt;-&gt; 'my location'::geometry
      LIMIT 1000
  
  SELECT * FROM nearby u
      WHERE NOT EXISTS (SELECT * FROM relations where user_id = u.id)
      AND radius &gt; ST_Distance(location::geography, 'my location'::geography)
      ORDER BY ST_Distance(location::geography, 'my location'::geography) * (current_timespan - last_active)
  </code></pre>
  
  <p>The query is split into two to make sure the first part is using the geo index on location. It works fine as long as its limited to a reasonable small number, like 1000. The problem comes when all the users returned by the first part is filtered away in the second part. </p>
  
  <p>Any suggestions as to how to redesign this system to make it support millions of users with hundred millions of relations?</p>
  
  <p>The whole system very similar to what Tinder must be doing, find users you have not yet interacted with and sort on activity time, location and filter list on a number of properties such as age and gender.</p>