LWN: Comments on "SCALE 8x: Relational vs. non-relational"

SCALE 8x: Relational vs. non-relational

dmag — Fri, 12 Mar 2010 19:44:26 +0000

> Seems to me like you want postgres to be able to just morph into any other type of database

No, I didn't mean that. I wouldn't want Postgres to add all of those features, because it would be lots of extra complexity to get some "simple" features.

I was pointing out some limitations of current systems (since most people are still in the denial stage, saying "what's wrong with the RDBMS?") The fundamental concept behind the NoSQL movement is "we need new tools in the toolbox", and there are a lot of "new tools" to explore.

SCALE 8x: Relational vs. non-relational

robert_s — Fri, 12 Mar 2010 00:54:28 +0000

Seems to me like you want postgres to be able to just morph into any other type of database instantly at your whim, no matter how fundamentally different some of the ideas behind them are.

The unfortunate "NoSQL" moniker and Polyglot Persistence

jwmittag — Thu, 11 Mar 2010 14:05:01 +0000

I always thought the NoSQL moniker was a quite unfortunate choice of naming, because it conveys exactly the opposite of what the whole NoSQL movement (another not quite so fortunate moniker) is all about: choosing the right tool for the job. Ruling out an entire class of databases is certainly not about choosing the right tool for the job. In fact, it is exactly what the NoSQL movement complains about.

A much better name would be the "Not-automatically-SQL" movement or "Not-only-SQL" movement. Or, as Ben Scofield calls it, "polyglot persistence" (in analogy to the polyglot programming movement, which has been very successful in bringing the point across that there are domains where Java might not be automatically the best choice). What polyglot persistence is really about is changing the persistence question from "Which Edition of Oracle is the right tool for job" to "Which data organization, query algorithms, atomicity level, consistency level, isolation level, durability level is the right tool for the job". It is also about offering fast, scalable, dependable, robust, mature persistence solutions for every point in that design space, but the first goal is the more important one, IMHO.

Regarding hierarchical RDBMS

gvy — Thu, 11 Mar 2010 10:35:24 +0000

I've met at least two bright examples of non-relational RDBMS which did shine where My|Pg or Ora just would not:

Daylight for cheminformatics just blew off whatever I could even primitively benchmark with mysql/postgresql back then (2001/2002) by *orders* in speed, not even trying to compare the exact problem domain value (e.g. fingerprints);
GT.M (remember MUMPS?) and temporal model users (whether in hospital IT or business) might have a good laugh with "If the data being stored has a life independent of the specific application and needs to be available to new applications down the road, SQL-relational is probably the right choice".
There was e.g. a discussion on sql.ru describing the details of a migration off a "legacy" hierarchical system to Java and Oracle -- which "doubled the performance" (forgetting to mention the need to go dual Xeon 51xx and external storage from something like dual Pentium with SCSI).

Basically, if you have to do things like "this attribute holds a value changing the *meaning* of that attribute", then you just reinvented a hierarchical database where adding another leaf might be less pain and overhead. And you might have wanted to look a bit wider. :)

Minor MS Access point

smitty_one_each — Tue, 09 Mar 2010 14:28:06 +0000

The ADODB.Connection object does support transactions:
http://msdn.microsoft.com/en-us/library/ms807955.aspx
Not that we'd go admitting familiarity with Redmond products, mind you.

SCALE 8x: Relational vs. non-relational

dmag — Mon, 08 Mar 2010 00:32:43 +0000

I think the "revolutionary" isn't so much a label for the technology, but the fact that people are "bucking the trend" socially. The RDBMS has been the "gold standard" for the last 30 years. But the RDBMS market has basically stopped innovating. Trust me, I LOVE postgres, but it's lacking a lot of useful features. such as:

- Ability to just "add nodes" to help performance, without massive administration overhead. This is non-trivial: You need merkel trees, vector clocks, gossip protocols, etc. See Cassandra and Riak (and the Dynamo paper).

- Ability to answer queries quickly - The RDBMS has tons of overhead, such as query parsing, index selection, query optimization, etc. Some of the Key-Value stores can answer a query before an RDBMS is finished figuring out what the query is.

- Ability to forget about the database enforcing schema -- that's the application's domain. (In the old days, the database had to enforce constraints because it was the "integration point". Now every app has an API, so the database is only used by one app.)

- Ability to forget about writing to disk. Writing to disk means you have to run disk-based recovery (fsck, special recovery code, etc.) It's slow and especially useless in the cloud (on EC2, you can instantly loose your disk and you don't get it back). Instead, if you want HA, you should do replication with fail-over (in multiple data centers.)

- Ability to forget about consistency - It's *much* easier to achieve HA if you drop consistency. Consistency isn't always a business requirement. (See Amazon, see airline overbooking policies)

SCALE 8x: Relational vs. non-relational

tack — Sun, 07 Mar 2010 23:01:19 +0000

Your reporting is fine (excellent, in fact). The GP and I have clearly read a different article.

