Sorry for the bullet points
- 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)
- 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
NOTIFYwith post data whenever a post is inserted
- subscribed to these
NOTIFYmessages from postgres via
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 https://news.ycombinator.com/item?id=12649826, 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.