Postgres 9.2 will feature linear read scalability up to 64 cores (opens in new tab)

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.

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.

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

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?

"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

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.

MySQL is not just some toy database. It's used by a lot of big installations that need a lot of performance, massive replication etc.

Sure it has its issues, but the objections people have against it mainly seem to be prejudice from the MyISAM days along with not appreciating how it shines on the workloads it's optimized for.

It sucks at reporting queries, but it shines when you have a large cluster of machines, multi-level replication chains, and mostly do queries that end up being primary key lookups or primary key range lookups with relatively simple constraints. That's the sort of thing you're likely to do for most of your traffic with any database once you scale up.

PostgreSQL also didn't have some of the features that made MySQL really fast until relatively recently, e.g. being able to entirely resolve a query on indexes without ever looking up the actual data rows.

I'd say the biggest problem MySQL has at scale is that replicated changes are applied in a single thread whereas updates on master servers are multi-threaded.

I'm very excited by recent improvements in PostgreSQL, and I wish it were the database I worked with professionally, but don't be so quick to dismiss MySQL.

> why doesn't PostgreSQL see better adoption from the big player (Google, Amazon, Facebook, etc.)

This is speculation on my part, but I believe its because those companies have huge engineering organizations, and see their engineering talent as a competitive advantage. So their priorities are very different from other large enterprises.

Organizations like Google, etc., are going to have huge architectural diagrams, and then use whatever tools fit most nicely and perform the best as a component of that architecture. And they have the engineering resources to shoehorn it in there, and work around all of the bugs, misfeatures, caveats, and usability problems.

In other words, such companies are never looking for a complete system, because they are the ones building the complete system.

But for organizations where engineering talent is more of a supporting role, even at very large enterprises, the equation changes. Those companies simply can't afford to hire google's engineering team and put it to work in a supporting role. So these organizations are looking for something a little more complete, safe-by-default, extensible, adaptable to their environment, robust, low-maintenance, etc.

I believe it's a big mistake to misjudge what kind of company you are. For instance, blindly following Google's technical choices may be a disaster if engineering is not the central focus of your business.

Speaking only in broad generalities, it's because PostgreSQL was not suitable for large scale businesses until lately. The lack of replication, limited performance on multiple cores, etc. drove businesses to MySQL/Oracle in the past, and inertia keeps them there now.

> But what I never understood was why doesn't PostgreSQL see better adoption from the big player (Google, Amazon, Facebook, etc.)?

An interesting selection of companies given that all three are known for their use of home-grown databases (BigTable, Dynamo, Cassandra) for their primary offerings that are not of the SQL variety at all.

Though I think it is still a good question. It may have something to do with the ease of setting up MySQL when you are a young startup trying to get something working as quickly as possible, leaving it often hard to justify a change after you've hit the big leagues.

Postgres was just not popular enough, I think; it's still considerably less popular than MySQL, and familiarity and operations counts for a lot. So I think the number of "marquee" names falls about in line with what one would expect. Facebook is very nearly out of the running because of its PHP lineage, Amazon has very close ties to Oracle, but Google could probably have easily gone either way, but MySQL is popular, so it probably got there first.

MySQL gets used because phpmyadmin gives people an easy stepping stone to get started, and then it keeps getting used just because it's what everybody is familiar with.

PostgreSQL 7 and earlier kind of sucked regarding performance. Noticeably that is, especially compared to MySQL. With PostgreSQL 8 and on, that changed. Unfortunately a lot of those well known projects started before PostgreSQL 8 came a long and so it was sort of inappropriate for the level of performance they required.

Heroku?

There is another change of note in Linux 3.2 which could matter PostgreSQL, the major changes to how writebacks of dirty pages works.[1] Now in your case the changes where mostly to read perfromance so I do not think the writeback changes mattered here.

1. http://kernelnewbies.org/Linux_3.2#head-fbc26b4522e4e990a9ea...

This is going to sound pathetic and off topic, but do you have pointers toward any good materials on database performance benchmarking? Your screenshots display exactly the kinds of presentation I am after, yet I've never managed to progress beyond windows performance monitor and a pile of unorganized spreadsheets. I keep getting more and more DBA work dumped on me and it seems like somewhere I've missed the principles of the art of DBA (if there are any). Other than some Celko and Date, everything I come in contact with has market-speak and corporate buzzwords written all over it.

That's a tremendous improvement. Obviously, I don't know the particulars of your situation but are you planning to update to more current hardware? The 5300 Cloverton was 65nm process and 2006 era. I'd be curious about the effect of using the 3.2+ kernel on the new e5-2600 Sandybridge process.

what distros are offering a 3.2 kernel?

Shane, I think most of the complaints about postgres at said company were made out of a pre-existing bias towards the expensive commercial solution, rather than against the (at the time, admittedly) somewhat less capable FOSS project. When I was asked about vertical scalability, I told them "16-20 cores right now, but at the rate they're improving things, it'll be 32, and then 64 in the next 3-5 years." Nice to see things tracking so closely with my predictions.

The llseek scalability issue was caused by the Linux kernel taking a lock on a data structure when no such lock was necessary. While it is possible that FreeBSD has the same problem with llseek, I see no reason to assume that is the case.

FreeBSD probably works as well as Linux >= 3.2 when it comes to llseek, but there might have some other scalability issue which is not present in Linux.

EDIT: Here is a link to the Linux kernel patch, PostgreSQL uses SEEK_END to check the file sizes.

https://lkml.org/lkml/2011/9/15/401

Oracle tends to be pretty opaque in their pricing, and part of that is because with any sale this big, there's always going to be a lot of negotiation.

Sure, it's going to be expensive, but only schmucks pay full price for a 64-core license.

Still, it's good to see the best open source database out there delivering cutting edge performance. Great work!

On HN anything that EC2 doesn't provide is considered exotic. And cheap servers still only have 16 cores/32 threads.

The number of places that run e.g. 4U $10,000 HP servers with 4 socket, 16 core Opteron servers is reasonably low I think.

Maybe the requirement is Oracle or SQL Server because the client has the resources (DBA, support contracts, etc) to support those platforms in house?

The bulk of the fixes were in the locking in PostgreSQL though, and before those fixes were made the llseek problem did not appear to many since in almost all cases people hit a bottleneck in the PostgreSQL code before hitting the Linux Kernel one.