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 finally convert the unlogged table to logged table.
In addition to that there are several ways to increase data writing performance for example the following configuration settings have a impact on write performance: synchronous_commit, commit_delay, max_wal_size, wal_buffers and maintenance_work_mem.
Regards
On Friday, December 22, 2017, 6:59:43 PM GMT+1, Timokhin Maxim <ncx2@xxxxxxxxxx> wrote:
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 = DEFAULT where ctime <= now() - interval '720h' AND is_paid != 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 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 me 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 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 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
--
Timokhin 'maf' Maxim
