Tests that run against a different relational database from production make me really nervous.
The Django ORM has provided the ability to test against SQLite and deploy against PostgreSQL with the same code base (and the same tests) for years - and while it works incredibly well, I still won't use that in any system that I build. How your database behaves is such a crucial component of your application!
These days I find spinning up a real PostgreSQL database to run the tests is so easy there's essentially no reason not to do it. I use GitHub Actions for my PostgreSQL testing insurance, and it ends up just being a few extra lines in the YAML file.
I agree with Simon. I think you could run tests against SQLite locally so they're quick but then run them against Postgres in CI to ensure it works against the database you're running on. There's a lot of subtle differences in how the two databases work even though they both support a lot of the same SQL syntax.
You could also run SQLite in production. Then you don't have to test against a different database locally. :)
The Django ORM has provided the ability to test against SQLite and deploy against PostgreSQL with the same code base (and the same tests) for years - and while it works incredibly well, I still won't use that in any system that I build. How your database behaves is such a crucial component of your application!
These days I find spinning up a real PostgreSQL database to run the tests is so easy there's essentially no reason not to do it. I use GitHub Actions for my PostgreSQL testing insurance, and it ends up just being a few extra lines in the YAML file.