
PostgreSQL 11.3 and 10.8 - oskari
https://www.postgresql.org/about/news/1939/
======
rtpg
For those stuck on older versions of Postgres, I highly recommend paying the
downtime to upgrade. Going from 9.x to 11 will get you a measurably large
performance gain for free.

~~~
dspillett
Out of interest (SQL Server guy mainly, so only partly keep up with what other
engines are doing), what changes significantly affect performance (without
making changes to your own code/configuration to make use of new features) in
10.x & 11.x?

~~~
ape4
It kinda bugs me that people say "SQL Server" to mean "Microsoft SQL Server".
I mean, there are other sql servers.

~~~
cstejerean
SQL Server is a registered trademark of Microsoft at least in the US and I’m
sure other jurisdictions. Using SQL Server to refer to Microsoft’s relational
database product is perfectly reasonable and pretty much every will know what
you mean. The generic term seems to be RDMS or some variation on that.

~~~
chc
Referring to a RDBMS as a "SQL Server" would probably confuse people. Besides
the fact that it's already the name of a specific product, the overall genre
of software doesn't even serve SQL.

------
eberkund
I maintain a couple of MySQL based applications. I don't really use any
features outside of "standard SQL" is there a reason to switch over to Pg? I
haven't used Pg before and usually default to MySQL.

~~~
kangoo1707
At my PHP-shop company, most projects are limited to MySQL 5.7 (legacy reason,
dependency reason, boss-likes-MySQL reason...). They are all handicapped by
MySQL featureset, and can't update to 8 yet. If they had used Postgres some
years ago, they would get:

\- JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as
Postgres)

\- Window functions (available in MySQL 8x only, while this has been available
since Postgres 9x)

\- Materialized views, views that is physical like a table, can be used to
store aggregated, pre-calculated data like sum, count...

\- Indexing on function expression

\- Better query plan explanation

~~~
Macha
Also suffering under mysql 5.7 here and agree. Also even stuff like CTEs/WITH
make queries more readable and composite field types like ARRAY are still
missing (you see GROUP_CONCAT shenanigans being used instead).

For indexing on function expressions in particular, the workaround we use is
to add a generated column and index that.

~~~
colanderman
Be warned that in PostgreSQL, WITH is an optimization barrier, and is planned
to remain that way to serve that purpose. If you can, prefer using views to
enhance readability (and testability as a bonus). PostgreSQL views (unlike
those in MySQL) do _not_ prevent optimization across them.

~~~
zkomp
No, CTEs are not planned to remain a barrier, this is already fixed in the
next version which is in feature freeze right now.

[https://www.depesz.com/2019/02/19/waiting-for-
postgresql-12-...](https://www.depesz.com/2019/02/19/waiting-for-
postgresql-12-allow-user-control-of-cte-materialization-and-change-the-
default-behavior/)

~~~
paulddraper
This is the best news I've heard all week.

~~~
munk-a
I have tried to express my joy at this news to my less SQL literate co-
workers... that failed so I wanted to let it out here. This is the best news,
I am overjoyed!

------
brightball
It's hard to believe that Google Cloud SQL still only has 9.6 available.

EDIT: Apparently 11.1 is available in beta as of April 9th.

~~~
cglace
Actually, 11 is now in Beta. If you create a new instance it is listed as an
option.

~~~
brightball
I tested it just before I posted the comment (to confirm) and didn't see it
listed. Maybe it depends on your account?

EDIT: I'll try again. Looks like it was added April 9th

