Beyond Relational Databases

CowboyRobot writes "Relational databases were developed in the 1970s as a way of improving the efficiency of complex systems. But modern warehousing of data results in terabytes of information that needs to be organized, and the growing prevalence of mobile devices points to the increasing need for intelligent caching on the local hardware. According to the ACM, the future of database architecture must include more modularity and configuration. Although no concrete solutions are included, the article is a good overview of the problems with modern data systems."

Comments

KISS

[mcrbids]

Some of the biggest problems that "new" database designs have:

1) Overly complex

2) Don't scale

3) Tied to a single platform/implementation

4) Poor performance

It's typical to see all four in a single try!

SQL, on the other hand:

1) Reasonably simple API

2) Scales to very large databsaes

3) Cross-platform/architecture

4) Performs very well.

Given the insane amount of inertia SQL has, it will extend into an object model, rather than be replaced by one. (EG: C/C++)

Re:KISS

[eggoeater]

Very true. My group had to rebuild an overly-complicated MIS system for a large call center about 5 years ago. The old system was built with RedBrick DB engine, informatica data transformations (i.e. pretty GUI..oooooohhhhh), and Brio reporting. It was a mess and NEVER worked. We replaced it with SQL Server and some relatively simple SQL stored procs and dumped the POS informatica and RedBrick.

IMHO, there are too many GUI tools out there to do data transformations. SQL works great but most people don't want to take the time to learn it!

Re:KISS

[NoOneInParticular]

Google's web index and desktop search facility is a database. I don't know about point 1, but Google definitely blows any relational database out of the water on point 2 to 4.

As for SQL I do not agree with point 4: SQL does not perform very well. We're in the age of Ghz processors, fast disk drives and it *still* is an performance issue to add a few million records to a database? What SQL sorely lacks is a recognition that the 4th generation of software languages was a bad mistake, and get back to a third generation language: explicit indices, explicit loops over these indices and fast (compiled) execution of said loops. Just freaking program the database instead of waving chicken bones at it.

Re:KISS

[MSBob]

Relational algebra is a very nice and tidy concept but SQL is a piss poor (and limited) implementation thereof.

In other words, relational databases are very nice and elegant but their interface (SQL) is bad and should be replaced.

Also relational databases by themselves can't supply the needs of a typical enterprise and that's where technologies such as OLAP are built on top of RDBMS to make certain data manipulations efficient.

Re:KISS

[Frank T. Lofaro Jr.]

Umm SQL does have explicit indexes and they make a big difference:

create index t_c_idx on t (c);

creates an index "t_c_idx" on column "c" of table "t".

I've shaved 2 multi-second queries to under 1/10 of a second (it was an overall speedup of over 100X) just by adding indexes and letting them be used (e.g. you can't have where some_function(c)=value and expect the DB to use the index on c).

Re:KISS

[NoOneInParticular]

Yes, you can expect the DB to use the index, which is subtly different from instructing the database to use the index. The whole idea behind SQL is great: a declarative language for specifying the job, and a procedural, smart, engine for performing the job. Unfortunately, databases have failed to deliver on the latter part, leading to implicit techniques to get the performance out. What I claim here is that we should take a step back, not forward, with SQL and get back to explicit programming of the databases, with strong guarentees about the performance. No more "let's add an index and I'm sure it will go faster", but, "instruct the query to first make and then use the index". Much easier to comprehend, and much faster.
The relational model is great, but what we need for speed is absolute control over the order in which records are stored. If you know that order and you have an underperforming "general" query, you might be able to speed up the thing with smart programming instead of trying to prepare the environment in such a way that the procedural engine does its job. Say you want to insert a bunch of records which have consecutive primary keys. Writing the procedure for adding them is trivial, letting the database sort it out often leads to dissapointing performance.

Re:KISS

[3nuff]

I'm currently working on one of those GUI applications, not Informatica. I am pleased with it's capabillities and I think it's a very powerful and reliable tool. Big projects can take a couple days once a good template is built and the metadata management is simple and useful. Reusable code and SMP processing are available and greatly enhance the speed of data transfer.

In the wrong hands though, such a tool can be made into a piece of crap. The problem is that too many "programmers" don't know SQL before they use these tools. They just go at it. No standards in place; just head down. Judgements need to be made on when to use the tool's sql generation and hand coded SQL to get best performance.

Too many companies get substandard people in place and then wonder why their $1M software doesn't work, it puts these tools in a bad light, that is undeserved.

Re:KISS

[WaterBreath]

instruct the query to first make and then use the index

build multiple indices on a 20M row table, on the fly? Is it worth the effort to build it on the fly if the database can figure out the best way to make and use the indices on its own 99% of the time?

I think that what you're looking for is a database that will allow, but not require, you to tinker programmatically with creating and using temporary indices on the fly. It's still indespensible to have optimizing index functionality in the database for the 99% of proper uses that don't require the extra oomph. And insisting on reinventing the wheel every time simply because "I can do it better than some stupid program someone else wrote" is a rather naive development philosophy. Yes there are times when it's better. But they are vastly outweighed by the times when it's just not worth it. The need for packaged indexing functionality is there.

Re:KISS

[WaterBreath]

Usually a piss-poor database reflects on piss-poor developers.

This is a BIG IMPORTANT POINT. But, unfortunately, the people that are architecting the databases in these cases usually don't realize that they're producing bad designs. All they see is "The database is slow. Why the *!?$ is it so slow!?" There's usually one or two answers: 1) Your schema is organized poorly. 2) You're just plain juggling too much data at once. And let's not forget the deadly combination of both 1 and 2. 100M rows, for example, is a lot of data, any way you look at it. If you've got 100 bytes per row (remarkably small in many cases), that's 10G of data. If you're shuffling through all that data every time, yeah, it's gonna be slow. Ya might want to look into reorganizing, or archiving.

Re:KISS

[waveman]

