3 ms·
In most relational database implementations, joins stop scaling after 10-50 million rows or so assuming an online transactional site. A time series data wareho
by parasubvert 13y ago
In most relational database implementations, joins stop scaling after 10-50 million rows or so assuming an online transactional site.
A time series data warehouse could go into the billions of rows with scalable joins with partitioning and bitmap indices ... but is also only applicable in the unlikely case you could afford oracle at $60-90k/CPU list price
Also, most databases that aren't Oracle don't have high performance materialized views to "preprocess" joins at upsert time, therefore people resort to demoralized tables and their own custom approach to materializing those views.
Then even denormalized tables begin to stop scaling at around 250 million to 500 million rows. So people resort to sharding managed in a custom way.
I haven't even begun to express the scalability impacts of millions of users on a LRU buffer cache used in most RDBMS - that usually is resolved through an in-memory cache (Memcached, Redis) whose coherency is also managed in a custom manner. Or you could spend $$$ for Coherence, Gigaspaces, Gemfire, etc. but that's also unlikely in most web companies.
At the end of all this, even if you bought a cache, you wonder why you're using an RDBMS at all since you're so constrained in your administrative approaches. Cue NoSQL.
of course in practice many devs ignore all of this history and "design by resume" assuming their new social-mobile-chat-photo-conbobulator will be at Facebook scale tomorrow.
- olavgg 13y agoThis is not true at all. I've worked on several databases with billions of rows in several tables. A good solution for improving your query performance is to use a multi column index http://www.postgresql.org/docs/9.3/static/indexes-multicolumn.html http://www.postgresql.org/docs/9.3/static/indexes-multicolum...
- parasubvert 13y agoWhat part isn't true? I'll restate my narrative: Single instance, normalized, unpartitioned databases run into scaling problems the several-hundred million row range especially when under heavy concurrent load. But once you start moving to multi-instance, partitioned databases, you start to lose the benefits of the relational model as most databases have to restrict how you accomplish things -- e.g. joins are severely restricted.
- batbomb 13y agoI routinely deal with joins on a 100 million row table and they work just fine. Other than that, I also use a 10 billion row table for searches. This is in Oracle.
- frederickf 13y ago"demoralized tables" :)
- darkmoth 13y agoOracle will handle anything you throw at it, assuming you have the $$$. Ebay uses it for 2 Petabytes of data: http://www.dba-oracle.com/oracle_news/news_ebay_massive_oracle.htm http://www.dba-oracle.com/oracle_news/news_ebay_massive_orac...
- parasubvert 13y agoThat's a link discussing an analytic database from SEVEN YEARS ago. eBay has moved on. Please understand what I am saying: - Traditional database architectures have limitations on what you can express in SQL for highly available and scalable online transaction processing once you introduce partitioning and clustering. - Oracle has probably the best support for partitioning and clustering out of all RDBMS, but even that has limits in the billions of rows - Many companies do not use Oracle for business reasons (licensing/sales/pricing practices) What I am not saying: - Oracle sucks (it's the most feature complete and robust RDBMS out there and is ); - Oracle is not used (Amazon, Yahoo, eBay, etc. all use Oracle in various contexts); - Oracle does not scale (it does, though it requires you, the SQL developer, to intimately know the database physical design at a certain point of scale, which defeats much of why SQL exists to begin with)
- k1w1 13y agoI've also used databases with more than a 100 million rows in a single table and received realtime query performance in multi-table joins. And this is using Sqlite! No expensive DB licenses - but it was using a high-end SAN since we actually ran thousands of these multi-million row databases in parallel on the same server.