3 ms·
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-
by DaiPlusPlus 6y ago
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).
- Retric 6y agoThat’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.
- nine_k 6y agoIf 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.
- DaiPlusPlus 6y agoIf 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.
- nine_k 6y agoWithin the same datacenter, quite likely. Across datacenters, I'm not so sure.
- Retric 6y agoIt’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.
- AtlasBarfed 6y agoAP systems are where actual scale is. Cockroach is a CP system. Spanner is probably similar, hardware clocks or not.
- DaiPlusPlus 6y agoI don’t see how CAP applies to distributed JOINs (provided the user is aware of it)
- p_l 6y agoConsistency 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