
Show HN: Postgresqlco.nf: PostgreSQL Configuration for Humans - ahachete
https://www.ongres.com/blog/postgresqlconf-configuration-for-humans/
======
taftster
I thought this was going to be a configuration generator and/or parser of an
existing configuration file. While the documentation here is excellent (and
the style of the site very nice), when I hit the home page, I am still left
asking "now what?" needing sort through an endless list of alphabetical
configuration parameters.

I think it would be much more interesting if I could load an existing
configuration file and have a tool like this parse my configuration, giving it
the highlighting and hyperlinks to documentation. It could analyze the file
and give recommendations. Or I could specify my desired configuration outcome
(high availability, low latency, multi-user, etc.) and it would create a
starting template for me to work from.

p.s. I also personally dislike/distrust disqus and would not lean on or trust
the comments there. Let alone it not working at all for anyone blocking cross-
site cookies, etc.

[EDIT]

OK, so maybe I didn't read the blog post about "what's coming" which might be
inline with what I just wrote. Specifically (from TFA):

 _> Right now we are working hard on a fully featured application service
where you can have a graphical configuration interface (or UI) with Drag &
Drop of your postgresql.conf files with automatic validation, and a REST API
where you can store and share your custom postgresql.conf configuration files.
You will also be able to download your configurations in several formats, like
the native postgresql.conf, YAML or JSON._

So I guess that's getting closer to what would actually be useful to someone
like me. Wake me up when that option actually exists.

~~~
ahachete
Yes, that is coming. If you want to be woken up when the config file
management is up, I'd recommend you to subscribe to the mailing list and/or
follow on Twitter, you will be promptly informed ;)

Re: Disqus. It's not our favorite service either. We tried with Commento on
other site and the experience was terrible. Data was permanently lost. We
welcome other suggestions, for now Disqus does the job.

~~~
kenniskrag
Do you have a rss feed? I have a lot of projects I follow that way. On twitter
the timeline moves forward and bookmarks are not as convenient. :)

~~~
ahachete
Noted. Good idea ;)

------
oefrha
Semi-OT rant: can we stop these "for humans" qualifiers? AFAIK it started with
requests and has since grown out of control:
[https://github.com/search?q=%22for+humans%22](https://github.com/search?q=%22for+humans%22)
(3234 repos at the moment) It hardly means anything any more and creators of
alternatives that these "for humans" projects are supposed to replace probably
won't be too happy with the implication that their work is somehow not for
humans.

Now, on topic: why link to a blog post rather than the actual site?
[https://postgresqlco.nf/en/doc/param/](https://postgresqlco.nf/en/doc/param/)

Maybe the blog post is linked because you want to highlight the not-yet-
released configurator?

> Right now we are working hard on a fully featured application service where
> you can have a graphical configuration interface (or UI) with Drag & Drop of
> your postgresql.conf files with automatic validation, and a REST API where
> you can store and share your custom postgresql.conf configuration files. You
> will also be able to download your configurations in several formats, like
> the native postgresql.conf, YAML or JSON.

Sounds great but <s>all we get for now is a screenshot</s> (sorry, the
screenshot is for a new feature available now) so it's kind of a bummer...

~~~
tomc1985
Yeah, I'm a human and I don't mind configuration files

Some of these trends are just stupid (I'm so glad the 'made with love' shit
has died down)

~~~
Operyl
I especially love my projects made out of pure hatred for the status-quo,
though!

------
grilledchickenw
This is on a tangent, but I hate the "for humans" trend that Kenneth Reitz
started with `requests`. What, does everyone else write code for porcupines?

~~~
sly010
> What, does everyone else write code for porcupines?

Computers.

------
thom
I clicked through expecting a tool like a la PGTune:

[https://pgtune.leopard.in.ua/#/](https://pgtune.leopard.in.ua/#/)

But this is just a site documenting all the parameters available. with added
extras like StackOverflow links. It's nice enough, but I suspect if Postgres
configuration was voodoo to you before, this isn't going to change that much.

~~~
ahachete
We don't believe much in such static tuning recommendations. Our experience
tells us that they can do as much good as bad.

It is much more important to understand and learn a bit about how to tune
them. You need to understand the workload, the usage pattern, to do a proper
tuning.

This site is a first step into this direction: provide guidance, centralize
the available documentation, provide general recommendations. Other steps will
follow suit, all focused on helping Postgres users tune the configuration
better.

But as of today, we know a lot of people using this site in their daily work,
as a very convenient mechanism to check information you need to have handy
when tuning Postgres. And/or use it to share stable and versioned URLs when
you want to provide a link to reference what you are talking about, be it a
blog post or a link in a document.

All in all, we hope it can be useful as it is, and even more with the steps
that will come after ;)

~~~
wakatime
> We don't believe much in such static tuning recommendations. Our experience
> tells us that they can do as much good as bad.

PGTune just automates the RAM + Connections math you would normally do
manually. PGTune is a good starting point, but you still need to know what
each config does and configure beyond PGTune. Nobody is saying PGTune does
your configs for you, it's just automating what we always did manually before.

~~~
ahachete
But I mostly disagree with that math also ;) Mostly, because that math is
subject to other parameters that are not easy to know statically --otherwise,
why aren't them Postgres defaults?

For example: shared_buffers is 1/4 of RAM and effective_cache_size 3/4\. Well,
several benchmarks have already pointed out that 1/4 is not necessarily a good
number, and you need to benchmark your own workload. Similarly,
effective_cache_size is slightly over dimensioned for dedicated servers and
definitely too big for shared servers.

Even more clearly, the max_connections recommendation may even become a
significant problem for your database. You should almost always have a
connection pooler in front of Postgres and have max_connections a _small_
multiple of your cores. PgTune's recommendation is probably an order of
magnitude higher than usual good values, which may lead to much worse
performance.

Another example: min_wal_size should be always a higher value than what is
recommended if you have enough disk, and max_wal_size should definitely be
something like significantly higher than what is recommended.

~~~
wakatime
You should blog about your learnings, would love to read more about "PG
performance configs you should look into". We're probably leaving too many
configs at default values just because we didn't know they were worth
tweaking.

~~~
ahachete
Maybe I haven't blogged (yet!) about that, but definitely spoken about it:
[https://aht.es/#talks-
postgresql_configuration_for_humans](https://aht.es/#talks-
postgresql_configuration_for_humans)

------
MadWombat
Do you really need to connect to Facebook, Amazon, Google and whatever all
these other hosts are just to provide PostgreSQL configuration reference?

[https://imgur.com/a/oxu1bfA](https://imgur.com/a/oxu1bfA)

~~~
ahachete
Interesting. Actually most of the dependencies come from the embedded
presentation on speakerdeck. Something to consider.

Amazon (AWS) is where the site is hosted (S3 + CloudFront, mostly) so that's
understandable.

------
nimbius
Ive always wondered where pushes for database "performance" tuning come from,
and i've suspected it might be an Oracle thing due to the applications nature
as somewhat of a black box with a prohibition in the license on any public
performance testing against other products.

MySQL and Postgres are generally good enough out of the box to handle most
workloads. Tuning efforts should best be spent at things like query
optimization and normalization, or identifying unintentionally inefficient
nested queries that might have cropped up through the life of the database.
things like old 'select *' reports that managers of long ago may have
mandated, or rogue cron jobs that run meaningless reporting. Identifying
records to truncate or creating new databases entirely for different types of
data instead of packing it all into one giant database as some companies tend
to do, is also worthwhile.

~~~
ahachete
I beg to disagree ;)

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.

~~~
Scarbutt
True but many setups can get away with just tuning 5-10 parameters.

~~~
ahachete
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.

------
yingw787
Wow, this is absolutely amazing! I’m planning on using PostgreSQL at a deep
level for YC Startup School, and I haven’t been able to find too many super in
depth PostgreSQL resources for what I’m doing (looking at event generation,
permissioning, and view refreshes). I’m sure this can help deepen my expertise
:)

