Re: Follow-Up to A Silly Idea for Vertically-Oriented Databases

On Fri, Sep 07, 2007 at 02:58:03PM -0700, Avery Payne wrote:
> In hindsight, I did miss quite a bit in my last post.  Here's a summary
> that might clear it up:
>
> Add a single keyword that specifies that the storage format changes
> slightly.  The keyword should not affect SQL compliancy while still
> extending functionality.  It can be specified as either part of the
> CREATE TABLE statement or part of the tablespace mechanism.
>
> When a table is created with this setting, all columns in a record are
> split vertically into individual, 1-column-wide tables, and each column
> in the table is assigned an OIDs.  Each OID corresponds to one of our
> "1-wide" tables.  An additional control column will be created that is
> only visible to the database and the administrator.  This column stores
> a single logical indicating if the record is allocated or not.  You
> might even be able to create a special bitmap index that is hidden, and
> just use existing bitmap functions in the index code.  In essence, this
> column helps keep all of the other columns in sync when dealing with rows.

OID's aren't the way to link this stuff together. It would make more
sense for there to be one file that stores all the MVCC and other row
overhead, and for that table to store ctids, because that will be the
fastest way to look up the columns.

> When writing data to the table, each individual column will update, but
> the engine invisibly wraps together all of the columns into a single
> transaction.  That is, each row insert is still atomic and behaves like
> it normally would - either the insert succeeds or it doesn't.  Because
> the updates are handled by the engine as many separate tables, no
> special changes are required, and existing storage mechanisms (TOAST)
> continue to function as they always did.  This could be written as a
> super-function of sorts, one that would combine all of the smaller steps
> together and use the existing mechanisms.
>
> Updates are performed in the same manner, with each "column" being
> rolled up into a single invisible mini-transaction for the given record.

The problem is that the idea of rows being a string of bytes within a
page is spread pretty widely throughout the code; I'm pretty sure it
extends far beyond just smgr. At some point stuff becomes just datums,
but I don't know that there's a nice, clean line where that happens. I
think this is probably the biggest obstacle that you're facing.

> Deletes are performed by marking not only the columns as deleted but
> also the control column as having that row available for overwrite.  I'm
> simplifying quite a bit but I think the general idea is understood.
> Yes, a delete will have significant overhead compared to an insert or
> update but this is a known tradeoff that the administrator is willing to
> make, so they can gain faster read speeds - ie. they want an
> OLAP-oriented store, not an OLTP-oriented store.

You do *not* want to try and change how MVCC works at the same time
you're doing this. There *may* be some possibility of changing things
afterwards, but trying to tackle that off the bat is suicide. On top of
that, HOT might well may this kind of optimization pointless.

> The control column would be used to locate records that can be
> overwritten quickly.  When a record is deleted, the control column's
> bitmap was adjusted to indicate that a free space was available.  The
> engine would then co-ordinate as it did above, but it can "cheat" -
> instead of trying to figure things out for each table, the offset to
> write to is already known, so the update proceeds as listed above, other
> than each part of the little mini-transaction writes to the same
> "offset" (ie. each column in the record will have the same "hole", so
> when you go to write the record out, write it to the same "record
> spot").  This is where the control column not only coordinates deletes
> but also inserts that re-use space from deleted records.
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)