The information principle. (was RE: Which database part 2)

-----Original Message-----
From: Kaarel [mailto:kaarel@future.ee]
Sent: Friday, June 13, 2003 2:17 PM
To: pgsql-advocacy@postgresql.org
Subject: [pgsql-advocacy] Which database part 2

<snip>
I have been reading a little documentation and mail-lists from both
sides. I noticed one interesting thing about MySQL: there are different
table types with different properties. Why doesn't PostgreSQL have
differently oriented/optimized table types? I found particularly
intresting the heap table type which is being stored entirely in memory
not on disk drive.
</snip>

The answer to this question is quite complex, and is based on some
theory.  If you have some time and don't mind some self improvement,
check out http://www.dbdebunk.com/ and read up on the information
principle, and why table types are a violation of that principle.  Here
is the answer in a nutshell:

Artifical structure you give your data based on characteristics imposed
by storage or the database itself is really metadata (oo databases are
an extreme example of this).  In a real relational database, metadata
serves no real purpose beyond mucking up your ability to make flexible
extractions based on the nature of the data itself.

Here are examples of metadata that do not belong in a relational
database:
1. row order.
2. column order.
3. table type.

Most SQL databases, themselves imperfect followers of the relational
model, have dumped fallacy #1 (which you can still find in flat file
servers like foxpro), buy not #2.  The mysql team introduced the third
one, IMO, for no good reason.

Let's take the specific example of the heap table.  Why not let the
database choose which tables or rows should be kept in memory?
Computers have very advanced caching algorithms that interoperate with
the logging, transactions, and other such utilities.  Whereas a heap
table will disappear when the power goes off, a frequently accessed
table in postgres is in memory anyways (thanks to cache) but is still
atomic and follows normal rules.  The point: let the dbms make the
decisions about what goes in memory. What type of table it is has
absolutely nothing to do with your data, which should be your primary
concern.

Regards,
Merlin