idi527

Filtering outgoing messages to pubsub by location

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.

3 comments

/phoenix #phoenix_pubsub #postgres #postgis

1 1061 3

2018-03-13 13:35:40 UTC

Most Liked

outlog

this sounds like the best plan - to allow GIS querying/filtering at the earliest possible..

what I don’t understand is how it can be slow currently, what is the current code/flow?

this I don’t quite understand do you have multiple listeners to the NOTIFY? - I would have one listener that gets the post INSERT and then queries/filters the user lat/lng data against the post lat/lng and does the appropriate channel broadcast(s) - also do you have one channel that all users share or do you have per user channels?

Post #1

outlog

one idea: I notice each user can have a different radius, so when you get to saving that to postgres, it might make sense to store that as a geo shape in pg (and not a point) - so that table has a lot of different “circles” (or rather polygon approximations I think) in it and then when the post comes in you can have a very simple (and fast!) query for those where the post geo point is within.. (and all the individual user radii etc is taken care of.)

also you probably want to process trap_exit and then mark that location as inactive in the channel terminate (or delete it)..

keep us posted..

btw: there is boltun for the postgres notifications stuff - boltun | Hex

Post #3