Sorry for the bullet points

I have

• a database table for “posts” each of which has a location (postgis point)
• users subscribing (phoenix channel) for updates in posts near their location (also postgis point, but stored in an ets table)

I want

• whenever a new post is inserted into the database (not necessarily from elixir app), notify (via phoenix channels) every user who has joined (and is still connected to the channel) and is within some radius around new post’s location

What I’ve tried

• created a trigger that calls NOTIFY with post data whenever a post is inserted
• subscribed to these NOTIFY messages from postgres via Postgrex.Notifications

What I’m struggling with

• right now I receive every post and need to “filter” and “dispatch” them on the elixir side, which is slower than I’d like (I keep subscribers’ (users) current locations and their socket ids in an ets table)

I’d like to filter posts in postgres before calling NOTIFY, but don’t know how to do that since postgres does not know the parameters by which to filter the data (currently connected users’ locations).

Should I try storing currently connected users’ locations in some table?

Should I try replacing phoenix pubsub (pg2) with postgres pubsub? Is that possible?

Postgres uses one process per connection, so it’s much harder to scale.

from One of the big differences that RethinkDB has going for it is that its connectio... | Hacker News, so probably not.

Would appreciate any advice.

It’s probably difficult to understand what it is I want. I’ll try to create a repo on github with what I have already.