Ask HN: What keeps you off of Postgres?

https://news.ycombinator.com/item?id=7272605

by moron4hire • 12 years ago

16 15 12 years ago

news.ycombinator.com

In my mind, the future of relational databases is Postgres, in the same way that Linux now dominates the server market. Or rather, I want for something like that to happen, and the only viable candidate seems to be Postgres right now.

What keeps you on <insert RDBMS here>? Is it "our developers only know X"? Is it, "the data would be too hard to migrate"? Is it, "the queries would be too hard to rewrite?" Something else entirely?

What about manpower? Would you be willing to pay a consultant to make a clean sweep through your code base and servers and just make the magic happen?

allendoerfer• 12 years ago

MySQL works for me and switching to Postgres would cost time and energy, which I like to spend on things actually making money.

That said, I have encountered some shortcomings of MySQL here and there (Constraints) and I can totally see how Postgres would be "nice to have", but I feel, that I am just not operating at a scale, where it really matters.

Edit: Typo

sheff2• 12 years ago

Postgres is a fantastic database.

That said, I've also used Oracle quite extensively in the past when clients have needed it and it has some features that would be great to have in Postgres - as well as some features which we won't see in Postgres as they don't match the Postgres philosophy. Lack of one or more of the features below have driven the choice for various clients to use Oracle despite the cost, so having them would help grow the Postgres ecosystem.

Features in Oracle I'd like to see in Postgres :

1) Flashback. This name encompasses lots of ways to see data as it existed at a previous point in time.

If you drop a table, make a big (wrong) change etc, being able to flashback to just before it is brilliant. Or if you have a multi-terabyte sized performance test database, running a test and then a flashback to before the test is much quicker than restoring from a backup.

2) More robust partitioning - Postgres is quite lacking in comparison here.

3) Better backup options. PG really needs an in core incremental backup to start with, with something like Oracles block change tracking for backups thrown in to do quick incremental backups of a large database.

4) Replication - the lack of something like Oracles switchover which is used in a lot of architectures where you have a application and DB replicated across two sites, with one site being the active one at a point in time. When you need to take one site down for whatever reason, in Oracle you can just do a planned "switchover" to the replica DB and then switch back whenever you want without having to do anything as the DB knows it was a clean switch.

5) The diagnostic and performance monitoring built into Oracle are more extensive than those in Postgres.

The one other really useful feature Oracle has which will likely never be in Postgres is RAC (Real Application Clusters) which lets you create a multi-node active-active DB cluster quite easily.

AlterEgo20• 12 years ago

3) Better backup options.

Already exist as "Continuous Archiving". Was done by third party tools in version 8, part of the core in 9. Details: http://www.postgresql.org/docs/current/static/continuous-arc...

4) Replication

Master - slave is in core of version 9. Allows fast switchover, but is somewhat harder to swith back.

RAC

Logical replication is already in 9.4 . And with logica replication we might soon see full master - master replication in core postgres.

sheff2• 12 years ago

Continuous archiving (WAL) is something quite different from incremental backups.

Incremental backups involve taking a full backup once in a while and then making interim backups of the changes since the base backup. WAL ( or archive logs in Oracle terminology) are still used for point in time recovery for changes that have occurred since the last incremental backup. One advantage is that if a row is updated 100 times between the base and incremental backup, you will only essentially be backing up the last change in your incremental backup versus WAL which will have all 100 changes. For big DBs, this can be very handy.

"somewhat harder to swith back"

Thats my point exactly. In Oracle switching between primary and standby is easy and you can do it back and forth as needed with no need to worry about rebuilding the database. In Postgres this is not yet the case, but most of the infrastructure is there already so its surprising it hasn't been done.

I'm also looking forward to logical replication. RAC is different however from master-master replication. Essentially its a specialized cluster with storage thats shared between all nodes in the cluster (so there is no replication going on) with "cache fusion" to share in-memory data between nodes .

