How to make MongoDB not suck for analytics • Hacker News Board

Re: How to make MongoDB not suck for analytics

#81

Post by SegFaultx64 » Mon, Jul 02, 2018, 3:37 PM UTC

stickfigure wrote: post #36

I tried using MongoDB for the customer-facing analytics of a large e-commerce marketplace. It didn't work very well. The problem is that at some point you end up wanting joins. MongoDB was actually the third try. My first two attempts were BigQuery and Keen, neither of which worked out because they support only one index - time. Users want to slice and dice by various axes! And there's an obvious additional index you…

We went really deep on this recently, as we looked to migrate off Keen.io for that exact reason (only time based queries, also it's really slow).

We didn't look at MongoDB due to too many people on the team having been burned by it in the past, but after looking at a lot of stuff we settled on Redshift with a Postgres database in front of it using dblink and foreign data wrapper. This allowed us to have extremely fast reads of common queries with only a 5 minute lag on data becoming available.

It's amazing what you can build with Postgres. I might write up a blog post about our exact strategy if that would be interesting to people.

Re: How to make MongoDB not suck for analytics

#82

Post by gshulegaard » Mon, Jul 02, 2018, 5:08 PM UTC

SegFaultx64 wrote: post #81

stickfigure wrote: post #36

I tried using MongoDB for the customer-facing analytics of a large e-commerce marketplace. It didn't work very well. The problem is that at some point you end up wanting joins. MongoDB was actually the third try. My first two attempts were BigQuery and Keen, neither of which worked out because they support only one index - time. Users want to slice and dice by various axes! And there's an obvious additional index you…

We went really deep on this recently, as we looked to migrate off Keen.io for that exact reason (only time based queries, also it's really slow). We didn't look at MongoDB due to too many people on the team having been burned by it in the past, but after looking at a lot of stuff we settled on Redshift with a Postgres database in front of it using dblink and foreign data wrapper. This allowed us to have extremely fas…

+1 for me thinking that would be interesting. It wouldn't be something completely new for me, but I still think it would be interesting. I am also sure people not very familiar with Postgres would find even greater value in a post like that.

Re: How to make MongoDB not suck for analytics

#83

Post by stickfigure » Mon, Jul 02, 2018, 6:41 PM UTC

georgewfraser wrote: post #57

stickfigure wrote: post #36

I tried using MongoDB for the customer-facing analytics of a large e-commerce marketplace. It didn't work very well. The problem is that at some point you end up wanting joins. MongoDB was actually the third try. My first two attempts were BigQuery and Keen, neither of which worked out because they support only one index - time. Users want to slice and dice by various axes! And there's an obvious additional index you…

You say you need indexes because “users want to slice and dice by various axes”, but have you checked that the query planner actually uses these indices? When an aggregation includes a meaningful percentage of the table, a sequential scan will be faster than a series of indexed lookups. This is part of the reason why column stores don’t use indexes—they just aren’t that useful for analytical queries.

YMMV but yes, for our application they were very significant. In a marketplace app with a significant number of sellers, most queries will be highly selective if only because they'll usually be filtered by seller.

One of the top queries will be "show me my all-time sales". If your only index is time, you will touch your whole database every single time a customer asks for it...

Re: How to make MongoDB not suck for analytics

#84

Post by stickfigure » Mon, Jul 02, 2018, 7:04 PM UTC

threeseed wrote: post #43

stickfigure wrote: post #36

I tried using MongoDB for the customer-facing analytics of a large e-commerce marketplace. It didn't work very well. The problem is that at some point you end up wanting joins. MongoDB was actually the third try. My first two attempts were BigQuery and Keen, neither of which worked out because they support only one index - time. Users want to slice and dice by various axes! And there's an obvious additional index you…

You are completely contradicting yourself. On one hand you complain about using technologies before you have done a prototype and evaluated the product. Then you blindly tell startups to just use MySQL/PostgreSQL without having any idea of their use case or whether it matches their query patterns. If you are a startup the right way to go is to document your use case, understand what queries those use cases demand and…

I was wondering if I would get a comment like this.

With two engineers starting from scratch, we launched a product in three months that was making millions (per month, in profit) by six. When I hear "done a prototype and evaluated the product", I think you operate on a very different kind of timeframe. We needed a customer-facing analytics solution ASAP for the sales that our customers were already making.

This is why Postgres would have been the right choice from the start (mea culpa). It may not be the best solution, but it will be an adequate solution and get you through enough scale that you can worry about the million other holes to backfill. I thought I was being clever with BigQuery; it looked great on paper. Keen has better marketing but really the same problems. Mongo at least I was familiar with going into it, and that solution worked for a couple months, right up until the queries got complicated. Which in retrospect they were always going to.

In a runaway startup you're not going to be an expert in everything, or have time to plan out an optimal solution. Pick technologies that you can be confident will be "good enough for now" and give you time to find the boundaries of your particular problem domain. Postgres is a good axe to start with.

Re: How to make MongoDB not suck for analytics

#85

Post by stickfigure » Mon, Jul 02, 2018, 7:19 PM UTC

198394549 wrote: post #77

lomnakkus wrote: post #45

Earlier quoted context omitted.

Without wishing to put words in their mouth, I think parent poster's point might be that PostgreSQL will do at least a decent job at most things you'll want to throw at it. This is not the case for most of the NoSQL databases where you'll pay for lack of certain features either by a) having to write a lot of code, or b) bad-to-crippling performance for use cases it wasn't meant to solve. So, unless you're already ver…

>This is not the case for most of the NoSQL databases where you'll pay for lack of certain features either by a) having to write a lot of code, or b) bad-to-crippling performance for use cases it wasn't meant to solve. Can you give a common example of these? This article is referring to issues related to row vs column data stores, not sql vs nosql.

Having implemented effectively the same customer-facing analytics problem in BQ, Keen, Mongo, and Postgres, I'll tell you specifically:

* Column stores like BQ and Keen don't let you efficiently slice and dice data by factors other than time. If you're slicing by customer or product, your queries become incredibly slow and expensive. You start writing hacky shit like figuring out when your customer's first sale was so you can narrow the time slightly, but that barely helps.

* MongoDB doesn't do joins. So you denormalize big chunks of your data, and now you have update problems because 1) you have to hunt all that down and 2) you don't have transactions that span collections. Also the aggregation language is tedious compared to SQL, requiring you to do most of the work of a query planner yourself.

* Some other person in this thread said MongoDB was faster than Postgres, but I found quite the opposite to be true. For the same real-world workload, basic aggregations on an index, we found Postgres to be much faster than Mongo. No idea what that other person is talking about.

Re: How to make MongoDB not suck for analytics

#86

Post by manigandham » Mon, Jul 02, 2018, 7:54 PM UTC

stickfigure wrote: post #36

I tried using MongoDB for the customer-facing analytics of a large e-commerce marketplace. It didn't work very well. The problem is that at some point you end up wanting joins. MongoDB was actually the third try. My first two attempts were BigQuery and Keen, neither of which worked out because they support only one index - time. Users want to slice and dice by various axes! And there's an obvious additional index you…

Columnstores don't use indexes, and many don't even support them (like BigQuery). You may be taking about clustering, which you can use to improve compression and scan speed by sorting data by commonly queried columns but it's unnecessary, and even table scans are fast in modern columnstores that can prune partitions and use sophisticated metadata to calculate your answers.

Also it's SQL, what is preventing anyone from searching on any field they need? You don't need indexes for that. BigQuery only supports partitioning by a time-based column but that's more for cost control than speed, especially in your case where the dataset is small enough to be handled by postgres in the first place. Generally a mainstream RDBMS is the best choice for all things if the data fits, just because of the performance and usability available today.

Re: How to make MongoDB not suck for analytics

#87

Post by manigandham » Mon, Jul 02, 2018, 7:59 PM UTC

This is called ETL, to a data warehouse.

Regardless of the choice of primary database, this is nothing new and just shows how a lot of startup technical talent seems to be discovering the same things all the time, usually with needlessly convoluted approaches, and writing blog posts about it.

Re: How to make MongoDB not suck for analytics

#88

Post by stickfigure » Mon, Jul 02, 2018, 8:00 PM UTC

198394549 wrote: post #75

sb8244 wrote: post #72

Earlier quoted context omitted.

The world is messy. The application will grow over time and those requirements can't be known. I think the point parent is making is that a rdms will allow for that future flexibility whereas NoSQL comes with a lot of caveats that make flexibility challenging.

What is this lack of flexibility you are speaking about? As in, actual specifics.

I feel like this should be pretty obvious. I'm pretty sure there are students in a bootcamp somewhere learning "joins make it easy to construct complex queries; denormalization eliminates expensive joins but sacrifices flexibility and adds potential data inconsistency".

Real world example: Consider an Order table and a Visit table; conversion rates aggregate orders over visits. In Mongo you can denormalize some of the Visit data into Order, but what happens when you change the logic for computing conversion ratios? Or you want conversion ratios broken down by web browser, source tag, or any of the other data elements that live in Visit but you didn't denormalize ahead of time?

Re: How to make MongoDB not suck for analytics

#89

Post by scottshamus » Mon, Jul 02, 2018, 8:16 PM UTC

SegFaultx64 wrote: post #81

stickfigure wrote: post #36

I tried using MongoDB for the customer-facing analytics of a large e-commerce marketplace. It didn't work very well. The problem is that at some point you end up wanting joins. MongoDB was actually the third try. My first two attempts were BigQuery and Keen, neither of which worked out because they support only one index - time. Users want to slice and dice by various axes! And there's an obvious additional index you…

We went really deep on this recently, as we looked to migrate off Keen.io for that exact reason (only time based queries, also it's really slow). We didn't look at MongoDB due to too many people on the team having been burned by it in the past, but after looking at a lot of stuff we settled on Redshift with a Postgres database in front of it using dblink and foreign data wrapper. This allowed us to have extremely fas…

Please do. It would be great to hear how a company took a pragmatic approach that worked well enough for them. The mindshare right now seems like people are pushing for using combination of five different technologies to do simple aggregation queries. Sometimes knowing a single tool really well is all you need...

Re: How to make MongoDB not suck for analytics

#90

Post by dvlsg » Mon, Jul 02, 2018, 8:31 PM UTC

naranha wrote: post #78

198394549 wrote: post #71

Earlier quoted context omitted.

>The problem is that at some point you end up wanting joins. It can join with the $lookup function these days. Although it is only to a "non-sharded collection". I don't know why it can't join to a sharded collection when the join is on the same shard though. There is also the option of using $in with a list of things you have pulled down in another query. Then there are client-side joins.

> Then there are client-side joins. AKA what you are doing when writing your SPAs with their own state management. Server-side joins rarely make sense in that context. For reporting/analytics.. yes. But these can be delegated to external system/databases optimized for that task. With elasticsearch for example you get very far very quickly without the need to write any SQL joins.

Would you suggest elasticsearch over sql for analytics like these? We're actually looking at a very similar situation, and I have a hard time believing aggregations in elasticsearch (especially when no full text indexes are required) are a better fit than sql. That could be my lack of experience with elastic though.