MySQL vs PostgreSQL Schema changes benchmarks

gistfile1.sql

The basic idea here is to substantiate the claims made by this square post:
http://corner.squareup.com/2011/06/postgresql-data-is-important.html

In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add 
and remove columns and add and remove indexes.  For columns without defaults this is
basically free in PostgreSQL and O(n) in MySQL.  For adding indexes its at best O(n) 
everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
prevent table interaction.

Also, PostgreSQL has _awsome_ documentation (it has real examples!). I always get 
lost in circular links looking at MySQL documentation.

EC2 m1.Small instance (instance-store) running Ubuntu

# POSTGRES 8.4.9

create table words ( word varchar(40) );
create language plpgsql;
CREATE FUNCTION "million" () RETURNS text AS '
DECLARE
BEGIN
  FOR i IN 0..1000000 LOOP
    INSERT INTO words values (''the'');
  END LOOP; RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION "fivemillion" () RETURNS text AS '
DECLARE
BEGIN
  FOR i IN 0..5000000 LOOP
    INSERT INTO words values (''the'');
  END LOOP; RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';

\timing

SELECT million();
Time: 21069.652 ms

ALTER TABLE words ADD COLUMN pos varchar(30);
Time: 1.867 ms

ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
Time: 4311.439 ms

ALTER TABLE words DROP COLUMN pos;
Time: 0.645 ms

ALTER TABLE words DROP COLUMN pos2;
Time: 1.249 ms

CREATE INDEX CONCURRENTLY ON words (word);
Time: 58110.661 ms

CREATE INDEX derp ON words (word);
Time: 56086.085 ms

DROP INDEX derp;
Time: 3.349 ms

# After wiping the table
SELECT fivemillion();
Time: 104633.836 ms

ALTER TABLE words ADD COLUMN pos varchar(31);
Time: 0.651 ms

ALTER TABLE words DROP COLUMN pos;
Time: 0.658 ms

# Occasionally other random ALTERs spiked to about a second, not sure why
ALTER TABLE words DROP COLUMN pos2;
Time: 1011.007 ms

select count(*) from words;
Time: 2536.649 ms

# MYSQL 5.1.41-3ubuntu12.3 (MyISAM)

create table words ( word varchar(40) );
delimiter $$
CREATE PROCEDURE million()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE (i<1000000) DO
        INSERT INTO words values ('the');
        SET i=i+1;
    END WHILE;
END$$
delimiter ;

delimiter $$
CREATE PROCEDURE fivemillion()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE (i<5000000) DO
        INSERT INTO words values ('the');
        SET i=i+1;
    END WHILE;
END$$
delimiter ;

call million();
Query OK, 1 row affected (1 min 17.99 sec)

ALTER TABLE words ADD COLUMN pos varchar(30);
Query OK, 1000000 rows affected (1.72 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
Query OK, 1000000 rows affected (2.04 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

ALTER TABLE words DROP COLUMN pos;
Query OK, 1000000 rows affected (1.70 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

ALTER TABLE words DROP COLUMN pos2;
Query OK, 1000000 rows affected (1.78 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

CREATE INDEX derp ON words (word);
Query OK, 1000000 rows affected (23.40 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

DROP INDEX derp ON words;
Query OK, 1000000 rows affected (1.15 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

# MYSQL 5.1.41-3ubuntu12.3 (InnoDB)
create table words ( word varchar(40) ) ENGINE=InnoDB;

call million();
Query OK, 1 row affected (2 min 35.03 sec)

ALTER TABLE words ADD COLUMN pos varchar(30);
Query OK, 1000000 rows affected (15.93 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
Query OK, 1000000 rows affected (17.30 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

ALTER TABLE words DROP COLUMN pos;
Query OK, 1000000 rows affected (16.70 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

ALTER TABLE words DROP COLUMN pos2;
Query OK, 1000000 rows affected (15.84 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

CREATE INDEX derp ON words (word);
Query OK, 1000000 rows affected (23.28 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

DROP INDEX derp ON words;
Query OK, 1000000 rows affected (15.39 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

# Dropping and recreating empty words table

call fivemillion();
Query OK, 1 row affected (12 min 57.41 sec)

ALTER TABLE words ADD COLUMN pos varchar(30);
Query OK, 5000000 rows affected (1 min 16.76 sec)
Records: 5000000  Duplicates: 0  Warnings: 0

ALTER TABLE words DROP COLUMN pos;
Query OK, 5000000 rows affected (1 min 16.92 sec)
Records: 5000000  Duplicates: 0  Warnings: 0

ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
Time: 23996.939 ms

select count(*) from words;
1 row in set (8.85 sec)