> SQL, on the other hand:
> 1) Reasonably simple API...

The outrageous success of SQL is in part because it complies with the "second law of axiomatic design": a good design has minimum entropy. Prior to SQL a lot of application knowledge was duplicated in the database which increased entropy. Also a lot of knowledge of the physical structure of the data was embedded in the programs which also increased entropy. This is bad because the more redundant information the system contains the harder it is to change.

With SQL you can change the physical structure without rewriting all your programs. And you can change your code without affecting the DBMS design eg you can get the rows back in a different order. In the bad old days if you wanted things in a different order you had to reload the database and recompile all your programs.

Even though SQL uses 3 to 10 times as many CPU cycles as hierarchical DBMSs or ISAM files it took over because of this flexibility.

Similarly, I think that one of the reasons OO programming has not delivered the productivity benefits it was supposed to is that OO somehow encourages people to embed a lot of duplicated knowledge of the application into the class structure. This duplicated information makes it hard to change the system. I have seen this again and again. In contrast, before OO a lot of this information ended up in data or in a database, where it could be easily changed.

Re:KISS

[Brian_Ellenberger]

Google's web index and desktop search facility is a database. I don't know about point 1, but Google definitely blows any relational database out of the water on point 2 to 4.

Google is a very unique case. There are two things in Google's advantage that most RDBMS system have to take into account:

1. Google does not have to update in realtime. If I add a page to my website it is not immediately available on Google. Contrast this to a normal RDBMS where if I add a record it must be available immediately. Google is much more similar to a Data Warehouse than a RDBMS.

2. Websites indexes are more easily parallelized because complex joining of data is not needed. Naively, Google can store all websites starting with 'A' on a server, 'B' on a server, etc. You can store the User table and the Address table on separate database servers and expect to query on users and their addresses with any sort of performance.

3. Going along with 3, the queries expected out of most RDBMS systems are much much more complex than any queries expected out of Google right now. I'm assuming you haven't seen any complex financial reports or statistics that have been generated from a RDBMS. Databases do alot more than "select name from user where id=1234".

Brian

Re:KISS

[isomeme]

Google's web index and desktop search facility is a database. I don't know about point 1, but Google definitely blows any relational database out of the water on point 2 to 4.

Google's performance and value are both amazing. But it's easy to drive yourself nuts (if you're an enterprise software architect, which I am) trying to get that kind of performance out of other types of application. You see, Google has two major advantages over nearly all other large data-backed applications:

1. There is no "right" answer

Google keeps their ranking and indexing schemes proprietary, so nobody can say what "should" come back from a given search. Indeed, execute the same query at the same time from different machines, or the same machine at different times, and you sometimes get wildly varying results. SEO folks call this the "Google Dance".

2. Writes are asynchronous with reads

As near as anyone can tell, the Google index is rebuilt by their crawler over the span of a few weeks, and then the whole new index is exported to production machines over the span of a few days. Only the crawler writes data to the index, and the index as it's being built is not read by end-user clients; the production index is *only* read.

These advantages let Google use a distributed, loosely coupled, inconsistent server farm made of cheap boxes that needn't be in sync with one another. It doesn't matter if queries to two of them give different answers, after all.

Contrast this with (e.g.) an online bookstore; once you order a book, every node in every part of the system needs to know about that order in order to keep everything consistent (stock level monitoring, end-user purchase tracking, and so forth). This is a much, much harder problem to solve.

So again, not to take anything away from Google's tech (which I more or less worship), but it's not a fair point of comparison for most large enterprise apps.

Re:KISS

[ejamie]

I fully agree with this the parent post. I've built several systems (web or otherwise) running on SQL Server over the last 5 years.

I've learned this from experience myself: There is no reason why a database driven application should be slow, provided the database is layed out appropriately and the built-in performance facilities of the RDBMS are utilized.

Show me a slow performing database-driven application, and I will show you a set of indexes, stored procedures, vertical/horizontal table partitioning, or table normalization/denormalization that will fix the problem.

The other cool things about DB optimization (one of my favorite development subject areas), is that generally putting in effort to optimize in these areas will give you an immediate and many-fold increase in performance. Not so easy to do in non-RDBMS environments!

Re:KISS

[Stu Charlton]

I disagree that a programmer can manually create a better query plan than (for example -- others apply) Oracle 10g. In certain cases -- perhaps. A couple of questions though....

A) will a programmer be expected to do statistical analysis of the data, checking for value skew, and also noting the current load of the system before coming up with the best way to perform a query? This is what happens automatically today, each time you parse a SQL statement.

B) if the skew of data changes, do they have to rewrite their query to switch access algorithms?

C) if I want to some day perform the query in parallel, do I also have to rewrite it?

D) if the business needs change, and I have to change the way I look up the data (its access path), do I once again have to rewrite the query?

E) if I rely on an exact order of rows in the database, doesn't that imply an O(log n) insert / update / delete penalty (assuming a B*Tree structure, which generally is the means of preserving order on disk).

F) if the volume of data in the table grows, wouldn't I have to rewrite the query at some point?

These are the reasons we have SQL today. And we have things like optimizer plan stability and now (with Oracle 10g) the ability to actually re-write the plan for a poorly written SQL statement. I think it's best to stick with declarative, logical querying. I'd love a replacement for SQL, but moving back to 3GL-based physical access is a step backwards.

Just because something is "old"

[Anonymous Coward]

Doesn't make it obsolete. "Databases are old and kludgey. Teh suXX0rs for R0xxng H4XX0rs liek me.

Just because people are too stupid to take the time to read and understand the theory and learn the application doesn't mean the technology is no longer relevant.

Of course no solutions are proposed. There are none because relational theory is correct, and appropriate for real database driven applications. Little crap bulletin boards can use MySQL.

Netcraft confirms relational databases are dead!

Re:Just because something is "old"

[Frank T. Lofaro Jr.]

Little crap bulletin boards can use MySQL.

