Smallest database that supports indexes, high write volumes, and is ACID?

Question

I don't really care if it's NoSQL or SQL based - as long as it uses int indexes (and stores them in RAM for fast searching) so I can find my data with simple queries based on criteria like user_id, lat, status, or other common int fields. The actual records can be stored on disk.

My first guess would be SQLite - but it moves slowly when dealing with a lot of concurrent writes.

Second, it also needs to be able to run in very small amounts of RAM for VPS with limited resources. This excludes MongoDB since it spreads to fill all available RAM (well, the diskcache does really). I also can't use MySQL Innodb since it uses about 100MB of RAM just to load and MyIsam doesn't support ACID.

So are their any RDBMS or NoSQL databases that meet all four requirements?

Update: When I say small databases, I mean databases that only use 8-60MB of RAM. I understand that actual data will increase this but most of my datasets are usually under 1GB for the smaller sites with about 5MB of indexes that would need to be stored in RAM. So an ideal database would use about 30MB when running with a fully index dataset of about 1GB. Take this site for example, I doubt the whole stackoverflow site takes much more than 1GB to store.

Update: To clarify, a setup would ideally store all data on disk. However, it would also keep column indexes in RAM (just ints after all) which would contain the needed pointers to data on the disk. This would avoid two things 1) keeping unneeded rows in memory like redis and 2) keeping indexes on the hard drive slowing searches (SQLite).

An example is MySQL which can be configured to only keep primary and secondary indexed columns in memory and all other data on the hard drive. However, MySQL either uses 100MB extra RAM just to add InnoDB or you forgo ACID compliance and stick with Myisam which is not transaction safe.

Again, the target is systems that are limited in RAM and can't handle more than a couple Megabytes of cached indexes - but that still need to allow frequent writes/updates of normally small data sets in a safe manner.

Update: apparently finding something that meets all these requirements is a bit much. So, starting with the most important features let me list them in descending importance.

1. Low memory usage
2. Indexes (or something to mimic them)
3. Handles concurrent writes
4. ACID

Expanding on #1, it is more important that data can be written than that reads are fast. Which also means that the amount of RAM should not have any affect on the amount of data that can be stored.

Expanding on #2, ideally (given how small they are) indexes should be stored in RAM since indexes should be nothing more than int values that are compared to filter results before actually accessing the disk for the data.

Answer 1

Any particular reason to not use raw btrees?

Answer 2

For high-volume concurrent inserts and updates, a hashed table beats b-trees hands-down. The tradeoff is that with a hashed table you cannot store the data physically in a meaningful sorted order, e.g. sorted by zip, to make selecting and ordering by zipcode faster; rather the hashFunction( primaryKey ) determines the location where the record is placed in the file, and if you want to extract all records with a particular zipcode, those rows won't be physically contiguous. But you'll have to define what you mean by "a lot of concurrent writes".

EDIT: just wanted to underscore the fact that with a hashed table, you don't need to keep the Primary Keys in RAM because the hash-key function is fed the key value and it returns the record location to your seek routine.

So keys-in-RAM per se need not be one of your requirements. Speedy retrieval is the requirement.

Answer 3

I'd say no: you have somewhat contradictory requirements

• In memory = data cache (which is data and indexes). Covering indexes will need more RAM. But you want small RAM footprint

• Write volumes depend on underlying IO stack, really. Eg Write Ahead Logging, required for ACID in some implementations

What would be most important, and what is priority of the rest?

Answer 4

Redis may be an option, as it store indexes (keys actually) in ram, is very compact on ram usage and with vm enabled values will go to disk. But it is a key value store, so modeling might be a bit different. It is also very fast on writes and reads.

CouchDB is also a option with low memory usage and it's a great database, but might not fill all your requirements (ACID and keys in RAM).

Answer 5

I'd try to squeeze the performance you need out of SQLite.

• Write performance is good if you do many writes per transaction.
• 3.7 introduces write-ahead logging (WAL) which, when enabled, can improve performance in many cases.

Answer 6

You can take a look at Firebird

Answer 7

OrientDB: it's the faster in insert. On common hw insert 150,000 documents per second. Supports indexes, ACID transactions and even it's a NoSQL dbms supports the SQL with extensions to treat trees and graphs of documents. Open source and always free (Apache 2 license).

Answer 8

You didn't say which OS you're targeting.

If it's Windows, you might consider ESENT - an ISAM database that comes as part of the OS (not a lot of people know about it).

Here's a blog post with an intro in C++.

Answer 9

I recommend BerkeleyDB. It doesn't use SQL at all, and you have to specify your lookup method explicitly (sequential records, btree, hash). However, using secondary databases, you do have support for indices, and you can set the shared memory consumption (cache size) per database - the default cache size is 256KB. It supports ACID transactions.