Your wording most certainly did not "lump together MySQL and MS Access." The only legitimate complaint concerning MySQL (InnoDB supports transactions) has been pointed out and defended (MySQL+MyISAM is the default, and therefore "the most popular database").

SCALE 8x: Relational vs. non-relational

bronson — Sun, 07 Mar 2010 16:53:12 +0000

Or, what if your queries don't easily translate into SQL's SELECT and UPDATE model?

I had this last year: the data itself fit perfectly into tables with foreign keys. Problem is, we were trying to allow marketing people to slice and dice it in fairly arbitrary ways, and their needs would change from week to week.

This was typical data warehouse-type stuff. Compute the monetary total of all orders for customers from this region. If that's above a value provided by marketing, then what's the average zip code and standard deviation for the remaining customers who have dogs, etc. (you get the idea)

I wish I had done the whole thing in MongoDB using map-reduce. I think it would have been a lot faster, both to develop and to run. I wouldn't have to spend as much time figuring out which indices, counter caches, and denormalization that would be needed to make this week's reports complete in time.

So, even if your data model is nicely tabular, that doesn't mean your usage patterns will be!

SCALE 8x: Relational vs. non-relational

kleptog — Sun, 07 Mar 2010 14:17:13 +0000

That's not entirely true. SQL has concepts like "tables", "columns" and "rows" which, if your database doesn't have them, is going to make it really hard to make SQL function at all. (SQL assumes a fairly rigid structure)

For example, suppose your database consists of a objects with attributes, dynamically created, which can contains references to other objects, not necessarily of the same types. Maybe there are no "types" at all and all objects are exactly as they come. They may reference other objects arbitrarily.

While you might try to make something that looks like SQL for such a database, but it's unlikely to be practical.

I think NoSQL should be reserved for databases which don't follow the relational model at all, and thus are unsuitable for SQL even if you wanted it to.

SCALE 8x: Relational vs. non-relational

njs — Sun, 07 Mar 2010 07:46:52 +0000

> I'm having a hard time even trying to come up with any examples of revolutionary software changes

rsync? Diffie-Helman/RSA?

The GPL?

But yeah, it's like the old rule about fields whose name contains the word "science"...

SCALE 8x: Relational vs. non-relational

jake — Fri, 05 Mar 2010 19:34:26 +0000

> he came off sounding like some unstable and embittered crank, resentful
> of the success of the competition and consumed with putting them down
> at every opportunity.

Wow, I am going to have to disagree. While Josh was a tiny bit PostgreSQL partisan in his talk, he was most certainly not unstable or embittered. If my report makes him seem that way, then I reported poorly, for which you (and he) have my apologies.

jake

SCALE 8x: Relational vs. non-relational

einstein — Fri, 05 Mar 2010 19:15:20 +0000

While he made some good points, the anti-mysql propoganda was a bit much - lumping mysql together with ms access with a straight face, claiming that mysql lacks transaction support, he came off sounding like some unstable and embittered crank, resentful of the success of the competition and consumed with putting them down at every opportunity.

Just for the record, from the official mysql documentation: http://dev.mysql.com/doc/refman/5.0/en/commit.html

Comparing the postgres of tomorrow with the mysql of 1997, way to go!

MySQL does have transactions

dps — Fri, 05 Mar 2010 19:13:07 +0000

It is a pity that the MySQL was claimed not to do transactions. MySQL has supported transactions, if you use InnoDB tables, for *many years*. Unlike most other databases MySQL also supports lighter weight table types that do not support transactions.

I have actual experience that demonstrates rolling back many updates of several tables works as advertised. If you are looking for things missing from MySQL then OLTP support is probably a good place to start looking.

I suspect that the OLTP support is MySQL is somewhere between nonexistent and minimal but my knowledge might be out of date.

SCALE 8x: Relational vs. non-relational

dps — Fri, 05 Mar 2010 17:37:02 +0000

Before using a database it is import to ask whether a database fit the problem. If you want to build a structure containing all or most of a set of data my answer would usually be ¨no¨.

If you want fast access to small portion of a large data set then databases obviously fit. Whatever the interface all databases offer efficient ways to find the portion of the data you want. The relational and security features of SQL databases are potentially valuable for some applications.

While I have not explored them in detail what I have seen of XML and NoSQL databases strikes me as inferior to the SQL alternative in almost all respects. The only exception is Berkley DB which is good in many instances where SQL would be serious overkill.

SCALE 8x: Relational vs. non-relational

rgoates — Fri, 05 Mar 2010 14:52:26 +0000

The GOODS database engine should be mentioned, though it never seemed to reach usage critical mass. I used it around the year 2000 and found it to be a reasonable alternative to commercial object oriented database engines of the time. You could even build a geographically distributed database with it, and its direct links between objects made complicated data schemas very tractable compared to a relational approach. A pity that its creator seemed to lose interest in it after it matured. He did some great work.

SCALE 8x: Relational vs. non-relational

skissane — Fri, 05 Mar 2010 06:03:28 +0000

