This was inspired by the Speeding up Django unit tests with SQLite, keepdb and /dev/shm article that demonstrates how switching from PostgreSQL to an in memory SQLite helps to speed up testing during the development phase. But, the article warns the readers to make sure on the CI there is a real database used:
But I'm only advocating this for use in development! Ultimately, Postgres or whichever database you're using will behave differently from SQLite. Django does a good job of abstracting away 90% of those differences, but that still leaves plenty of strange edge case behaviours to do with default values, ordering and transactions that can easily trip you up.
Now, I am curious, what are these possible edge cases, what can potentially go wrong if one would continue to use SQLite for testing on a CI server as well? Is there any evidence or existing historical examples of this kind of issues?