Comment by RubyPinch | Hacker News Reader

Comment by RubyPinch | Hacker News Reader

> But this would have been totally disastrous under any type of serious load. Even if I would have simplified the above query to only include three conditions/sorting operations, it would have been disastrous. Why? Because this is not how a database is supposed to be used. A database should query only on one index at a time, which is impossible with these geospatial queries.

> On the database side, I separate the snaps into a few different collections: all snaps, most liked snaps, newest snaps, newest valid snaps and so forth.

Pardon my ignorance, but don't most databases have some method of handling these issues?

(defining multiple indexes for use, having support for geospatial data, having support for like, subsections of the existing dataset, etc?)

I thought that the main goal was to offload the developer's code's logic onto the performant database, as opposed to offloading the database's logic and caching onto the developer's code? is the former not practical?

Yes, with Postgres (which I am most confident to talk about), most precisely using PostGIS, you can do that in a matter of hours using it for geo-queries and indexing for getting important stuff (new, trending, etc...). Plus Postgres is supported basically everywhere in any tech stack. I still don't get one point, why people totally ignore SQL dbs by default with new products? I know MongoBD, RethinkDB, CouchDB, etc... are really fascinating solutions, but why not considering SQL eliminating it by default? I am just curios.

I think its because the only really viable scaling option for Postgres is vertical scaling. Even just setting up any sort of replication with automatic failover is still a pain (multimaster is not yet built in, master-slave also needs 3rd party failover program...)

So, how large would his application have to get before that became a problem?

Replication with automatic failover? I'd go for it immediately, unless you are okay with long downtime and some data loss in case the server goes down.

But if you can live with that, then yes, you're unlikely to have actual scaling problems - at least not for projects like the OP.

That's only needed for the coordination... that said, it would be easy enough to segment channels based on a certain precision of geohash... messages sent target 9 channels, your current and neighboring channels... you subscribe to the channel you are in, and this updates every N seconds.

Channel position/calculation can happen client side, and subscribe/unsubscribe can happen server-side. Though that may leave room for unscrupulous behavior, it could be locked down a bit more by moving sub/unsub server-side.

The issue will be growth/routing/rerouting of channel data... even then, you can get pretty far with RabbitMQ backed socket.io ... you might need to custom create something before hitting 10M simultaneous users, which at current growth rate would be an issue anyway.

Yeah, I don't get it either. Even without geospatial features its not too hard to put an index on (lat, lng) and then run a between query for the 4 given coordinates taking the minimum and maximum of those 4 latitudes and longitudes. Need to also sort by likes/abuse reports? Add those to composite (compound) index too.

No need to manage separate collections.

Don't do that... calculate a geohash, and send to self and neighboring chat channels identified by geohash. Then you don't even need lookup, only chat routing... auth/isolation/provision of connection is a related issue though.

Didn't know about geohash. So simple and obvious in hindsight! Thanks!

Reply on news.ycombinator.com