SQL is just a query language. You can query anything you want with SQL, if you are willing to write the required code - a SQL parser, and code to execute the queries.

Likewise, any query language you want, you can use to query a SQL database. Once more, you just need to write the parser for your favourite query language, and some code to execute it.

In both cases, you may be able to reuse the target systems query engine to some extent, but may need to supplement it with your own code in some cases. For example, if I want to map SQL to LDAP, LDAP has equivalents to SELECT, FROM, WHERE, ORDER BY (if I use the sort control); but LDAP has no equivalents to JOIN or aggregates, so you'd have to write your own code for those.

SQL is not my favourite language -- if I was designing it, I would make the syntax much less COBOL-ish. I think its a pity we don't use something like QUEL or Tutorial D or BS12's query language. On the other hand, its the industry standard.

But please, no more trying to distinguish databases based on whether they support SQL or not. Any database can support SQL, or any other query language you want -- at most, its just a question of whether the database has that support builtin, or if you have to build it yourself. Its just an interface issue.

And also, too many people attack "relational" databases when they really mean to attack various real world implementations of relational theory. Most of their gripes are really with the implementations rather than the theory, and often the solution is not to junk the theory, but to improve the accuracy of its implementations.

SCALE 8x: Relational vs. non-relational

iabervon — Thu, 04 Mar 2010 17:30:32 +0000

There are plenty of revolutionary software changes. None of them work, and they won't work until a number of evolutionary changes make them viable. Static typing, automatic garbage collection, parameterized typing, and type inference were all revolutionary at different times, and were all unusable for generations before they had evolved to the point where people could use them, be able to do what they need to do, and get the benefits from the change.

When something claims to be revolutionary, the claim is generally really that its evolution has just made the revolutionary change available to people who want a system that works and don't want to redesign their application regularly to account for recent discoveries.

SCALE 8x: Relational vs. non-relational

felixfix — Thu, 04 Mar 2010 15:30:04 +0000

Maybe he could have been a bit more pedantic and said "the most popular installation of the most popular database" but the answer is the same: it is the most popular database.

SCALE 8x: Relational vs. non-relational

nye — Thu, 04 Mar 2010 14:14:28 +0000

>The new systems are not revolutionary, just evolutionary.

It seems a truism that any product described by its adherents as 'revolutionary' isn't.

(Off the top of my head I'm having a hard time even trying to come up with any examples of revolutionary software changes - this is in part the reason why I think sw patents are fundamentally wrong.)

SCALE 8x: Relational vs. non-relational

irabinovitch — Thu, 04 Mar 2010 09:07:46 +0000

Session audio is available online as an mp3

SCALE 8x: Relational vs. non-relational

ringerc — Thu, 04 Mar 2010 05:35:29 +0000

Also: Whichever database system(s) you choose, you need to learn about them and understand them.

This is particularly true for more powerful (and complex) systems like relational databases, but still applies to even simple key-value stores. You don't just need to understand the system, though, but also established techniques and ideas about how to best use the system(s) you've chosen.

I see this a lot on the PostgreSQL mailing lists. For example, people will ask "why is my bulk data load so slow" and it'll turn out they're doing a million individual INSERT statements, one per record, over a high latency network - and they're running each as a standalone transaction. Each sequentially-executed statement requires at least one network round trip, plus an fsync() to make sure the data has hit disk before the server can reply with a completion notice, so of course it's slow. Get them to use COPY, or batch the inserts into large multi-VALUEd blocks inside one transaction and suddenly it's a thousand times as fast.

In addition to that sort of thing - learning about how to best use the technology you've chosen - you also need to understand how to manage the data. Some simpler non-relational databases may not require formal schema definitions, but you still need to understand how data will be stored, retrieved, pruned, etc - and failing to plan for that will lead to a nightmare down the track.

SCALE 8x: Relational vs. non-relational

ringerc — Thu, 04 Mar 2010 05:20:49 +0000

"Transaction support is something that many open source people don't know about because the most popular database (MySQL) doesn't implement it."

... if you're talking about ancient MySQL 3.x, sure.

It'd be rather more accurate to say "... because the default MyISAM storage system used by the popular MySQL database doesn't support it."

I only hope the quote is inaccurate or an oversimplified paraphrase, because I'd expect better of him.

I'm a PostgreSQL user for good reasons, but I still get bugged by people pushing outdated misinformation about MySQL. It has more than its fair share of *real* faults and limitations; no need to hang onto ones that're thankfully in the past :-P

PostgreSQL conference

Webexcess — Thu, 04 Mar 2010 03:19:54 +0000

Seems relevant that PGCon just opened its registration: http://www.pgcon.org/2010/

LWN: Comments on "SCALE 8x: Relational vs. non-relational"

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

The unfortunate "NoSQL" moniker and Polyglot Persistence

Regarding hierarchical RDBMS

Minor MS Access point

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

MySQL *does* have transactions

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

SCALE 8x: Relational vs. non-relational

PostgreSQL conference

MySQL does have transactions