Probably the single most obnoxious production defect I ever found related to a database would never have made it into production if we had been using a real database instead of a test double. It happened because the test double failed to replicate a key detail in the database's transaction isolation rules.

After figuring it out, I swapped us over to running all the tests that hit the database against the real database, in a testcontainer, with a RAM disk for minimizing query latency. It was about a day's worth of work, and turned up a few other bugs that hadn't bit us in production yet, too. Also sailing past our test suite because the test double failed to accurately replicate the behavior in question.

Total time to run CI went up by about 10 seconds. (For local development you could chop that way down by not starting a fresh server instance for every test run.) Given how many person-hours we spent on diagnosing, resolving, and cleaning up after just that first defect, I estimated the nominally slower non-mocked tests are still a net time saver if amortized over anything less than about 50,000 CI runs, and even then we should probably only count the ones where an engineer is actually blocking on waiting for the tests to complete.

That said, there was a time when I thought test doubles for databases was the most practical option because testing against real databases while maintaining test isolation was an unholy PITA. But that time was 5 or 6 years ago, before I had really learned how to use Docker properly.