Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eYtPe-0004tl-Pp for pgsql-performance@arkaria.postgresql.org; Tue, 09 Jan 2018 12:53:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eYtPe-0006t2-65 for pgsql-performance@arkaria.postgresql.org; Tue, 09 Jan 2018 12:53:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eYtPd-0006st-RX for pgsql-performance@lists.postgresql.org; Tue, 09 Jan 2018 12:53:54 +0000 Received: from forward100p.mail.yandex.net ([2a02:6b8:0:1472:2741:0:8b7:100]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eYtPa-0001Rk-O7 for pgsql-performance@lists.postgresql.org; Tue, 09 Jan 2018 12:53:53 +0000 Received: from mxback14j.mail.yandex.net (mxback14j.mail.yandex.net [IPv6:2a02:6b8:0:1619::90]) by forward100p.mail.yandex.net (Yandex) with ESMTP id E555B51027E0; Tue, 9 Jan 2018 15:53:45 +0300 (MSK) Received: from web26o.yandex.ru (web26o.yandex.ru [2a02:6b8:0:1a2d::5:126]) by mxback14j.mail.yandex.net (nwsmtp/Yandex) with ESMTP id ULJWq00vhF-riCeqkVs; Tue, 09 Jan 2018 15:53:44 +0300 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zsrv.org; s=mail; t=1515502424; bh=dYkxjxWYCy9vIr+pHuOyS8/OVjYyGJKQ+YHn8Uu+Lvc=; h=From:To:In-Reply-To:References:Subject:Message-Id:Date; b=f/z3pyoS/HzBIhgL7ICVhn6aabSONxtrKrIeIlln7xEUE56uQCxHrHTJT7FnyNmEV Wb6fh2vE8z/myAvKBrUgTqbD0P18j1xQAGsS/h72bOUJwJdZKXcOjMZaSZcWkSNof8 PPeuR4xW9GSxs20hBQhE4A/jLAQa6feDjv7zlMcQ= Authentication-Results: mxback14j.mail.yandex.net; dkim=pass header.i=@zsrv.org Received: by web26o.yandex.ru with HTTP; Tue, 09 Jan 2018 15:53:44 +0300 From: Sergei Kornilov To: Timokhin Maxim , Tomas Vondra , "pgsql-performance@lists.postgresql.org" In-Reply-To: <4917721515500328@web47j.yandex.ru> References: <145941513961163@web46o.yandex.ru> <59776cbc-bd4a-7ce9-133c-be5665075429@2ndquadrant.com> <4917721515500328@web47j.yandex.ru> Subject: Re: Updating a large table MIME-Version: 1.0 Message-Id: <1037971515502424@web26o.yandex.ru> X-Mailer: Yamail [ http://yandex.ru ] 5.0 Date: Tue, 09 Jan 2018 15:53:44 +0300 Content-Transfer-Encoding: 7bit Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hello > 1. ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT FALSE; this is wrong. To avoid large table lock you need DEFAULT NULL: ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT NULL; Default null changes only system catalog, default with any non-null value will rewrite all rows. After adding column you can set default value - it applied only for future inserts: ALTER TABLE clusters ALTER COLUMN "is_paid" SET DEFAULT FALSE; And then you can update all old rows in table by small chunks. Finally, when here is no NULL values you can set not null: ALTER TABLE clusters ALTER COLUMN "is_paid" SET NOT NULL; But unfortunately this locks table for some time - smaller what rewrite time, but time of full seqscan. I hope my patch [1] will be merged and not null can be set in future by temporary adding check constraint (not valid, then validate) - which not require large table lock [1] https://www.postgresql.org/message-id/flat/81911511895540@web58j.yandex.ru#81911511895540@web58j.yandex.ru Regards, Sergei