I've implemented a system that uses event sourcing at "scale" (YMMV at what is "scale").
1. Data storage is not "10x bigger", it depends on how long you maintain the event stream in the past. Think about a bank account. The "monthly opening balance" is effectively a snapshot in time of all of the previous events for that account. So you can archive events to secondary storage as needed.
2. You don't "fix fuckups by manual database edits/scripts". You make sure that for every business level event, you have a reversal event defined as well. A fuckup is "reverse the affected events, then replay them with corrections". It requires the design at the start to understand that this needs to be dealt with.
The benefits:
* Each entity has its own state and events, it's self contained and easily mockable and testable on its own. The events are idempotent.
* Dependencies between entities is entirely driven by the events, so they are fully decoupled and can be deployed, blue/green, sharded, scaled etc independently.
* The events are all that is needed to recreate a scenario or failure, so can be saved and replayed as needed.
* Deployment of new entities and/or blue/green and/or scaling becomes trivial.
Cons:
* If there's an error in production, you have to apply reversal events and then replay the previous events to "fix" a problem. There isn't a "main DB" as such, so there's nothing to change there, but anything that takes the stream of events and turns it into RDBMS (for reporting etc) needs to also understand the reversals and replacement events. This can lead to confusion when a report generated on day X is reversed/redone and now it is "different" to what the report showed at the time. However, this can be covered by actually dealing with the reversals as first-level events in their own right.
* When you reverse/replay events for an entity, you need to be aware of side effects on other entities. For example, if you reverse/replay an order, you need to ensure that you don't trigger another round of payment processing. That means adding logic to the payment entity to ensure that it "knows" what order it got payment for so that it doesn't repeat.
* Event sourcing is not about "structured data storage", they're entirely about business logic. The events are business events. Programmers seem to have trouble dealing with the fact that the low level storage and processing are independent of the business events.
* You need to have versioning of event schemas so that you can add/remove attributes of the event type over time. Your processing has to accomodate the versioning (semver is your friend).
* If you have your event processing separate from your query processing, you cannot rely on the query when processing events for the entity itself. If you have multi-threaded and or scaled processing that deals with the entity at the same time, you need to have some form of internal caching/locking/vector-clocks to ensure that you don't have two processes updating the entity from two different events simultaneously.
So it has distinct advantages when in production and in the CI/CD deployment. It has additional complexity at the architecture and high level design phase and it needs some standardized libraries and infrastructure to support the necessary features (primarily the internal synchronization of entity/event processing between threads/processes to ensure monotonic updates of the state of the entity).