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.
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.
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.
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.
Hopefully that makes it a little clearer.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
