4 ms·
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
by rb2k_ 15y ago
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 15y agoLet'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 15y agoYou 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 15y agoI could have turned on timing too (\timing in psql). All I know is it returned within one sec. Oh well, next time, I suppose.