Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eSi2T-0003ye-VE for pgsql-performance@arkaria.postgresql.org; Sat, 23 Dec 2017 11:32:26 +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 1eSi2S-0006Zk-Vy for pgsql-performance@arkaria.postgresql.org; Sat, 23 Dec 2017 11:32:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eSi2S-0006Za-Fs for pgsql-performance@lists.postgresql.org; Sat, 23 Dec 2017 11:32:24 +0000 Received: from sonic315-20.consmr.mail.ne1.yahoo.com ([66.163.190.146]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eSi2K-0006We-ME for pgsql-performance@lists.postgresql.org; Sat, 23 Dec 2017 11:32:22 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1514028734; bh=yYUL9m4X+RGSd5PLpJb4vYtDz8NMabv0h4kvuD1OhiA=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject; b=puO6RNeNeg+WAy+CVHG1js8lLTxcMnODd4aas4MxvNnXJneqj66KZ5Mjy2Rk5+EB7w4FnFR1OXBayr9hD0TdftwSS/Eh+J+ZiQkv2w1+cYpbn6jxynzweBHOZ4ruPtDZRkyIN/MZHWDISal7vfqDOOEjPVfu3/lh/rW2Xs3ofHAQWmbfQdr81BTrDnPiAgLFrkhHPENH84IjnpIGqHuJR9hQCmYbn3F3g0KeUS0MAzbgTrtwJyZ5dNQeIdgO1agClNUrdm9U3f14e6fCp+hfEKvkikTrbWswf7up0ZA/OFMZJEV/tat37wZHVDZUg61qIKUFh/TZNCqDCtct8hNz7Q== X-YMail-OSG: zueVW1QVM1nIyhQmLvSadRSYOOIvwqzuBk.zjG8qlYheQYgLYRnLI_.hF3clX7K 4mmxOieIUmihVjhV7xvSKO8GoACJNcfMU3T_WNAjvv2kwnoayFlq3tGBfMaYXkvJhj8wlCYQlyUo aCFMUqEOd5ETy4UGzqdDnbOlJ4_Kymp63nlIlXiCs8Y9Z3ebJewE3VQG9nyO_87m9TxtBVMtmhM5 7NlyKjHz0pJHPDwj3MwVF6UUDVGdeJOccddkTeVT3qBGvJwQYmkV9lbp0LZrGMxphsZPZPDDPPDM LgxqE_TBlx25cdpU0L.p9oDg7A9BpPo9BkPCnCXvsHpfHWpGNewmsLsuf3r9xB0PtjhfhTw5z8Fp P71UlhdR_0JcalYgAc5WmNbNPwRBsPUODbbmO5XtROKObmILKLIXJKQwnbSFJQQYPF.EX3e.pIj_ YlEMZu2n1_P3hd65x1UYq_hG577CQe.Ig7a9Z4nXl53M5av9EqHzB.PSapJc9Nr88e7P0TD103gH 7IdWjcNs1VwUOvPTMnZgSZ6TB3MjiLudPvw-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic315.consmr.mail.ne1.yahoo.com with HTTP; Sat, 23 Dec 2017 11:32:14 +0000 Date: Sat, 23 Dec 2017 11:31:28 +0000 (UTC) From: salah jubeh To: pgsql-performance@lists.postgresql.org, Timokhin Maxim Message-ID: <1467235197.3635394.1514028688664@mail.yahoo.com> In-Reply-To: <145941513961163@web46o.yandex.ru> References: <145941513961163@web46o.yandex.ru> Subject: Re: Updating a large table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3635393_903865564.1514028688661" X-Mailer: WebService/1.1.11051 YMailNorrin Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36 Content-Length: 6661 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3635393_903865564.1514028688661 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello, Does the tale have foreign keys, if not you could create another table may = be unlogged and then do the changes you want via INSERT ... SELECT; and fin= ally convert the unlogged table to logged table.=C2=A0 In addition to that there are several ways to increase data writing perform= ance for example the following configuration settings have a impact on writ= e performance: synchronous_commit, commit_delay, max_wal_size, wal_buffers = and maintenance_work_mem.=C2=A0 Regards=C2=A0 On Friday, December 22, 2017, 6:59:43 PM GMT+1, Timokhin Maxim wrote: =20 =20 =20 Hello! We have a large table 11GB ( about 37 million records ) and we need = to alter a table - add a new column with default values is false. Also 'NOT= NULL' is required. So, first I've done: ALTER TABLE clusters ALTER COLUMN "is_paid"; after that: UPDATE clusters SET is_paid =3D DEFAULT where ctime <=3D now() - interval '= 720h' AND is_paid !=3D FALSE; Everything went ok. Then I tried to run it again for an interval of 1 years= . And I got that no one can't see - the was no available space on a disk. T= he reason was WAL-files ate everything. Master-server couldn't send some WAL-file to their replicas. Bandwidth wasn= 't enough. Well, I'm searching for a better idea to update the table. Solutions I found. 1. Separate my UPDATE by chunks. 2. Alter a table using a new temporary table, but it's not convenient for m= e because there is a lot of foreign keys and indexes. 3. Hot-update. This is the most interesting case for me. Speaking of HOT-update https://www.dbrnd.com/2016/03/postgresql-the-awesome= -table-fillfactor-to-speedup-update-and-select-statement/ The article says: it might be useful for tables that change often and moreo= ver It would be the best way to increase the speed of UPDATE. So, my questions are will it work for all tuples? It says that - no https:/= /www.dbrnd.com/2016/03/postgresql-alter-table-to-change-fillfactor-value/, = but I could not find a confirmation in official postresql's documentation. Why do I need to launch vacuum after updating? How should I reduce the better fillfactor? What will be with WAL-files it this case? Thank you! PostgreSQL 9.6 --=C2=A0 Timokhin 'maf' Maxim =20 ------=_Part_3635393_903865564.1514028688661 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello,

Does the tale have fo= reign keys, if not you could create another table may be unlogged and then = do the changes you want via INSERT ... SELECT; and finally convert the unlo= gged table to logged table. 

In addition to t= hat there are several ways to increase data writing performance for example= the following configuration settings have a impact on write performance: s= ynchronous_commit, commit_delay, max_wal_size, wal_buffers and maintenance_= work_mem. 

Regards 

=20
=20
On Friday, December 22, 2017, 6:59:43 PM GMT+1, Tim= okhin Maxim <ncx2@yandex.com> wrote:



Hello!= We have a large table 11GB ( about 37 million records ) and we need to alt= er a table - add a new column with default values is false. Also 'NOT NULL'= is required.
So, first I've done:

ALTER TABLE clusters ALTER COLUMN "i= s_paid";

after that:

UPDATE clusters SET is_= paid =3D DEFAULT where ctime <=3D now() - interval '720h' AND is_paid != =3D FALSE;

Everything = went ok. Then I tried to run it again for an interval of 1 years. And I got= that no one can't see - the was no available space on a disk. The reason w= as WAL-files ate everything.
Master-server couldn= 't send some WAL-file to their replicas. Bandwidth wasn't enough.
=

Well, I'm searching for a bette= r idea to update the table.
Solutions I found.
1. Separate my UPDATE by chunks.
2. Alter a table using a new temporary table, but it's not conveni= ent for me because there is a lot of foreign keys and indexes.
3. Hot-update. This is the most interesting case for me.
<= /div>
The article says: it might be useful for tables th= at change often and moreover It would be the best way to increase the speed= of UPDATE.
So, my questions are will it work for= all tuples? It says that - no https:= //www.dbrnd.com/2016/03/postgresql-alter-table-to-change-fillfactor-value/,= but I could not find a confirmation in official postresql's documentat= ion.
Why do I need to launch vacuum after updatin= g?
How should I reduce the better fillfactor?
=
What will be with WAL-files it this case?
<= div dir=3D"ltr">Thank you!



PostgreSQL 9.6

-- 
Timokhin 'maf' Maxim

------=_Part_3635393_903865564.1514028688661--