Postgresqlco.nf: a PostgreSQL configuration guide
postgresqlco.nf
postgresqlco.nf
Read more from a recent announcement https://www.ongres.com/blog/postgresqlconf-configuration-for...
Any feedback is very welcome!
P.S. postgresqlCO.NF responsible here
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.
It’s not clear to me exactly what one might want out of a mobile version of this site given the information overload. That would be my first consideration versus assuming it should be 1:1 with just a slightly different layout.
But if it is to contain the same information, I’d suggest it would be better to have a dedicated page that organizes all the different possible settings by topic or module, or even by “beginner to advanced” tuning. Such a directory can then easily be understood because to get there it would have a primary hero labeled button/link telling you what you’re about to see (the current page is mostly videos and some pretext for the site but all the actual value is hidden by the hamburger menu). You can always flip back to that, knowing that the browser will retain the scrolling history to go back to your previous place (which a hamburger menu doesn’t necessarily do).
Another style would be to have a chapter type layout where you can advance along some existing narrative (like beginner to advanced), much like FreeBSD documentation does (from memory).
All of this could also benefit from a search at the top that would let you random access what you’re looking for.
As someone else suggested here, another style would be to think about recommended configurations for typical usage patterns.. on VMs with X type workload vs Y, letting you directly lookup what you want in a hierarchically organized layout on a flat single page, or using drop downs/segmented controls to configure from the set of possible options. Then you could just punch in your actual needs and out would pop recommendations that hopefully had some insight and further guidance to back them up.
Just some ideas from the top of my head. The main principal behind all of this is to avoid sticking everything where you don’t know where to put it in a hamburger menu, and instead making your information architecture explicit and predictable.
Looks like a hamburger to me. It opens a menu when you click on it.
Everything else _has_ to be somewhere. Doesn't it? So, behind a menu seems like the only consistent and logical place.
I feel like your argument is for "apps" not "websites".
Some feedback/things I'd like to see (or not see):
- Make the Comments section collapsible, right now it takes 1/4 of the screen
- Filtering by categories is kinda confusing, since filters affect both the left sidebar as well as the main view I'd expect the filtering UI to sit on top of both
- Better visibility/categorization for parameters that can only be set via command line
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)
fsync = off synchronous_commit = off full_page_writes = off
And truncate all tables between each test
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.
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.
Also, do the truncations all in a single transaction. Since the actual dropping/truncations only happens after commit (for obvious reasons), we've at least optimized multiple truncations to happen in one pass.
It's unfortunate that a larger shared buffers can hurt this way :(
I've long hoped to find the cycles to finish a prototype that converted the buffer mapping to a hash table (for relations) over radix trees (or blocks in the relation).
128MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 15:19:04 benchmarking make test time 226.4 s (NaN s .. 240.6 s) 1.000 R² (0.999 R² .. 1.000 R²) mean 222.3 s (220.6 s .. 224.4 s) std dev 2.390 s (104.1 ms .. 2.986 s) variance introduced by outliers: 19% (moderately inflated)
128MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 16:18:57 benchmarking make test time 225.8 s (208.8 s .. 233.9 s) 0.999 R² (0.999 R² .. 1.000 R²) mean 221.6 s (215.8 s .. 224.8 s) std dev 5.576 s (1.671 s .. 7.491 s) variance introduced by outliers: 19% (moderately inflated)
2MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 18:30:27 benchmarking make test time 216.9 s (204.5 s .. 229.5 s) 1.000 R² (0.998 R² .. 1.000 R²) mean 219.7 s (216.7 s .. 221.3 s) std dev 2.863 s (1.283 s .. 3.658 s) variance introduced by outliers: 19% (moderately inflated)
2MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 19:29:26 benchmarking make test time 209.6 s (192.8 s .. 224.1 s) 0.999 R² (0.999 R² .. 1.000 R²) mean 216.0 s (211.0 s .. 218.5 s) std dev 3.732 s (1.442 s .. 5.168 s) variance introduced by outliers: 19% (moderately inflated)
So it seems like 2MB shared buffers shaves a few seconds off our tests. Is 2MB in the range you were thinking of?
Well, hard to say without knowing anything about your tests. I think the cases where I've seen it make a really big difference were more ones where people upped and upped shared_buffers trying to improve test performance, without noticing that it made things worse.
I'd probably not go below 16MB, otherwise it's plausible you could run into some errors (e.g. if you have a large number of cursors or such - they can end up pinning a few buffers, and if there's only 256 buffers, you don't need to be too creative for that).
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?