For my company I wrote internal django apps that are completely database agnostic. They can be run in testing with sqlite, postgres, oracle, etc.
And I'm not talking about postgis, hstore or such exotic features. I'm talking about indexes, transactions, locking and other basic feature that is often missing or implemented very different across databases.
You may not need, say, GIST indices that help when sorting "tweets by recent activity" for your proof of concept or even your test-env but you certainly do for production, or else you are truly missing out. And such basic tools are avaialble in most database-engines, but differ very much in how exactly they are implemented, to be used and to be tuned.
90% of what I need is already there. And if I need something special, I can add it in later.
The migrations and ORM allows for a lot of indexing, but lacks the last 20%, which -logically- is DB specific indexing. But also the indexing that gets the majority of the improvements.
Locking is another thing: lowest commnon denomimator is bad. But works, for sqlite, mysql and postgres (and probably oracle, never tried). Yet is so naive that it does more harm that good. In Rails they've worked on this and moved far beyond the lowest-common-denominator with Adapters and other patterns.
That adds such an amount of indirections that it does more harm than good, when debugging where that weird "why did it choose to use a GIST and not a partial BTREE here".
(edit: kept story in line with one usecase. sorry)
Like I said elsewhere, Django offers a lot, and what it doesn't offer can always be added with raw SQL.
The problem is "lowest common denominator".
Think of it in an extreme way. as a thought experiment: what if you build an ORM that supports flawless migration from PostgreSQL to Redis and back. That abstracts away all the differences.
That ORM would bery extremely limited: it could basically only do key-value storage; would have no WHERE filters on values, for example, or if it did, the performance would be horrible: because redis does not have this.
Most RDBses follow most of ANSI SQL, so on the surface, they appear very similar, and a lot of times a developer never gets to the pieces that differ so much that either the ORM does not support it, or that the ORM gets terrible performance issues when using this.
More practical: if you use `ltree` or `GIS` features, you are now bound to postgres; which is why most ORMS don't offer support for this; or if they do, they only offer support for it when using the underlying engine (in other words: you cannot use e.g. sqlite anymore). Maybe that is fine and you can avoid using such "database specific features": good for you. But they are there, and are begging to be used; so please don't cludge around with two `BigDecimal` columns `lat:` and `lon:`: you are going to run into problems that have been solved for decades. Please just use PostGIS instead, and accept that this means you are now locked to postgres: you are going to thank yourself later.
The parent comment mentioned GIST indexes; I'd add partial indexes and triggers as two features that can make a huge difference when used even in a very few places in an app (which is what we do in Zulip, on PostgreSQL.)
It matters for production sure if you start getting slowdowns and you can have migrations that do that. But for cranking out APIs on DRF, say, it matters a lot less than you think it does.
I think for us it's the case that all the fancy DBMS features we rely on in the core functionality of the app are pure optimizations. So if we wanted to run tests on SQLite, that'd work fine except for when testing the parts of the app that happen to rely on those fancy DBMS features that aren't only optimizations.
But I'd consider that a pretty unstable situation -- it'd mean that we had one test setup for most of the app, and then still needed another test setup (with Postgres) in order to test some parts of the app. When I've heard from people working on apps that use different DBMSes for test and production, generally they don't take this strategy and instead they just limit themselves to the lowest-common-denominator features that exist in both. You can totally do that (many people have!); but as berkes's original comment above said, if you do you're missing out on some really valuable features.
And if you ever want to use even a little bit of those fancy DBMS features, beyond pure-optimization index features, in some core part of the app -- boom, you can't test on the more limited DBMS at all.
And you should always dev/test/stage on the same database that you'll use in production.
It's one thing if you want to run postgres locally, that's fine, but who really wants to run Oracle on their laptop just to run unit tests, or to test an api locally?
You should be running the same database in test as in production anyway - there are heaps of problems you won't catch if you don't.
You should be free to use the features available, not waste your time optimising for databases that don't have those features but that you will likely never switch to anyway.