

Ask HN: What keeps you off of Postgres? - moron4hire

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.<p>What keeps you on &lt;insert RDBMS here&gt;? Is it &quot;our developers only know X&quot;? Is it, &quot;the data would be too hard to migrate&quot;? Is it, &quot;the queries would be too hard to rewrite?&quot; Something else entirely?<p>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?
======
sheff
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
> 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...](http://www.postgresql.org/docs/current/static/continuous-
archiving.html)

> 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.

~~~
sheff
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.

------
dventimi
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
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.

------
allendoerfer
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

------
ohsnap
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
What are postgres solutions for HA? Mysql has mmm and more recently galera...
Percona backup is good for backup as well...

------
staunch
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.

~~~
moron4hire
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.

~~~
staunch
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.

------
AznHisoka
What keeps me off Postgres is hopefully something they just fixed today:
[http://www.postgresql.org/about/news/1506/](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
Postgres doesn't work out of the box with Meteor.