Like Slashdot :) Remember yesterday's fiasco where posts were migrating into other articles and the time before that where it happened and the time before that. :)

Why people don't use PostgreSQL is beyond me - unless they don't like fact it is under a BSD license instead of the GPL (please don't get me started on that).

Re:Just because something is "old"

[darthium]

I concur with this opinion, I've seen a lot of supposedly 'Top notch' designs and I've seen flaws that made me blush myself....mostly the problem is with stupid design mistakes and poor concepts.

Instead of blaming the technology and tools, they should improve skills in the Sytem Artchitects and all the way down the road the people involved in Software Development.

Wake me up when it's ready

[Vile Slime]

People,

Have been crying for the need to replace relational databases since the early nineties at least.

We can all see where that got them.

. . . no concrete solutions are included. . .

[kfg]

Funny how they never are, eh?

KFG

Author's conclusion in case of slashdotting

[Anonymous Coward]

The future will not be found in the relational model, object model, or hybrid, but in the comma-delimited list.

Re:I did not RTFA

[AKAImBatman]

I didn't RTFA but for my needs

Or the summary

mySQL suits me quite well.

That's nice. It won't handle a multi-terabyte database, though. That's the domain of Terabase, Oracle, and (blech) DB2. It's also what the article is about.

The power of PHP and mySQL is all I need.

And a moped is all you need to get to work. If you want to haul 300 metric tons of rock from point A to point B, you need a dump truck. Again, that's what this article is about.

Back on topic, this entire article is mostly speculative for the moment. A lot of excellent work has been done in OODB and XMLDB designs, but no singular design has yet emerged to solve all our woes. For example, I love the Prevayler concept. It solves a lot of problems, lowers data access times, and provides for complete data security. It also isn't usable or scalable without a lot more design work.

The future will hold some very interesting things, but for now we'll have to keep inventing until we come up with a consolidated solution.

SQL Dying...film at 11...NOT!

[Gorm the DBA]

Wow..."SQL and Relational Databases to be replaced by new technology"...film at 11.

See "COBOL to be replaced...." for an example of just how unlikely that is...sure, the latest hip "Tres Kewl" software for business might be written in something else, but SQL will be around for a long, long time.

Consider just the fact that "Relational Database" technology as laid out by Cobb back in the early days specifically says "You don't *HAVE* to do it this way, but it will be more effecient if you do"...realize that SQL handles Denormalized Warehouse and Datamart tables just as well as it does the 5th normal form model of perfection...and relax...it ain't goin nowhere.

Re:SQL Dying...film at 11...NOT!

[tyler_larson]

After extensive research, our Databases Of The Future department has come up with the following prediction:

In the future, databases will be more "good."

While we can't yet go into detail as to how this will all work, suffice it to say that we have a pretty solid idea what the future holds.

Databases? Bah!

[GillBates0]

Real men work with raw data.

Nothing builds character like manually searching megabytes of raw, unorganized information for a relevent entry. Except maybe sorting it by hand.

Databases are for sissies.

Re:Databases? Bah!

[techwolf]

That's what Perl is for...

Re:Databases? Bah!

[The Angry Mick]

Nothing builds character like manually searching megabytes of raw, unorganized information for a relevent entry. Except maybe sorting it by hand.

True, but eventually, you have to give in to the need for less complexity - after all, there's only so many hours in a day.

Now I keep all my "1"'s and "0"'s in two separate containers. This makes it tremendously easy to find exactly what I looking for . . .

Re:Databases? Bah!

[temojen]

As are filesystems. "Real men" grep their block devices for magic numbers like "ReIsEr34" when looking for the start of their data.

Re:Databases? Bah!

[shish]

Nothing builds character like manually searching megabytes of raw, unorganized information for a relevent entry

Wow, someone who likes slashdot's search feature :o

Relational Filesystems

[Doc Ruby]

How about just getting filesystems to be relational? Replace the ancient 1960s-era hierarchical inode database that underlies filesystems with a modern relational one. Then distributed databases can provide a more consistent platform for all our distributed apps.

Enough stuffing metadata into filenames. Enough shoehorning all data into a file/folder/cabinet model, now less familiar to people than the networked infosystems that mimic them. Enough fake hierarchies inconsistent with accurate data models, forcing whole technologies like Apple Spotlight, GNU Dashboard, and Google Search just to transact basic relatioships buried in the data. Enough reinvention of the wheel with every initial RDBMS schema, just a layer on top of the DB's actual hierarchical filesystem - a shell for an inode database. Enough empty promises of "WinFS" and "OLEDB" vapor - get relational filesystems into developers' hands, and developers will move beyond them, building apps that meet users actual needs, dragging the database tech along.

Re:Relational Filesystems

[AKAImBatman]

How about just getting filesystems to be relational?

For what purpose? I used to work with a Unisys NX Series (and a predecessor that I don't remember) and it had all data stored in database tables. PC files were stored in new tables with a record length matching the size of the file. It was more a PITA than actually useful.

Enough stuffing metadata into filenames. Enough shoehorning all data into a file/folder/cabinet model, now less familiar to people than the networked infosystems that mimic them. Enough fake hierarchies inconsistent with accurate data models, forcing whole technologies like Apple Spotlight, GNU Dashboard, and Google Search just to transact basic relatioships buried in the data.

You don't need a relational model for that. All you need is a metadata area in the FS, and a metadata indexing scheme. You can then save psudeo-directories as metadata searches. i.e. A bit like the Label system used by GMail. This concept has been done to death in the reseach community, but no one has yet had any success in getting users to accept the idea.

Re:Relational Filesystems

[turgid]

The Pick OS.

Re:Relational Filesystems

[RiotNrrd]

How about just getting filesystems to be relational? Replace the ancient 1960s-era hierarchical inode database that underlies filesystems with a modern relational one. Then distributed databases can provide a more consistent platform for all our distributed apps.

I think that this is what Microsoft was trying to do with WinFS. The only problem would be that the RDBMS that replaced the FAT would be an embedded version of SQL Server.

Of course this would be *great* for p2p apps!

Re:Relational Filesystems

[AKAImBatman]

We've already got RDBMS tech - why reinvent an inadequate version of it?

Because current RDBMS designs are unsuitable for filesystems. Relational theory still holds (just as it does for OODBs), but the physical design should be quite different if it's going to be effecient.

As I said, this has been beaten to death in the research communities. BeOS even included a DBFS design, but it went largely unused. NTFS also has all the necessary stuff in it, but Microsoft constantly removes it in final releases. ReiserFS has DBFS features, but these also go largely unused.

I think the problem is that making effective use of a DBFS requires a very different set of applications. i.e. If the applications are aware of the functionality, then they can assist the user and provide useful support. But without this form of OS and application support, the user will find that the metadata is nothing but added confusion.

Re:I did not RTFA

[techwolf]

Quite true. MySQL does very well into the gigabytes. I haven't seen any good evidence of its abilities in handling terabytes of data. Don't get me wrong, I'm a huge fan of the MySQL, but I'm a bigger fan of using the right tool for the job. For your web message board, MySQL works fine. For holding product, sales, distribution, etc. information for, say Levis, it would not.

SQL isn't a database

[Nytewynd]

SQL, on the other hand:
1) Reasonably simple API
2) Scales to very large databsaes
3) Cross-platform/architecture
4) Performs very well.
Given the insane amount of inertia SQL has, it will extend into an object model, rather than be replaced by one. (EG: C/C++)


