All of these differences are actually assets for the embedded DB market. They could be fixed, but you would end up with a database that was a winner in neither space.
I think the killer problem with SQLite for businesses is that it essentially locks their data inside the application. With a full SQL server, the data is trivially exposed for use / integration with other systems.
http://www.oracle.com/technetwork/database/berkeleydb/overvi...
But, I haven't seen nearly as much discussion/declared-usage of this as I would have expected, given the benefits of 'SQLite API way beyond prototype/single-user scale'.
Does anyone have experience with it? Any theories why it isn't so widely used/known?
Note: Personally I would pick PostgreSQL any day, unless developing an embedded application.
1) Very limited ALTER TABLE support. 2) Very limited JOIN support. 3) No real multiuser/multiprocess concurrency support. Limited concurrency in-process with WAL. 4) Poor query optimizers, compared to PostgresSQL and even MySQL. Poor index analysis in complex queries.
4 is really a big one. It's surprisingly easy to hit situations where SQLite is orders of magnitude slower than real databases, fails to make proper use of available indexes to narrow range queries, does terabytes more write traffic than was necessary, etc. And unlike MySQL/PostgresSQL, the query planner inspection tools are horrible, too.
On top of that, some SQLite features (R-tree, slightly less bad index analysis, ...) must be enabled and aren't compiled in by default. This complicates deployment.
I was simply stating that SQLite is also over looked by corporate America.
None of your reasons would impact, say, a department running their departmental "internal" blog on SQlite.
Why small companies get talked into running MSQL or Oracle or MySQL is beyond me. And even if (and that's a big IF) they needed more "power", there's Postgres.
I'm explaining you that SQLite has big, very real limitations that any of those alternatives are a good workaround for. And as for Postgres, that's just one more argument never to use SQLite. So how can it be "underestimated"?
SQLite make sense in one single situation: if you absolutely need an embedded database. A corporate blog is not one of them. SQLite isn't used much because it's limited compared to the alternatives.
Of course, that's my speculation, but what's the downside to going with PostgreSQL/MySQL in the first place unless you never intend on getting bigger?
And even if you have no plan of becoming big (e.g if you are in business to business) I see little reason to pick SQLite since PostgreSQL can do everything SQLite can and much more so you never need to worry about outgrowing it.
SQLite is fast, small, portable, easy & simple to maintain and backup, AND reliable. And unless you are running a high traffic site (or application) it could handle everything a small (even medium) business would need.
Why small companies get talked into running MSQL or Oracle or MySQL is beyond me. And even if (and that's a big IF) they needed more "power", there's Postgres.
PS: Sorry for hijacking this thread. I'm a big fan boy of both SQLite and Postgres.