4 ms·

Let me add another database that's "underestimated" (by mainstream corporate America): SQLite3. SQLite is fast, small, portable, easy & simple to maintain and

by crag 15y ago

Let me add another database that's "underestimated" (by mainstream corporate America): SQLite3. 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.

Cieplak 15y ago
"SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic." http://www.sqlite.org/whentouse.html http://www.sqlite.org/whentouse.html
jeltz 15y ago
SQLite even provides real MVCC (multi-version concurrency control) with decent read/write concurrency if you run it in WAL mode. Ran in WAL mode SQLite is a very competent database. http://www.sqlite.org/wal.html http://www.sqlite.org/wal.html
justinsb 15y ago
SQLite is a great embedded database, but is deliberately not designed for replacing a 'full' database server. It doesn't support highly concurrent usage, it can't be run as a network service, it has a comparatively weak type model. 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.
- jeltz 15y ago
  I would say when SQLite added the WAL mode they greatly improved the concurrency. In WAL mode SQLite has better concurrency characteristics than MyISAM which not too long ago was commonly used.
  - justinsb 15y ago
    Thanks for the WAL pointer; it does sound like concurrency is better now. Benchmarking concurrency against MyISAM is a bit like benchmarking reliability against MongoDB though ;-)
    - jeltz 15y ago
      Agreed, my point is that since people could (and still can) run mostly fine on MyISAM and MongoDB despite their lack of real concurrency, then SQLite in WAL mode should not have any problems for a small to medium website. Note: Personally I would pick PostgreSQL any day, unless developing an embedded application.
- gojomo 15y ago
  From the description and benchmarks, it seems that the 'SQL API for BerkeleyDB', as a drop-in replacement for SQLite, should provide an easy fix for many SQLite-using projects that hit barriers dur to concurrency. See: http://www.oracle.com/technetwork/database/berkeleydb/overview/sql-160887.html 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?
ceol 15y ago
I'd imagine these small companies dream of being big some day, so why would they invest the time to use SQLite when, should they hit it big, they would have to completely switch databases? 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?
- jeltz 15y ago
  I agree with the thought. While I really like SQLite as a piece of software I see little reason to use it over PostgreSQL unless I need an embedded database. 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.
lobster_johnson 15y ago
SQLite is indeed a very good piece of software. It's also a fairly idiosynchratic piece of software: For example, table schemas are essentially untyped, and you can assign any type of value to a column value. The lack of good support for date/time data is problematic. And the complete lack of support for GIS is pretty serious; for many people, PostGIS is the "killer app" for Postgres.
- jeltz 15y ago
  I agree with your general sentiment, but there actually exists a GIS extensions for SQLite, SpatiaLite[1], so there is no "complete lack of support for GIS". Still I think PostGIS is vastly superior to most if not all of its competitors. 1. https://en.wikipedia.org/wiki/SpatiaLite https://en.wikipedia.org/wiki/SpatiaLite
gcp 15y ago
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.
- crag 15y ago
  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.
  - gcp 15y 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.

by crag 15y ago

Cieplak 15y ago

"SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic." http://www.sqlite.org/whentouse.html http://www.sqlite.org/whentouse.html

jeltz 15y ago

SQLite even provides real MVCC (multi-version concurrency control) with decent read/write concurrency if you run it in WAL mode. Ran in WAL mode SQLite is a very competent database. http://www.sqlite.org/wal.html http://www.sqlite.org/wal.html

justinsb 15y ago

SQLite is a great embedded database, but is deliberately not designed for replacing a 'full' database server. It doesn't support highly concurrent usage, it can't be run as a network service, it has a comparatively weak type model. 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.

jeltz 15y ago
I would say when SQLite added the WAL mode they greatly improved the concurrency. In WAL mode SQLite has better concurrency characteristics than MyISAM which not too long ago was commonly used.
- justinsb 15y ago
  Thanks for the WAL pointer; it does sound like concurrency is better now. Benchmarking concurrency against MyISAM is a bit like benchmarking reliability against MongoDB though ;-)
  - jeltz 15y ago
    Agreed, my point is that since people could (and still can) run mostly fine on MyISAM and MongoDB despite their lack of real concurrency, then SQLite in WAL mode should not have any problems for a small to medium website. Note: Personally I would pick PostgreSQL any day, unless developing an embedded application.
gojomo 15y ago
From the description and benchmarks, it seems that the 'SQL API for BerkeleyDB', as a drop-in replacement for SQLite, should provide an easy fix for many SQLite-using projects that hit barriers dur to concurrency. See: http://www.oracle.com/technetwork/database/berkeleydb/overview/sql-160887.html 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?

ceol 15y ago

I'd imagine these small companies dream of being big some day, so why would they invest the time to use SQLite when, should they hit it big, they would have to completely switch databases? 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?

jeltz 15y ago
I agree with the thought. While I really like SQLite as a piece of software I see little reason to use it over PostgreSQL unless I need an embedded database. 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.

lobster_johnson 15y ago

SQLite is indeed a very good piece of software. It's also a fairly idiosynchratic piece of software: For example, table schemas are essentially untyped, and you can assign any type of value to a column value. The lack of good support for date/time data is problematic. And the complete lack of support for GIS is pretty serious; for many people, PostGIS is the "killer app" for Postgres.

jeltz 15y ago
I agree with your general sentiment, but there actually exists a GIS extensions for SQLite, SpatiaLite[1], so there is no "complete lack of support for GIS". Still I think PostGIS is vastly superior to most if not all of its competitors. 1. https://en.wikipedia.org/wiki/SpatiaLite https://en.wikipedia.org/wiki/SpatiaLite

gcp 15y ago

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.

crag 15y ago
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.
- gcp 15y 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.