
Reasons to choose PostgreSQL 9.6 - postila
http://blog.taadeem.net///english/2016/10/01/6_reasons_to_choose_PostgreSQL_9.6
======
csbubbles
I love PostgreSQL, regardless of the version as far as it's >= 8. Super
efficient and reliable RDBMS yet less "enterprise" that Oracle. I always
wondered why MySQL gained more popularity.

~~~
evanelias
MySQL's replication story has been mature for substantially longer than
Postgres's. ~10 years ago, most of today's biggest social networks and other
high-volume OLTP sites were starting to scale. Replication is essential and
there was really no contest at the time. (EDIT to add specifics: pg added
built-in replication in 9.0, released only 6 years ago.)

Today the majority of the largest sites on the internet use MySQL either as
their primary database layer or at least as an essential storage system:
Facebook, Google, Twitter, LinkedIn, DropBox, Alibaba, YouTube, WordPress.com,
Wikipedia, GitHub, Yahoo, Yelp, Pinterest, Etsy, Tumblr, Flickr, Uber, Box,
Booking.com... and meanwhile I keep hearing about more moving from pg to mysql
partially or entirely (Instagram, Lyft, etc)

The result is that the MySQL ecosystem is huge. There are multiple
distributions/branches, many third-party tools, expert consulting companies,
knowledgeable people to hire. If you're scaling a high-volume OLTP
site/app/product MySQL is a very reasonable choice.

Postgres is a great database, and is a better choice than MySQL for many
applications. But some people like to think it's strictly better than MySQL in
absolutely every way and every conceivable situation -- this is demonstrably
not the case.

~~~
feld
MySQL's replication is not mature. There are too many edge cases / pitfalls
where it doesn't replicate to slaves. It requires you to monitor for these
situations and build systems to work around it. I don't know why anyone thinks
this is OK.

Postgres is late to the replication game but they have worked hard to make
sure that replication doesn't have surprises.

The character set is another nightmare that surprises novice MySQL admins...
and then the fact that it allows schema operations to destroy data by
default...

~~~
evanelias
Can you cite examples in recent versions? In my experience, the defaults in
recent versions (mixed or row-based replication, strict sql mode) are quite
sane and prevent most historic problems.

I don't have enough pg experience to make a valid comparison, but I have
worked extensively on the largest mysql environment in the world (among many
others), and can say that properly-configured mysql replication does not
encounter the problems you are describing.

Yes, there are a lot of pitfalls for novice admins to avoid. That's true of
all databases to some degree. MySQL may well have more pitfalls than others,
but it also has a much wider net of experts who can help you avoid these
pitfalls.

Anyway, by "mature" I was also referring to the sheer number of solutions
offered, most of which are now battle-tested by many large users. Out-of-the-
box in the latest version you get 2 types of logical replication (and a third
type mixing the two), 3 options for level of synchronicity (async, semi-sync,
and now sync via the new group replication), bidirectional replication, and
multi-source replication. And there are additional third-party solutions for
other synchronous replication setups (e.g. Galera), and even physical
replication has been implemented internally by both Amazon and Alibaba.

~~~
feld
Looks like floating point values still aren't handled:

[https://dev.mysql.com/doc/refman/5.7/en/replication-
features...](https://dev.mysql.com/doc/refman/5.7/en/replication-features-
floatvalues.html)

This is scary

[https://dev.mysql.com/doc/refman/5.7/en/replication-
features...](https://dev.mysql.com/doc/refman/5.7/en/replication-features-
transaction-inconsistencies.html)

These look like something rare, but scary to those who don't know about them

[https://dev.mysql.com/doc/refman/5.7/en/replication-
features...](https://dev.mysql.com/doc/refman/5.7/en/replication-features-
auto-increment.html)

I used to hit issues with replication and IF EXISTS and it was a nightmare:
[http://bugs.mysql.com/bug.php?id=77684](http://bugs.mysql.com/bug.php?id=77684)
... glad to see they (allegedly) fixed it

edit: I don't have anywhere near the experience you do with MySQL, but the
odds of the average web developer / sysadmin configuring it properly is
unlikely. The advantage of Postgres is that it protects your data first and
there are much fewer foot-shooting options.

~~~
evanelias
First link: Floating point values replicate fine. The manual says you may have
problems if your master and replica are running different _computer
architectures_ , or were built using different compilers (and you're compiling
MySQL yourself from source). Both situations are exceedingly rare.

Second link: Despite the admittedly scary wording, these things only impact
two scenarios: either you're using MyISAM, and/or you're using MySQL 5.7's new
LOGICAL_CLOCK multi-threaded replication but with a vital safety option
disabled. Multi-threaded replication is not enabled by default anyway, and
only users with extremely high write rates will ever need it.

Third link: these are all edge-cases relating to statement-based replication,
which is no longer the default binlog format as of MySQL 5.7. Even with
statement-based replication, these are rare; in 13 years of working with MySQL
I've personally never encountered any of these specific issues.

Overall: I sort of get your point, yes there's a lot of knobs. But none of the
things you've mentioned affect 99.9% of people running MySQL in the first
place. MySQL replication works quite well out of the box, but provides a lot
of options for advanced users to handle special cases. If you play with those
options without knowing what you're doing, yes, you'll shoot yourself in the
foot. That's true of most complex computing infrastructure.

The defaults in MySQL used to suck. They don't anymore, and haven't for a
while.

------
akurilin
Can't wait for RDS to make this available <3 Probably around 9.6.2 I'm
guessing, if 9.5 is to be used for reference here.

------
known
[https://en.wikipedia.org/wiki/Comparison_of_relational_datab...](https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems)
has better information :)

------
crypto5
Please, please add transparent compression like most of the other RDBMSs have
for many-many years already..

~~~
dijit
Anything large enough to get into TOAST tables is compressed, what are you
hoping to achieve with compression? It neuters your ability to do a scan on
disk and doesn't help in memory anyway.

Not saying PostgreSQL shouldn't do it, just curious what benefits there are.

~~~
crypto5
TOAST means that individual cell/row needs to be extra-large to trigger
compression. Compression can obviously save a lot of disk space, and also
reduce IO traffic which benefits performance.

> It neuters your ability to do a scan on disk

Sorry, I didn't understand why is this..

~~~
Jweb_Guru
Compression is not really that useful except on large values or large numbers
of values. Other than column stores, I don't think most storage engines
actually compress the _data_ (as opposed to indices) across rows (because it
would slow down reads and writes dramatically) so there's really no point in
compressing tiny strings. Page-level compression _might_ be useful, I guess,
depending on your use case.

~~~
crypto5
> I don't think most storage engines actually compress the data

Most major RDBMs (Oracle, MS SQL Server and even MySql) support page level
compression.

> because it would slow down reads and writes dramatically

Not really, modern compression algorithms (e.g. zippy) have much faster
decompression/compression speed than IO speed even for SSD drives, and having
smaller IO traffic due to data compression can make actual performance higher
with compression. Also you can fit more compressed data into FS cache, which
again reduces IO traffic.

> Page-level compression might be useful, I guess, depending on your use case.

Sure, here are some examples:
[http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-...](http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-
server-2008-page-compression-compression-ratios-from-real-world-
databases.aspx) and
[http://sqlblog.com/blogs/linchi_shea/archive/2008/05/16/sql-...](http://sqlblog.com/blogs/linchi_shea/archive/2008/05/16/sql-
server-2008-page-compression-performance-impact-on-table-scans.aspx)

