Re: Key features for data warehousing

On Wed, Apr 09, 2003 at 01:38:15PM +0200, Reiter, Oliver wrote:
> 3. I believe the row overhead is 40 bytes (recently reduced to 36 or 32 ?).
> This is WAY too much for fact tables with many small rows. High-end
> storage is pretty expensive, and less rows per page means more I/O.
> I believe Sybase and Informix can do with 4, and Interbase / Firebird
> (using MVCC) with 16 bytes per row.
>
> Could anyone kindly explain why PostgreSQL needs this much space?

See http://developer.postgresql.org/docs/postgres/page.html about two-thirds
down the page. If you can see a way to remove most of those values, I'm sure
the developers would love to hear from you!

> + We need a pointer to the row (4 bytes) - unless the row size is fix.
> + There is the OID - unless deactivated in the table definition.
> (I always wished OIDs were off by default, for better portability.)
> + For MVCC, each row must be associated with a transaction number
> number or version - unless it is not involved in any transaction,
> which
> is usually true for most rows, and after a restart for all. There
> must be
> a way to recognize obsolete versions in VACUUM or after a crash, but
> why isn't a tristate enough (committed/uncommitted/rolled back) ?

The transaction number defines which transactions can currently see this
tuple. This is what the xmin, cmin, xmax, cmax fields are for.

> + Why not hold the "version map" in a system table, only for versions
> that are valid and not involved in any transactions?
> + Why not hold the "version map" in RAM, swapping or explicitly
> paging parts to the disk when necessary?

So that when you crash you can still tell which tuples are valid. Consider:

begin;
update table set ...;
*crash*

After the update you have all the rows updated stored twice and no way to
tell which one is the right one except for the xmin,xmax values and the list
of commited transactions. So, these need to be stored on disk. Preferably
with the tuple so you don't end up with problem of the system table getting
out of sync with the data table when the disk lost one of the updates while
the machine was dying.

Hope this helps,

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington