Re: postgresql vs mysql

On Wed, 2007-02-21 at 10:12, Jack Orenstein wrote:
> Scott Marlowe wrote:
> > You can't change a table in any way without rewriting the whole thing,
> > resulting in a very long wait and a complete table lock on any alter
> > table action on big tables. Don't forget that if you've got a really
> > big table, you need that much space free on the drive to alter the table
> > for the rewrite that's going to take place.
>
> Forgive a dumb question: What does postgresql do with ALTER TABLE?
> What sort of modifications do not require time proportional to the
> number of rows in the table?

It's an interesting subject, and it's not a dumb question. In
PostgreSQL, indexes live in another file than the table. In MySQL they
are part of the main table file with myisam tables. I don't know what
innodb does in this regard.

The only thing I can think of that rewrites a whole postgresql table
would be reindexing it, or an update without a where clause (or a where
clause that includes every row). Normal operations, like create index,
add column, drop column, etc do not need to rewrite the table and happen
almost instantly.

For instance, on a table with about 30 columns and 100,000 rows, I can
add a column this fast:

alter table brs add column a int;
ALTER TABLE
Time: 57.052 ms

alter table brs rename column b to c;
ALTER TABLE
Time: 33.281 ms

alter table brs drop column c;
ALTER TABLE
Time: 31.065 ms

Of course, mvcc (which both postgresql and innodb use) have other
issues, like doubling the table size if you update every row until the
dead tuples can be reclaimed.