{"id":11313,"date":"2018-06-05T11:53:12","date_gmt":"2018-06-05T09:53:12","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/"},"modified":"2018-06-05T11:53:12","modified_gmt":"2018-06-05T09:53:12","slug":"postgresql-11-instant-add-column-with-a-non-null-default-value","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/","title":{"rendered":"PostgreSQL 11: Instant add column with a non null default value"},"content":{"rendered":"<p>As I am currently preparing my session for the <a href=\"https:\/\/www.pgday.ch\/2018\/#tabs-4\" target=\"_blank\" rel=\"noopener\">Swiss PGDay<\/a> which is about some of the new features for PostgreSQL 11, I though this one is worth a blog post as well. Up to PostgreSQL 10 when you add a column to table which has a non null default value the whole table needed to be rewritten. With PostgreSQL 11 this is not anymore the case and adding a column in such a way is almost instant. Lets check.<\/p>\n<p><!--more--><\/p>\n<p>We start by creating a test table in PostgreSQL 10:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                       version                                                           \n--------------------------------------------------------------------------------------------------------\n PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit\n(1 row)\n\npostgres=# create table test ( a int, b text, c timestamp );\nCREATE TABLE\npostgres=# insert into test (a,b,c) \n           select aa.*, md5(aa::text), now() \n             from generate_series ( 1, 1000000 ) aa;\nINSERT 0 1000000\n<\/pre>\n<p>This gave us 1&#8217;000&#8217;000 rows and what I want to do is to check the amount of sequential scans against the table before and after the alter table.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass;\n seq_scan \n----------\n        0\npostgres=# alter table test add column d text default 'a';\nALTER TABLE\nTime: 1252.188 ms (00:01.252)\npostgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass;\n seq_scan \n----------\n        1\n<\/pre>\n<p>As you can see a sequential scan happened when the alter table was performed and it took more than a second for the alter table to complete. Lets do the same in PostgreSQL 11.<\/p>\n<p>Creating the table:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                                            version                                                            \n-------------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 11devel dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit\n(1 row)\npostgres=# create table test ( a int, b text, c timestamp );\nCREATE TABLE\npostgres=# insert into test (a,b,c) select aa.*, md5(aa::text), now() from generate_series ( 1, 1000000 ) aa ;\nINSERT 0 1000000\n<\/pre>\n<p>Doing the same test again:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass;\n seq_scan \n----------\n        0\n(1 row)\n\npostgres=# alter table test add column d text default 'a';\nALTER TABLE\nTime: 5.064 ms\npostgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass;\n seq_scan \n----------\n        0\n(1 row)\n<\/pre>\n<p>No sequential scan at all and it only took 5 ms for the alter table to complete. This is quite a huge improvement. The question is how does that work in the background? Actually the idea is quite simple. The catalog table <a href=\"https:\/\/www.postgresql.org\/docs\/11\/static\/catalog-pg-attribute.html\" target=\"_blank\" rel=\"noopener\">pg_attribute<\/a> got two new columns called &#8220;attmissingval&#8221; and &#8220;atthasmissing&#8221;:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [19,28]\">\npostgres=# d pg_attribute\n              Table \"pg_catalog.pg_attribute\"\n    Column     |   Type    | Collation | Nullable | Default \n---------------+-----------+-----------+----------+---------\n attrelid      | oid       |           | not null | \n attname       | name      |           | not null | \n atttypid      | oid       |           | not null | \n attstattarget | integer   |           | not null | \n attlen        | smallint  |           | not null | \n attnum        | smallint  |           | not null | \n attndims      | integer   |           | not null | \n attcacheoff   | integer   |           | not null | \n atttypmod     | integer   |           | not null | \n attbyval      | boolean   |           | not null | \n attstorage    | \"char\"    |           | not null | \n attalign      | \"char\"    |           | not null | \n attnotnull    | boolean   |           | not null | \n atthasdef     | boolean   |           | not null | \n atthasmissing | boolean   |           | not null | \n attidentity   | \"char\"    |           | not null | \n attisdropped  | boolean   |           | not null | \n attislocal    | boolean   |           | not null | \n attinhcount   | integer   |           | not null | \n attcollation  | oid       |           | not null | \n attacl        | aclitem[] |           |          | \n attoptions    | text[]    |           |          | \n attfdwoptions | text[]    |           |          | \n attmissingval | anyarray  |           |          | \nIndexes:\n    \"pg_attribute_relid_attnam_index\" UNIQUE, btree (attrelid, attname)\n    \"pg_attribute_relid_attnum_index\" UNIQUE, btree (attrelid, attnum)\n\n<\/pre>\n<p>As soon as a new column with a non null default value is added to a table these columns get populated. We can see that when we check for our current table. The column we added has that set in pg_attribute:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select attmissingval\n                , atthasmissing \n             from pg_attribute \n            where attrelid = 'test'::regclass \n              and attname = 'd';\n\n attmissingval | atthasmissing \n---------------+---------------\n {a}           | t\n\n(1 row)\n<\/pre>\n<p>We know that all the rows in that table should have the new default value but we know also that the table was not rewritten. So whenever you select from the table and a row is missing the default it will be populated from pg_attribute:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select d from test where a = 1;\n d \n---\n a\n<\/pre>\n<p>For new rows the default will be there anyway and as soon as the table is rewritten the flag is cleared:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# vacuum full test;\nVACUUM\npostgres=# select attmissingval\n                , atthasmissing \n             from pg_attribute \n            where attrelid = 'test'::regclass \n              and attname = 'd';\n\n attmissingval | atthasmissing \n---------------+---------------\n               | f\n(1 row)\n<\/pre>\n<p>Nice feature.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As I am currently preparing my session for the Swiss PGDay which is about some of the new features for PostgreSQL 11, I though this one is worth a blog post as well. Up to PostgreSQL 10 when you add a column to table which has a non null default value the whole table needed [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-11313","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 11: Instant add column with a non null default value - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 11: Instant add column with a non null default value\" \/>\n<meta property=\"og:description\" content=\"As I am currently preparing my session for the Swiss PGDay which is about some of the new features for PostgreSQL 11, I though this one is worth a blog post as well. Up to PostgreSQL 10 when you add a column to table which has a non null default value the whole table needed [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-05T09:53:12+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 11: Instant add column with a non null default value\",\"datePublished\":\"2018-06-05T09:53:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/\"},\"wordCount\":326,\"commentCount\":1,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/\",\"name\":\"PostgreSQL 11: Instant add column with a non null default value - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-06-05T09:53:12+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 11: Instant add column with a non null default value\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 11: Instant add column with a non null default value - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 11: Instant add column with a non null default value","og_description":"As I am currently preparing my session for the Swiss PGDay which is about some of the new features for PostgreSQL 11, I though this one is worth a blog post as well. Up to PostgreSQL 10 when you add a column to table which has a non null default value the whole table needed [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/","og_site_name":"dbi Blog","article_published_time":"2018-06-05T09:53:12+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 11: Instant add column with a non null default value","datePublished":"2018-06-05T09:53:12+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/"},"wordCount":326,"commentCount":1,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/","name":"PostgreSQL 11: Instant add column with a non null default value - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-06-05T09:53:12+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 11: Instant add column with a non null default value"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11313"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11313\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11313"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}