Re: Probable faq: need some benchmarks of pgsql vr.s mysql

> It isn't that simple as I described earlier in this thread. InnoDB has
> the insert buffer:
> http://www.google.com/search?hl=en&q=insert+buffer+innodb
>
> InnoDB can do a disk seek for each PK/unique index on a table during
> an insert. It does not do disk seeks for each secondary index.

How does InnoDB make sure that sessions see the buffered inserts if they
should transactionally be visible?

Postgres doesn't have a concept of "primary" vs. "secondary" indexes,
since unlike InnoDB, table rows are not btree-ordered on disk. This
means that, for example, it takes a much larger table before we hit the
"won't fit in memory" limit where the indexes become expensive to
update. It also makes insert buffering much less valuable, since when a
table gets busy we can just start tacking stuff onto the end.

Not that I wouldn't love to have someone working on "fractal indexes" in
Postgres. Maybe I need to find a Postgres-based business model for
Tokutek. ;-)

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com