10 ms·
Postgresqlco.nf: a PostgreSQL configuration guide
- 1996 7y agoVery nice, but the #1 problem remains: discoverability. Say you have a IO intensive database, with about 6000 new insertions per seconds, and every 20 minutes a few reads to use that data for analytics. Which parameters should you tweak? Personally, in a situation like that I first focus on the filesystem, to have write cache (xfs_syncd_centisecs etc). Then I analyze the queries and work mostly on RAM assignation. For the postgresql.conf, I use recipes that build based on my experience, derived from A/B testing a set of individual modifications. However it remains premature optimization. Only benchmarks can say for sure. So when I feel fancy, I set aside a few days for tests and do a dense exploration of the parameter space for what I think will matter. Yet this remains constrained by discoverability, i.e. by what I think will matter: if I don't know a parameter may matter, it will not be explored in the test set to avoid the curse of dimensionality. TLDR: This website is nice, but could benefit from a few "typical scenarios" to know at least which knobs to try to tweak, along with some methodology for tweaking (ex: filesystem first)
- ahachete 7y agoThat's the next step we will be taking: offering subsets of parameters that are recommended to be tuned, depending on the "desired" level of expertise. And then, some more direct indications on how to tune this parameters for your particular use case. Stay tuned!
- ahachete 7y agoAs of today, it is more of a Reference than a Guide. This is intended. Further enhancements of the website will include functionality to fully help less and more experienced users to fine tune PostgreSQL configurations. Read more from a recent announcement https://www.ongres.com/blog/postgresqlconf-configuration-for-humans/ https://www.ongres.com/blog/postgresqlconf-configuration-for... Any feedback is very welcome! P.S. postgresqlCO.NF responsible here
- romanr 7y agoWhy not make preset configurations for various RDS machine types. 90% of users running on AWS RDS repeating same steps to get same results.
- ahachete 7y agoIf it would be this straightforward, why doesn't Postgres do this directly? There is the misconception that tuning Postgres is just about the memory and CPUs. It's also a lot about the usage patterns, whether you use or not a connection pooler in front (note: you should), about your OLTP-ish or OLAP-ish pattern, about the disk speed, disk technology, types of queries that you do.... it's, unfortunately, not simple at all. And the problem is that it's easy to think "oh, even if I get it close to right and maybe improve performance by 20% instead of 50%, that's still ok". But the problem is that a bad tuning may decrease your performance significantly. So it's a matter not to take it lightly.
- azinman2 7y agoI’d highly suggest removing the custom scrolling / scroll bar logic with your hamburger menu on mobile (and probably redesigning the site to avoid the hamburger menu all together — it’s an anti-pattern). The non-standard nature means I lose all the natural momentum scrolling I get on iOS by default, the scroll bars can’t be moved like I’m expecting, and it makes the whole site feel janky and not usable.
- FpUser 7y agoAlternatively they could realize that not everybody is willing to spend too much time and send people to something like https://pgtune.leopard.in.ua/ https://pgtune.leopard.in.ua/
- anarazel 7y agoThe suggestions pgtune makes are really bad. It sets max_wal_size way too low, max_parallel_workers_per_gather to ridiculously high values, shared_buffers way too low, the work_mem settings make no sense whatsoever.
- krn 7y agoCould you recommend any other tool that would generate more sensible PostgreSQL configuration values given similar input? In other words: here is my PostgreSQL version, use case, OS, number of CPUs, available memory size, and storage type. What's the most optimal configuration?
- ahachete 7y agoAs I mentioned upthread (https://news.ycombinator.com/item?id=22224019 https://news.ycombinator.com/item?id=22224019) and in the other thread (https://news.ycombinator.com/item?id=22140773 https://news.ycombinator.com/item?id=22140773), it's not that easy. And simple rules to compute parameters may lead to worse performance than the defaults. Otherwise, they would be the defaults ;)
- johnx123-up 7y agoFYI https://github.com/le0pard/pgtune/issues/35 https://github.com/le0pard/pgtune/issues/35
- anarazel 7y agoHeh, that's a bit awkward... Anyway, went into some details about why they are bad.
- MaxGabriel 7y agoDo you have any recommended config for speeding up tests that hit Postgres? We currently use these settings: fsync = off synchronous_commit = off full_page_writes = off And truncate all tables between each test
- 1996 7y agoYou really don't want to do that, because the test environment will not be representative of the real environment (unless your production db has fsync=off etc., which can only be justified in very specific cases)
- MaxGabriel 7y agoI’m willing to accept less-than-perfect simulation of our production environment in order to speed up development, especially for settings that eg only matter in the case where the machine crashes (big deal in production—not in tests). We already make a leap of faith by developing on Mac/Linux distros other than what we use in production. Making Postgres not-crash safe locally is a pretty small change compared to that.
- anarazel 7y agoOne caveat: Disabling fsyncs can hide problematic access patterns. If you were to e.g. accidentally perform a large set of DML in one-row increments and forgot to do so in one transaction, you'd not see a huge penalty in your test environment, but a lot more in production (one fsync for each transaction in the non-concurrent case). Btw, disabling synchronous_commit isn't going to do much once you've disabled fsyncs. What s_c=off basically does is to defer the journal flush to a background process that will do them regularly (unless already done by another session / reason) - but if fsync=off, that's not necessary anyway. And s_c=off does have some small overhead too.
- CameronNemo 7y agoYou can have two pipelines, one for typical development / merge requests and another for pre-releases.
- Deimorz 7y agoPosted and discussed 9 days ago as well: https://news.ycombinator.com/item?id=22139975 https://news.ycombinator.com/item?id=22139975
- RookyNumbas 7y agoIt would be really nice to have a "start here" section. What are the 10 parameters I should look at and understand first?