Adding columns is super easy (and performant) in postgres 11

Generally databases are always good at adding the column metadata into the data dictionary – normally the performance issues are around adding NOT NULL or a default value because a back population needs to happen.

This can be problamatic when tables have billions of rows and an exclusive lock needs to be taken out. Luckily, in Postgres 11 some underlying changes have been made to allow this previously difficult task to happen instantly.

Someone has done a great job of explaining the difference for version 11 here – https://brandur.org/postgres-default

My testing showed the following results on a 400 million row table (200GB):

alter table stock_performance_by_version add column mos int;
--81 msec
alter table stock_performance_by_version add column mosdefaultint int default 0;
--87 msec
alter table stock_performance_by_version add column mosdefaultnotnull int NOT NULL default 0;
--104 msec
alter table stock_performance_by_version add column mosdefaultstring varchar NOT NULL default 'hello';
--126 msec

If a table column is added with NOT NULL and a default value, the value is now stored in pg_attribute and referenced back when the original data is selected from the table. To the end user, it just looks like they are selected columns from tables, but behind the scenes, the db engine is joining the “missing attributes” back to the table.

Screen Shot 2019-11-13 at 11.50.59

underying meta data for the above can be seen in pg_attribute

SELECT c.oid,
  n.nspname,
  c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT a.attname,
    pg_catalog.format_type(a.atttypid, a.atttypmod),
    (
      SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
      FROM pg_catalog.pg_attrdef d
      WHERE d.adrelid = a.attrelid
        AND d.adnum = a.attnum
        AND a.atthasdef
    ),
    a.attnotnull, a.attnum,
    (
      SELECT c.collname
      FROM
        pg_catalog.pg_collation c,
        pg_catalog.pg_type t
      WHERE c.oid = a.attcollation
        AND t.oid = a.atttypid
        AND a.attcollation <> t.typcollation
    ) AS attcollation,
	a.atthasmissing,
	a.attmissingval
  FROM pg_catalog.pg_attribute a
  WHERE a.attrelid = c.oid
    AND a.attnum > 0
	AND a.attmissingval is not null --TO ONLY SEE MISSING ATTRIBUTES
    AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(public)$'  -- YOUR SCHEMA HERE
AND relname = 'stock_performance_by_version'
AND pg_catalog.pg_table_is_visible(c.oid);

Screen Shot 2019-11-13 at 11.56.39

Great Feature!