Re: Is it possible to speed up addition of "not null"?

On Thu, Feb 02, 2012 at 07:26:15AM -0500, Andrew Sullivan wrote:
> > I need to add not null on one of the columns, but it seems to require
> > full table scan.
> Of course it does. If you want a constraint added to the table, the
> first thing it ought to do is check that all your data actually
> matches the constraint. If not, your constraint doesn't work.

Sure. But at least theoretically, it could use index - for example, if
I had index "where column is null".

> Not faster than a table scan, no. How fast do you want, though? It
> doesn't sound like an unreasonably large table. Have you done any
> tuning? Do you have adequate hardware?

oh yes. very much so.

But this should be nearly instantenous. This machine is very busy. In
the low-traffic moments we have ~ 5k transactions per second.

> Maybe faster would be to create a new table with the schema you want,
> and then use COPY to pull the data out of the old table and into the
> new table. (It sounds like what you really want is a primary key,
> however, and that's going to be faster if you build the unique index
> after the data's all loaded.

This table is concurrently used. Taking it offline is not an option.

Of course, I could:
1. add triggers to log changes
2. create side table with proper schema
3. copy data to side table
4. apply changes
5. swap tables

but this seems like overly complex thing, while simple index
theoretically could solve the problem.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/