Waiting for PostgreSQL 11: Pain free ADD COLUMN with non-NULL defaults
Fast ALTER TABLE ADD COLUMN with a non-NULL default.
On 28th of March, Andrew Dunstan commited the following patch:
commit 16828d5c0273b4fe5f10f42588005f16b415b2d8
Author: Andrew Dunstan <[email protected]>
Date: Wed Mar 28 10:43:52 2018 +1030
Fast ALTER TABLE ADD COLUMN with a non-NULL default
Currently adding a column to a table with a non-NULL default results in
a rewrite of the table. For large tables this can be both expensive and
disruptive. This patch removes the need for the rewrite as long as the
default value is not volatile. The default expression is evaluated at
the time of the ALTER TABLE and the result stored in a new column
(attmissingval) in pg_attribute, and a new column (atthasmissing) is set
to true. Any existing row when fetched will be supplied with the
attmissingval. New rows will have the supplied value or the default and
so will never need the attmissingval.
Any time the table is rewritten all the atthasmissing and attmissingval
settings for the attributes are cleared, as they are no longer needed.
The most visible code change from this is in heap_attisnull, which
acquires a third TupleDesc argument, allowing it to detect a missing
value if there is one. In many cases where it is known that there will
not be any (e.g. catalog relations) NULL can be passed for this
argument.
Andrew Dunstan, heavily modified from an original patch from Serge
Rielau.
Reviewed by Tom Lane, Andres Freund, Tomas Vondra and David Rowley.
Discussion: https://postgr.es/m/[email protected]
It’s not a secret that database lifecycle includes migrations — tasks which change the database schema, such as adding new, changing or removing existing tables, indexes, columns and so on. Some of those migrations sometimes are quite painful, for example, adding a new column with automatic default value — ‘ALTER TABLE … ADD COLUMN column_name data_type DEFAULT value‘. If you do this with one command, it might easily block an access to an altered table. But also, as a result of this ALTER, the table is completely rewritten because all tuples must have default value in the new added column. Take a look at the following example.
# select count(*),pg_relation_filenode('products'),pg_size_pretty(pg_relation_size('products')) from products;
count | pg_relation_filenode | pg_size_pretty
----------+----------------------+----------------
60000001 | 42070 | 5322 MB
# alter table products add column payload int not null default 0; ALTER TABLE Time: 242439,750 ms (04:02,440) # select count(*),pg_relation_filenode('products'),pg_size_pretty(pg_relation_size('products')) from products;
count | pg_relation_filenode | pg_size_pretty
----------+----------------------+----------------
60000001 | 42080 | 5786 MB
In this example, a new integer column has been added with default value. As you can see, file inode has been changed and it means that as a result the table is completely rewritten. But take a closer look at the command’s timing — 4 minutes, this is the time of table’s rewriting. Within this time, access to the table is completely blocked, thus any query will get stuck as it’s trying to access the table. Obviously, it’s not acceptable in many cases.
To avoid a prolonged block DBAs often organize such migrations into three stages. First, adding a new column with no DEFAULT value — this is inexpensive operation. Second, setting a default value which will be used for new inserted or updated rows. Third is updating the existing rows and setting the default value into the added column. It’s important to note that all these steps wouldn’t block access to the table, though, of course, they do take more time than a single heavy ALTER.
The main aim of the new patch is to make DBA’s life easier, and make the addition of the new column with defaults as lightweight as possible. Let’s take a look at the same migration in after-patch Postgres:
# select count(*),pg_relation_filenode('products'),pg_size_pretty(pg_relation_size('products')) from products;
count | pg_relation_filenode | pg_size_pretty
----------+----------------------+----------------
60000001 | 16394 | 5322 MB
# alter table products add column payload int not null default 0;
ALTER TABLE
Time: 65,898 ms
# select count(*),pg_relation_filenode('products'),pg_size_pretty(pg_relation_size('products')) from products;
count | pg_relation_filenode | pg_size_pretty
----------+----------------------+----------------
60000001 | 16394 | 5322 MB
ALTER command executed in 65 milliseconds — is lightning fast compared to the previous example.
Thanks Andrew Dunstan and all who have been involved in the writing of this patch.
