Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
MySQL vs PostgreSQL Schema changes benchmarks
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment