Hacker News Reader

> The meaning of "joins don't scale" is that they don't scale across database servers, when your dataset is too big to fit in a single database instance.

You're correct. However, most applications databases are fine with billions of records on a single instance. It's a rare application that needs the level of scale you're talking about. In other words, a typical startup doesn't, at least not at first ....

> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day

Worth noting that both Twitter and Facebook started with MySQL.

https://dzone.com/articles/facebooks-user-db-is-it-sql-or-no...

http://highscalability.com/scaling-twitter-making-twitter-10...

> Worth noting that both Twitter and Facebook started with MySQL.

And both are still using it as primary data store.

According to Stanford prof who launched ramcloud Facebook indeed uses MySQL. But MySQL by itself was hopelessly slow. That's why Facebook has over 4000 memcache servers (this as of 2009). By the time you add ram underlying disks he estimated 75pct of all Facebook data is cached in memory. That's why it works.

Instead of Stanford as source here is an example from the FB engineering blog about migrating from HBase to MySQl for messaged in order to reach performance: https://engineering.fb.com/core-data/migrating-messenger-sto...

And yes, they certainly cache data in multiple layers using different technologies but from all I as an outsider can see MySQL still seems to be their source of truth for most services. Meaning all writes go to MySQL and at least when going deep in history or doing searches (which typically can't be served from a cache) hit MySQL.

memcached + rdbms is really super cheap and effective. Properly architected it can be tolerant of all sorts of latency/failure/CAP issues, up to some limits of course.

there was a variant library that allowed you to replace memcached servers on the fly without worrying about the size of the cluster list or having to recache.

Define primary? Are you claiming every tweet lives in MySQL?

http://highscalability.com/blog/2011/12/19/how-twitter-store...

that's nearly a decade old

if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day

Neither twitter nor facebook started with anything like the architecture they use today, nor did they have to, nor could they ever have known they'd need it or even what they would need.

> whether you pick an RDBMS or NoSQL solution is mostly a tooling issue.

Eh, when the time comes to scale I’d still very likely pick a RDBMS but hand my team copies of Eric Evans and ask them to design data schema and sharding scheme such that any given entity has a bounded context that doesn’t join across the network.

Probably then using a Merkle-like structure for shard selection.

However as you say there’s no need to start like that. A big fat server can do a lot. It may be cheaper just to run a multi-TB database server and do everything in memory, than invest in re-engineering and all the flow-on operational costs of a distributed store. For sure a DBA would have an easier time tuning it, even if NUMA is in the mix.

And building out a distributed store without restricting your ability to pivot requires more experience than almost anyone but the big clouds possess.

edit/addendum: when you take a bounded-context domain-specific approach to your data persistence you will probably will still need to join things, but the option arises to do so periodically rather than on-demand, and in batches i.e it has more of a reporting flavour than OLTP, and work can be managed for quiet times or offloaded to map-reduce spot instances or whatever you like. From the outside it looks like an eventually-consistent application but only with regards to the parts that don’t need to change instantly.

> Eh, when the time comes to scale I’d still very likely pick a RDBMS but hand my team copies of Eric Evans and ask them to design data schema and sharding scheme such that any given entity has a bounded context that doesn’t join across the network.

That will only help you scale out to N servers, where N is the number of bounded contexts. And it might end up being less helpful than it sounds, if the scaling needs are dominated by data in one or two bounded contexts.

Still, it could be a useful approach.

No, the dimension is in the other axis. Otherwise there’d be no need for sharding keys.

But if you have nice natural sharding keys for your data (the most common example is something like TenantId), how do bounded contexts help?

EDIT: Perhaps you mean something different by bounded context than what is usually meant in the context of DDD, where they separate groups of entities not data within those entities.

No, I mean exactly that, but tenant_id sounds like a SaaS or a Shopify. Their pods don’t have this kind of scale to begin with, the datasets just don’t have more than a few billion entities. Assuming there’s a “nice natural sharding key” at all already limits scope to a subset of applications, most of which you can stick inside a single instance, rendering the point moot.

> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins. And then whether you pick an RDBMS or NoSQL solution is mostly a tooling issue.

Even then you probably get a few years of use out of that simple setup while you work on migrating to a horizontal solution. And by then more likely than hitting Twitter-scale, your company has gone bankrupt.

And while we're making pie-in-the-sky long-term plans, the amount of RAM you can shove into a single server chassis is growing faster than the world population. So you might as well use joins -- the people maintaining your code in the year 2999 will thank you!

> Obviously indexed joins on a single database server scale just fine, that's the entire selling point of an RDMBS!

Is it really obvious, though?

In my experience, many people consistently fail to grasp this point. It seems likely that the underlying reason is that it's easy for most people to conflate nested loop join with hash join (or with merge join). Hash join really doesn't scale in the sense that the author conveys (though this in itself many not matter, because they're not really in competition in any practical sense).

When you point out that the key to good performance is to ensure that query results are fetched rather than searched and computed, it sounds obvious. It still seems very helpful to connect the dots.

reality is 99% of companies out there will be just fine with a single db server

all this nosql fad is simply because SQL is hard and newbies would just rather call it old and slow because they can't be bothered to learn it

SQL is easy. Data is hard.

SQL queries that can beat NoSQL are easy but you still have to think about schema. Which you can just not do in K/V(you just need the key) or NoSQL.

As such it makes starting really easy which is the allure. Many seem to hope they can avoid doing schemas and well architected data. Or push it in to the future. The rude awakening comes when you do need to run complex queries (and you do not have SQL or joins at all). Or when the people do not understand they have to take care about data integrity in app layer now.

At the end people going for NoSQL have a much harder to manage system without the tools (like SQL, schemas, constraints)

I think NoSQL is great for very specific purposes. Caches, keeping state, logging stuff. For many things it is misused and postgre with jsonb can do everything you need (faster, easier) without rude awakenings.

A single DB server is a SPOF. You normally need a hot standby if you care about availability. You can also replicate transactions to read-only copies to alleviate the read load.

What most companies can do well with is a single master DB which handles updates.

(Some companies can't, though; e.g. GrubHub switched to Cassandra because no monolithic RDBMS could handle the write load.)

How many op/s was that? Bytes/s? Just curious because I was involved in migrating a write-heavy service off of C* to SQL Server. Our writes have higher latency now, but using managed SQL is worth it. (much cheaper too)

I personally enjoy writing SQL as a novice programmer. The basics are easy. Getting to know all the intricacies of what executes how fast and such is HARD.

Also: Designing a system that is prepared for unknown unknowns is practically impossible. Initial design is not too bad, but the continuous adjustments kinda suck.

Your single server is a CA service.

If you want CP or AP, then you need a "fad" database.

single server sql databases are not "CA" it doesn't make sense to talk about cap theorem like this.

I don't mean to say some companies like Google don't need NoSQL. Obviously they do.

Also, no you don't. It's possible to have distributed RDBMS.

Cross-database JOINs should work if the database server is written to handle that - take Spanner or CockroachDB: they’re both capable of high-performance cross-database and cross-server joins.

I think the poor reputation that cross-server joins have comes from servers like MS SQL Server that do cross-server joins through ODBC which is obviously going to be terrible for performance (though I don’t know exactly how - or even if - SQL Server handles a highly-selective INNER JOIN differently compared to a join with lower specificity on the remote table).

That’s extremely dependent on how much data needed to be passed around. Network bandwidth is extremely limited. Loading an extra 10GB of data into RAM from an array of SSD‘s is no big deal, tossing 10GB of data to each of 50 servers needs to be a rare event.

If you value speed over storage, you can just replicate all the tables you need to join to every node, do a join on a subset on each node, then merge. It would improve latency quite a lot.

The problem arises when you can't fit an entire table on a single node, no matter how big your SAN is. But this would be really big data, few companies face this problem.

It’s not just a problem of dataset sizes. You now need to send every update to every DB server. For something like an open world MMO it’s easy to start hitting billions of updates per second. Databases have hard trade offs and you need to make compromises somewhere.

If you’re using a SAN, then surely every server node has at least read-only access to every table? The only thing they’d missing is in-memory indexes and cached execution-plans.

Within the same datacenter, quite likely. Across datacenters, I'm not so sure.

AP systems are where actual scale is. Cockroach is a CP system. Spanner is probably similar, hardware clocks or not.

I don’t see how CAP applies to distributed JOINs (provided the user is aware of it)

Consistency of the data joined. That's why Spanner has both hw clocks AND notion of time of update baked into its transaction system, because it allows to both distribute the data up front and give guarantees about it being in sync

> if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins.

If your company end up being as large as Twitter or Facebook, you probably can raise enough money to overhaul the entire infrastructure to make it efficient in your custom scenario, which is much better than prematurely optimizing when you have 100 users.

Indeed; the initial Twitter implementation ran on Ruby on Rails, of all things. Several hundred copies. (Remember "fail whale"?)

Then they re-engineered it to scale.

If they started not with a simple RoR prototype but with a ton of scalable infrastructure, they'd launch maybe a couple years later, and burn much, much more money before the launch.

> they'd launch maybe a couple years later

If at all; probably obvious, but important to point out that in a startup environment this kind of mistake could just outright kill the company.

I work on an open source project (Sandstorm[1]) that was started by a now-defunct startup. There's a repo that's unmaintained at this point (It might still work?) containing a high-scalability backend that had been used to manage Sandstorm Inc's hosted offering. Apparently they never got to the scale where they couldn't have thrown one giant server at it and been fine, and it pulled a lot of resources away from other (ultimately more important) development tasks.

...and now I'm stuck developing against mongo for no good reason :P

[1]: https://sandstorm.io

> The meaning of "joins don't scale" is that they don't scale across database servers, when your dataset is too big to fit in a single database instance. Joins scale across rows, they don't scale across servers.

But people have been saying "joins don't scale" for a lot longer than there have been companies that have today's giant's horizontal infrastructure. It's just been a sort of hipster anti-hipster anti-correctness brag since at least 1996, when I first heard it.

> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins.

No, both of them started with MySQL. If you make it, you have plenty of resources to throw at the scaling problem, it's a good problem to have. A bad problem to have is running out of money and shutting down because you over engineered from the start. Don't do that. Most startups fail, and you're no exception. Build one to throw away, and if you're lucky enough to succeed, you can use your new-found resources to rebuild what needs to be rebuilt.

> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day

The only way you’ll know this is if you are already Google or Facebook

Best just always start with a RDBMS and then split out subsets if needed down the line