You are absolutely right Andres. I tested restore by keeping synchronous_commit and autovacuum ON. No performance degradation. On Tue, May 7, 2019 at 9:14 PM Andres Freund <andres@xxxxxxxxxxx> wrote: > > Hi, > > On 2019-05-07 18:04:04 +0530, Siddharth Karandikar wrote: > > But before reaching there I had done following config changes > > - maintenance_work_mem 1GB > > - autovacuum off > > unlikely to matter for a bulk load. > > > > - synchronous_commit off > > unlikely to matter for a bulk load. > > > > - shared_buffers=4GB > > > > And finally I explicitly set wal_buffers = 128MB becasue with -1 it > > was calculating it to be just 16MB. > > > > With above configurations, pg9.5 dump of large tables could be > > restored on pg10 in just `94m31.869s` - this is huge improvement > > compared to 831m25.539s that I was getting before. > > Yay!! > > > > Still not sure why it just worked on to restore on pg9.5 without any > > of these config changes. > > Ah, I bet the wal_level is different in the two clusters. The default in > 9.5 wasn't usable for replication and hot backups. But the newer one > also has increased overhead in precisely one case - when loading data > into a new table. Try setting wal_level = minimal in 10. > > > > And also not sure if this is the best performance that one can get. > > Will making wal_buffers 512MB improve it? > > Extremely unlikely. > > Regards, > > Andres
