How can I implement a large relational database schema on a basic key value store?

views:

139

answers:

How can I implement a large relational database schema on a basic key value store?

How can I implement a large relational database schema on a key value store?

The following are the requirements:

1) uses no stored procedures or special database vendor specific features

2) Uses indexes

3) Uses joins

4) Many complex types in tables (VARCHAR, INT, BLOB, etc)

5) Billions of records

6) Full text search

7) Timestamped backup possible

8) Transactions not needed (atomic single row/field updates only)

+1 A:

You will have to, in essence, build your own relational database system. Without it, joins will be horribly slow (no query optimizer). You can get full text search by grafting on Lucene.

Have you considered an open source RDBMS (e.g. Derby)?

bmargulies 2010-02-12 13:51:31

I took a look at Derby but it doesn't scale onto many nodes

Zubair 2010-02-12 18:44:58

Well, I don't need transactions which is a large part of what databases do. But Lucene looks like it might have a place in what I want to do.

Zubair 2010-02-12 18:47:26

@Zubair Query optimization is another other large part of what they do. If you have only simple joins, then you might not need it.

bmargulies 2010-02-12 18:50:00

Yes, I guess that makes sense what you are saying if I only need simple joins then I am ok. Does this mean that key-value stores are not so good as complex reporting systems which often use multi level joins?

Zubair 2010-02-12 20:08:24

+1 A:

Often, the whole point of a key-value store is to get away from the constraints of relational databases, but it sounds like you want them back here. Everybody wants to have their cake and eat it too, but I'm confused about what you're trying to accomplish here. if you want the power of a relational database, you should use a relational database.

That said, you may want to take a look at MongoDB, which represents a very good compromise between the rigid, structured nature of relational databases and the more free-form approach of a key-value store.

John Feminella 2010-02-12 13:54:52

Yes, I've seen Mongo. Right now I'm seeing MongoDB, Cassandra, and Riak as possible contenders. But I still need to use some solid practices whichever store I choose.

Zubair 2010-02-12 18:46:24

And, yes, I do want my cake and to eat it :)

Zubair 2010-02-12 18:48:00

ansaurus

tags:

views:

answers:

How can I implement a large relational database schema on a basic key value store?

related questions