PostgreSQL Configuration for Humans
postgresqlco.nf
postgresqlco.nf
https://ssl-config.mozilla.org/#server=postgresql&version=12...
It's tempting to think it may be a good starting point. Unfortunately, it might as well be a bad starting point: form-based configurations may be totally counter-productive. For instance, max_connections is a parameter that may cause outages or very bad performance if not properly configured. And its value depends, if anything, on having or not a connection pool. work_mem heavily depends on the types of queries that you run. Plus I disagree with some of the other choices made by pgtune's default behaviour.
All in all, I'd recommend to do a deeper study of the parameters, or seek some expert help, specially for production environments.
Disclaimer: I'm part of the team behind postgresql.conf
Unfortunately the site is unavailable:
I never wrote it's a brainless goto conf generator.
>max_connections is a parameter that may cause outages or very bad performance if not properly configured.
That's why you can define the "Number of Connections"
My point here is that some of the suggestions provided, if taken by a non expert user, may do more harm than good. And the reason is that there are many factors that need to be considered for most of the parameters. Something it's hard to capture from a form-based configuration generation tool.
That's why my recommendation of not using form-based configuration generation tools. They can be counter-productive.
I know there are some ways that reduce the downtime, like hard linking the data between the clusters instead of version, or using logical replication between the clusters and switching to the new version cluster. The problem is that since I am not a full time DBA (and my organisation doesn't have one) I don't trust myself with these techniques and rely to the trusted pg_upgrade method that will leave the old cluster as it was with all its data intact in case anything went wrong!
The only good thing is that postgresql supports each major version for a lot of time (5 years) so such updates don't need to be very frequent :)
I agree upgrades are still a pain point, but pg_upgrade running time is not proportional to the size of the data.
You need to have a proper backup of the database anyway, so I don't really see the risk in using the --link mode (which makes the downtime pretty much independent of the size of the actual data).
Also I'd really rather avoid restoring the data from a backup unless there's a real disaster that happened.
Thought you might find this useful, as no one should be considering a Postgres backup as a downtime requiring process!
https://blog.dbi-services.com/what-is-a-database-backup-back...
Shame on me for not looking at the domain.
It is a conference.
Which are the most important knobs to twist? Which ones should you never, never touch without 20 years' experience and a very peculiar requirement?
Give me something to read, please. "In one ear and out the other" is a saying for a very good reason.
There's an upcoming new version that will come with something similar to what you say: some "guide" on which parameters to tune, classified by categories, and with varying level of "expertise" (basic, medium, advanced). With specific guidance for those set of parameters.
Would that be useful to you?
This is important feedback for us. We will fix this landing page soon to include a search box, like search engines landing page, just for parameters.
There's also a significant new version coming soon, with capabilities to manage postgresql.conf configurations. Stay tuned. Hope that would become clearer.
Disclaimer: I'm part of the team behind postgresql.conf
Hit us up to see if we can do the same for you: https://ottertune.com/demo.html
Highly recommend it.
IMHO doing the hashing and password comparison in application code has benefits in both shifting the compute workload to horizontally scalable non-db workers and keeping the plaintext off the wire/queries.