Upgrading/restoring a large PostgreSQL database

rausch.io

2 pointsa_bonobo3 comments

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!

IMO tuning and indexing / constraints are two different things.

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.

Reply on news.ycombinator.com