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.
It's not really about what Django supports, but about what all the databases you're using support. If you run it in testing with SQLite, you are not going to have the same set of features for indexing, transactions, and so on as you do in production with, say, PostgreSQL.
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.)
Sure, but does it matter when unit testing code or developing the 95% where it doesn't?
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.
For that I guess it comes down to whether the features you're relying on are purely optimizations (like partial indexes, fancy GIST index types, etc.), or go to the semantics of how the code behaves (like triggers).
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.
I'm not familiar enough with Django, but assume it being very similar to Rails (Ruby).
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)
TBH, I can't tell if you're complaining about Ruby or Django here. They're two very different things with different methodologies. You can't complain about how Ruby does it's database in a discussion about Django.
Like I said elsewhere, Django offers a lot, and what it doesn't offer can always be added with raw SQL.
I was pointing out issues that stems from "supporting multiple databases", which are issues that are the same in Django, Rails (not Ruby), PDO and basically any other ORM out there.
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.
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.