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.
