- Features by release: https://www.postgresql.org/about/featurematrix/
- How caching works: https://madusudanan.com/blog/understanding-postgres-caching-in-depth/
- Database server hardware: https://www.packtpub.com/sites/default/files/0301OS-Chapter-2-Database-Hardware.pdf
- Internals: http://www.interdb.jp/pg
- VACUUM, ANALYZE and REINDEX (CLUSTER).
- Can be automated with PgCron https://github.com/citusdata/pg_cron/
Conventional (disk based)
autovacuumDisable auto-vaacum and perform it daily at night.
Number of planned connections.
Expected memory to be available in the OS and PostgreSQL buffer caches, not an allocation!. Used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not.
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.
25% of system RAM and no more than 8GB.
Alternative?: higher values may perform much better if you can comfortably fit the working set inside shared_buffers leaving a generous amount of memory left over for other purposes. http://rhaas.blogspot.co.at/2012/03/tuning-sharedbuffers-and-walbuffers.html
Intermediante results, sort, hashjoins, materialized views, etc.
4 to 64MB.
Maintenance operations like vacuuming and index creation.
5% of system RAM and no more than 512MB.
No more than 16MB.
Deprecated on 9.5. Now max_wal_size.
Better performance on bulk data loads.
0.9 will decrease the performance impact of checkpointing on a busy system.
Increasing checkpoint_timeout from 5 minutes to a larger value, such as 15 minutes, can reduce the I/O load on your system, especially when using large values for shared_buffers.
random_page_cost and seq_page_cost
An estimate for the relative cost of disk seeks.
2 and 1 (0.1 for both values if the DB completely fits in memory).
- PostgreSQL documentation "Non-Durable Settings" https://www.postgresql.org/docs/9.6/static/non-durability.html
- In-memory configurartion summary: