undefined | Better HN

0 pointsgcp14y ago0 comments

Reasons not to use SQLite?

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.

0 comments

2 comments · 1 top-level

crag14y ago· 1 in thread

And where did I ever say one should use SQLite over Postgres. I wasn't comparing the two.

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.

gcpOP14y ago

You stated:

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.

j / k navigate · click thread line to collapse