------
cosmie
Does anyone happen to have any advice for configuration tuning when running
Postgres within Docker for Mac? I'm sure there are multiple ways to enhance
the configuration for running within that environment, but the filesystem
consistency concerns[1] in particular have always made me a bit paranoid.

This site seems really helpful for diving deeper into each of the individual
parameter options, but it'd still be really helpful for any practical tips
anyone may have.

[1] [https://docs.docker.com/docker-for-mac/osxfs-
caching/](https://docs.docker.com/docker-for-mac/osxfs-caching/)

------
miguelmota
I found the font hard to read. The light blue color doesn’t help readability
either.

~~~
ahachete
Thanks for letting us know. We will definitely consider this.

------
overcast
The layout of this website feels like it was made by someone who thought that
simultaneously combining every single design trend of the last 15 years, was a
good idea.

------
RobertRoberts
I have been using MySQL/MariaDB for many years. And I have wanted to switch to
Postgres, but I have read so many stories over the years of difficulties
configuring (like this article/video is addressing) and tuning/debugging
Postgres to cause me some concern.

Is there any validity to my concerns? (use case is CRUD apps and CMS's)

~~~
vbezhenar
I never made any changes to Postgres for development and it was blazing fast.
I made few simple changes for production to let it use more memory (just few
lines that you'll find in any guide) and that was about it. I guess, there are
plenty of handles for very pro users when they need to squeeze last few
percents of performance, but generally it just works fast.

~~~
topspin
This site is actually misleading in my opinion; someone with no prior exposure
might think postgresql users are obligated to spend their time in endless
configuration push ups. That is definitely not the case. As the parent points
out, developers rarely have to change more than a few things. Production means
real loads and real loads drive administrators to configure and tune, but
that's as true for MySQL/MariaDB as it is for Postgresql.

Over here[1] you can read about the 1163 snake cased MariaDB variables if you
want... 231 of them are specific to innodb alone.

[1] [https://mariadb.com/kb/en/server-system-
variables/](https://mariadb.com/kb/en/server-system-variables/)

------
gtaylor
Here's a direct link to the config param docs:
[https://postgresqlco.nf/en/doc/param/](https://postgresqlco.nf/en/doc/param/)

------
the-alchemist
Beautiful UI for this sort of thing, my hats off to you! I wish I could use
this UI for some of my projects!

The only thing that would make it better is keyboard navigation (specifically,
scrolling through search results).

------
dstola
Anyone else get a headache after looking at the main page of that site

~~~
somidscr21
very much so. there was a lot going on and it took my attention away from the
content so much so I was confused what I was looking at

------
zeypar
Also worth checking out for postgres users:

[https://github.com/ankane/pghero](https://github.com/ankane/pghero)

------
LargoLasskhyfv
Site looks like something SIEMENS from the 90ies with all that MINT,
reimagined through compression artifacts on a digital screen. Glitchy,
somehow.

------
abbadadda
This is boss. Thanks for sharing. Also going to throw out a recommendation for
pgAdmin - useful for querying and doing admin stuff.

------
skunkworker
I like the design and interface, but when you click on an element in the
scroll menu you lose your scroll position.

------
pettycashstash2
UI comment: does anyone dislike the vertical lines across the site? they give
me a headache for some reason.

------
lhopki01
What'd I'd like is something similar for pgbouncer.

~~~
ahachete
Thank you for the feedback, note taken.

