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...
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.
table scans are fast in modern columnstores
I guess that depends on your expectations of 'fast'. Even with our smallish dataset, both BQ and Keen had multi-second responses -- frequently 10+s. It was totally unacceptable for user-facing analytics. And we had a lot of customers making a lot of queries - it started to get expensive fast.
I'm sure 10s responses would be very 'fast' for terabyte-sized data volumes. But that's not the problem we were trying to solve.
Keen isn't a columnstore, it's a custom database built on top of Cassandra where they take JSON records and split them into compressed batches with each unique property stored in the CQL data model, and it's processed by Storm workers. It's an outdated architecture compared to modern columnstores that can now handle unstructured/nested data really well.
BigQuery is designed for throughput instead of latency. There is a minimum 3-5 seconds to schedule your query across the server pool before it even starts processing. It's also a single shared cluster for all customers so performance is variable, but the trade-off is that 100TB also takes seconds to scan.
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.
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.
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.
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 then find the right database that satisfies it e.g. don't pick MongoDB if you are doing lots of joins and don't pick PostgreSQL if you are doing wide-table feature engineering type analytics.
Right tool for the right job.
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 very clear on what your exact use case is going why the spend time analysing before even getting your project off the ground?
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.
* 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.
Because if you don't know what you want you are almost guaranteed to pick the wrong technology.
That's one way to look at it...but a bit shortsighted.
Requirements can and do change, and a well designed model in an RDBMS will be far more extensible than a similar one in NoSQL document store. So RDBMS' aren't the "wrong" technology, they the safest bet; not to mention most modern relational DBs already out-perform mongo, so the point is sort of moot anyway.
Because that sounds like magic.
Also MongoDB destroys any RDBMS (minimum 10x faster) if you have embedded structures instead of joining against 10 tables in a normalised design. Hence the importance of understanding your query patterns and domain before selecting the database.
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?
I would argue that since both Mysql and PostgreSQL are JSON document stores with mostly the same capabilities when it comes to querying and aggregation I don't see the advantage of using MongoDB at all.
I wouldn't even use MongoDB for caching when redis does a better job at it. Logs? I don't see why logs cannot be shoved into a RDBMS. Prototyping? create a table with a JSON field and a primary key. Distributed file system? I don't know any business which uses gridFS as a CDN, full text search? PostgreSQL does it better. So what is the job your are talking about? PostgreSQL is so much powerful for analytics because of the power of SQL.
I agree with your overall argument of PostgreSQL & MySQL >> MongoDB(for querying and aggregation). But in all the experiences I’ve had doing analytical work with both; Postgres easily comes out ahead. If you’re starting from a blank slate, I’d definitely recommend it over MySQL. Just update/feature addition rate along with the better community quality are enough for me to prefer Postgres over MySQL.
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.
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 need - "merchant" - which column stores usually say propose setting up isolated partitions for. If you do that, you can't ask questions across the whole system!
We ended up with Postgres. It was actually faster than MongoDB for simple aggregations, and joins made it much better/faster for complicated queries. Of course it only works quickly if your dataset fits in RAM, but terabyte-size instances are pretty affordable and give you a lot of headroom.
That was a couple years ago. I don't know what they're using now, probably the same. It was a frantic few weeks figuring out what was going to work - each of those systems made it to production and quickly discovered to be inadequate in vivo. If you're in a startup, even if you're using exotic NoSQL systems like Google Cloud Datastore or DynamoDB - just use Postgres or MySQL for analytics. It will work long enough for you to figure out something else when you need it.