Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a storage-engine or database that naturally support this use-case:

1) Key-Value storage. Small Fixed Length Key, and value within [1KB, 64KB]
2) Access is read-mostly
3) Latency more important than throughput.
4) Use discs for values; not a memory-database, but keys could be kept in RAM.
5) Transactions (that involve several values) are NOT required
6) It should be possible to roll-back to a point-in-time (say, 1 week ago max).
7) All changes are attributed to some specific entity (a user)
8) It should be possible to roll-back changes done by a specific group of users
   within a certain time interval.
9) Rollback happens rarely, so transaction logs should not be kept in RAM.
10) Online backups are possible.
11) I should be directly or indirectly accessible from a JVM.

While I know several engines / DB that generally fit, point 7 and 8 are more problematic.

What would fit this use case best?

[EDIT] This is my first "independent"/private project and I don't have much money to invest for software. Also I'm not sure yet if I will charge for it, or open-source it when it is done, so I would prefer a free storage, or at least one with a free version. Here a clarification of some questions raised in the first answer.

3) I'm sold to SSD already.
4) I said "use disc" simply because the data will not all fit in the RAM I can
   afford. Also I suspect I will have a lot of "cold" data, that gets created once
   and never accessed again, so it would seem a waste to keep it all in RAM, as
   would be the case with VoltDB, for example.
7) Adding explicitly the UserID is not problem in most database. I just thought that
   some storage system might have some kind of "special" ID that makes rollback
   easier.
8) This is where the real problem is. Writing a traditional "undo/rollback" by hand
   is time-consuming and error-prone. I know I can do all the rest with most DB.
   What I'm looking for is one that implements the undo/rollback for me, given some
   transaction ID or similar. And most importantly, without undoing other
   independent transactions that happened before or after.
9) I think redis does that; not sure.
10) I was (implicitly) thinking about a free one, where it's not always a given.
share|improve this question

migrated from stackoverflow.com Aug 14 '11 at 14:08

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

    
I don't think you can get a good answer to #8 until you put some more business logic around it. For example, User A member of group #1 makes an edit on Monday. Tuesday User A leaves group #1 and becomes a member of group #2. Wednesday you want to roll back all the edits for members of group #1. Tell us what you think should happen. Then make it more complex and tell us what you think should happen. This is an unusual requirement and just the sort of thing hand coding is required for. –  kevinsky Aug 15 '11 at 21:43

4 Answers 4

up vote 3 down vote accepted

Here's the problem that I'm struggling with, and that I think you will find when attempting to find this type of system. (Particularly when it goes to requirements 7 & 8.)

Let's say that you have User A who changes a value from 1 to 2. Then User B changes it from 2 to 26. If you attempt to rollback User A's value, what do you roll it back to? Do you change it back to 1 or do you leave it at 26? Do you do some strange business logic and make it 25?

There are several possible scenarios, but ultimately, there is no generic way of handling this type of data. If you don't roll back, then you fail to satisfy Rule 8. If you do roll back, then you fail to satisfy Rule 7 (allowing ONLY changes from a certain user to roll back). If you do some crazy math rules, then each rule will have to be very specific to the column that modify.

I don't think that there are any databases that can handle this on a general basis. It is possible to roll back on a point-in-time basis, but that completely breaks Rule 7 (allowing you to roll back changes from a certain group).

Outside of a custom-written, specific use application, I don't see how it's possible to implement 7 & 8 on a generic database system.

share|improve this answer
    
For my "most important" data, in your example, I would want to leave it to 26. But I can see that a "simple" rollback logic would probably leave it to 1, and that it is in fact use-case specific. And if the "correct" behavior is use-case specific, then I won't find it in any DB/Storage implementation. In other words, the answer would be: "No, there isn't one. You have to do it yourself." –  Sebastien Diot Aug 14 '11 at 20:22

You did not specify open source or No-SQL or the platform where the database would be installed so your choices are not limited. Most databases will do what you want. I grant that #8 would require some customization so changes by a group can be rolled back.
As an example: Oracle
1) key value: storage can be done in a table
2) mostly read only usage does not enforce any choice of database.
3) if latency is important spend some money on solid state drives and good hardware combined with good table design for your application
4) use discs for values: what is driving this? Is there a defined end user requirement that needs this or is this a requirement looking a problem?
5) all databases can do linked or individual transactions
6) Oracle has the flashback query to allow you query the state of your database at a point in time. This can also be accomplished by detailed transaction logging or even restoring from a backup at the specified point in time.
7) all changes attributed to a user. This is implicit in connecting to a database that has detailed transaction logging. If your application is pooling connections and allowing anonymous edits this is not a good practice.
8) rolling back all changes by a group of users: this is harder to do. It can be done by forcing all insert, update, delete transactions to be done through stored procedures which log everything
9) why does it matter whether transaction logs are kept in RAM? You need them, let the database decide where to keep them.
10) I can't think of a commercial database that does not allow online backups.
11) accessible from a JVM. This is possible from Oracle any many other products.

I think there is context missing from your question that would explain your use-case better. What did I miss?

share|improve this answer

For the storage type & issues (1/2/3/5), I'd personally lean towards LDAP, but then again, I'm not that familiar with other NoSQL implementations.

For the ability to log the changes & rollback, however, I'd look to any database system that allows you to set triggers. As you said (#2) that it's read-heavy, a little extra overhead on writes might not be so bad -- you just do your typical journal table**, and make a copy of the record pre-modification, with a timestamp and the user that made the change.

I don't tend to use the various table design tools, but I seem to recall that some of 'em had options for journaling, where it'd automagically create the second table and the necessary triggers. You might need to add logic yourself that'd keep it from going past 1 week and to apply rollbacks.

If any of the NoSQL implementation support triggers, that might give you the best of both worlds.

** at least, that's what they're called in Oracle, I had difficulty finding if they're called something else in other databases

share|improve this answer

For #8 on Oracle you could look into Oracle Workspace Manager. Here is the short description:

Workspace Manager, a feature of Oracle Database, enables application developers and DBAs to manage current, proposed and historical versions of data in the same database.

Applications and DBA operations often work with more than one version of the data. Three common reasons to have multiple data versions are concurrency, auditing and scenario creation. Oracle Workspace Manager provides workspaces as a virtual environment to isolate a collection of changes to production data, keep a history of changes to data and create multiple data scenarios for “what if” analysis. It can save money, time and labor over traditional approaches.

share|improve this answer

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.