Hacker News Reader

Have you ever noticed that every time there's an article like this there's one 20-year DBA veteran who says the original author doesn't understand SQL?

Yeah.

Anyway, having built both transactional systems (trading algorithms) and big social systems (delicious) I think the main issue is: The right data structure and tool for the right job.

Banking is a radically different problem then internet-scale social software (which I assume is what they are talking about when they say "doesn't scale".) Access patterns are different, read and write loads are different, etc.

The main issue here is that a lot of social software wants something like a fast per-user data store, something like a distributed inverted index for globally finding things.

There's NO great reason for one user's data to be in the same table as someone else's. I guess it was handy for stuff like calculating the average number of tags, etc.

Instead, you want to be able to have better control over data locality and similar, given your access patterns.

Now, personally, I would use an actual SQL engine for a single-machine persistent store, and build a distribution layer on top of that. Concurrency is hard, etc.

But assuming RAC is the right solution to all problems is probably not a good one. I've seen this go terribly awry.

SQL wins at things that btrees and hash indexes are good at; but a lot of things are better with other organizations of data.

You totally ignore the background of the author -- his company Heroku : Ruby/Rack :: Google AppEngine : Python/WSGI

But there's a huge problem -- AppEngine succeeds at seamless multi-tenant truly-distributed clustered hosting thanks to BigTable. Heroku needs to support standard Rails apps, so Postgres is the best they can do, and it's a huge hole in their offering.

You just can't make Postgres (or Oracle) scale on an ideal horizontal the same way you can distribute IP, DNS, HTTP proxying, HTTP serving, memcache, message queues, or bigtable. You can't expose Postgres as an ideal service that just keeps up with what you throw at it.

WRT exposing Postgres, you're right that you can't simply expose a database and then expect the DB to scale under random selects, inserts, etc. (RAC is another matter I guess.)

