PostgreSQL developer Robert Haas has begun a
series of articles
comparing the architecture of PostgreSQL and MySQL.
"So, all that having been said, what I'd like to talk about in this
post is the way that MySQL and PostgreSQL store tables and indexes on disk.
In PostgreSQL, table data and index data are stored in completely separate
structures.... Under MySQL's InnoDB, the table data and the primary key
index are stored in the same data structure. As I understand it, this is
what Oracle calls an index-organized table. Any additional ('secondary')
indexes refer to the primary key value of the tuple to which they point,
not the physical position, which can change as leaf pages in the primary
key index are split.
to post comments)