There is your idealization, and there is reality. Mocks are to be avoided. I reserve them for external dependencies.

> What higher fidelity do you get with a real postgres over a SQLite in memory or even pglite or whatever

You want them to have the same syntax and features, to the extent that you use them, or you'll have one code path for testing and another for production. For example, sqlite does not support ARRAYs or UUIDs natively, so you'll have to write a separate implementation. This is a vector for bugs.

You're right that sqlite doesn't support array's or uuid's natively. SQLite was only a suggestion on how one might go about separating your database engine concerns with your data layer concerns.

If you fail to understand why this separation is important, you'll fail to reason with why you'd do it in the first place so continue building apps like it's 1999, tightly coupled and you need the whole stack to run your thing. God forbid you expand beyond just 1 team.