speeding up ALTER ... SET NOT NULL

From: Ben Chobot <bench(at)silentmedia(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: speeding up ALTER ... SET NOT NULL
Date: 2013-03-11 18:12:33
Message-ID: A09673DF-D000-49CC-A888-5C01360C54CF@silentmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm in an unfortunate position of needing to add a unique, not null index to a very large table with heavy churn. Without having much impact, I can add a NULL column that reads default values from a sequence for new rows, and then do batch updates over time to fill in the old values.... but then I hit a big wall of locking when updating that column to be NOT NULL.

If I were to make a partial index on my new, sequence-feed column where value is null (concurrently, of course), is postgres smart enough to use that index when checking existing values for the ALTER command? Or even any index (I'll have to make a unique one eventually anyway).

Browse pgsql-general by date

  From Date Subject
Next Message Ben Chobot 2013-03-11 19:17:13 Re: Splitting Postgres into Separate Clusters?
Previous Message Jerry Sievers 2013-03-11 18:07:45 Re: restoring to different architecture with WAL