At pganalyze, we build observability and automation tools for optimizing the performance of Postgres databases. Our product helps customers such as Auto Trader, Notion and Robinhood to understand complex Postgres problems and solve performance issues. We are bootstrapped, profitable, with over 500 customers, small and large.
We're looking for the second hire on our marketing team! You might wonder, why post about it on HN? If you are the kind of tech-savvy marketer that has worked on developer tools or infrastructure software before, and reads HN to keep up with the latest, this might be a fit for you. Our marketing stack utilizes Claude Code for some automation (summaries, posting, etc), but we keep the actual writing human authored - if that appeals to you, reach out!
Email me directly (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.
I don't think Tom's perspective has necessarily changed (and there is certainly concern from others that this could cause less reports on planner bugs), but Tom is pretty good about not standing in the way of others (i.e. Robert Haas in this case) trying to make things work, and being open to new perspectives.
I do know that one of the important criteria for getting this in was that a bad advice can't cause the planner to fail, and that's something that was explicitly included in the design of pg_plan_advice.
Also, I'll add my perspective: I think "EXPLAIN (PLAN_ADVICE)" is a key piece to making this a plan stability feature, not (just) a hinting feature. The extensibility/framework pg_plan_advice adds is a foundation, that over time will over time address the age-old "Postgres doesn't have hints" problem, even if the initial release doesn't check all the boxes yet, e.g. no way to use advice for adjusting row/join estimates.
To give an example on extensibility: Some people that I've spoken to are asking "but why is it not a comment-style hint". There are reasons why Postgres didn't go that way for this release (comment parsing in core is non-existent today, and comments don't work correctly e.g. for functions), but its easy to write an extension that sets up an advisor hook to parse comments: https://github.com/pganalyze/pg_advice_comment
That said, this post predates that LinkedIn post, so I don't think it was intended to compete with the original author's own efforts, but rather to provide continuity for PGX's customers.
>If this somehow does end up being a reproducible performance issue (I still
suspect something more complicated is going on), I don't see how userspace
could be expected to mitigate a substantial perf regression in 7.0 that can
only be mitigated by a default-off non-trivial functionality also introduced
in 7.0.
> Maybe we should, but requiring the use of a new low level facility that was introduced in the 7.0 kernel, to address a regression that exists only in 7.0+, seems not great.
Completely right. This sounds like a communication failure. Maybe Linux maintainers should pick a few applications that have "priority support" and problems with these applications are also problems with Linux itself. Breaking Postgres is a serious regression.
Reminds me of a situation where Fedora couldn't be updated if you had Wine installed and one side of the argument was "user applications are user problem" while the other was "it's Wine, like come on".
Performance regressions are different from ABI incompatibilities. If the kernel refused to do any work that slowed down any userspace program, the pace would go a lot slower.
Or be a lot uglier. See: Microsoft replacing its own API surfaces with binary-compatible representations to workaround companies like Adobe adding perf improvements like bypassing the kernel-provided kernel object constructors because it saved them a few cycles to just hard-code the objects they wanted and memcpy them into existence.
I’m absolutely flabbergasted by the performance left on the table; even by myself - just yesterday I learned Gentoo’s emerge can use git and be a billion times faster.
The time spent by emerge is utterly dwarfed by the time spent to build the packages, so who cares? Maybe it's different if installing a binary system but don't think most people are doing that.
If you can emerge in 2.86s user you can do it right before you emerge world, meaning it's all "done in one interaction" (even if the actual emerge takes an hour - you don't have to look at it.
Whereas if emerge is taking 5-10 minutes, you have to remember to come back to it, or script it.
That's really not universally true. Building can be parallelized on modern multi-code CPUs (minus configure), emerge cannot and portage is really really slow.
Bad because as of Splunk 10.x, Splunk bundles postgres to integrate with their SOAR platform. Parenthetically, this practice of bundling stuff with Splunk is making vuln remediation a real pain. Splunk bundles its own python, mongod, and now postgres, instead of doing dependency checking. They're going to have to keep doing it as long as they release a .tgz and not just an RPM. The most recent postgres vuln is not fixed in Splunk.
1) That is about transparent huge pages which is a different thing and 2) it is always clear cut for PostgreSQL. If you can you should always use huge pages (the non-transparent kind).
Java can work with transparent hugepages (in addition to preallocated hugepages), but you just use +AlwaysPreTouch to map them in during the startup so that at runtime there won't be any delays or jitter.
Redis should add a similar option
AIUI in that thread they're saying "0.51x" the perf on a 96-core arm64 machine and they're also saying they cannot reproduce it on a 96-core amd64 machine.
So it's not going to affect everybody both running PostgreSQL and upgrading to the latest kernel. Conditions seems to be: arm64, shitloads of core, kernel 7.0, current version of PostgreSQL.
That is not going to be 100% of the installed PostgreSQL DBs out there in the wild when 7.0 lands in a few weeks.
It's a huge issue of ARM based systems, that hardly anyone uses or tests things on them (in production).
Yes, Macs going ARM has been a huge boon, but I've also seen crazy regressions on AWS Graviton (compared to how its supposed to perform), on .NET (and node as well), which frankly I have no expertise or time digging into.
Which was the main reason we ultimately cancelled our migration.
I'm sure this is the same reason why its important to AWS.
Macs are actually part of pain point with ARM64 Linux, because the Linux arm set er tend to use 64 kB pages while Mac supports only 4 and 16, and it causes non trivial bugs at times (funnily enough, I first encountered that in a database company...)
Yes, I did reproduce it (to a much smaller degree, but it's just a 48c/96t machine). But it's an absurd workload in an insane configuration. Not using huge pages hurts way more than the regression due to PREEMPT_LAZY does.
With what we know so far, I expect that there are just about no real world workloads that aren't already completely falling over that will be affected.
So why does it happen only with hugepages? Is the extra overhead / TLB pressure enough to trigger the issue in some way? Of is it because the regular pages get swapped out (which hugepages can't be)?
I don't fully know, but I suspect it's just that due to the minor faults and tlb misses there is terrible contention with the spinlock, regardless of the PREEMPT_LAZY when using 4k pages (that easily reproducible). Which is then made worse by preempting more with the lock held.
So perhaps this is a regression specifically in the arm64 code, or said differently maybe it’s a performance bug that has been there for a long time but covered up by the scheduler part that was removed?
Turns out the amd machine had huge tables enabled and after disabling those the regression was there on and too. So arm vs amd was a red herring.
Of course not a nice regression but you should not run PostgreSQL on large servers without huge pages enabled so thud regression will only hurt people who have a bad configuration. That said I think these bad configurations are common out there, especially in containerized environments where the one running PostgreSQL may not have the ability to enable huge pages.
That should be obvious to anyone who read the initial message. The regression was caused by a configuration change that changed the default from PREEMPT_NONE to PREEMT_LAZY. If you don’t know what those options do, use the source. (<https://git.kernel.org/pub/scm/linux/kernel/git/torvalds/lin...>)
Yes, I had a good laugh at that. It might technically be a regression, but not one that most people will see in practice. Pretty weird that someone at Amazon is bothering to run those tests without hugepages.
I doubt they explicitly said "I'll run without huge pages, which is an important AWS configuration". They probably just forgot a step. And "someone at Amazon" describes a lot of people; multiply your mental probability tables accordingly.
The number of people at Amazon is pretty much irrelevant; the org is going to ensure that someone is keeping an eye on kernel performance, but also that the work isn’t duplicative.
Surely they would be testing the configuration(s) that they use in production? They’re not running RDS without hugepages turned on, right?
> The number of people at Amazon is pretty much irrelevant; the org is going to ensure that someone is keeping an eye on kernel performance, but also that the work isn’t duplicative.
I'd guess they have dozens of people across say a Linux kernel team, a Graviton hardware integration team, an EC2 team, and a Amazon RDS for PostgreSQL team who might at one point or another run a benchmark like this. They probably coordinate to an extent, but not so much that only one person would ever run this test. So yes it is duplicative. And they're likely intending to test the configurations they use in production, yes, but people just make mistakes.
True; to err is human. But it is weird that they didn’t just fire up a standard RDS instance of one or more sizes and test those. After all, it’s already automated; two clicks on the website gets you a standard configuration and a couple more get you a 96c graviton cpu. I just wonder how the mistake happened.
No… I’m assuming that they didn’t use the same automation that creates RDS clusters for actual customers. No doubt that automation configures the EC2 nodes sanely, with hugepages turned on. Leaving them turned off in this benchmark could have been accidental, but some accident of that kind was bound to happen as soon as the tests use any kind of setup that is different from what customers actually get.
You're again assuming that having huge pages turned on always brings the net benefit, which it doesn't. I have at least one example where it didn't bring any observable benefit while at the same time it incurred extra code complexity, server administration overhead, and necessitated extra documentation.
It is a system-wide toggle in a sense that it requires you to first enable huge-pages, and then set them up, even if you just want to use explicit huge pages from within your code only (madvise, mmap). I wasn't talking about the THP.
When you deploy software all around the globe and not only on your servers that you fully control this becomes problematic. Even in the latter case it is frowned upon by admins/teams if you can't prove the benefit.
Yes, there are workloads where huge-pages do not bring any measurable benefit, I don't understand why would that be questionable? Even if they don't bring the runtime performance down, which they could, extra work and complexity they incur is in a sense not optimal when compared to the baseline of not using huge-pages.
> Yes, there are workloads where huge-pages do not bring any measurable benefit
I really doubt it, except of course workloads where you just use a trivial amount of memory to begin with. In systems I've seen, anywhere from 5% to 15% of the CPU time is spent waiting for TLB misses. It's obvious then that huge pages can be hugely beneficial if properly used; by definition they hugely relieve TLB pressure.
You can of course end up in situations where transparent TLB scanning is worse than nothing, but that's exactly why I pointed out there's a variety of ways to use huge pages.
You don't seem to understand the idea that CPU spending time on TLB misses and at the same time seeing no measureable effects in E2E performance because much larger bottleneck is elsewhere can be both valid simultaneously. In database kernels with large and unpredictable workloads, high IO and memory footprint, this is certainly easy to prove.
For production Postgres, i would assume it’s close to almost no effect?
If someone is running postgres in a serious backend environment, i doubt they are using Ubuntu or even touching 7.x for months (or years). It’ll be some flavor of Debian or Red Hat still on 6.x (maybe even 5?). Those same users won’t touch 7.x until there has been months of testing by distros.
Ubuntu is used in many serious backend environments. Heroku runs tens of thousands (if not more) instances of Ubuntu on its fleet. Or at least it did through the teens and early 2020s.
and they are right, this is because a lot of junior sysadmins believe that newer = better.
But the reality:
a) may get irreversible upgrades (e.g. new underlying database structure)
b) permanent worse performance / regression (e.g. iOS 26)
c) added instability
d) new security issues (litellm)
e) time wasted migrating / debugging
f) may need rewrite of consumers / users of APIs / sys calls
g) potential new IP or licensing issues
etc.
A couple of the few reasons to upgrade something is:
a) new features provide genuine comfort or performance upgrade (or... some revert)
b) there is an extremely critical security issue
c) you do not care about stability because reverting is uneventful and production impact is nil (e.g. Claude Code)
but 99% of the time, if ain't broke, don't fix it.
On the other hand, I suspect LLMs will dramatically decrease the window between a vulnerability being discovered and that vulnerability being exploited in the wild, especially for open-source projects.
Even if the vulnerability itself is discovered through other means than by an LLM, it's trivial to ask a SOTA model to "monitor all new commits to project X and decide which ones are likely patching an exploitable vulnerability, and then write a PoC." That's a lot easier than finding the vulnerable itself.
I won't be surprised if update windows (for open source networked services) shrink to ~10 minutes within a year or two. It's going to be a brutal world.
Too often I see IT departments use this as an excuse to only upgrade when they absolutely have to, usually with little to no testing in advance, which leaves them constantly being back-footed by incompatibility issues.
The idea of advanced testing of new versions of software (that they’ll be forced to use eventually) never seems to occur, or they spend so much time fighting fires they never get around to it.
I’ve seen more 5k+-core fleets running Ubuntu in prod than not, in my career. Industries include healthcare, US government, US government contractor, marketing, finance.
I'd say about 2/3 of the places I've worked started on Linux without a Windows precedent other than workstations. I can't speak for the experience of the founding staff, though; they might have preferred Ubuntu due to Windows experience--if so, I'm curious as to why/what those have to do with each other.
That said, Ubuntu in large production fleets isn't too bad. Sure, other distros are better, but Ubuntu's perfectly serviceable in that role. It needs talented SRE staff making sure automation, release engineering, monitoring, and de/provisioning behave well, but that's true of any you-run-the-underlying-VM large cloud deployment.
.. which confirms all of my stereotypes. Looks like the AWS engineer who reported it used a m8g.24xlarge instance with 384 GB of RAM, but somehow didn't know or care to enable huge pages. And once enabling them, the performance regression disappears.
Honest question: what's the value of running the benchmark and reporting a performance regression if the author is not familiar with basic operation of the software? I'd argue that not understanding those settings disqualifies you from making statements about it.
The performance was reduced without a settings change. That is still a regression even if huge pages mitigates the problem.
I'd be curious to know if there's still a regression with hugepages turned on in older kernels.
If you are benchmarking something and the only changed variable between benchmarks is the kernel, that is useful information. Even if your environment isn't correctly setup.
Yet we're talking about postgres, specifically. The whole point is that benchmarks about postgres better know how to configure postgres or their conclusions be irrelevant at best. What does redis have to do with this discussion?
And, its noted in the video/transcript, but for clarity: This is talking about new extensibility in Postgres 19 that makes it easier to do Postgres planner hints / plan management extensions.
The patch that was committed is part of a larger proposal (pg_plan_advice) which, if it ends up being committed, would add a version of planner hints to Postgres itself (in contrib). It remains to be seen where that goes for Postgres 19.
This is great, I have harped on PG's aversion to plan hints quite a bit in the past (and also lack of plan caching and reuse).
One of the biggest issues I run into operationally with relational db's is lack of plan stability and inability as a developer to tell it what I know is the right thing to do always in my application.
I use MS SQL Server more than PG currently and if it did not have plan hinting it would have been catastrophic for our customers.
Its still not perfect and over the years I am starting to think that having smart optimizers that use live statistics may be more of a detriment than help due to constantly fighting bad plan generation at typically the worst times (late at night in production). At least with compiler optimization it happens at build time and the results can be reasoned about and is stable. SQL is like some insane runtime JIT system like a javascript engine that de-optimizes but not deterministically based on the data in the system at the time.
Using various tricks and writing the query in slightly different ways while praying to the planner god to pick the correct plan is well infuriating due to lack of control over the system.
I much prefer systems like Linq or Kusto where it's still declarative but the pipeline execution order follows the order as written in the code. One of the most helpful and typical hints that solves issues is simply forcing join order and subquery / where order such that I want it to do this filter first then this other one second, then typically the optimizer picks the obviously correct indexes to use. Bad plans typically try and rewrite the order do much less selective thing first destroying performance. I as the developer normally know the correct order of operations for my applications use and write the query that way.
I've actually come around to the Postgres way of thinking. We shouldn't want or need plan hints usually.
Literally every slow Postgres statement I worked on in the last few years was due to lack of accurate statistics, missing indexes, or just badly designed queries. Every one was fixable at the source, by actually fixing the core issue.
This was in stark contrast to the myriads of Oracle queries I also debugged. The larger older ones had accumulated a "crust" of plan hints over the years. Most not so well thought out and not valid anymore. In fact, often just removing all hints made the query faster rather than slower on newer Oracle versions.
It's so tempting to just want to add a plan hint to "fix" the suboptimal query plan. However, the Postgres query planner often has an actual reason for why it does what it does and overall I've found the decisions to be very consistent.
>I've actually come around to the Postgres way of thinking. We shouldn't want or need plan hints usually.
They only come out at night, mostly.
PG is 40 years old and still has planner bugs being fixed up regularly, and having no control and waiting for a new version when a hint could fix the issue at runtime is an obvious problem that should have been addressed long ago.
It's great the devs want to make the planner perfect and strive for that, it is an unattainable goal worth pursuing IMO. Escape hatches are required hence the very popular pg_hint_plan extension.
But in the end after many years of dealing with these things I have come to the opposite conclusion, let the query language drive the plan more directly and senior devs can fix juniors devs mistakes in the apps source code and the plans will be committed in source control for all to see and reference going forward.
SQL comes from an idea of non technical people querying a system in ad-hoc ways, still useful, but if you are technically competent in data structures and programming and making an application that uses the db, the planner just gets in your way at least in my experience.
But, I'm hopeful that in 2-3 years from now, we'll see this bear fruition. The recent asynchronous read I/O improvements in Postgres 18 show that Postgres can evolve, one just needs to be patient, potentially help contribute, and find workarounds (connection pooling, in this case).
Since there seems to be some confusion in the comments about why pg_query chose Protobufs in the first place, let me add some context as the original author of pg_query (but not involved with PgDog, though Lev has shared this work by email beforehand).
The initial motivation for developing pg_query was for pganalyze, where we use it to parse queries extracted from Postgres, to find the referenced tables, and these days also rewrite and format queries. That use case runs in the background, and as such is much less performance critical.
pg_query actually initially used a JSON format for the parse output (AST), but we changed that to Protobuf a few major releases ago, because Protobuf makes it easy to have typed bindings in the different languages we support (Ruby, Go, Rust, Python, etc). Alternatives (e.g. using FFI directly) make sense for Rust, but would require a lot of maintained glue code for other languages.
All that said, I'm supportive of Lev's effort here, and we'll add some additional functions (see [0]) in the libpg_query library to make using it directly (i.e. via FFI) easier. But I don't see Protobuf going away, because in non-performance critical cases, it is more ergonomic across the different bindings.
1) When do pages get removed? (file on disk gets smaller)
Regular vacuum can truncate the tail of a table if those pages at the end are fully empty. That may or may not happen in a typical workload, and Postgres isn't particular about placing new entries in earlier pages. Otherwise you do need a VACUUM FULL/pg_squeeze.
2) Does a regular VACUUM rearrange a single page when it works on it? (i.e. remove empty pockets of data within an 8kb page, which I think the author calls compacting)
I think the answer to that is yes, e.g. when looking at the Postgres docs on page layout [0] the following sentence stands out: "Because an item identifier is never moved until it is freed, its index can be used on a long-term basis to reference an item, even when the item itself is moved around on the page to compact free space". That means things like HOT pruning can occur without breaking index references (which modify the versions of the tuple on the same page, but keep the item identifier in the same place), but (I think) during VACUUM, even breaking index references is allowed when cleaning up dead item identifiers.
At pganalyze, we build observability and automation tools for optimizing the performance of Postgres databases. Our product helps customers such as Auto Trader, Notion and Robinhood to understand complex Postgres problems and solve performance issues. We are bootstrapped, profitable, with over 500 customers, small and large.
We're looking for the second hire on our marketing team! You might wonder, why post about it on HN? If you are the kind of tech-savvy marketer that has worked on developer tools or infrastructure software before, and reads HN to keep up with the latest, this might be a fit for you. Our marketing stack utilizes Claude Code for some automation (summaries, posting, etc), but we keep the actual writing human authored - if that appeals to you, reach out!
Email me directly (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.
All details: https://pganalyze.com/careers