Show HN: Postgresqlco.nf: PostgreSQL Configuration for Humans
ongres.com
ongres.com
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.
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.
isn't that just a pastebin?
Now, on topic: why link to a blog post rather than the actual site? 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...
This answers that question. I've discovered many local startups by just browsing the web than I would have otherwise.
But still, back on point, how does this "Made with love in <X>" bring people together as you claim previously?
It wasn't really intentional, and I actually didn't even know where or when this originated. It just sounded "right" (non native English speaker here). There are several two reasons why this felt right:
* Tuning Postgres well is hard. Not everybody, not every "human" can easily do it.
* There are several efforts (mostly from Academia) to have non humans (A.I.) tune databases automatically (e.g. Andy Pavlo's efforts).
> why link to a blog post rather than the actual site?
Because the blog post contains an explanation of what the web site is, how you can use it and provide much more context than the direct parameters page.
About the new functionality, it is something in the works and early disclosure is good for potential feedback. And yes, the screenshot is what you can get today, so feel free to enjoy it!
Disclaimer: I work @OnGres.
Some of these trends are just stupid (I'm so glad the 'made with love' shit has died down)
Did it though? I still add a "Built with heart" badge to the README of every new project. /s
Some people like to keep it simple, others like to geek out on esoteric settings and full configurability. Personally I like when a software is upfront about who it's for, instead of pretending to cater to everyone's taste.
Even Windows comes in several flavors because one size doesn't fit all.
Computers.
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.
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 ;)
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.
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.
What we're thinking is providing a set of "cards", where each card contains a theme: logging tuning, memory parameters, autovacuum, etc. And then, every card contains a recommended set of parameters to tune, and guidance about how to tune them.
Thanks for the feedback :)
Amazon (AWS) is where the site is hosted (S3 + CloudFront, mostly) so that's understandable.
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.
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.
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.
Also, throwing some hardware like a bigger SSD or memory increase, both being quite cheap these days, will increase performance when dealing with an out of the box server as well.
Example: Year 2017, client started to have bigger traffic and users were started seeing timeouts. Server was 16 GB RAM. Mind you, it was the developer server as well, so lots of stuff were running there. Bought dedicated server, upped the memory to 64 GB and in one night we did the switch together. Downtime of only 30 minutes and to this day the client is good to go.
(Actually, question for the audience, anyone with experience in heavily-OLAP workloads: what kind of DBMS would you load an OLAP cube into, if it were still too large after projection/dimension-reduction to fit into memory, and you needed to run CPU-intensive, non-embarrassingly-parallel queries on it?)
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.
Is there any validity to my concerns? (use case is CRUD apps and CMS's)
The area MySQL people get hung up on, generally, is the security model in pg_hba.conf. But this is a seriously powerful feature, and not at all hard to configure once you grasp what's going on.
I will look into the security conf the next time I try it out. Thanks!
My experience is that open source software is almost always managed through config files... Is this your point?
Over here[1] you can read about the 1163 snake cased MariaDB variables if you want... 231 of them are specific to innodb alone.
There are about 6 parameters that I might touch on every deployment:
Listen address, shared buffers (default it to 25% RAM), connection counts, max workers (CPU core count), max parallel workers and max WAL keep segments.
That's 95% of the tuning you need on an average deployment. The rest is a bit dependent on workload (like checkpoint timeouts on heavy-write boxes).
EDIT: And when I say "need", the actual defaults out of the box will usually get you a long way. Just set the right listen address and let it do its thing.
The comments here have bolstered my resolve to move to Postgres. :)
The only thing that would make it better is keyboard navigation (specifically, scrolling through search results).