As 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.
I’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.
What’s the alternative to a hamburger menu that you would recommend for mobile? It’s a very common ux. For example, target, Walmart, and amazon all have a hamburger menu on mobile.
If you notice no Apple app has a hamburger menu, nor does their website on mobile. Hamburger menus hide all the details, with no predictability as to what might be there or that you should need to click on it (the icon tells you nothing). There’s plenty written out there about this issue.
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.
Feeling shame for my own company! Wow! It’s marketing that runs that versus the actual designers (to my knowledge), and I wish they hadn’t resorted to that.
I think you can put maybe 2 (3 in extreme cases) links at the top of a mobile page. That's it. Which most sites use for a call to action. (buy, call us, specialty forms, etc...)
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".
If 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.
Amazing work, I wish every service was covered like this!
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
Very 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)
That'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!
If you're truncating/dropping tables all the the time, it can actually be beneficial to set shared_buffers to a pretty small value. When dropping/truncating, postgres needs to scan the buffer cache to get rid of all entries belonging to that relation (happens at the end of the transaction). The mapping is a hashtable, so there's no ordered scan to do so in a nice way.
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?
> 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).
You 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)
I’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.
One 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.
The 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.
Could 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?
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