PG lets the OS do the stuff it does best versus RAC which is largely a OS cluster solution specifically tailored for a multi node DB - so its unlikely (in my opinion) that the PG developers will ever produce anything similar.

ohsnap• 12 years ago

To do a clean sweep you really need a 'order of magnitude' type of justification ... some benefit that completely justifies the risk and time of changing platforms.

Postgres does a lot of things better than Mysql, but unless you have a really unique data problem to solve (perhaps say a robust spatial database) it's hard to switch midstream.

taf2• 12 years ago

What are postgres solutions for HA? Mysql has mmm and more recently galera... Percona backup is good for backup as well...

staunch2• 12 years ago

For a number of use-cases Linux is technically and practically superior to Windows and OSX. For servers there is essentially no viable competition. Postgres by comparison has a hundred viable competitors and very few exclusive killer features. It's a free Oracle alternative in an age where people don't even want Oracle.

moron4hire2• 12 years ago

Yeah, but that was sort of my point of the hedge in my original comment. Linux wasn't always clearly superior. It took the dedication of early adopters to improve it so that we have a thing today that is great. I think Postgres could be that for relational databases, given time.

staunch2• 12 years ago

I'd argue Linux was superior almost from the start. As of around maybe ~1996 it became a viable alternative to Solaris AND it was entirely free and open source.

dventimi• 12 years ago

Breaking the question down into parts, so that Postgres is compared one-on-one with each of its potential competitors, consider Postgres-vs.-MySQL. After all, it's plausible that one of the first decisions you make is whether to use a proprietary database or an open-source database. If the former, then you're probably deciding among Oracle, Microsoft, and maybe IBM. If the latter, you're probably deciding between MySQL and Postgres. So let's concentrate on the latter for a moment.

Why do people choose MySQL over Postgres?

I don't know the answer to that, but I was TOLD by a DBA colleague of mine who believes Postgres to be technically superior that the reason companies choose MySQL is because of the commercial support. Though it's open-source, MySQL evidently is in some sense "owned" or at least "sponsored" by Oracle, and if you go to www.mysql.com you'll find that you can purchase various services (support, training, etc.) directly from Oracle. Contrast that with PostgreSQL which even today appears to be more a house divided. At www.postgresql.org, you're presented with a list of third-party consulting organizations. I'm not positive, but I would be unsurprised if that made corporate managers a little nervous. "No one was ever fired for buying IBM." as the saying goes.

Of course, this is just conjecture on my part. Perhaps someone with better intel can comment.

deadfall• 12 years ago

For me, I use MySQL over Postgres because it was easier for me to configure and set up. I know MySQL much more coming from PHP and now I use Python/Django. I spent a few hours trying to setup Postgres and it would not install properly with Vagrant and I couldn't find really any helpful articles. I said fuck it, and installed MySQL on the VM in a matter of minutes and was up and running. Again, I am familiar with MySQL and got a production database on Amazon RDS pretty quickly.

moron4hire2• 12 years ago

Linux was the same way. At least from my perspective, Red Hat put Linux on the map for me. For a lot more people, that was Canonical. Given the right sort of organizations, having a lack of a single, explicit owner isn't necessarily a bad thing.

collyw• 12 years ago

MySQL is like PhP. Its almost always there, and no install hassles. Postgress takes a bit of farting about initially to get set up with an initial user. Thats probably enough to put a fair percentage of potential users off. I think thats why most go for MySQL.

AznHisoka• 12 years ago

What keeps me off Postgres is hopefully something they just fixed today: http://www.postgresql.org/about/news/1506/

Binary replications to slave had a very nasty bug that would make your replica data corrupt at random moments of time. Apparently I was one of the unlucky few that encountered this issue time after time.

maxharris• 12 years ago

Postgres doesn't work out of the box with Meteor.

Ask HN: What keeps you off of Postgres?

Related Stories