SQL is a language for set operations. By itself it isn't a database or storage utility. There are some different versions similar to what you describe. Oracle's PL/SQL allows you to make temporary tables and materialized views. Neither solves the overall problem the article describes.

SQL by itself doesn't perform. It is based on the database engine, and how good the developer is. I have gotten SQL queries that took minutes to exectue in seconds by adding indexes, analyzing tables, and totally rewriting inefficient code. It is only "cross-platform" if you follow the ANSI SQL standard. Each database has it's own set of handy functions that make the code database centric.

SQL doesn't really have an API. It is a specification that is sometimes followed by database designers, and sometimes ignored. For example, in Oracle you can either use the ANSI joining sytax (LEFT OUTER JOIN) or use the (+) in the where clause.

It scales to large databases only when they are designed properly. I work with 18 terabytes of data. My sql code wouldn't work so hot if the tables weren't designed correctly. Indexing, partitioning, and table structure have more to do with performance at that level than the code. The code can make a large difference too, but if the underlying structure is wrong, even the best SQL won't help you.

Re:SQL isn't a database

[mcrbids]

SQL by itself doesn't perform. It is based on the database engine, and how good the developer is.

A truth I hold to be self-evident. The language of SQL provides all the tools you need to make your application perform well, as you state.

SQL doesn't really have an API.

Realistically, SQL is an API. It's a highly abstracted interface for communicating between two programs. (your app, and the DB server software)

It is only "cross-platform" if you follow the ANSI SQL standard.

Sorta. See, I can write a script using PHP with a particular SQL call, and do the same thing in Perl, Java, ASP, C, C++, Python, Ruby, and even BASH, on Linux, Windows, Mac, or just about anything else with a tcp stack and a compiler. Sure, SQL implementations are different, with various shortcuts and extensions, but I'd call that cross platform if ever there was one.

Let me ask you this: How often do you see an OSS product (EG: phpwiki) that doesn't offer support for numerous databases?

Re:SQL isn't a database

[Shadowlore]

Let me ask you this: How often do you see an OSS product (EG: phpwiki) that doesn't offer support for numerous databases?

Quite a bit actually. In my experience most only support MySQL. Why? They don't understand the value of data integrity and the features MySQL lacks/lacked. As a result they have had to poorly implement things that the DB itself should do, and when they try to "port" this to another DB they run into all sorts of issues.

And SQL is a language, not an API.
So they give up.

Synchronization

[3770]

Most mainstream databases support replication. They are designed to be as fast as possible under heavy load.

Synchronization for a mobile device has another main requirement, robustness when the connection to the server is lost. A mobile device has to gracefully handle when the owner runs down into the subway.

It's already done

[duffer_01]

"one size no longer fits all"

This is absolutely correct when referring to databases for different application. However, why do people always assume that they have to choose the Oracle's, MS and IBM's out there? There are already databases that have been tailored for certain application environments. Take for example http://www.ianywhere.com/ who has databases like SQL Anywhere and UltraLite which are tailored for smaller workgroups and mobile devices.
I don't think the solution to the problem is to build a more complex non-relational system but rather to choose the right tool for the job. Why reinvent the wheel when you don't have to?

Re:I did not RTFA

[abigor]

Yeah, for those terabytes of data taken up by your mom's recipes and your cd collection, the extreme power of PHP and MySql is all you need, man.

Why 'Beyond'?

[Anonymous Coward]

Designed in the 1970s, the RDBMS has nevertheless proven to be the cornerstone of Web development three decades later. Thanks to systems like MySQL deployments are surely at record levels.

Essayist Clay Shirky has gone to far as to suggest that MySQL is at the center of a whole new software movement.

In my experience with Web applicaions the chief problem with the RDBMS seems to be that it does not do text indexing and search very well, so I have to keep a second store of data in something like Lucene.

The other major problem is the level of skill required to tune the database to achieve high-performance SQL queries, so hopefully the RDBMS will evolve with more self-configuration capability.

The article, which I only skimmed, actually addresses these two concerns but seems to pooh-pooh the notion of simply refining the existing RDBMS systems. Instead it says " Old-style database systems solve old-style problems; we need new-style databases to solve new-style problems. "

The paper seems awfully squishy on what this means. The clearest I found was a call to "produce a storage engine that is more configurable so that it can be tuned to the requirements of individual applications."

