postgresql参数配置杂记

shared_buffers

引用

if you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the memory in your system. If you have less ram you'll have to account more carefully for how much RAM the OS is taking up, closer to 15% is more typical there.

引用

on Windows (and on PostgreSQL versions before 8.1), large values for shared_buffers aren't as effective, and you may find better results keeping it relatively low and using the OS cache more instead. On Windows the useful range is 64MB to 512MB

effective_cache_size

引用

Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount.  HUP is enough  ,shared_buffers 一半

checkpoint_segments

引用

doing one every 48MB will be a serious performance bottleneck.  小于3性能慢
Unless you're running on a very small configuration, you'll almost certainly be better setting this to at least 10, which also allows usefully increasing the completion target.至少10
For more write-heavy systems, values from 32 (checkpoint every 512MB) to 256 (every 4GB) are popular nowadays.稍重的写,32 - 256
Normally the large settings (>64/1GB) are only used for bulk loading. 大负载,用64以上

fsync关闭会使数据不一致
full_page_writes  synchronous_commit只会丢失整个事务,不会不一致