Re: On columnar storage

http://vldb.org/pvldb/vol5/p1790_andrewlamb_vldb2012.pdf

In sketch:

There is the concept of a Write-Optimized-Store (WOS) and
Read-optimized-store (ROS), and a TupleMover that moves records from WOS to
ROS (some what like vacuum), and from ROS to WOS for updates. It seems to
me that heap is naturally a WOS, and only vacuuming for a column-backed
heap table would move records from the heap into the column store. Of
course, there would need to be a deeper vacuum when the column store itself
needs to be vacuumed.

When a record in column store needs to be updated, a top-level transaction
moves the record into the heap by marking the row as deleted in the column
store and inserting the record into the heap store. The updates could then
proceed according to the current heap transactional logic.

I am not sure if this makes sense, but it seems plausible and

1/ retains the heap transactional logic code which is very hard to get right
2/ makes column store essentially a storage optimization that users do not
need to be too concerned with; heap is kept small and old data are moved
into column store automatically
3/ no need to keep 20+bytes of visibility info on the rows in column store
4/ instead of column store, this could be a heap (without visibility) store
if you prefer row

I haven't thought about the indexing aspect of this. From a DW angle, I am
more interested in a heap store that is backed by multiple column stores
via partition keys.

Regards,
-cktan

On Mon, Jun 15, 2015 at 12:02 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
wrote:

> On Fri, Jun 12, 2015 at 10:58 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
> wrote:
> >
> > Amit Kapila wrote:
> > > On Fri, Jun 12, 2015 at 4:33 AM, Alvaro Herrera <
> alvherre(at)2ndquadrant(dot)com>
> > > wrote:
> > > > One critical detail is what will be used to identify a heap row when
> > > > talking to a CS implementation. There are two main possibilities:
> > > >
> > > > 1. use CTIDs
> > > > 2. use some logical tuple identifier
> > > >
> > > > Using CTIDs is simpler. One disadvantage is that every UPDATE of a
> row
> > > > needs to let the CS know about the new location of the tuple, so that
> > > > the value is known associated with the new tuple location as well as
> the
> > > > old. This needs to happen even if the value of the column itself is
> not
> > > > changed.
> > >
> > > Isn't this somewhat similar to index segment?
> >
> > Not sure what you mean with "index segment".
>
> The part similar to index segment is reference to heap for visibility
> information and tuple id (TID). Have I misunderstood something?
>
> > > Will the column store obey snapshot model similar to current heap
> tuples,
> > > if so will it derive the transaction information from heap tuple?
> >
> > Yes, visibility will be tied to the heap tuple -- a value is accessed
> > only when its corresponding heap row has already been determined to be
> > visible.
>
> Won't it possible that all columns of a table belong to column-store?
> I think for such a case heap will just be used to store transaction
> information
> (visibility info) for a column store tuple and depending on how the
> column-store is organized, the reference to this information needs to be
> stored in column-store (the same row reference might need to be stored for
> each column value). Also any write operation could lead to much more
> I/O because of updation at 2 different locations (one in column-store and
> other in heap).
>
> > One interesting point that raises from this is about vacuum:
> > when are we able to remove a value from the store?
>
> Yes, that could also be quite tricky to handle, may be one naive way
> could be to make list of all TID's from heap that needs to be expired
> and then search for references of all those TID's in column-store.
>
> I understand your point for re-using the existing transaction
> infrastructure
> for column-store by keeping that information in heap as it is done now,
> but I think that won't be free either.
>
> Another point to consider here is does the column-store needs
> transactional consistency, do other commercial/opensource column-store
> implementation's are transactional consistent and if yes, then can't we
> think of doing it in a way where data could be present both in heap as well
> as in column-store (I understand that it could lead to duplicate data,
> OTOH, such an implementation anyway eliminates the need for indexes,
> so may be worth considering).
>
>
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com
>