Adding Columns to Big Tables in PostgreSQL

The Problem

I needed to add a boolean column to a relatively large table. The standard advice is simple: avoid anything that locks the table. A naive ALTER TABLE with a default value on a big table does exactly that.

The column was a simple boolean flag. My first instinct was to make it nullable. A nullable column is just a metadata change — no rewrite, just a brief ACCESS EXCLUSIVE lock, done in milliseconds. But nullable means three possible states: true, false, and null. In Go, a plain bool will fail on a null scan. You have to use either a pointer (*bool) with nil checks, or a wrapper like sql.NullBool. Neither is clean.

But I also didn't want to use NOT NULL DEFAULT false and lock the table. So I ran an experiment. The results were not what I expected.

The Experiment

I tested both approaches on a table with 60 million records. Here's roughly what I ran:

-- nullable column (expected: fast, metadata-only)
ALTER TABLE users ADD COLUMN phone TEXT;

-- non-null column with a default (expected: slow, full table rewrite)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

Both finished in milliseconds, very close to each other.

I expected a full rewrite on 60 million rows to take a long time. Clearly something was off.

What PostgreSQL Actually Does

Since PostgreSQL 11, adding a non-null column with a stable default no longer requires a table rewrite. Both cases — nullable and NOT NULL with a default — are handled the same way: a fast metadata update.

The trick is in pg_attribute, a system table that stores information about every column. PostgreSQL 11 added two new fields to it:

• atthasmissing — marks that this column was added after some rows already exist
• attmissingval — the default value to use for those old rows

When you run ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT true, PostgreSQL doesn't touch any existing rows. It just writes atthasmissing = true and attmissingval = {true} into pg_attribute. Done.

When PostgreSQL reads a row that was written before is_active existed, it checks pg_attribute. It sees atthasmissing = true, reads the default from attmissingval, and returns it. The old rows are never touched.

If a row is updated later, PostgreSQL writes the real value into it at that point. From then on, it no longer needs attmissingval for that row.

See It Yourself

Let's walk through this on a real table. Create a simple users table and fill it with 60 million rows:

CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    name  TEXT NOT NULL
);

INSERT INTO users (name)
SELECT 'user_' || i
FROM generate_series(1, 60000000) i;

Now add both columns and time them:

\timing on

-- nullable column
ALTER TABLE users ADD COLUMN phone TEXT;
-- Time: 29.901 ms

-- non-null with a stable default
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
-- Time: 6.901 ms

Check what PostgreSQL stored:

SELECT attname, atthasmissing, attmissingval
FROM   pg_attribute
WHERE  attrelid = 'users'::regclass
  AND  attnum > 0
  AND  NOT attisdropped;

  attname  | atthasmissing | attmissingval
-----------+---------------+---------------
 id        | f             |
 name      | f             |
 phone     | f             |
 is_active | t             | {t}

is_active has atthasmissing = true and attmissingval = {t}. No rows were touched.

The Exception: Volatile Defaults

This only works when PostgreSQL can store a single fixed value. If the default is a volatile function — one that returns a different value every call — there's no single value to store. PostgreSQL falls back to a full table rewrite:

-- this forces a full rewrite on every existing row
ALTER TABLE users ADD COLUMN score FLOAT NOT NULL DEFAULT random();
-- Time: 39453.727 ms  (~39 seconds)

Common volatile functions: random(), clock_timestamp(), gen_random_uuid().

After the rewrite, check pg_attribute again. The atthasmissing flag on is_active is now cleared — PostgreSQL wrote the real values physically while it was already rewriting the table.

SELECT attname, atthasmissing, attmissingval
FROM   pg_attribute
WHERE  attrelid = 'users'::regclass
  AND  attnum > 0
  AND  NOT attisdropped;

  attname  | atthasmissing | attmissingval
-----------+---------------+---------------
 id        | f             |
 name      | f             |
 phone     | f             |
 is_active | f             |             ← cleared, now physically stored
 score     | f             |             ← volatile, was never set