Of course im not bashing event sourcing altogether but right now resume boosting seems to be the driving force to apply it everywhere.
And this comes from my experience at my current workplace. Event sourcing has brought so much unnecesary complexity to a an older system and it’s not even implemented properly. The engineers who took the initiative padded their resumes then went on to greener pastures while leaving a giant mess behind.
I've also thought it made sense on an application that regularly synced down data from 3rd party systems, where it wasn't uncommon to have some kind of weird data introduced and struggle to reconcile when/where it came from against changes made by application users on our end. Having everything as a series of events can be really awesome in that case.
It's far easier for us as humans to think "this change will affect new data going forward" rather than "when I make a change in the present, it will transform all data from the past as if it had always been the case." Perhaps comic book writers would make good event sourcing engineers - but I digress.
One of the saving graces of event sourcing is that you can spin up a parallel world where you make those changes, rerun your entire event history, and do a diff between your production derived data and your proposed production derived data as of a point in time, identifying the actual situations where pathological cases would occur. But this takes a lot of discipline, and it's not perfect, because events don't exist in a vacuum - they are snapshots of a human being taking an action relative to the data they saw at the time, and if you're retconning the data they saw, it might not match the reasons they took the actions they did.
A common pathological case I find is: "Someone overrode value X to Y, now I have the ability to automate that fix - but did they mean to freeze Y for a different business reason outside the one I did? If at some time in the future it changes upstream from X to Z, should I keep the user's intent to freeze Y or change it to Z because the freeze to Y was just because we didn't have the fix?"
Relational approaches don't solve this, to be sure, and in fact there's worse tooling to have good answers to this question. But they do incentivize the discipline of talking to stakeholders before making a giant wide-scoped UPDATE on data at rest. Event sourcing's power to retcon makes it far too easy to say "let's just reinterpret the history, what's the worst that can happen?"
The biggest pain point I had working on an event sourced system at an early stage startup was around schema changes, where changes (especially quick or unplanned) introduced something pathological in event data for some period of time that we didn't realize was an issue until it conflicted with some change much later on.
Often the discovery of these issues would come at a bad time and block something important, e.g. because of small differences in dev environment seed data and real production data, leading to temptation and/or pressure to "just do a quick tiny mutation" to old events to address the problematic events from the past mistake, which realistically often just caused a different issue, because reasoning about all of the potential impacts of the change was hard.
These days in early-stage land I am only interested in event sourcing for contained areas of a product where it can provide real value, rather than as a cornerstone of an entire application.