But this call for new highly modular/configurable storage "engines" seems to me to require at least as much fussy care and feeding as a traditional RDBMS. You're just replacing one DBA with another. And throwing out decades of refinement in the process.

The raison d'etre of the RDBMS is to allow the programmer to treat storage as a black box while gaining nifty ACID features. Extending this to text indexing seems logical.

Kind of OT SQL Question

[Over_and_Done]

I agree with the sentiments of the posters that SQL is not going anywhere, but I had a question.
As I am designing more and more complex web apps, I am constantly having to think of new, innovative ways to design the tables and databases and am currently making it up as I go. Does anyone have a reccomendation for books/sites that talk about good design proactices, that is not "How to use SQL" and relatively agnostic on the specific brand on DB?
Sorry for the OT post, its just something that has been bugging me for a while

Re:Kind of OT SQL Question

[genus babbage]

Depends what level you're after really, but here are some pointers that might help:

I know it says access on the cover, but Steve Roman's book Access Database Design & Programming is pretty good as a starting point, IMO.

You could also try Michael Hernandez's Database design for mere mortals

I found both to be pretty readable and a good introduction to the theory.

Once you've got the basics down, there's lots of further stuff you can try, from the obvious books by Codd and Date, to the more esoteric and weird.

I would suggest, however, that you don't stress the "platform independance" of databases; you can do it, but it's not going to be pretty - most of them have their own syntax, additions, quirks and foibles, and that's just in the SQL; it gets worse when you actually try and design code stuff; for example if you program your application to work with Oracle for locking, and then switch to, for example, SQL Server, you're going to have to redesign the way it works, since they have completely different strategies (or at least they did - been a while since I've done any SQL Server).

If you try for DB independance, you'll also end up coding to the lowest common denominator (no sequences for example... 'cause they all have their own standard...), which kinda takes the fun out of anything.

For Oracle, Tom Kyte is probably a good bet - Expert One-on-One Oracle is very good, IMO, well worth the asking price; I can't really recommend anything for non-oracle though, maybe someone else can help out; O'Reilly are bound to have something for pretty much everything and everyone.

the usual database blatherings

[Anonymous Coward]

by MARGO SELTZER, SLEEPYCAT

Sleepycat? The guys who make a brain-dead key/value database with no data manipulation or integrity capabilities? Who are they to educate others on the topic of relational databases? (Sleepycat's products are useful tools, but they are not true databases).

while data management has become almost synonymous with RDBMS, however, there are an increasing number of applications for which lighter-weight alternatives are more appropriate.

Ahh, so the proper title of this paper should be: "Beneath Relational Databases" or "Below Relational Databases". Because the relational model is a *complete* model for data storage and manipulation, so if you have a subset of this functionality, you are not "beyond" it.

As argued by Stonebraker, the relational vendors have been providing the illusion that an RDBMS is the answer to any data management need. For example, as data warehousing and decision support have emerged as important application domains, the vendors have adapted products to address the specialized needs that arise in these new domains. They do this by hiding fairly different data management implementations behind the familiar SQL front end. This model breaks down, however, as one begins to examine emerging data needs in more depth.

Well, the mention of Stonebraker's name as an authority on databases is generally an indicater of a content-free paper, but let's be sure we're talking about the same thing: the relational *model* is a *complete* model. There is no other more effective model, in fact as far as I know, there are no other complete models!

So if you want to use the relational model as a foundation to build new database products, go right ahead. If you're talking the same old vendor BS about "post relational" or "XML" (hierarchical) or "object" (network and/or hierarchical), then please shut up!!

My feeling is when he says "in depth", he means "less depth".

As more documents are created, transmitted, and operated in XML, these translations become unnecessary, inefficient, and tedious. Surely there must be a better way. Native XML data stores with XQuery and XPath access patterns represent the next wave of storage evolution. While new items are constantly added to and removed from an XML repository, the documents themselves are largely read-only.

Uh, yes there is a better way: create an XML data type in a relational database with a full set of XML operators. The relational model doesn't care about data types.

I have no interest in giving up the general relational model for a hierarchic model (rejected decades ago as not being general enough) based on a TEXT FILE FORMAT.

Stream processing is a bit of an outcast in this laundry list of data-intensive applications.

I smell Stonebraker.. yes, it's an outcast because stream processing has nothing to do with data storage!!!

Some argue that database architecture is in need of a revolution akin to the RISC revolution in computer hardware

Yes, all these people need to study and understand the relational model which was developed 30 years ago and is still the only complete data model. The relational model can be described in half a page, and consists of a small number of core operations from which any possible data storage and manipulation need can be developed. Stop thinking about implementations, think about the *model* and then use that develop new implementations!!

Old-style database systems solve old-style problems; we need new-style databases to solve new-style problems.

What does this mean exactly? I need to store and manipulate data without limitations. The relational model offers this. What is "old" or "new" here? I'm not going to switch to an ad-hoc subset of the relational model because it's "new".

This "paper" (wasn't there one a couple weeks from some Microsoft dude, which was equally useless?) commits the same old sins: 1) look at existin

Re:the usual database blatherings

[kimanaw]

This "paper" (wasn't there one a couple weeks from some Microsoft dude, which was equally useless?)

I suspect you mean this article by (the highly regarded) Jim Gray, whose opinion I'm much more inclined to seriously consider than the ramblings of the subject article's author (someone who, based on his company's product, apparently considers a B-Tree to be an innovative solution).

But I admit I found Gray's article equally disconcerting (perhaps he's been spending too much time on the sailboat in the SF Bay, and MSFT needed a shill to push their "XML Uber Alles" and Longhorn vision?).

SQL has managed to overcome a lot of derision over the years, and, tho the language has often been a bit slow on the uptake, it has managed to adapt admirably to abstract datatypes, recursive queries, OLAP, etc. Perhaps as importantly, the 90's left us with empirical evidence that the O-R juggernaut may have been ill conceived, with the failures of GemStone (which has since re-invented itself), and the disasterous impact of Informix's Illustra (nee Postgres) adventure.

Let's face it, SQL just works. Most people I've met who complain about SQL usually

• (a) don't know how to tune a database and/or
• (b) don't know how to properly use SQL and/or
• (c) are stuck using a half-assed DBMS

There may be an inherent beauty/advantage to a declarative language, in that it can adapt to change by adding a few nouns, some adjectives and/or adverbs, and, as a last resort, one or two new verbs, rather than forcing a redefinition of the entire language (C -> C++, COBOL -> ObjectCOBOL, etc.).

So, to paraphrase Twain, "The rumors of SQL's death have been greatly exaggerated."

A thought on XML documents

[It doesn't come easy]

I woke up and had an interesting thought...

I can imagine XML documents created in such a manner that they could constitute an object from an OOP (Object Oriented Programming) perspective, containing their own schema, characteristics, relationships and data. Further, I can imagine the ability of accepting such an XML document object into a range of other things, such as a modular program (dynamic program extensibility) or a database (temporary database extension). I can imagine the ability to package up such XML documents so that databases can be built simply by linking the XML documents together.

So (for example), one could send a request to the XML index in the sky, find and link documents containing a subset of know medical facts, and then kick off a data mining process that could discover previously unknown medical relationships. All without needing to know anything other than where to find the XML files.

Now imagine a tool that could convert all of the terabytes of data the world is generating every day into small, linkable, OOP like XML files... Sounds like a great open source project to me...

Re:A thought on XML documents

[The Slashdolt]

Here is the problem with your idea. Unlike the relational model, XML does not link facts. XML documents can be joined in any way, either valid or invalid, without you knowing one way or another. The relationships between documents are weak. There is no referential integrity. Within a proper relational model you are stating facts and factual relationships. Joins of those facts generate derived facts that are as true and accurate as your original model. Why add the overhead and complexity of xml? Why not just use a proper relational model?

Improving the efficiency????

[Dammital]

"Relational databases were developed in the 1970s as a way of improving the efficiency of complex systems.

Huh? Go back and reread some of Codd's papers (in the late 60's, BTW) and you'll see that efficiency was never a motivator. Simplicity was his aim, filesystem details were made irrrelevant, explicit navigation was obsoleted, and a built-in security model was included.

When relational systems finally began to appear (and I'm thinking specifically about IBM's System R) they were dog slow, and the extant hierarchical and CODASYL network databases of the day ran rings around them. Still do, unless you throw lots of hardware at the RDBMS.

RDBMS have lots of advantages over older technologies, but performance is not among them.

Re:KISS (I can prove SQL will be around)

[gosand]

SQL, on the other hand:
1) Reasonably simple API
2) Scales to very large databsaes
3) Cross-platform/architecture
4) Performs very well.

