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