[rb2k_]

My main problem with schema migrations was that once you reach 100 million records or so, those tend to lock down the DB server and take quite a while

[einhverfr]

Let's see. On Pg:

postgres=CREATE TABLE alter_benchmark(id bigint);

CREATE TABLE

postgres=# explain analyze

postgres-# insert into alter_benchmark (id) select * from generate_series(1, 200000000);

postgres=# create temporary table alter_benchmark(id bigint); CREATE TABLE postgres=# explain analyze insert into alter_benchmark (id) select * from generate_series(1, 200000000); QUERY PLAN

-------------------------------------------------------------------------------- ---------------------------------------------------------

Insert (cost=0.00..12.50 rows=1000 width=4) (actual time=1082180.877..1082180. 877 rows=0 loops=1)

   ->  Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (a

ctual time=87400.737..512954.539 rows=200000000 loops=1) Total runtime: 1086336.466 ms (3 rows)

postgres=# alter table alter_benchmark add test text;

ALTER TABLE

takes insignificant time (less than a second).

I feel so spoiled using PostgreSQL :-D

As I understand it PostgreSQL doesn't rewrite the table to change the column. It might to change the data type of a column. EXPLAIN ANALYZE doesn't work with ALTER TABLE because there is no query plan generated, so I have no idea how quickly the statement actually executed. All I know is it completed in under a second.

[phaylon]

You could try `time psql < alter-statement.sql`. I know, it'd not really be useful as it measures lots of overhead. But if it's fast on that, it's fast during an active session.

[einhverfr]

I could have turned on timing too (\timing in psql). All I know is it returned within one sec. Oh well, next time, I suppose.