[https://cloud.google.com/sql/docs/postgres/create-
instance](https://cloud.google.com/sql/docs/postgres/create-instance)

~~~
fernandotakai
i created an 11 (beta) instance yesterday and it worked as expected.

------
SnowingXIV
Running 10.7 and 10.6 on two production applications with Heroku. Thinking
about moving to 11 to ensure support for the long run as I rarely need to
touch this and it's very stable but would like to minimize any headaches in
the future.

Any complications or hiccups I need to worry about moving from 10 to 11?

Per Heroku Docs: ___By supporting at least 3 major versions, users are
required to upgrade roughly once every three years. However, you can upgrade
your database at any point to gain the benefits of the latest version._ __

~~~
skymt
The release notes for version 11 include a list of potentially incompatible
changes:
[https://www.postgresql.org/docs/11/release-11.html#id-1.11.6...](https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.8.4)

~~~
SnowingXIV
Thanks, yeah I just did some testing locally and made the upgrade on Heroku
(the documentation was rock solid).

------
rooam-dev
Question for PG happy users.

How do you manage failover and replication? At my previous job this was done
by a consultant. Is this doable on a self hosted setup?

Thank you in advance.

~~~
combatentropy
PostgreSQL has replication built in now. I set it up at work, and it
replicates reliably, in a fraction of a second. I've never had to fail over,
but it seems straightforward to do so. The only hard part was following
Postgres's documentation in setting it all up. It seemed to me a bit scattered
to me. I had to jump around to different sections before I put it all together
in my mind.

~~~
throw0101a
What do you use? Are there some instructions/articles that you'd recommend
reading? Is it anything like Galera?

I know of BDR, but there hasn't much news about it lately, especially with
more recent versions of Pg.

We like Galera for our simple needs: we use keepalived to do health checks,
and if they pass the node participates in the VRRP cluster. If one node goes
down/bad, another takes over.

~~~
duckehlabs
If you want multi master in Postgres, I think BDR is going to be your best
option, but the version for PG 10+ isn't open source so you'll have to pay for
it. We're using the open source version on PG 9.4 currently in production,
it's worked fine so far.

If you're just looking for a hot standby and dont need a multi master setup,
you can set those up just with pg. [https://www.postgresql.org/docs/9.4/hot-
standby.html](https://www.postgresql.org/docs/9.4/hot-standby.html)

------
kumarvvr
Question from a Python web developer. (Django mainly, exploring Flask
presently)

For a complex web-app, would you suggest an ORM (looking at SQLAlchemy) or a
custom module with hand written queries and custom methods for conversion to
python objects?

My app has a lot of complex queries, joins, etc. and the data-model is most
likely to change quite a bit as the app nears production. I feel using an ORM
is an unnecessary layer of abstraction in the thinking process. I feel
comfortable with direct SQL queries, and in some cases, want to directly get
JSON results from PGSQL itself.

Would that be a good idea, and more importantly, scalable?

Note : My app will be solely developed by me, not expecting to have a team or
even another developer work on it.

~~~
kangoo1707
Use both. Many of the business logics are just as simple as query by id,
filter/sort by a couple of columns. A smart ORM will handle fetching
relationships without hitting N+1 problem

For advanced queries, you can write raw SQL

The way I see it, an ORM has three useful features:

\- A migration/seed mechanism (you will need it anyway)

\- A schema definition for mapping tables to object

\- A query builder

If you feel that an ORM is too heavy, you can seek for just the query builder.

~~~
fernandotakai
i worked on a mid-sized django app and that was basically what we did:

* for normal queries (select _/ cols from table where id etc etc) we just used plain django orm. even for weird joins, django orm makes it a lot easier than using raw sql

_ when we needed raw speed, we just wrote raw sql and delegated to django sql
layer -- that way we leverage everything the framework has with raw sql power.

------
Tomdarkness
Totally wish we could upgrade but for some reason AWS have still not
implemented any upgrade path for Aurora PostgreSQL other than dump and
reimport despite apparently working on it for a year...

~~~
mevile
Does AWS Aurora actually use postgres or is it simply a postgres compatible
API on top of their own technology?

~~~
darkr
As with RDS Postgres, it's Amazon's fork of Postgres.

With Aurora, the storage layer is swapped out entirely for a distributed
storage engine, that I believe is based upon DynamoDB.

The wire protocol and server interface are much the same as regular Postgres,
though there are some additional benefits as well as caveats as you might
expect

------
throw0101a
MySQL has Galera: is there a multi-master option for Pg?

I know of BDR, earlier versions of which are open source, but there hasn't
been much movement with Pg 10 or 11 AFAICT.

We don't do anything complicated, but simply want two DBs (with perhaps a
quorum system) that has a vIP that will fail-over in case one system goes down
(scheduled or otherwise).

Galera provides this in a not-too-complicated fashion.

~~~
smilliken
PostgreSQL has logical replication built-in since version 10. This allows you
to replicate specific tables between multiple master databases, accepting
writes on each. You define a merge function in case there's conflicts.

------
mistrial9
impressive and .. upgrade on 10.x now in process, easily, quickly, thanks to
the Postgres PGDG Debian/Ubuntu repos .. BUT do not choose meta-package
_postgres_ ! Under Ubuntu at least, upgrading the meta-package postgres adds
an entire new server 11+ without confirmation .. why is this tolerated..
genuinely annoying

~~~
shawnz
I think you are looking for "apt-get upgrade" and not "apt-get dist-upgrade".
Or, just install the version you specifically want

------
dochtman
It's unfortunate that the official Docker images haven't been updated yet (on
DockerHub).

~~~
Xylakant
keep in mind that the "official" docker images are "offical" in the sense of
docker inc marking them as official, not in the sense of "the upstream
provides these". This is the repo for the Dockerfiles
[https://github.com/docker-library/postgres](https://github.com/docker-
library/postgres) and it begins with:

> This is the Git repo of the Docker "Official Image" for postgres (not to be
> confused with any official postgres image provided by postgres upstream)

------
micmil
I'm not a database guy so have no clue, but why are there so many versions
receiving support? Is there just that much legacy crap they can't get away
from, like Python?

~~~
dspillett
People are slow to upgrade database systems, as it can take a log of
regression testing to make absolutely sure your applications don't rely on
unsupported/undocumented/undefined behaviours that make them compatible with
the newest release (or are affected by officially acknowledged breaking
changes). Especially in enterprise systems. Even if developers upgrade
quickly, their clients with on-prem installations may not. That means that to
be taken seriously you need to support your major and minor releases for some
time to be accepted as a serious option in some arenas.

Supporting five versions is no more than MS do: currently SQL Server versions
2017, 2016sp2, 2016sp1, 2014sp3, 2014sp2, 2012sp4, 2008R2sp2 and 2008sp3.
2008sp3, 2008R2sp2, and 2016sp1 will hit their final EOL in a couple of months
taking SQL Servers's supported list back down to 5 too.

I expect other significant DB maintainers have similar support life-time
requirements for much the same reasons, though I'll leave researching who
does[n't] as an exercise for the reader.

~~~
greggyb
2008 and R2 are still in a supported phase of life. It's the "exorbitant
support fee" phase. Nevertheless, you can still get Microsoft support for the
two after the "EOL". It's more an end-of-public life

~~~
dspillett
Aye, and by the same technicality you can still get support for 2005.

Similar with PG I assume. You could always pay someone an expensive
contracting fee to support your use of an older version than is publicly
supported.

