Today at the SOUG Day I did some little demos and one of them was about creating typed tables. In the demo the two tables did not contain any rows and one of the questions was: When these tables contain a million of rows would adding a column be instant as well? Lets do a quick test.
Same setup as in the post referenced above: Two schemas, one type, two tables based on the type:
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
postgres=# create schema a;
CREATE SCHEMA
postgres=# create schema b;
CREATE SCHEMA
postgres=# create type typ1 as ( a int, b text );
CREATE TYPE
postgres=# create table a.t1 of typ1;
CREATE TABLE
postgres=# create table b.t1 of typ1;
CREATE TABLE
postgres=# insert into a.t1
postgres-# select a.*
postgres-# , md5(a::text)
postgres-# from generate_series(1,1000000) a;
INSERT 0 1000000
postgres=# insert into b.t1 select * from a.t1;
INSERT 0 1000000
postgres=#
Both of the tables contain 1’000’000 rows so how much time would a modification of the type take?
postgres=# timing
Timing is on.
postgres=# alter type typ1 add attribute c timestamp cascade;;
ALTER TYPE
Time: 9.338 ms
Time: 0.867 ms
postgres=# d a.t1
Table "a.t1"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
a | integer | | |
b | text | | |
c | timestamp without time zone | | |
Typed table of type: typ1
postgres=# d b.t1
Table "b.t1"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
a | integer | | |
b | text | | |
c | timestamp without time zone | | |
Typed table of type: typ1
Almost instant. Btw: Of course you can also remove an attribute from the type:
postgres=# alter type typ1 drop attribute c cascade;
ALTER TYPE
Time: 14.417 ms
postgres=# d a.t1
Table "a.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | text | | |
Typed table of type: typ1
postgres=# d b.t1
Table "b.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | text | | |
Typed table of type: typ1