
Upgrading/restoring a large PostgreSQL database - a_bonobo
https://rausch.io/posts/2018-02-27-opensnp-db-migration.html
======
ggm
The effect is huge, but tempered by filesystem cache effects. I was trying to
clone the OpenMAP postgres DB, and crawled until I turned this off. But..
subsequently my ZFS arc cache filled up and the Kernel crawled, due to
thrashing trying to both update the DB on disk, and file buffer cache. Days.

Also I forgot to turn things back on afterward. De-tuning your PG to make
things load fast, you have to re-tune afterward!

~~~
anarazel
IMO tuning and indexing / constraints are two different things.

~~~
ggm
Yes, they are. but you do both, right? if the dataset is big enough, anyway.

de-index, add, re-index. but if you have enough, do more. and don't forget
your underlying filesystem semantics will influence things, as the DB writes
data to disk.

