Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have a data-intensive application that desperately needs a database make-over.

The general data model: There are records with RIDs, grouped together by group IDs (GID). The records have arbitrary data fields, (maybe 5-15) with a few of them mandatory and the rest optional, and thus sparse.

The general use model: There are LOTS and LOTS of Writes. Millions to Billions of records are stored. Very often, they are associated with new GIDs, but sometimes, they are associated with existing GIDs.

There aren't as many reads, but when they happen, they need to be pretty fast or at least constant speed regardless of the database size. And when the reads happen, it will need to retrieve all the records/RIDs with a certain GID.

I don't have a need to search by the record field values. Primarily, I will need to query by the GID and maybe RID.

What database implementation should I use?

I did some initial research between document-oriented and column-oriented databases and it seems the document-oriented ones are a good fit, model-wise. I could store all the records together under the same document key using the GID. But I don't really have any use for their ability to search the document contents itself.

I like the simplicity and scalability of column-oriented databases like Cassandra, but how should I model my data in this paradigm for optimal performance? Should my key be the GID and should I create a column for each record/RID? (there maybe thousands or hundreds of thousands of records in a group/GID). Or should my key be the RID and ensure each row has a column for the GID value? What results in faster writes and reads under this model?

share|improve this question

migrated from stackoverflow.com Oct 1 '12 at 21:50

This question came from our site for professional and enthusiast programmers.

1  
how many writes per second are there? For low number of writes/s (up to 100-200/s), you are likely to find RDBMS able, for high number of writes/s, not so much. –  miraculixx Jan 1 '13 at 2:10
add comment

2 Answers 2

Unfortunately there isn't quite enough information for one recommendation. How important is the data? Do you need full ACID compliance or is eventual consistency ok? How about quorum (majority) consistency? What is the budget?

If you aren't too concerned about queries then a non-indexed NoSQL implementation where GID:RID is the key is probably the highest performance write implementation you can have. If you set triple redundancy with async writes (fsync off) and quorum you get very high durability, but there is a very slight chance on full failure to multiple systems of losing writes.

If full ACID-compliance is key, MySQL/SQL/Oracle cluster or a sharded SQL implemenation can be created with full durability, but disk IO will be your main limitation. In that case a design that results in sequential writes will be your best option, and enterprise SSD RAIDs can help achieve 3GB+/s write speeds if needed. Some NoSQL implementations may be an option also.

As is always the case, there is no substitute for load testing, every situation is unique. Document vs Column really depends on the data, from the limited information it doesn't sound like a document dataset. If you query at all by RID and store documents by GID, then either the queries will be tremendously slow (and possibly fail) or you will have to introduce secondary indexes. It sounds like a NoSQL column or document system with the key being GID+RID might be optimal.

Database design is always full of trade-off, there is no answer for best read and write, the best write will generally have the worst read performance and vice-versa. For example, indexes improve read performance, but writes get worse because of the insert/update of the index. Durability can be reduced for better performance by turning off fsync or turning off quorum, but then dirty reads or lost writes are possible. It really is all about the trade-offs.

share|improve this answer
add comment

I would think that most relational databases would be able to handle this problem.

in postgresql: create table big (rid bigserial primary key, gid bigserial, data bytea ); create index big_gid_index on big(gid);

Since you do need to search on the data keep it as a 'blob' in XML or some such.

You can then partition on the GID for preformance.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.