Primarily slowness and brittleness. Tests with side effects are much more likely to be flaky, and flaky tests are the worst. Especially if they're slow and hence hard to debug.
Of course, you do test your use of the database, but in a much smaller test suite. For the business logic tests you just spin up a copy of your app with fake adapters, and you're done. Quick, deterministic tests.
I agree that integrated tests tend to be brittle, but if we need database tests either way, the database harness can't be flaky. So any flakiness there is something that has to be fixed regardless. Slowness, I agree, but unless you're spinning up new containers for every test, the overhead of each query being real is going to be small.
If the database interactions are trivial, I agree—just use stubs. But if you've got non-trivial joins, then you'll need a separate database test suite anyway. And if there's stateful logic involving the database and you want to use fakes in unit tests, you need a whole extra set of tests to validate that the fakes behave the same way that the real database does.
I do actually prefer to avoid relying on the database for unit tests—it's cleaner, it's faster—but often testing with the database is simpler & reliable, and I can't justify the extra lines of code required for fully isolated unit tests. That's extra surface area for tech debt that could lead to stuff like fakes behaving differently from their production equivalent.
Primarily slowness and brittleness. Tests with side effects are much more likely to be flaky, and flaky tests are the worst. Especially if they're slow and hence hard to debug.
Of course, you do test your use of the database, but in a much smaller test suite. For the business logic tests you just spin up a copy of your app with fake adapters, and you're done. Quick, deterministic tests.