Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eYwp4-0001xR-2j for pgsql-performance@arkaria.postgresql.org; Tue, 09 Jan 2018 16:32:22 +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 1eYwp3-0004vz-Ib for pgsql-performance@arkaria.postgresql.org; Tue, 09 Jan 2018 16:32:21 +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 1eYwo9-0000SK-Jc for pgsql-performance@lists.postgresql.org; Tue, 09 Jan 2018 16:31:25 +0000 Received: from forward101o.mail.yandex.net ([2a02:6b8:0:1a2d::601]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eYwo5-00066g-58 for pgsql-performance@lists.postgresql.org; Tue, 09 Jan 2018 16:31:24 +0000 Received: from mxback1o.mail.yandex.net (mxback1o.mail.yandex.net [IPv6:2a02:6b8:0:1a2d::1b]) by forward101o.mail.yandex.net (Yandex) with ESMTP id 7299F1341FA2; Tue, 9 Jan 2018 19:31:18 +0300 (MSK) Received: from web34g.yandex.ru (web34g.yandex.ru [2a02:6b8:0:1402::84]) by mxback1o.mail.yandex.net (nwsmtp/Yandex) with ESMTP id vIjNuW9td9-VHXS76Uu; Tue, 09 Jan 2018 19:31:17 +0300 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex.com; s=mail; t=1515515477; bh=tIoxj2zXZElf3uH4lbfc+VuGuNtxiCtWntNdt1FOwRE=; h=From:To:In-Reply-To:References:Subject:Message-Id:Date; b=XKFI0nziPad/WF3zDhKOpUqqcZNomJ47yd1I5G96yLVDwPDltXGITqu0LePYgzZaz doXyASdLmgUGnaAammKy1oXJ+SIUL1RpIL9lja7QFtVDZzdr55OBv1bQM2U5X4HmLE 90OJ9AWC/xOnFaF05y6iS9wK+mBJ2tEPUMaH0qds= Authentication-Results: mxback1o.mail.yandex.net; dkim=pass header.i=@yandex.com Received: by web34g.yandex.ru with HTTP; Tue, 09 Jan 2018 19:31:17 +0300 From: Timokhin Maxim Envelope-From: ncx2@yandex.ru To: Sergei Kornilov , Tomas Vondra , "pgsql-performance@lists.postgresql.org" In-Reply-To: <1037971515502424@web26o.yandex.ru> References: <145941513961163@web46o.yandex.ru> <59776cbc-bd4a-7ce9-133c-be5665075429@2ndquadrant.com> <4917721515500328@web47j.yandex.ru> <1037971515502424@web26o.yandex.ru> Subject: Re: Updating a large table MIME-Version: 1.0 Message-Id: <585321515515477@web34g.yandex.ru> X-Mailer: Yamail [ http://yandex.ru ] 5.0 Date: Tue, 09 Jan 2018 19:31:17 +0300 Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset=utf-8 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hello, Sergey! 09.01.2018, 15:53, "Sergei Kornilov" : > 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: What you wrote are exactly I'm doing. Moreover, I'm checking current metrics to avoid previously problems. > 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 Hope your commit will be merged. It will be realy useful. > > [1] https://www.postgresql.org/message-id/flat/81911511895540@web58j.yandex.ru#81911511895540@web58j.yandex.ru > > Regards, Sergei --  Timokhin 'maf' Maxim