Who wants "in-memory" mode?

If you sign up for the Panorama beta, you can find the documentation for the new "in-memory mode" feature in the Help:

   SQL Anywhere Server
     Database Administration
       Starting and Connecting to Your Database
         The database server
           Database server options
             -im server option

What's the point of in-memory mode? Speed, at the cost of durability.

This isn't a "database file mode", it's a "server operation mode" which means you create and load a database file in "SQL Anywhere Classic" mode and then run it in one of the two in-memory modes: Never Write or Checkpoint Only.

And before anyone gets their knickers in a knot, Panorama isn't introducing any kind of cheesy non-transactional data store like MySQL.

SQL Anywhere is still a fully-relational database management system, fully transactional, almost fully ACId even when running in-memory mode.

ACId means Atomic, Consistent, Isolated and durable, the Four Horsemen Of The Transaction. Durability means a COMMIT is permanent. A little "d" is used here because the Checkpoint Only flavor of in-memory mode preserves all changes up to the last checkpoint, but doesn't allow forward recovery beyond that point. And no recovery at all for the Never Write flavor... the name kinda gives that away. More on this later...

Oh, yeah, it's the same engine, not some bogus Lite version with all sorts of syntactical and semantic differences. It is, however, a "separately licensed component" like high availability.

Who wants it?

What I'd like to know is, who wants "in-memory" mode? I think I do, for some things. Maybe for Foxhound which is a classic "high throughput" application doing huge numbers of inserts and deletes.

The Panorama Help talks about some of the reasons you might want to use in-memory mode, I won't repeat them here. What I will say is this, "Never Write" doesn't mean "Read Only", it just means if you want to save your changes to the disk you're gonna have to find another way; UNLOAD TABLE is one suggestion.

Here's how I see the in-memory modes stacking up against one another, and against Classic mode:

                   -im nw  -im c
                   Never   Checkpoint
                   Write   Only        Classic
                   ======  ==========  =======
Transactional       yes     yes         yes
.db file updated    no      yes         yes
Transaction log     no      no          yes
Temporary file      no      no          yes
Checkpoint log      no      yes         yes
Checkpoints         no      yes         yes
Dirty page flushes  no      yes(1)      yes
Unlimited growth    no      yes(2)      yes
Recoverable         no      yes(3)      yes

(1) Dirty pages are flushed only on checkpoint.

(2) Without a temporary file, checkpoints are the only way to keep the cache from being exhausted.

(3) After a crash, a restart will perform the usual automatic recovery to the last good checkpoint, but without a transaction log, it is not possible to run a forward recovery from the last good checkpoint to the last committed transaction.

What's missing from the table above is a column for "UltraLite"... will in-memory mode give UltraLite some competition?