Re: Disk buffering of resultsets

On 09/21/2014 11:24 AM, Lussier, Denis wrote:
> This does seem very worthwhile. Can someone please sketch out a
> mini-design and see if it makes sense to the pgjdbc core? I'd be
> willing to hack some code, but, I'd want the design to be pre-vetted.
>
> Here's my first quick strawman:
>
> 1.) Get the TEMP directory (may be OS specific).

Actually, on second thought, if we're going to do this we'd be silly to
restrict it to spilling to disk.

What we should have is the ability to flush a resultset to non-memory
storage that provides a given interface when it exceeds a given size.

That non-memory storage might be disk, it might be
Redis/memcached/EHCache/BigCache/BigMemory/GemFire .../ whatever. In
fact, it's more likely to be one of those than it is to be a simple
on-disk cache for people who care about performance and have big result
sets.

All we need from a resultset storage layer is the ability to:

* Register a new result set with the storage

* Append a tuple to the storage

* Fetch a tuple from the storage

* Reliably destroy all storage associated with a resultset when the
resultset is closed, the JVM exits, or the JVM/host crash. For crash,
this cleanup might be a clean sweep.

* Copy an existing, possibly incomplete result set to a new storage
location (e.g. copy an in-memory resultset to disk).

* ... and do this in a manner that makes the storage location
unpredictable and minimises risk of attacks that aren't already possible
using reflection against in-memory result sets.

An API like that is a good fit for K/V stores like Redis, as well as for
Memcached, and for disk storage.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services