I am proof that SQL will be around for a while. When I first saw Unix back in the late 80s, I thought "this is too hard to use, why would anyone need this?" I have been a Unix/Linux user since about '92.

When I took my first SQL class, I thought "these queries are very cumbersome. SQL is stupid." I still use it today.

In '93 I heard about this thing called the World Wide Web, and thought "This is unnecessary. I can find whatever I need on gopher and ftp sites. Why would I want a gui thrown on top of it?"

As you can see, I am quite the visionary.

Re:Multi-Terabyte Data Warehouse and MySQL

[AKAImBatman]

I have no doubt that terabytes could be stored in MySQL. My overall point is that MySQL is not designed to effectively manage that much data. For example, the presentation you link to shows that Terabase is the workhorse of the business. Data is then offloaded to a disposable MySQL database for data warehousing analysis. The database is then purged after one week.

The holy grail of information technology would be to eliminate the need for such cumbersome replications, and instead have a single, reliable data source that can be queried for any information needed at any time. Unfortunately, MySQL isn't it. ;-)

Re:AS-400

[Monkelectric]

As/400's are great for accounting and manufacturing. They are *NOT* used by IT professionals or Scientists and are only used in accounting (primarily gambling -- to monitor slot machines) and manufacturing.

AS/400's are hopelessly complex even to seasoned IT professionals such as myself, and they're only around is not because people like them but because the work SO GOD DAMNED WELL :)

point being, tractors work well to but you dont drive one day to day do you? :)

The future exists today

[erasmix]

Many of the capabilities that, according to the article, should exist in the next generation of databases exist today in IBM Informix Dynamic Server. Examples: The hability to use other than B-Tree+ indexing technology, the hability do describe data beyond traditional types, etc. Furthermore the article relies heavily on comments by Stonebreaker, the father of Informix's Object Relational capabilities.

MUMPS

[stuffduff]

MUMPS had one data type, a string and one data structure, an array. Commands abbreviable to a single letter. MUMPS ran whole Hospital Information Systems on DEC PDP-11's where MUMPS was both the Operating System and the Language and was called Digital Standard MUMPS or DSM for short. It was way ahead of it's time, it predated SQL, and was one of it's progenitors. Anyone who offers a survey of databases and fails to include it is seriously myopic. Google 'MUMPS Language' for the tip of the iceberg.

As for the future, I think that while OO databases are all the vogue, that O databases, where each bit of information is represented by it's own object and will have the ability to demonstrate autonomous agency is a good area for research. Then we can just let the data speak for itself! ;^)

"Every person takes the limits of their own field of vision for the limits of the world."
- Arthur Schopenhauer

Sleepycat?

[halosfan]

To me, this entire article reads like a plug for Sleepycat, written, not surprisingly, by a Sleepycat person.

Relational data model is just that -- a data model. It doesn't concern itself much with implementation (and therefore, performance in any particular environment) or with how applications use the data. And that's really the point -- relational databases are application-agnostic. They are designed to store the data that will be possibly accessed by applications that are not yet conceived. That's the reason they put great emphasis on internal correctness of the data. Once you have a database specialized for one application, that's not really a database in the relational sense -- that's a way to persist your application data. And that's where Berkeley DB shines. It doesn't replace relational databases, it just serves a totally different purpose.

