PostgreSQL 10 Is Going To Be Very Feature Rich

[jacob]

Logical replication is an area where MySQL was far ahead of PostgreSQL. Very happy with the decision to finally implement this feature. Still, it's too late. There are very little chances that PostgreSQL will eventually become a "default" database software.

"Default" for what? PostgreSQL will probably never become the default database for web apps but it doesn't have to. MySQL is perfect for that role. What it can do is offer a real alternative to potential new Oracle or DB2 clients who want to steer clear of the vendors' licence racket and abuse policies. It has been gaining traction on that market for some time now and with the substantial improvements going into each release, it may start making proprietary databases obsolete before long.

[niner]

PostgreSQL will probably never become the default database for web apps but it doesn't have to. MySQL is perfect for that role.

In what way is MySQL perfect for web apps? My web app needs transactional DDL. MySQL doesn't support that at all.

To this date, MySQL does not even support most basic SQL constructs like check constraints. From the docs: "The CHECK clause is parsed but ignored by all storage engines.". So you think the database can e.g. prohibit creation of overlapping entries in your time tracking software? Nope. Not a chance.

What about deferred constraints which would e.g. allow you to change an id column of rows that are referenced by other tables? Not supported.

Common table expressions, most notably needed for recursive queries in tree data structures? If you're into running development versions of your DBMS, you may try them. Have they seen much bug fixing and performance optimization there? Of course not.

Those are just some of PostgreSQL's features that are missing in MySQL and that we actively use in our web apps. So I really don't understand where your "MySQL is perfect for that role" is coming from.

[uid313]

To be correct: Your distribution clutters /usr/bin with PostgreSQL binaries. The developers recommend to install PostgreSQL to something like /usr/local/postgresql/<version>... This way you can have multiple versions installed and running.

Oh, I use Ubuntu.
I guess that uses the Debian packaging.
Where do you find this developer recommendation?
And do you know why Debian doesn't follow it?

[uid313]

Not only pg* binaries, sometimes even things without any prefix, like 'createdb'.

Oh, yes that too!
I forgot about that. Yes, very annoying and confusing.

[cj.wijtmans]

"Default" for what? PostgreSQL will probably never become the default database for web apps but it doesn't have to. MySQL is perfect for that role. What it can do is offer a real alternative to potential new Oracle or DB2 clients who want to steer clear of the vendors' licence racket and abuse policies. It has been gaining traction on that market for some time now and with the substantial improvements going into each release, it may start making proprietary databases obsolete before long.

MySQL is on its way out. Looking at mongoDB now.

[bpetty]

Logical replication is an area where MySQL was far ahead of PostgreSQL. Very happy with the decision to finally implement this feature. Still, it's too late. There are very little chances that PostgreSQL will eventually become a "default" database software.

For a simple schema with few relationships and not a lot of multi threading that could get you into dead-lock / blocking situations, you are right... try MySQL. MySQL is a great database for certain use cases. However, it can't really be used effectively under many "enterprise" workloads because of its naive locking implementation. PG finally made the investment in 9.3. I was working for an Enterprise Content Management company that wanted to move their default / embedded database to MySQL. Huge failure. Oracle keeps MySQL highly tuned for simple jobs you'd see on the web, but wont allow it to actually compete with its enterprise offering. PG also has some of the best JSON support I've seen... giving it an edge for web services, etc.

The thing I don't like about PG is that it has an old Apache style process model where it forks up a process per connection... mirroring what Oracle was doing until recently.
They really need to architect that entire piece. Its been a while since I've looked at PG, but they also had one of the worst db backup / recovery solutions I've seen.

[cj.wijtmans]

Mysql is terrible with deadlocks its highly annoying i will see how mongodb goes and completely patch out the need for locking if it ever becomes an issue

[-MacNuke-]

Where do you find this developer recommendation?
And do you know why Debian doesn't follow it?

The official (= from the PostgreSQL developers) yum packages for RedHat/CentOS are doing it that way and the default prefix (and exec-prefix) of the configure script is "/usr/local/pgsql". And the documentation itself https://www.postgresql.org/docs/9.6/...all-short.html and following pages.

Also the pg_upgrade script expects you to have both, the old and the new version running in parallel. Not possible if PostgreSQL is installed to /usr/bin directly.

Maybe Debian does not do it, because there will be no silent major upgrade of the database.

[uid313]

The official (= from the PostgreSQL developers) yum packages for RedHat/CentOS are doing it that way and the default prefix (and exec-prefix) of the configure script is "/usr/local/pgsql". And the documentation itself https://www.postgresql.org/docs/9.6/...all-short.html and following pages.

Also the pg_upgrade script expects you to have both, the old and the new version running in parallel. Not possible if PostgreSQL is installed to /usr/bin directly.

Maybe Debian does not do it, because there will be no silent major upgrade of the database.

The culprit seems to be the postgres-client-common package.

http://packages.ubuntu.com/zesty/postgresql-client-common

See the list of files: http://packages.ubuntu.com/zesty/all...ommon/filelist

I don't like this.