
PostgreSQL 12 - craigkerstiens
https://www.postgresql.org/docs/12/release-12.html
======
sytse
I love the partitioning improvements in PostgreSQL 12
[https://www.2ndquadrant.com/en/blog/partitioning-
enhancement...](https://www.2ndquadrant.com/en/blog/partitioning-enhancements-
in-postgresql-12/) We are planning to use them for GitLab.com, particularly
for the events table.

More information is at [https://www.percona.com/blog/2019/05/24/an-overview-
of-shard...](https://www.percona.com/blog/2019/05/24/an-overview-of-sharding-
in-postgresql-and-how-it-relates-to-mongodbs/) (including a comparison to
MongoDB) and
[https://momjian.us/main/writings/pgsql/sharding.pdf](https://momjian.us/main/writings/pgsql/sharding.pdf)

~~~
foou
keep an eye on the query planner. Last time I checked, the autovacuum doesn't
run analyze on the parent table and it causes some issues.

~~~
sytse
Thanks for the tip.

------
jeffdavis
For those using the "password" or "md5" authentication method, this would be a
good time to start using SCRAM instead.

First, set

    
    
        password_encryption = scram-sha-256
    

in postgresql.conf[1].

If setting up a new server, use auth method "scram-sha-256" in
pg_hba.conf[2][3].

If upgrading an existing server, use auth method "md5" in pg_hba.conf, which
actually allows either MD5 or SCRAM. Then, start updating the users' passwords
to be encrypted with SCRAM by resetting the password (the
"password_encryption" setting causes the new password to be encrypted with
SCRAM). After there are no more md5 passwords in the system, you can set the
pg_hba.conf auth method to "scram-sha-256" and you're done.

Note that this all requires connecting clients to support scram (version 10 or
later of libpq).

[1] [https://www.postgresql.org/docs/12/runtime-config-
connection...](https://www.postgresql.org/docs/12/runtime-config-
connection.html#GUC-PASSWORD-ENCRYPTION) [2]
[https://www.postgresql.org/docs/12/auth-pg-hba-
conf.html](https://www.postgresql.org/docs/12/auth-pg-hba-conf.html) [3]
[https://www.postgresql.org/docs/12/auth-
password.html](https://www.postgresql.org/docs/12/auth-password.html)

------
goatinaboat
Further JSON improvements make MongoDB even less relevant than ever
[https://www.postgresql.org/docs/12/functions-
json.html#FUNCT...](https://www.postgresql.org/docs/12/functions-
json.html#FUNCTIONS-SQLJSON-PATH)

~~~
beardedman
Less relevant than ever? Do you not think that MDB's API is part of what makes
it so fantastic?

~~~
spamizbad
I've worked with MongoDB for close to 7 years, from 2.2 all the way through
4.0. I would not call its API good. Its greatest asset IMO is its straight
forward replication and the fact that if you know JavaScript you "know" its
syntax (albeit none of its idiosyncrasies).

It's shortcomings are its limited type system, verbose query semantics,
expensive indexes, unsophisticated query planner and memory limitations that
force it to spill over to disk far more often than your typical SQL database.

~~~
tracker1
I wouldn't call mongo's replication that straight forward... you get
redundancy/or sharding (iirc) and if you want both, you have to layer them...
and managing a cluster isn't always great...

When the azure apocalypse happened a few years ago, with up/down, the 3
replica node cluster I was running was so damaged, the nodes couldn't catch up
when it finally came all the way back up... fortunately, it was all
denormalized data sourced from an rdbms, so was able to rebuild the cluster. I
_could_ have dropped two of the nodes, re-created them and rebuilt from there,
in the end the total re-creation was faster.

It wasn't anything resembling straight forward at all and if it was the source
of truth would have potentially been much, much worse.

------
jakobegger
If you want to try the new features of PostgreSQL 12 on a Mac, I just uploaded
new builds of Postgres.app [1]. Postgres.app also includes the latest version
of PostGIS (beta of the upcoming PostGIS 3.0) and PL/v8 (for writing functions
and procedures in Javascript!).

[1]:
[https://postgresapp.com/downloads.html](https://postgresapp.com/downloads.html)

~~~
philshem
Thanks for all your hard work!

------
eloff
A lot of major performance improvements. Some big ones:

CTEs no longer act as an optimization barrier.

JIT enabled by default.

Speed and space improvements for many index types, including b-trees.

REINDEX CONCURRENTLY doesn't block table writes.

~~~
jeltz
To clarify: REINDEX CONCURRENTLY is a new feature. Previously there were only
DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY, which meant that while
you could first create a new index and then drop the old one renaming still
required an exclusive lock (this is also fixed in 12), as did switching over
all foreign keys to the new index. REINDEX CONCURRENTLY essentially does all
this work for you in a single command while also making sure no reader or
writer is locked out from the table.

~~~
ioltas
An advantage of REINDEX CONCURRENTLY is that it becomes much easier to handle
dependencies of a rebuilt index with constraints, as what the feature roughly
does it to add a swapping phase between the creation and the drop portions
which switches the dependencies from the old index to the new fresh one.
Table-level reindex also makes life easier.

The feature is equivalent to external tools like pg_repack (formerly pg_reorg)
in more performant, and has a couple of limitations to be aware of:
[https://www.postgresql.org/docs/12/sql-reindex.html#SQL-
REIN...](https://www.postgresql.org/docs/12/sql-reindex.html#SQL-REINDEX-
CONCURRENTLY)

------
faizshah
> Add CREATE ACCESS METHOD command to create new table types (Andres Freund,
> Haribabu Kommi, Álvaro Herrera, Alexander Korotkov, Dmitry Dolgov)

> This enables the development of new table access methods, which can optimize
> storage for different use cases. The existing heap access method remains the
> default.

[https://www.postgresql.org/docs/12/tableam.html](https://www.postgresql.org/docs/12/tableam.html)

Interesting, this could allow purely in memory or columnar tables. A
combination of postgres JSON features with an in memory or columnar table
would be a great alternative to some nosql use cases.

~~~
jeltz
Yeah, there are a couple of people from Pivotal working on a column store
right now, but a major limitation is that while the storage is now pluggable
the query planner and the executor still assume all engines are row based
which prevents PostgreSQL from taking full advantage of a column store.

Edit: Another project for a new storage engine is zheap which is a new row
based storage which uses undo log rather than storing multiple row versions to
reduce the amount of bloat.

------
throw0101a
Any multi-master options available for 12?

We like using Galera with My/MariaSQL, as we can get HA very easily, and just
throw up a _keepalived_ vIP to point apps to. If one node goes sideways then
the health check fails and the vIP moves to the next node.

Closest thing I've seen is BDR, but nothing recent has been open sourced from
that.

~~~
pgaddict
No, and I wouldn't hold my breath for that to happen. The trouble with multi-
master is that there are way too many possibilities what it might mean -
different purposes require different trade-offs, and PostgreSQL is unlikely to
commit to one of those options (at the expense of others). So I'd expect the
current situation to continue, i.e. different multi-master projects (open-
source and proprietary) built on top of PostgreSQL, catering to different use
cases.

For HA, the best option at the moment is probably a physical standby with an
external monitoring/management tool (repmgr, patroni, pacemaker, ...) handling
the failovers. So not really a multi-master. There are ways to do something
similar with logical replication (that's what BDR does), but I don't think
there's a widely available tool to manage that.

~~~
MuffinFlavored
How long would an HA switchover like that take?

Is your “slave” master off to the side, getting data streamed from your “hot”
master the whole time to stay up?

~~~
ants_a
I have measured a planned switchover on a lightly loaded system (100tx/s) at
500ms from commit to commit. On larger systems it might be a couple of
seconds. Unplanned failover depends mainly on the chosen timeout. Typical used
value is 30s, which gives tolerance for small network problems without causing
failovers while not being excessively long.

Yes, the standby is constantly streaming and applying transaction logs from
the master.

~~~
pgaddict
Right, that's about the right ballpark - tens of second for unplanned events
(failover), a couple of seconds for planned events (switchover).

We can have a lenghty discussion about all the caveats and options, but in my
experience trying to reduce the times below these (somewhat vague) thresholds
is mostly pointless.

For switchovers, a couple of seconds should not be a big deal - you can pick
when it happens, and there are ways to make it non-disruptive for the
application (i.e. you can design the app to tolerate this, or you can use
PAUSE in pgbouncer, or whatever).

For failovers, the "tens of seconds" may seem a bit too high, but most of the
time will be spent determining whether to do the failover or not. Make it too
aggressive and you'll be sad.

Ultimately, it's a matter of money. People sometime say things like "It has to
be 24/7, absolutely not outages, it's a non-negotiable critical business
requirement." A good response that is "So you're telling me a 60-second outage
of this system will put you out of business?"

------
jfbaro
Congratulations to everyone involved in this release. Many important and
needed improvements.

------
truth_seeker
BEST PERFORMANCE ORIENTED RELEASE EVER !

------
devmunchies
unrelated, but how is PG monetized? do they run completely on donations?

~~~
jakobegger
Most of the core team work for companies that offer PostgreSQL consulting
services.

------
javagram
How long will it typically take AWS to add this as an option for RDS?

~~~
agildehaus
PostgreSQL 11 was released 2018-10-18 and became available in RDS generally
2019-03-13.

So about 6 months.

~~~
philliphaydon
That was mostly because they held off releasing it until some bugs were
resolved. Otherwise it probably would have come sooner.

~~~
agildehaus
PostreSQL 10 was 2017-10-05 to 2018-02-27.

10 took 146 days, 11 took 145 days. Just a tad under 5 months for both.

------
Dowwie
What is the impact of the inline CTEs? I've found CTEs very useful for
avoiding multi round trips for related modifications, although this is
limited.

~~~
ohlookabird
I suppose it is much more common that CTEs are used to make queries more
readable (I certainly do!). If this is the case, I feel making the common case
the default behavior (inlining) is preferable, which is what PG12 did. It also
is less surprising behavior in my opinion. The old behavior can be enforced
using the MATERIALIZED keyword with the CTE.

However, I agree that this will make me go through every CTE I use and make
sure there is no MATERIALIZED keyword missing. And I already know quite a few
where I, like you, use CTEs to prevent recomputation and promote reuse. I will
likely add the MATERIALIZED keyword there for clarity, but inlining will only
happen if the CTE isn't used multiple times anyway (they will also not be
inline if they have side-effects or are recursive).

~~~
pgaddict
Yeah, there was a lengthy discussion about what the default behavior should be
during development. Ultimately, inline by default prevailed, and I think it
was the right decision.

Inlining seems like the right choice for vast majority of cases, but yeah -
there are cases where enforcing materialization is a win.

Once you try this on your application, it'd be interesting to share some stats
about the queries / CTEs that benefited from materialization. That might be
quite useful for improving the "materialize vs. inline" logic in future
releases.

~~~
ohlookabird
That's a good suggestion. I try to gather some numbers and post to the mailing
list.

------
etxm
Generated columns look awesome.

[https://www.postgresql.org/docs/12/ddl-generated-
columns.htm...](https://www.postgresql.org/docs/12/ddl-generated-columns.html)

------
gigatexal
With so much to love in PG 12 I can't wait to see what they do in PG 13.

~~~
ainar-g
MERGE[1], hopefully. If I understand correctly, it would allow us to do
upserts while checking against multiple conditions. It's also standard SQL,
unlike UPSERT, INSERT OR REPLACE, INSERT … ON CONFLICT, and all the other
names different RDBMSes gave it over the years.

[1]
[https://en.wikipedia.org/wiki/Merge_(SQL)](https://en.wikipedia.org/wiki/Merge_\(SQL\))

~~~
gigatexal
I’m all about the single keyword and it being standard compliant.

------
netcraft
How long till RDS supports it I wonder? Around 6 months to a year is what it
typically takes right?

Such a great release - I've been asking for the CTE change for a few years,
can't wait to get my hands on it.

~~~
philliphaydon
11 took a while because there were some bugs that prevented them releasing it.
So we got it shortly after they were resolved. If testing for 12 goes well we
will hopefully see it sooner. It’s already in preview.

------
qatanah
loved the improvements! More power to the pg team!

------
diminish
Why a major release soon after another one? Fast release cycles?

~~~
throw0101a
Semi-recent change in policy:

> _Starting with PostgreSQL 10, a major version is indicated by increasing the
> first part of the version, e.g. 10 to 11. Before PostgreSQL 10, a major
> version was indicated by increasing either the first or second part of the
> version number, e.g. 9.5 to 9.6._

* [https://www.postgresql.org/support/versioning/](https://www.postgresql.org/support/versioning/)

This is to help indicate that an in-place upgrade is not possible. To put it
simply, you have to do a dump/restore to go from 11.x to 12.x (there are
'advanced' ways around this).

There was some confusion in the past with new users about why something like
that was needed for "only" a 'minor' update of going from 9.5 to 9.6.

~~~
wrs
According to the release notes, dump/restore is not necessary to get to 12 —
pg_upgrade works.

~~~
throw0101a
You're generating a new set of data files, which IMHO is the same principle.
(There's nothing wrong with this, it's just telling folks you can't just drop
in a bin bin/postgres and run it blindly.)

~~~
petergeoghegan
That's not how pg_upgrade works or has ever worked. The only data files that
are regenerated are those used for system catalogs.

The policy change for version numbers more or less happened because we could
never justify bumping the most significant number for the latest stable
release. It had become meaningless.