How about....

[plopez]

really implementing a relational model to begin with? Then we can decide if the relational model is broken or just the vendor implementation.

How about... a query language that is fully set operations compliant, i.e., something other than ANSI SQL which is a strange mixture of set and bag operations, and a mixture of relational algebra and relational calculas and some other 'extensions'.

How about... realizing that a major design goal for the relational model was data integrity. Modularity and configurability are also good goals but if you are serious about your data, integrity will be at the top of the list.

The biggest problems I see with databases is very few people understand how to use them. Here's a few tips:
1) a table is *not* a class or an object. Tables + constraints + user defined types + constraints etc. when used properly can define domains which are close to classes and objects.

2) Learn how to normalize. A badly (or flat out not) normalized database threatens data integrity by violating the once-and-only once rule. As a rule of thumb if the table has more than 20 fields in it you should review your data model and make sure it is properly normalized.

3) Point 2 is often the consequence of mindlessly slurping in spread sheets or MS Access database tables. Anyone doing this has no business being within 50 feet of an IDE.

4) Ditch Raid 5. 0+1 will give better perfomance in most cases. Manager like Raid 5 because it is cheap, you get what you pay for.

5) Have multiple channels for data, transaction logs, large indices and O/S or user applications to reduce bottle necks. This is expensive but for large databases going cheap will hurt you.

6) Learn a little theory, it won't hurt you. In fact it can save a large amount of time and trouble. Do not be afraid of learning about the technology you are using. After all, technology is what you are good at, right?

7) If it is a read only database, turn off logging for speed (impossible to due under SQL Server 2000 btw). Also, if a table is on a purge and load paradigm (many reporting and/or datawarehouse tables are) turn off logging on the table level if your version of database engine allows you to do so. Likewise, turning off logging on a hand held or other single user system may be appropriate, just make sure two people do not try to use the database at the same time.

8) Avoid XML. Too much bloat.

9) Learn how to use indices on tables.

10) Learn how to read a perfomance monitor/top etc.

Postgresql is both working hard to become truly relational AND is adding support for geographic objects and objects. The MySQL crew is working hard to improve. Oracle has some nice perfomance features but I think their 'Object/Relational' implementation is broken. SQL Server is getting 'long in the tooth'. There is also a great need for temporal databases and lightwieght engines. But remember, there is no 'silver bullet', no short cuts. Just hard work to be done.

Re:How about....

[Anonymous Coward]

1) a table is *not* a class or an object.

Good advice. A table is simply a snapshot of a relational value. True relational values have no top/bottom or left/right ordering so you can't really show them on paper or on the screen. It doesn't make sense to map classes or objects to tables.

2) Learn how to normalize. A badly (or flat out not) normalized database threatens data integrity by violating the once-and-only once rule.

Normalization has nothing to do with integrity per se. You can add constraints to a denormalized database that make it logically equivalent to a normalized one. I'm not aware of any "once and only once" rule in relational theory.

Normalization is about dependencies: your data shouldn't have certain types of dependencies in it.

As a rule of thumb if the table has more than 20 fields in it you should review your data model and make sure it is properly normalized.

Wrong, the number of fields has nothing to do with normalization. And you should review your data model even if it has 1 field.

Ditch Raid 5. 0+1 will give better perfomance in most cases. Manager like Raid 5 because it is cheap, you get what you pay for.

Yes, 0+1 is faster, but lots of RAM is even faster. However this is a physical detail and not anything to do with the relational model.

6) Learn a little theory, it won't hurt you. In fact it can save a large amount of time and trouble.

This should be point #1 with a double underline. The lack of understanding and even hostility toward theory is frightening in the IT industry. If the author of the paper above had any foundation knowledge her paper would be much different.

8) Avoid XML. Too much bloat.

Just remember that XML is FILE FORMAT like Jpeg, CSV, and you'll do fine.

9) Learn how to use indices on tables.

Christ, I hope people know this one! They should also realize that an index is a PHYSICAL manifestion of a logical idea: keys.

Hmm ..

[ghakko]

Has anyone noticed that the author of the article is from Sleepycat (which sells commercial licenses for Berkeley DB to embedded systems developers)?

She puts forth a case against SQL and relational databases in general and claims that many applications (like directory services and search engines) have read-heavy, hierarchial access patterns which favour lighter-weight, non-relational, transaction-optional databases.

And .. it just so happens that Sleepycat's flagship products are Berkeley DB (a flat-file database) and DBXML (an XQuery engine built on top of that).

Issue of dimensions in RDBMS

[jonbrewer]

One of the problems of current databases when is that a typical relational database doesn't have enough dimensions. Designing a table to store data is trivial - but what happens when you need to know the intersection of X and Y at time Z?

This is a fairly common question in data warehousing: What is the data today, what did it look like yesterday, last week, and last year?

I have seen it worked around in silly ways (snapshot and rename a table every day/week/month) and more clever ways (use separate transaction tables to record changes), but never in a particularly elegant way.

Wiser colleagues whispered to me the dirty answer "object relational" and scurried away to their dens of Rob Zombie and J2EE. I never got my head around object relational databases before leaving that world, and so am left to ponder papers from IDC with statements like this one:

"putting object extensions on RDBMSs is tantamount to adding stereo radios and global navigation systems to horse-drawn carriages"

Ouch, is that a swipe at Oracle? Seems that as far back as 1997 pundits have said that the future is in ODBMS, and not RDBMS or ORDBMS. Hmm...

Re:5th normal form model of perfection.

[Frank T. Lofaro Jr.]

If full 3rd normal form is too slow, you need better hardware.

