
Why I Choose PostgreSQL Over MySQL/MariaDB - SunTzu55
http://news.dice.com/2015/03/19/why-i-choose-postgresql-over-mysqlmariadb/
======
randomsearch
Having used MySQL for every side-project and website I've ever worked on, last
month I finally switched to Postgres.

The main motivation was that MySQL doesn't support a native UUID datatype
(!!!) but, having switched, it seems that Postgres is much better thought-out
and designed. For example, it supports a wider range of types and I find it
less confusing to choose the right type compare to MySQL. It appears to
support unicode better. Its system of privileges and roles seems more logical.
Lots of small things like this add-up.

I'd definitely recommend checking it out.

~~~
collyw
For one problem I had recently, I found MySQL a better fit.

It supports unsigned ints which Postgres doesn't and it has bitcount built in.
I am aware these are probably rarely used, but I wanted them for what I was
trying to do (compare large combinations of DNA sequence). I tried
implementing it in Python / Numpy and Java and PG but MySQL was still the
fastest. (I am fairly sure if I spent more time, I could improve any of the
solutions, but for a similar level of effort MySQL (plus Python) won.

~~~
masklinn
Postgres does have a bit/varbit which may have been closer to your use case,
though you're correct that there's no native bitcount (assuming by "bitcount"
you mean "count the number of bits set to 1 in the field).

Also even with CHECK constraints being available, the question of unsigned
ints seems frequent enough that there's at least one extension implementing
unsigned types:
[https://github.com/petere/pguint](https://github.com/petere/pguint) (no idea
about the quality or production-readiness).

~~~
collyw
To be honest I could have probably used signed integers but conceptually
representing DNA sequence (4 possibilities at each position) was far easier to
think about that two's compliment. The idea was to make everything as small
and lean as possible, to get the maximum amount into memory, and it seemed to
work.

Bitcount / popcount / hamming distance as a CPU operation will have saved a
lot of CPU cycles over the PG method I tried.

You sound like you know Postgres fairly well. How feasible would it be to
write a CPU based popcount extension?

------
SwellJoe
"Licensing hassles". That's silly. 99% of people using MySQL or PostgreSQL
will never have to think about the license. LGPL is sufficient for making
software that interacts with the database without having any obligation to
provide source for anything _other_ than changes you make to the database
software. So, unless you're shipping the database itself as your product, you
don't need to care about the license of MySQL/MariaDB or Postgres beyond
knowing you can use them as a database for your products without encumbrance.

I consider PostgreSQL the better database most days, but license FUD is
counter-productive.

------
dijit
a binary comarison of features does not properly compare the technologies.

the implementation makes a large difference in the quality of the software and
it's use.. objectively you could compare a ferarri and a honda this way and
they would look comparatively similar.

postgresql isn't as feature complete as oracle.. but comparing it to mysql now
is almost a moot point.

if it's not going to kill your company- or you're making something new from
scratch- you're really doing yourself a disservice by not using postgresql.

there's more than a few reasons for this but a few that come to mind is:

* Sane Connector libraries (especially in C++ where mysql is inconsistent, lock prone and buggy)

* SQL standards compliance, not my pgsqlisms (there are, but they have namespaces which are obvious)

* MySQL has a habit of being massively inconsistent or not running in an expected way (input validation is very lacking, along with things like, changing column widths which irrevocably alters data)[0]

I mean, personally I'd rather not have a relational database which eats my
data.

[0] -
[https://www.youtube.com/watch?v=emgJtr9tIME](https://www.youtube.com/watch?v=emgJtr9tIME)

------
fnord123
> ANSI Standard Compatible: Tie

The ANSI standard was done in 1986. Since then there have been several ISO
standards updates and AFAIK MySQL has lagged significantly.

~~~
takeda
> Table Changes Without Locking > MyIsam uses table-locking to gain speed.
> That’s fine if many sessions involve reading; but when writing to a table,
> the writing session gets exclusive access and other sessions must wait until
> its finished. But PostgreSQl and InnoDB both use row-level locking…so again,
> it’s much less of an issue.

I am not sure I can agree with this one as well, but at least postgres does
normally not use row level locking anymore (in 10 years?) because of MVCC.

~~~
AlisdairO
Postgres does use row level locking, it just doesn't lock for reads - so you
still get write-write conflicts on the same row. The same is true for InnoDB.

------
mordocai
I didn't feel like signing up to comment on the article, but he's wrong about
the license.

It is rare that you are linking the database code with your code. Therefore,
the difference between BSD and GPL code will rarely, if ever, matter for a
database user.

If you think you'll have to modify the code of the database, the license would
matter.

~~~
tszming
You still need some client libraries to connect to the database:
[http://stackoverflow.com/questions/2038881/gpl-and-
libmysqlc...](http://stackoverflow.com/questions/2038881/gpl-and-
libmysqlclient)

~~~
spudlyo
Yes, and you you absolutely have to ship proprietary software that connects to
MySQL you can use libdrizzle[0] which is a BSD licensed cleanroom
implementation of the client library. Alternately you can go dig up the old
original public domain version of the client library that existed before Monty
got the clever idea of using fear of the GPL to sell more MySQL client
licenses.

The license issue is FUD.

[0]: [https://launchpad.net/libdrizzle](https://launchpad.net/libdrizzle)

~~~
tszming
It is not FUD. libdrizzle as you mentioned still lacking of MySQL 5.6 support.
(libdrizzle 5.1 stable version was released two years ago)

------
nine_k
While I generally like Postgres more, the comparison does not include
replication. It looks like master-master replication is still better supported
on MySQL (but I may be wrong).

Another important thing for me is table partitioning. Both databases support
it, but I'd love a comparison. MySQL seemingly covered a lot of ground in this
regard.

~~~
jasonlotito
A co-worker of mine actually wrote on replication in postgres recently:

[http://peter.eisentraut.org/blog/2015/03/03/the-history-
of-r...](http://peter.eisentraut.org/blog/2015/03/03/the-history-of-
replication-in-postgresql/)

~~~
spudlyo
Thanks for sharing, great article. I think most of the folks singing the
praises of Postgres over MySQL have never had to operationally support both
databases in complex multi-DC replication environments. The answer to many
replication issues with Postgres is 'rebuild the DB from scratch', which is a
terrible answer when you have large databases.

I thankfully no longer have to support Postgres in production, but I can still
vividly recall that terrible sense of dread I'd get in my stomach whenever I'd
receive a replication alert for a Postgres database.

~~~
nierman
what sort of "replication issues"? if you use archive_command and ship/rsync
wal files somewhere you will safeguard against falling too far behind (past an
arbitrary wal_keep_segments limit). Also, in 9.4 you have replication slots so
you can get rid of wal_keep_segments altogether: the master knows the
replication progress/status of each slave and keeps required wal files around
until they are no longer needed.

or were you referring to some other issue with failed replication? something
that mysql handles better/differently?

------
mattdeboard
Kind of glossed over the whole "CREATE INDEX CONCURRENTLY" thing in the "Table
Changes" section, no?

~~~
masklinn
"table change" looks to be changes to the table's data since it only mentions
table or row locking.

If it were about schema change, I don't think there's any way to talk the
result into a tie, that's really one of the things MySQL is absolutely
terrible at.

~~~
arenaninja
See, things like _these_ should've been in that article. The rest of it was
"everything is a tie except for JOINs but they've been working fine in MySQL
since 5.6.5 so I chose Postgres". I've personally suffered through hours of
deployment in a legacy MySQL application with a few million records. Thanks
for this tidbit, I'm that much more curious about Postgres now

------
nvivo
Having used a lot of sql server management studio, I got used it this kind of
UI where the focus is writing and executing sql. MySql has Devart Mysql Studio
that is similar and got me really productive with mysql.

Is there anything of similar quality to postgres nowadays? I user pgadmin
years ago, but it was quite crude in comparison even to Query Analyzer in sql
2000.

------
pathikrit
Is there a website like [http://www.pg-versus-ms.com/](http://www.pg-versus-
ms.com/) for this? pg-versus-my.com?

------
adrianlmm
How come no one ever tries FirebirdSQL?, it is awesome.

~~~
parfe
Write up an article about the pros/cons compared to mysql and postgres and
submit it.

edit: Seriously, the google results for firebirdsql vs postgresql are abysmal.

