Re: Caching in the dynamic database

Richard,

> If you are interfacing to a database from *any* language
> and you do your own caching and the database engine does
> not know about the caching you are doing and has no way
> to invalidate your cache, then you *WILL* lose the ACID
> guarantees and eventually you *WILL* get inconsistent
> results.
>
> You can get away with your own caching if (and I would like
> to say only if):
>  - nobody at all is changing the data, or
>  - you are changing the data and nobody else is looking at it, or
>  - you keep the database engine fully informed of what you are
>    up to and give it the power to invalidate your cache.

You're absolutely right, of course. The fact is that modern web
services use multiple servers and distributed databases to deal with
high volumes of requests and large quantities of data. To achieve
this, they tend to sacrifice consistency somewhat, aiming instead for
eventual consistency.

I don't know if you've tried to navigate the maze of buzzwords and
snake oils associated with the recent NoSQL database movement. One
simple approach to accelerate a "traditional" web application built
around a relational database is to use an in-memory key-value store to
cache the database. Memcached (http://memcached.org/) is a popular
tool for this that I mentioned earlier in this thread. It's a large
(in-memory) hash table distributed over a number of servers which
allows clients to get and set values. It's used as a cache in front of
a persistent database to speed up web services so that the backend
database is not slowed down with many redundant reads - some usage
examples are given in the Wikipedia article
(http://en.wikipedia.org/wiki/Memcached). The memcached servers
automatically discard older bins when they run out of memory, but it's
up to the application how and when to flush dirty values that are out
of sync with the backend. Obviously this means that the system as a
whole has no ACID guarantees whatsoever.

Some systems, such as MemcacheDB, Couchbase Server and Tarantool offer
the same interface as memcached but actually persist the data so that
no other database is needed. And of course there are many other
key-value stores and document-oriented data stores offering various
combinations of performance, durability and consistency, with various
levels of schema sophistication. The article on NoSQL
(http://en.wikipedia.org/wiki/Nosql) lists many and gives a few
categorisation attempts. I'm just mentioning all of this so that you
don't shoot the messenger. :-)

My thinking was simply that it seems wasteful to put an in-memory
database between my SWI-Prolog server(s) and the persistent database,
because Prolog already has its own in-memory database that stores data
"natively". (Dynamic facts can even be persisted using snapshotting
and journalling as in library(persistency)). I take your point,
though, that the dynamic database is oriented towards procedures, not
data.

> What happens with that is that you link the database engine
> as part of your program and you talk to it directly.  The
> cost of a call to the database is pretty light, and the
> huge thing here is that
>
>         THE DATA BASE DOES THE CACHING
>
> so you are not losing any of the benefits of using a
> relational data base.

Agreed, but for web applications where there are multiple web servers
accessing the same database, an embedded database such as SQLite does
not help in this regards. That is, even if the node with the database
caches data in memory, it still has to pass over the network to reach
other nodes, and their spare memory is wasted.

> The very fact that you speak of "read(ing) a row" suggests to
> me that you might not be used to thinking in terms of column-
> oriented databases like InfoBright or Monet or C-Store. [...]

Thanks for the tip!

> I'd like to point out also that the Prolog clause store has always
> been designed for *procedures*; clauses that might do serious
> pattern matching and are likely to have significant bodies.
> It is likely that a specialised storage system for predicates
> containing only ground unit clauses could
>  - take less space
>  - make richer/better indexing straightforward
>  - be cheaper to modify
> I think if you want to cache (views of) relational data inside
> Prolog, _this_ is the place to start, not the existing clause
> store.

All right, so after taking your advice here is a list of options in
order of consideration:

1) Use a (possibly column-oriented) RDBMS through the ODBC layer
and/or one of the existing packs and trust it (and configure it) to
cache data effectively.
2) If that's too slow and data does fit in RAM, use
library(persistency). Also, Raivo Laanemets has made a very
nice-looking document-oriented database for SWI-Prolog called
"docstore" (http://www.swi-prolog.org/pack/list?p=docstore) using
similar persistence techniques.
3) If data is too large for RAM, choose a suitable existing NoSQL
database and create an SWI-Prolog interface for it such that things
"look nice" from the Prolog side.
4) In the unlikely event that even that's not good enough, create a
special-purpose indexed and persisted store for ground facts in C
(rather than (ab)using Prolog's own dynamic database).

Thanks for the help!

Michael