News for hackers. Stuff that matters.
Re:Postgres 9.2 will feature linear read scalability up to 6... by crag on Tuesday April 03, 2012 @05:25PM (#3794896)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by justinsb on Tuesday April 03, 2012 @05:54PM (#3794986)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by gojomo on Tuesday April 03, 2012 @11:24PM (#3796066)
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/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?

Re:Postgres 9.2 will feature linear read scalability up to 6... by jeltz on Tuesday April 03, 2012 @06:26PM (#3795118)
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.
Re:Postgres 9.2 will feature linear read scalability up to 6... by justinsb on Tuesday April 03, 2012 @06:48PM (#3795201)
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 ;-)
Re:Postgres 9.2 will feature linear read scalability up to 6... by jeltz on Tuesday April 03, 2012 @07:22PM (#3795320)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by gcp on Wednesday April 04, 2012 @03:59AM (#3796616)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by crag on Wednesday April 04, 2012 @04:20AM (#3796652)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by gcp on Wednesday April 04, 2012 @04:56AM (#3796730)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by jeltz on Tuesday April 03, 2012 @05:46PM (#3794956)
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

Re:Postgres 9.2 will feature linear read scalability up to 6... by ceol on Tuesday April 03, 2012 @05:56PM (#3794996)
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?

Re:Postgres 9.2 will feature linear read scalability up to 6... by jeltz on Tuesday April 03, 2012 @06:20PM (#3795095)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by Cieplak on Tuesday April 03, 2012 @05:45PM (#3794954)
"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

Re:Postgres 9.2 will feature linear read scalability up to 6... by lobster_johnson on Tuesday April 03, 2012 @05:57PM (#3795002)
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.
Re:Postgres 9.2 will feature linear read scalability up to 6... by jeltz on Tuesday April 03, 2012 @06:06PM (#3795036)
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