Mastering PostgreSQL Administration
Most of this stuff I knew already, so the notes are short.
- local -- Unix sockets
- Significantly faster than going through host
- host -- TCP/IP, both SSL and non-SSL
- hostssl -- only SSL
- Can delay connection startup by 25-40%
- hostnossl -- never SSL
- You can use template databases to make a standard DB for when you
create new ones. For example, if you want to always have a certain
function or table, put it in
template1. This works with extensions and contrib like
xxx_fsmfiles are freespace map
pg_xlogis the WAL log directory
pg_clogis compressed status log
Config file settings
shared_buffersshould be 25% of total RAM for dedicated DB servers. Don't go over 40-50% or machine will starve. Also, overhead of that many buffers is huge.
- If you can get five minutes of your working set into
shared_buffers, you're golden.
- Going over a couple hundred connections, it's worth it to look at a pooler.
- Heavily-used tables
- Unnecessary indexes
- Additional indexes
- Index usage
- TOAST usage