However, as I said in the first comment to the blog post, if you define an interface using stored procedures (a pattern familiar to many Oracle DBAs), then PL/Proxy (http://pgfoundry.org/projects/plproxy/) lets you do hash-based partitioning in a way that's more or less transparent to the end-user of a DB, again assuming that it has a defined interface. The PL/Proxy installs form a 'bus' between the DB and the user.

Self-promotion: I'm currently working on hacking PL/Proxy into something that can be used to auto-scale a Postgres cluster on-demand, which has interfaces as a pre-requisite, along with some other things. The end-goal is to do exactly what you say: to scale-out Postgres like any other internet service on an ideal horizontal. Link: http://code.google.com/p/hotrepart

(Hum, I'm gonna get accused of spamming for repeating myself so often :-)

While Skype's PL/Proxy is a great way to make PostgreSQL more scalable, it doesn't do much to refute the basic argument that SQL databases aren't scalable since the SQL it helps you scale is limited to short RPC style operations.

That's a good point, and it brings to light an unspoken assumption underlying my post, which is that the use-case is a web-based, read-heavy OLTP-style system. If you were thinking of things like OLAP and data warehousing, then I'd agree with you absolutely.

However, under my re-qualified assertion :-) for large, complicated commits, the logic would either have to be at the database level for it to be in the same transaction, or a solution using temporary tables could be put together for more convoluted calls.

Neither of these are elegant, I'll grant you, but the two basic approaches - longer transactions over logic closely coupled to the datastore, or staged writes - are what most DBAs on high-end databases end up doing anyway, and would probably be reproduced in some form or another in any ACID system, no? Either way you'd still have a database that scales.

For what it's worth, you can most definitely do that with Oracle. I can't speak to Postgres scalability, but it is my DB of preference. (I guess it might have something to do with the fact it's as close to Oracle as you're going to get in Open Source).

We design apps to treat the datasource as a network service, and have no problems load balancing DB connections across our database cluster, and adding new DB nodes as required.

The biggest problem I've seen is the relative lack of support for Oracle in something like Rails, or non-Oracle app servers in general. I've had to custom write some connection pool failure detection for a few apps to deal with cluster failure (something that comes out of the box with the Oracle app server, but we wanted to use JBoss and GlassFish), so while it's not perfect, it's most definitely doable.

Just because his particular architecture or offering doesn't fit well with typical database deployment architectures, doesn't mean that "SQL doesn't scale".

Maintaining connections in the face of failure is a relatively little concern, what I perceive as the big problem here is having the database handle an arbitrary application with zero application-specific administration.

Google's datastore promises that if you write your app for their platform and it works on the small scale, it will scale out without problems. Datastore latency is constant regardless of how many records you have there. How will you scale a join between two tables, each being partitioned between multiple different servers, transparently for the app?

Whilst I can believe that Oracle probably does scale, when it costs $17.5k per CPU in licensing then it's not for everyone.

It would seem that problems of a bank with a large already established userbase and lucrative, stable business model is very different to a start-up with no tested business model, may never become popular enough to need to scale and may not survive.

Uhmmm... duh?

The original article mentions nothing about cost or the ability (or lack thereof) of any company to afford it.

He made a flat out statement that said SQL DOESN'T SCALE, which is wrong.

There are other methods for dealing with initial growth and the cost constraints... no need to blow your wad on Oracle out of the gate.

But then I imagine a lot of non-Oracle types aren't even aware that Oracle can be very flexible with their licensing for startups. You can, for example, lease/rent your Oracle licenses on a monthly basis to help get the most out of your cash flow.

I've been involved with a number of startups where we did our initial rollout on Postgres, but ensured that the application architecture allowed for us to fairly easily swap in a larger, more scalable solution if/when needed.

For that matter, in my opinion, too many people and startups tend to over-engineer their initial product offerings, making them too complex and worrying more about nonexistent problems (like Google-sized scaling) rather than on solid features and business process. But that's fodder for another thread, I'm sure.

CPU core. Not CPU (which is generally taken to mean CPU die).

Really? That's 4 times worse than I thought then

Yep. Oracle are famous for this - at my office we often talk about counting the molecules to work out Oracle licensing.

The thing is that this makes it even harder to scale because the cost of licensing additional nodes is now several times the hardware cost.

For future reference, cold hard facts are much more useful than posting your resume. I'm not trying to say you're unqualified or unintelligent in any way, just that rationalizing an opinion with a job history is far less compelling evidence than factual examples. It's also interesting that you would immediately associate a well-made and supported argument (for those of us who have any experience scaling with technologies other than Oracle) with someone who isn't a database expert and doesn't know what he's doing.

Now let's get to the facts: while perhaps Oracle might have nice features, that doesn't mean that SQL is the best we can come up with, which is one of the primary points of the article (and one that seems completely unaddressed here). In a sense he brings to attention a common occurrence throughout human history wherein we reject change for comfort and these comments are doing nothing more than supporting that. Oracle is, for the most part, entirely too expensive for most businesses and even the businesses that are large enough to adopt it aren't making the profit they could with more affordable technology. You should also make note that, while banks do have strict requirements on data handling, they are really responsible for serving very small user bases when compared to things like Google, Amazon, Ebay, Facebook and the like. Sure the requirements are different for each of these, but ultimately your argument hold no water against these infrastructures, which are inherently not SQL and, at the same time, seem to be much more accepted as innovators of scalable technologies for the future...

One big problem is that real innovation comes at the expense of backwards compatibility, which would involve making a lot of changes. I can relate, since massive changes in most case imply bugs, which is a very unsettling position for some of these, but it doesn't mean there isn't a problem. Sure banks and other largely established companies would rather shell out the cash to support their legacy ideals than innovate new solutions, but he's right in that we've been spending many many years of man-hours trying to tackle the problem of porting a dated philosophy to an age that requires more scalability at lower cost. Lets also say that banks haven't been proving their practices to be economically sounds lately, so what is their input worth in this matter? Other than large boatloads of cash for Oracle that is.

Just to clarify, I never said Oracle was the be-all end-all solution for everything.

And I didn't rationalize anything with a "job history", but rather with large systems that actually have been built and are working.

I'd be very interested to see just how many people in this discussion actually have actual experience building large, scalable systems?

The original article made an asinine, generic statement without any context, or mention of cost, and I said it was silly, and pointed to the obvious (and easiest) reason why it was silly, and that was Oracle.

All of a sudden a bunch of people started making statements and assumptions about the scenario the article was probably talking about, that weren't actually made in the article, to discount Oracle as an option. Cost, commodity hardware, etc., etc. Then people started to point out other large websites that actually HAVE scaled, without the use of Oracle, as if that disproves Oracle's abilities... and yet it totally disproves the statement of the original article.

If you want to get into some context-specific details of why certain specific SQL technologies don't scale well (or at all), at a certain price-point, then that's a whole other discussion that I'd be happy to enter into, and would probably agree with.

It's also interesting to note that most of the "DB technologies" that are being used to scale those sites aren't DB's at all, but rather various levels of data caching that are employed to reduce the load on the databases, and are only applicable to the general read-only and non-transactional nature of social sites.

The whole reason I brought up banking sites in the first place is that they are one of the few, more obvious scenarios where most of your end-user interactions are actually hitting the database in real-time, and all data must be current and consistent. There is no real option for caching to save your ass, except at the DB layer itself, via such mechanisms as Oracle's Cache Fusion technology.

Social sites generally don't have any of those real-time, consistent constraints, and are therefore much easier to scale larger, because the nature of the site and the data allows for so much more technology to be used in front of the database.

The plain and simple fact of the matter is that building a large, scalable system is hard work. It requires that you analyze and design ALL aspects of the entire system to scale, not just the database. (Network, caching servers, application, database, hardware, etc).

Oh, it may also be worth noting that engineers and administrators would have to change their way of thinking in all this too, which doesn't affect some too much, but may have a bit of an impact on somebody who's been sitting on the same technology platform for 20 years.

Yeah, and if Facebook used Oracle instead of MySQL and memcached, they'd be looking at a $50 million/year+ bill no doubt. Those silly kids with their minor technologies should learn to use a real DB. If those 100 million users that login every day only knew...

Firstly, I've never said that you couldn't build big stuff with MySQL or other Open Source tech. But if you for a second think that MySQL has the same kind of performance and feature set as Oracle, you're sadly mistaken.

The example you have given has nothing to do with the technologies, and everything to do with the application requirements.

An Internet Banking site has the need for real-time, centralized, transactional updates. There is no real option to cache a lot of stuff, or to delay the distribution of updates, or to shard/replicate data for reads, etc. It also has the need for real-time transactional replication, sophisticated auditing, global fault-tolerance, etc., etc. It's also a much more transactional site than something like Facebook.

Facebook's application requirements allow for a totally different set of tools to be used in a totally different manner. The dynamics of the site also has a lot to do with how it can be built. For example, Facebook is, for the most part, a read-only, fairly static site. That makes things a HELL of a lot easier to build out with their choice of tech. Same goes for SlashDot.

There are a ton of ways to build out something to the scale of Facebook, Slashdot, or LinkedIn using nothing but open source tools/technologies.

You cannot, however, build a realistic, large scale internet banking site using those same technologies. The only option is something like Oracle.

That's why a truly scalable system is so much more than just bolting on a DB to make it scale. It's about mapping the proper technology and tools to the business requirements, and figuring out how to deal with scaling issues before you even start to write a line of code.

Or, even better, abstract the database layer (Hibernate, etc), so that you can drop in more sophisticated DB technologies as you need to.

Just because something is free (MySQL), doesn't mean it sucks. Likewise, just because something costs money (Oracle), doesn't mean it sucks.

But there are many reasons (some of them even technical) why lots of companies have no problem paying stupid money for Oracle.

"For example, Facebook is, for the most part, a read-only, fairly static site."

FaceBook's actually one of the more dynamic sites out there. I'd bet that the average FaceBook user makes many more updates to their FaceBook profile than they make financial transactions.

The real difference in requirements is that FaceBook can - and does - drop updates on the floor. If your friend throws a sheep at you and you don't get it on your news feed - oh well, FaceBook never claims the news feed is exhaustive anyway. But if somebody writes you a check for $10,000 and they see that you cashed it and you don't see it in your account, that's a problem.

I agree... never meant to imply that Facebook had fewer hits/etc than a banking site in raw numbers, just that the ratio of DB reads (not considered a transaction, very easy to cache, etc) to DB writes, makes the site much more read-only than write-only.

Facebook has the ability to do a TON of edge caching, with very few (relatively speaking) operations having to go to the database to perform an actual write operation.

It's the DB writes that really kill performance and limit your caching strategies and gains.

My guess is apps hosted on Heroku (like the majority of the internet applications) are a lot like Facebook and little like online banking: they don't really need reliable transactions, they need to be flexible, scalable and cost-effective.

For what its worth, Facebook does have a big Oracle install.

I find it strange that everyone trotted out their preformed opinion for this piece, but no one comments on the cassandra article which actually proposes a solution...although it requires you to learn something, how horrible.

http://incubator.apache.org/cassandra/

are you sure facebook has a big oracle install? how do they use it?

It was the failure of Oracle to scale cost-effectively that led them to create HIVE.

You can't be talking about Hive[1], the SQL system for structuring and quering Hadoop datasets. That would never scale...

1- http://hadoop.apache.org/hive/

SQL atop an unstructured datastore, with minimal metadata. Something of an innovation, not a traditional RDBMS, but yes - still SQL :)

The nice thing about HIVE is - you're not limited to just SQL, though. You can still analyze your files on HDFS any whichaway, with Pig, with your own MapReduce jobs, whatever. Personally though, I look forward to Apache Pig getting SQL, and being able to run SQL queries on any intermediate state of a Pig script.

I don't think that anyone is really complaining about SQL. SQL is a swell query language for certain kinds of data. They're complaining about static schemas in relational dbs and having to store objects via SQL - reasonable complaints.

I don't think that anyone is really complaining about SQL. They're complaining about relational dbs.

Actually I suspect Mr. Wiggins is really only complaining about problems with MySQL and PostgreSQL. He would be less inaccurate if he admitted as much rather than making sweeping uninformed claims about SQL and relational systems.

They use it for OLAP but are building out most of the new stuff in Hadoop,Hive because its cheaper.

I've just read all your comments. Do you have a blog?

Nope. Had one for a while (http://orageek.com) but found I didn't really have much to say all that often to warrant maintaining it.

You can only scale Oracle on Big Iron, which means it doesn't scale well because its too expensive. Cost matters.

Absolutely wrong.

We use Dell 2950's for our DB nodes in our Oracle clusters. Biggest one right now is 12 nodes.

And if it scales, but costs too much (a relative concept at best), then it somehow doesn't scale any more?

Of course cost matters, and it comes down to what your application requirements are, and what your revenue model is, and your risk management requirements are, etc., etc.

If you want to talk technology only, it's a no-brainer.

If you want to talk cost and context-appropriate implementation of technology, then provide a detailed context, not generalizations.

Again, you're not going to see an online bank use CouchDB as their back-end, and likewise, you're not going to see a relatively free service use stupidly expensive technology.

How much data, and with a SAN? How much did the SAN cost? What kind of data?

But yes - if its too expensive to do, that means it doesn't scale well. Since when does money not matter in everything? The entire point of all of this is to use commodity PCs to achieve linear scalability cost-effectively, and to escape relational structure for data ill-suited to relational schemas.

I don't think anyone is suggesting an online bank should not use existing commercial databases and SQL.

It's one thing when we're talking about facebook. It's entirely another matter when we're talking about a large bank. Do you want your bank to use mysql to store your financial data?

Regarding the importance of money, look at the existing infrastructure in financial institutions. They spend millions of dollars a year on mainframes from IBM. The cost of oracle compared to this is relatively low.

We're talking about two entirely different markets and applications of "databases" here. The claim of SQL db's not scaling is not true.

You know what's funny... no ones talking about a bank but you. For most apps built by most startups, Oracle is absurdly expensive, so yes, it doesn't scale well.