A similar approach is so-called "object prevalence" (http://en.wikipedia.org/wiki/Object_Prevalence). Basically, keep all the data in RAM and write a journal of changes. On startup, the journal is played back, incrementally restoring the state. Snapshots are taken periodically to keep the journal down to a manageable size.

It's a form of the Command design pattern and has some nice properties. You can get transparent thread synchronization by executing queries in parallel but serializing commands that modify state. For web apps, the HTTP request offers a natural representation for commands (but of course you'd want to strip them down to their essence). You can get fault-tolerance and scalability by feeding the command stream to replica servers. (State-changing commands must be executed by the master server but queries can be load-balanced across the replicas.) And if you keep the journals around, you have a complete history of the application's state.

EDIT: See also http://www.advogato.org/article/398.html

I probably should have pointed out the implications for disk I/O. In many cases, the serialization of the command in the journal has a smaller footprint on disk than the data that's modified. Consider an extreme case where a small HTTP POST touches dozens or hundreds of records.

And appending data to a log is essentially an ideal disk access pattern.

Of course this can't be said for the snapshots, but you can offload that task to a replica server.

I did the same when our mysql queries were taking around 30 seconds each (iirc the db had about 20 million rows, but I didn't have anything to do with it). The replacement I wrote ran each query in about 100ms. The hard part was in getting my data structures good enough that the whole dataset could fit in 3GB (that's how much memory was in the server I was using).

memcached (google it) implements this idea (giant in-memory hash table), and scales to multiple machines. used by facebook and lots of other very high volume sites.

it's often also used as an LRU cache on top of a normal database so that you can still get the benefits of persistence and rich queries but use memcached to lower the number of hits to the disk/DB.

With memchached you pay the performance price for interprocess communication to get to the data in your hashtable, even when the data are on the same machine. The RoR / PHP / Python models for web development have each request running in different process, so this is unavoidable for them. Java / C++ are more suitable for pg's architecture (obviously Arc / Lisp too).

Also when you use memchached as a db cache, be preprared to lose some transactional properies of the database storage.

From "The Other Road Ahead":

"When you have the users on your server, you don't have to rely on benchmarks, for example. Benchmarks are simulated users. With server-based software, you can watch actual users. To decide what to optimize, just log into a server and see what's consuming all the CPU. And you know when to stop optimizing too: we eventually got the Viaweb editor to the point where it was memory-bound rather than CPU-bound, and since there was nothing we could do to decrease the size of users' data (well, nothing easy), we knew we might as well stop there."

Couldn't you have done better if, instead of keeping all hash entries in memory, you kept them on disk and used memory as an LRU buffer? It seems like you could have fit more users on a server that way.

That is exactly what the operating system's virtual memory system does. But, there comes a point where there is too much swapping.

The OS isn't very smart about it because it quantizes everything along page boundaries and has no semantic understanding of the data. That works fine in the typical desktop-system case where you have 100 processes using a few megabytes each and at any given time only one or two are active. When you're running one server process that uses all the system's memory, you can be a lot more efficient by handling it yourself.

Pg, that's exactly the technique I used for a sudoku site I built for myself. Everything was cached and only read from disk when something changed.

It's snappy and with very little overhead.

(!!!) I'd rather re-implement LISP (as in Greenspun's 10th law) than Oracle.

LOL keep downmodding me. I'd love to see the hashmap-based transaction handling. Was it as good as this:

http://worsethanfailure.com/Articles/I_Think_I_0x27_ll_Call_Them__0x26_quot_0x3b_Transactions_0x26_quot_0x3b_.aspx

Sometimes a solution that is specific and targeted is more efficient and usable than a general solution molded to fit the problem.

No one says that if you need a relational database you should reimplement it yourself - just that the DB is not the only solution for all web back ends.

Yep. No matter how you phrase it, there are people out there who will never see how that might be true. I've said before that using the file system or caching data in memory isn't an anti-database technique, it's simply a solution to a specific problem. It won't matter though - someone will have a fundamental issue with the approach, no matter how you present it. That's life.

_molded to fit the problem_

Most problems I've worked on had to take scaling, concurrency, searching/lookup, report generation (joining), etc, into account...all of which a dbms gives you out of the box. Maybe we're dealing with _very_ different kinds of problems.

Did it ever need to scale beyond one server?

Yes, but we did that by just assigning a user (merchant) a specific server, to which they were redirected when they logged in to edit their store, view stats, or retrieve orders.

The problem was completely parallelizable; stores didn't interact.

I think it's even less of a problem now a days. With cheap 64bit hardware, you can have a humongous virtual memory space, so you can go quite far with each hardware you add.

Makes such perfect sense too.