every time I read a PostgreSql vs MySQL post the only thing I can seem to take away from it is that DBA's really seem to hate the fact that MySQL has made them obsolete and really want you to think you should switch to a platform where they are necessary.
Postgres is definitely a pain in the butt to properly setup in a network, in particular if you have never done it before. (And just talking about non-cluster use)
However, once you know the steps necessary to setup a postgres server, it's a piece of cake. In fact it's even fun because Postgres is a software that comes in pretty much the same packaging in every version. And I can even top that: with pgAdmin it has a powerful, consistent and stable tool to manage databases. (MySQL seems to be miles away from that)
I think the impression that Postgres is more difficult than MySQL roots in the fact that it's more difficult to install at home. (Why so many installation steps? MySQL is basically just installing the package, changing pw and done?)
I worked around 2 years with Postgres as a developer and I loved it. Very stable, very solid, many features and no surprises.
MySQL on the contrary seems like a toy db, at least when you want to do a lot of relationship stuff with foreign keys etc. It feels really awkward that outer white-spaces have no meaning, there is no boolean type and that the admin tools out there seem to be really immature.
Having to work since nearly two years with MySQL, I find it still painful. If you don't store Petabytes of data and don't use it for a highly frequented website, Postgres is probably your choice.
> I think the impression that Postgres is more difficult than MySQL roots in the fact that it's more difficult to install at home. (Why so many installation steps? MySQL is basically just installing the package, changing pw and done?)
I think much of the blame here can be put on the people writing installation guides on the Internet. The guides often suggest setups more suited for networked databases than for a local database. When I install PostgreSQL for development machines it is just these two steps.
After that I can create whatever databases I like.
So it is the same number of steps to setup a PostgreSQL and a MySQL database. I admit it took me some time to figure out that this was the simplest way, and I have yet to see a installation guide which suggests this method.
First, I get frustrated whenever I have to use MySQL. However, if you think the MySQL tools that come with the software seem immature you should try Oracle's. There's a reason why TOAD for Oracle is the de facto admin tool and why it is not made by Oracle.
I worked around 2 years with Postgres as a developer and I loved it. Very stable, very solid, many features and no surprises.
Every database has surprises. Oracle has fun with transactions and DDL, null handling of string types, etc). PostgreSQL has surprises galore when dealing with collections and no these are not well documented.
For example the following two are handled very differently by PostgreSQL because nobody can agree on what correct behavior is for collection tables:
CREATE TABLE foo (
bar int not null,
CHECK (bar > 0)
);
CREATE TABLE bars (foo foo);
INSERT INTO bars (foo) values (row(-1)), (row(null));
The above is allowed, but:
CREATE DOMAIN baz int not null check (value > 0);
CREATE TABLE foo (bar baz);
CREATE TABLE bars (foo foo);
INSERT INTO bars values (row(null)); --not allowed
INSERT INTO bars values (row(-1)); --not allowed
The thing is that in collections domains are not handled like column types. You'd think this was intentional but if you:
ALTER TABLE foo ADD is_bar NOT NULL DEFAULT true;
Postgres will happily refuse to do so, saying it can't follow this if foo is used as a type on another table. There is obviously a bug here, but as discussed repeatedly on various email lists, nobody can agree on what needs to be done about it.
Though if you are working with collections I suppose you can assume you are going to find all sorts of surprises. As I say multiple table inheritance is far better as long as you can think in terms of composition instead of OO inheritance.
My first job was at a company that made the UK's fastest growing list something like five times in a row. Eight years after being founded by two guys in their college room it was sold for GBP100m.
Prior to that sale we never had a dedicated DBA. Our two and a half sysadmins kept MySQL up to date and handled the replication setup for teams that didn't want to do it themselves (which was perhaps 2/3). Table layout and query optimization were left entirely up to developers.
It worked far better than the setup at either of my subsequent jobs, which have been postgres and oracle with dedicated DBAs.
I have one customer who has a couple of sysadmins do all that for both MySQL and PostgreSQL servers. If that's all you need, you probably don't need a dedicated DBA unless you are running Oracle and that's just because with Oracle your DBA can always find something to do.
If MySQL can be said to make DBA's obsolete because it doesn't take that much maintenance, Informix beat them to that by a few decades.
>If MySQL can be said to make DBA's obsolete because it doesn't take that much maintenance, Informix beat them to that by a few decades.
Right, but is that true for Postgres? If companies that use MySQL generally don't employ dedicated DBAs, and companies that use Postgres generally do, then it's fair to say this might result in some bias when dedicated DBAs compare the two.
My sense is that companies choosing to use Postgres are more likely to have dedicated DBA's before Postgres is deployed. In these companies the move to Postgres is often DBA-initiated.
In general, though, DBA can mean a bunch of things. It can range from a dev-ops kind of role to something like a sysadmin kind of role and a bunch of things in between. One thing I think we see industry-wide is that strict specialization in DBA tasks seems to be on the decline and for good reasons. It is a move I think from a strictly parts-oriented, details-centric operations approach to a big-picture, approach where the ability to communicate across teams is helpful.
Also regarding the BIG users of Postgres, the DBA-like people I have known who have worked there have been part-time DBA's and part-time C programmers doing things like porting Postgres to new platforms or building new replication systems. As far as I can tell, the dedicated "nothing-but-a-dba" is something that exists mostly waning.
I've either inherited or hired a mysql dba at my last four jobs. In fact I'd say its a close call as a young/small internet company grows whether they should hire their first dba before they hire their first sysadmin, as you're probably going to get more immediate wins out of the dba.