If a few joins (assuming you've got indexes, etc setup) make it too slow you are too close to the edge.

Saving on hardware but spending more in dealing with data problems is false economy.

No data integrity

[Tony]

The problem with MUMPS is the data integrity issue. As essentially a collective of hashed indexes where nodes can contain both data and more branches, it sucks for *any* kind of serious data retrieval. You basically have to create and maintain your own indexes, unless you go with Fileman, which is a massively moby hack, but a hack nonetheless.

The idea of the relational model is simple: it is based on set theory, which has a strong mathematical model. There is no equivelent model for object databases, nor for tree-based databases like MUMPS. There is no strong mathematical basis by which you can judge the integrity of your data.

Cache', by Intersystems (a Post-Relational Database!) is based on MUMPS. You've seen their adverts here on Slashdot. They claim to be object-relational, but they are no such thing: they are MUMPS. They went on a buying spree and purchased up most of the failing MUMPS vendors (DSM, MSM, etc), and now they are the big guys in the M world.

They have some pretty nify hacks which compiles their "object-oriented MUMPS" programming language (I forget what it's called) into straight M. Fun. Doesn't stop it from sucking hard.

MUMPS is, at best, a fairly bizarre language with persistent storage of global arrays.

MUMPS drives me nuts. It uses whitespace for blocking just like Python, but they had so much trouble with it, they eventually allowed a '.' to replace the whitespace, so you end up with code like this:

S node=""
F S node=$O(^ZZZYYY(node)) D
.. Q:node=""
.. W "^ZZZYYY(node)=",node,!
.. R Pause

(I stole that from this duscussion.)

(Sidenote: I have to admit, my exposure to MUMPS is one of the primary reasons I despise Python's whitespace-as-blocking. It seems replaces the poor aesthetics of brace-blocking with something more error-prone and stupid-looking, though more aesthetically pleasing. But all that's just opinion. I'm sure Python is a good language, just as I'm sure MUMPS is not.)

Re:5th normal form model of perfection.

[orderb13]

I actually once asked a Terradata consultant what the 4+ normal forms were used for. His response was "So high priced consultants can finish out their contract without actually having to do anything."

JSR 170 - Unified Data Repository

[uss_valiant]

Another approach to the problem: JSR 170: Content Repository for JavaTM technology API
Standardizing the interfaces to various data resources (filesystem, database, cache, ...).

The expert group reads like a who's who in data management. And it seems to be very near to the final draft.

Another one?

[ColdWetDog]

I've already got Windows and a girlfriend, I really don't need another irrational database.

JDBC further standardizes SQL dialects

[irritating environme]

JDBC (probably ODBC too, tho haven't used it in eight years) helps to standardize key generation (in JDBC 3.0 FINALLY), and Date processing (christ, date functions are so annoying). Most other operations can usually be done by the platform language that is processing the data, so you can avoid the tie-in that results from various SQL dialects' built-in functions. XML databases were a total flop, and so were object databases. I agree, SQL engines are so mature now that I don't see any database tech replacing them for another ten years. By the way, can we get PostGres (and now Oracle's) support of regular expression LIKEs standardized? And can we please get JDBC to support something like: INSERT $price INTO pricetable where productid = $productid rather than using ?'s and counting which ? to set values to? Hibernate's query language does this, and I really like it.

What I'd really like to be able to do...

[thewils]

If any smart DBMS developers are listening, is to define a set of queries within the database (like for a _simple_ example "male" and "over 60" and "salary x") and then be able to refer to these criteria by name only, having the database build the query based on these rules as I choose to combine them (select xxx from yyy where 'male' or where 'male' and 'over 60').

Sort of like stored procedures in implementation - they could be called stored query definitions.

Because these query definitions would already be parsed, they don't require overhead to re-parse each time the stored query definition is executed.

Please have this feature ready in about 6 months :)

XML documents are an hierarchical database

[irritating environme]

An XML doc is a tree, thus it is hierarchically organized data. There have been hacks to try to extend around this limitation, but relational data still has superior flexibility.

that's why XML databases flopped

Re:I did not RTFA

[AKAImBatman]

Also, until db2 v8 came out two years ago, it was pretty far behind oracle 8 & 9. Now, it's in a great competitive position.

Last time I did a comparison, it was DB2 7.1 vs. Oracle 8. Most of what bit me in the ass with DB2 was its flakey management tools and multitude of minor details that needed tweaking from the days when it was a mainframe database. (e.g. Why does it need a buffer large enough to hold the entire blob chunk that's going to be transferred? That's just stupid. It should pull across as much as the buffer can hold, fill the requesting array, then go back for more and repeat.)

As I said, it would only start a pointless discussion on who likes what database.

3rd form, no dup data==no totals on invoice.

[HornWumpus]

You're advice: don't store total's on invoices and just requery the line items every time? (don't let me construct a straw man please correct me if I'm misinterperting)

You do realize you don't know how big my parent table is nor how infrequently the children change.

All generalizations are false.

IMHO When the child data changes very rarely or never, update triggers that recalculate parent totals are sometimes the way to go. This violates third normal form and is the most common de-nomilization I've done. Hell I've lived without the update triggers and just stored totals. When I was a kid we ran data checking batch jobs to check data validity.

To pronounce PostgreSQL, read the FAQ.

[tepples]

I think it's more because people look at the name, try to figure out how to pronounce it, then give up

Why, given that this FAQ entry clearly states that it's "post-gress-cue-ell"?

Re:5th normal form model of perfection.

[HornWumpus]

Sure it could. But it does'nt.

The DBMS could theoretically fix badly stated SQL and do it right anyhow. But it does'nt.

I've tuned data collection processes that were originally written by a moron who wrote his masters thesis on query optimizing. My version turned a two and a half minute data collection, reduction and display process into a fifteen second process. Hearing him exclaim 'that's impossible' over the cube walls was a highlight of that job. (Bozo did a masters thesis on optimizing, but did'nt know how to read a query plan).

Just because someone has ivory tower credentials does'nt mean they know their head from their ass.