A Near Miss: How I Froze My Website by Adding a Generated Column

This is a story of a near-miss where I didn’t know what I was doing and accidentally took down a website. Luckily, it was the staging site, so at least I wasn’t playing with live ammo. Hopefully, you can learn from my mistake.

I was doing what seemed like a simple task in Postgres: creating a generated column. I wanted to concatenate a person’s first, middle, and last names into one string and normalize it by removing extra whitespace, stripping accents (so “José” becomes “Jose”), and making everything lowercase.

It seemed simple enough… until I ran the migration. For a table with half a million records, the website froze.

The Normalization Function

Here’s the Postgres function I wrote to do the normalizing:

‍

CREATE OR REPLACE FUNCTION normalize(VARIADIC inputs text[]) RETURNS text AS $$
  DECLARE normalized text;
  BEGIN 
    normalized := array_to_string(inputs, ' ');
    normalized := trim(normalized);
    normalized := unaccent(normalized);
    normalized := lower(normalized);
    normalized := regexp_replace(normalized, '-', ' ', 'g');
    normalized := regexp_replace(normalized, '\s+', ' ', 'g'); 
    normalized := regexp_replace(normalized, '[^a-z0-9 ]+', '', 'g');
    RETURN normalized;
  END;
$$ LANGUAGE plpgsql IMMUTABLE;

‍

The “Bad” Way: Generated Columns

My first instinct was to use a generated column. In Rails, I wrote a migration like this:

‍

class AddNormalizedFullNameGeneratedColumn < ActiveRecord::Migration[7.0]
  def up
    execute <<~SQL
      ALTER TABLE people ADD COLUMN normalized_full_name text
      GENERATED ALWAYS AS ( normalize(first_name, middle_name, last_name) )
      STORED;
    SQL
  end
end

‍

That seemed elegant… until I ran it.

Why It Froze the Website

So what happened?

Postgres stores each row as a “tuple”, an immutable array of values. When you add a generated column, Postgres has to rewrite the entire table. It reads every old row, computes the generated column value, and writes a brand-new row. For a table with half a million rows, that’s a huge operation.

This also means the table gets locked against writes during the operation. And since my people table is used on nearly every page of the site, the staging website froze solid.

That was a lesson. In a zero-downtime environment, even 60 seconds of downtime is unacceptable.

The Safer Solution: Nullable Column + Trigger

Instead, I took a safer approach:

• Add a nullable column for the normalized value.
• Create a trigger to keep it updated when names change.
• Backfill existing rows in small batches.

Step 1 – Add the Nullable Column

Adding a nullable column is fast because Postgres doesn’t physically change any existing rows immediately.

‍

class AddNormalizedFullNameColumn < ActiveRecord::Migration[7.0]
  def change
    add_column :people, :normalized_full_name, :string
  end
end

‍

Step 2 – Create the Trigger

Next, I created a trigger to keep the column updated automatically:

‍

class AddNormalizedFullNameTrigger < ActiveRecord::Migration[7.0]
  def up
    execute <<~SQL
      CREATE OR REPLACE FUNCTION update_normalized_full_name() RETURNS trigger AS $$
        BEGIN
          IF TG_OP = 'INSERT'
            OR NEW.first_name IS DISTINCT FROM OLD.first_name
            OR NEW.middle_name IS DISTINCT FROM OLD.middle_name
            OR NEW.last_name IS DISTINCT FROM OLD.last_name
          THEN
            NEW.normalized_full_name := normalize(
              NEW.first_name,
              NEW.middle_name,
              NEW.last_name
            );
          END IF;

          RETURN NEW;
        END;
      $$ LANGUAGE plpgsql;
    SQL

    execute <<~SQL
      CREATE TRIGGER trigger_update_normalized_full_name
      BEFORE INSERT OR UPDATE ON people
      FOR EACH ROW EXECUTE FUNCTION update_normalized_full_name();
    SQL
  end

  def down
    execute <<~SQL
DROP TRIGGER IF EXISTS
trigger_update_normalized_full_name
ON people
    SQL

    execute "DROP FUNCTION IF EXISTS update_normalized_full_name();"
  end
end

‍

Step 3 – Backfill Existing Data

Of course, all existing rows still had a null normalized_full_name. I backfilled the data in small batches so as not to lock the table for too long:

‍

Person
  .where(normalized_full_name: nil)
  .find_in_batches(batch_size: 1_000) do |batch|
    ids = batch.map(&:id)
    ActiveRecord::Base.connection.execute <<~SQL
      UPDATE people
      SET normalized_full_name = normalize(
        first_name,
        middle_name,
        last_name
      )
      WHERE id IN (#{ids.join(', ')})
    SQL
  end
end

‍

Why This Works

You might be wondering why adding a nullable column doesn’t also freeze the table. Here’s the magic:

• When you add a nullable column, Postgres does not rewrite old rows.
• Instead, any missing value in an old tuple simply reads as NULL.
• Only rows you actually update or insert get a new physical row version.

So Postgres is smart enough to leave old data untouched until you explicitly change it.

Final Thoughts

So now you know!  Software sometimes feels like performing open-heart surgery on a patient who’s running a marathon.  Remember: always test your migrations on staging first, understand how Postgres stores data under the hood, and be cautious of seemingly “simple” changes like generated columns.

‍