7 ms·

To me, Postgres is the most underestimated database. Not sure if this is a bad thing...

• leftnode 15y ago
  I just switched from MySQL to Postgres for everything (as a result of seeing how powerful and stable it is at my full time job) and it is simply amazing. Easily one of the most impressive pieces of software built.
• ryandvm 15y ago
  Agreed. I get that MySQL is incredibly simple to set up, so I can sort of understand why people use it for pet projects or whatever. But what I never understood was why doesn't PostgreSQL see better adoption from the big player (Google, Amazon, Facebook, etc.)?
  • rdl 15y ago
    Heroku?
  • randomdata 15y ago
    > 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.
    • justinsb 15y ago
      They may be known for their home-grown databases, but they shouldn't be known for their use of them: Amazon's primary database is Oracle. Dynamo is used for their shopping carts i.e. for storing sessions; Memcache would probably work just as well. Facebook's database is MySQL, with sharding and Memcache. I was under the impression they stopped using Cassandra entirely? Google's business (advertising) is built on MySQL, as are many of their sites e.g. YouTube. I think the newer Google-developed sites (e.g. GMail, Reader etc) are indeed built on BigTable.
      • pooriaazimi 15y ago
        Thanks. I didn't know about Amazon's... Can you provide more links/sources on this matter?
        • justinsb 15y ago
          Here's a 2007 post: http://highscalability.com/blog/2007/9/18/amazon-architecture.html http://highscalability.com/blog/2007/9/18/amazon-architectur... In short (and filling in some of the gaps with my interpretation), they used "one big Oracle database" up until 2001. The webapps originally talked directly to the database (presumably with local caching), but they introduced an application tier later on. I'd imagine this was as much for data integrity reasons. As they outgrew the one big DB approach, the service approach also let them split their database along service boundaries. So each service still used one big DB, but there were many services. My understanding is that now there are hundreds of services, and each service is run by a team that can choose their own internal components. But each team is directly responsible for their uptime; if their service breaks the developers get paged in the middle of the night. Traditional databases are therefore still widely chosen; even if sexy technologies like Dynamo get the press. "Dynamo for show, Oracle for dough" to mutilate an old golfing expression :-)
          • pooriaazimi 15y ago
            Thank you.
      • haberman 15y ago
        > Amazon's primary database is Oracle. Dynamo is used for their shopping carts i.e. for storing sessions; Memcache would probably work just as well. What? Dynamo is a distributed, persistent, highly-available storage system with incremental scalability and advanced techniques for dealing with slow or unavailable servers. Memcache is a single-process daemon that vends an in-memory hash table via TCP. They are not even remotely comparable. I'm not dissing memcached, it's cool and useful, but its scope is far, far more limited. In particular, have you noticed that when you add something to your shopping cart on Amazon but don't buy it, it's still there months or years later? The data doesn't disappear just because some process that was holding the data in-memory crashes.
    • justincormack 15y ago
      Skype is a big Postres user, including contributing back.
      • einhverfr 15y ago
        It will be interesting to see how long it takes Microsoft to move it all to MS SQL. My guess is that it will take at least a decade ;-)
  • falcolas 15y ago
    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.
    • vidarh 15y ago
      The big deal for me: Upgrading Postgres between major versions requires a dump and restore or using upgrade tools, meaning potentially long downtime, unless you set up some really ugly replication solutions. I have servers I'm upgrading to 9.1 now, from 8.x, and we have wasted lots of time on it on some really hacky solutions because taking the downtime required to do an offline upgrade is just not acceptable. We'd have paid thousands to avoid that problem, as that's what the time spent on avoiding that downtime is costing us (well, our clients).
      • einhverfr 15y ago
        Look into pg_upgrade.
        • vidarh 15y ago
          It still requires the server to be taken down during the upgrade, and has a bunch of limitations that means it's still more cost effective for us to spend a few days on ugly custom hacks to avoid the pain.
          • fdr 15y ago
            The most obvious balm is a logical replication solution. Unfortunately those require non-trivial implementation, but it's something that is in its broad generalities not controversial for inclusion. Dimitri Fontaine has posted a large patch to implement a devilish component of that, DDL/Command triggers, receiving a lot of detailed review and attention: https://commitfest.postgresql.org/action/patch_view?id=768 https://commitfest.postgresql.org/action/patch_view?id=768 But I think a cohesive solution can only be realistically realized in releases >= 9.3.
  • fdr 15y ago
    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.
  • mhurron 15y ago
    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.
  • avar 15y ago
    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.
    • wisty 15y ago
      One of the big fundamental differences between MySQL and Postgres is that MySQL has "real" primary keys (the table is sorted by primary key), and Postgres primary keys are just an index with constraints. Neither solution is really better, but for some workloads MySQL will have an advantage here.
      • avar 15y ago
        Indeed, one of the main advantages of having the primary key indicate the layout of your data on disk is that you can optimize access to your data for the least amount of filesystem page cache misses by making the primary key be the column (or combinations of columns) that you know you're going to have predictable batch access to. Benchmarking that can be tricky for the same reason that benchmarking anything that reduces cache expiration can be tricky. If you reduce cache expiration your query might not benifit, but if you take a holistic approach to it and apply it to the whole database it'll benefit as a whole. Any big MySQL powered application is likely to make wide use of this. Which goes to show that doing any benchmark comparisons between databases is often meaningless. You can't benchmark the same schema/queries between databases because they'll be optimized for the database you started out with.
  • notatoad 15y ago
    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.
  • jeffdavis 15y ago
    > 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.
    • einhverfr 15y ago
      We are seeing that, slowly. It's a slow process because PostgreSQL used to suck, and MySQL used to be a lot easier to use. But this is changing as PostgreSQL has grown up.
• stesch 15y ago
  It won a lot of awards: http://en.wikipedia.org/wiki/PostgreSQL#Awards http://en.wikipedia.org/wiki/PostgreSQL#Awards
• 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.