Re:Show HN: Postgresqlco.nf: PostgreSQL Configuration for Hu...
by Scarbutt on Friday January 24, 2020 @03:57PM
(#22142373)
True but many setups can get away with just tuning 5-10 parameters.
Re:Show HN: Postgresqlco.nf: PostgreSQL Configuration for Hu...
by ahachete on Friday January 24, 2020 @06:20PM
(#22143316)
Absolutely. But we found over many years that know which 5-10 (out of the 300) and what values are good is a hard job for most.
Despite that, we end up tuning 30-40 for most of the customer environments we work with. For instance, logging (for appropriate logging or troubleshooting) is like a dozen. Autovacuum takes its fair share too, and if you have a heavy traffic db, it is a must.
Postgres performance with the default configuration could be significantly slower (30-40%, sometimes more than 100%) than a properly tuned configuration. It is quite important and one of the main recommendations and jobs we do on our daily work.
For example, if you leave random_page_cost at the default value and have fast SSDs, it is very likely that the fancy indexes you created may not be used and seq scans may be used instead. No amount of query tuning may fix that.
A classical one is shared_buffers, whose default size is 128MB of RAM. Unless you are running PG on an AWS Lambda ^___^ or a Raspberri Pi, this is typically a very low number.