Ask HN: What keeps you off of Postgres?
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?
15 comments
[ 2.5 ms ] story [ 47.1 ms ] threadThat 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
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.
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.
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.
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.
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.
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.