PostgreSQL 12 | Hacker News Reader

PostgreSQL 12

postgresql.org

373 pointscraigkerstiens83 comments

I love the partitioning improvements in PostgreSQL 12 https://www.2ndquadrant.com/en/blog/partitioning-enhancement... 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... (including a comparison to MongoDB) and https://momjian.us/main/writings/pgsql/sharding.pdf

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.

Thanks for the tip.

We've been considering which minimum version requirement of PostgreSQL at GitLab here: https://gitlab.com/gitlab-org/gitlab/issues/31454. We will add PostgreSQL 12 for consideration as well.

The partitioning improvements will help with our events tables (https://gitlab.com/gitlab-org/gitlab/issues/24538) as Sid notes above. Also the notable improvements in query performance, especially over large data sets will help with the other large tables we need to address. Most notably: merge_request_diff_commits https://gitlab.com/gitlab-org/gitlab/issues/31454

and ci_builds https://gitlab.com/gitlab-org/gitlab/issues/30441

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... [2] https://www.postgresql.org/docs/12/auth-pg-hba-conf.html [3] https://www.postgresql.org/docs/12/auth-password.html

Further JSON improvements make MongoDB even less relevant than ever https://www.postgresql.org/docs/12/functions-json.html#FUNCT...

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

I'm not GP, but I don't know what about Mongo's API is fantastic, or, frankly, even good (and I like mongo fine as a database). It is overly verbose, and, without a good set of client utilities, feels more like writing instructions for a black box query planner than asking for data from a database. SQL is vastly superior.

Mongodb aggregation queries are far more intuitive and easy to explain to juniors than sql, because they run in the order you write them and don't need blog posts like https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with... (just found this on front page on HN). Well, actually there is a query planner in between also but they run as-if they were executed in that order. Anyway. They also by default protect you from sql injections and are easier to construct programmatically because appending a step in the pipeline is literally just appending to a standard json array, without having to know the complete lexer and AST of SQL and all. On the other hand they do make you pretty tired of curly brackets :)

There are plenty of good ORMs for Postgres if that's what floats your boat, but the advantage of Postgres is you can always drop down to SQL.

> but the advantage of Postgres is you can always drop down to SQL.

All the project i've worked on eventually devolved into a weird mess of part orm and part SQL.

Would have been easier and cleaner just to go with SQL from the start. Sadly devs always think it will be different if they try a different ORM next project and we always end up in the same place.

As a developer working on projects using both approaches, it would be hard for me to pick a clear winner.

One key difference for less experienced developers is that ORMs are usually integrated for you as part of a bigger framework (Django, Rails...). This provides some guidelines to get started on a more or less sane way.

On the other hand plain SQL usually relies directly on the database client (or a thin layer like SQLAlchemy core) that requires you to make many decisions about the architecture upfront. I feel that one needs to experiment a lot (and fail a bit) before getting this part right.

A good ORM acknowledges this and allows that to happen gracefully, while still allowing the 90%+ of straightforward queries to use the less error prone higher abstractions of the ORM.

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.

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.

From my experience using a SQL database is much better than using MongoDB. I can choose from either a ORM or direct SQL and there are a ton of resources .

When I used mongoDB there was a learning curve that wasn’t transferable. It was hard to figure out how to enable security features. I did find a good GUI to run ad hoc queries.

I do not like the Mongo query language. But Spring's "ORM" made mongo decent to work with. And RoboMongo is a pretty usable client. I also like how quick it is to start working without an explicit schema and how db/table/fields are automatically created when referenced.

MongoDB (both the DB and the company) are still very relevant. I am surprised that a (large and economically successful) PostgreSQL-specific company didn't emerge.

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

Thanks for all your hard work!

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.

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.

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...

> CTEs no longer act as an optimization barrier.

I've been waiting for years for this, one of my last few complaints about Postgres that some other DBs did better. It's so frustrating to have to resort to worse composability in order to get reasonable performance.

What other DB does this better?

At work I constantly have to create temporary tables instead of CTEs in MS SQL server to avoid crashing the server completely.

I guess Oracle maybe but I've never used that db, or DB2.

But regardless of who does it better, this is very very welcome news for postgres in any case :)

Oracle does, as well as MySQL. I thought MSSQL did as well, but I guess not...never really used it though.

looks like CTEs also support materialization which most devs thought implicitly happened anyway, but didn't. by removing the fence _and_ supporting materialization CTEs are now useful.

Not sure what you mean. Previously CTEs were always materialized (i.e. stashed into a buffer, somewhere), in PostgreSQL 12 it's optional and disabled by default for various cases that don't require it. The problem with materialization is that it prevents various optimizations (e.g. using index for a condition from outside the CTE).

And while some devs might be aware of the materialization (and possibly even leveraged it intentionally, to block some optimizer behavior) many did not - it's widely used as if it was just a nice way to name the subqueries.

> JIT enabled by default.

Only if you actually happened to compile the support for it, which is not the default.

Depends on packaging. Most distros should have that compiled as a separate package, e.g. postgresql12-llvmjit. So you install that (along with the regular packages) and you'll have JIT.

> 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

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.

Here are some slides from a presentation of Andres Freud on the matter: https://anarazel.de/talks/2018-10-25-pgconfeu-pluggable-stor...

Slides 4 and 5 give some insight about the architecture changes which have been done.

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.

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.

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.

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?

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.

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?"

> I have measured a planned switchover on a lightly loaded system (100tx/s) at 500ms from commit to commit.

What does that switchover look like?

You have services that resolve the database DNS on startup and create a connection pool. While they are doing 100tx/s, how do you get them pointed to another instance without forcing them to restart?

Also, how exactly is a Postgres commit streamed to a DR instance? I'm not interested in all of the possible configurations of master/slaver as much as I am just trying to understand... you've got a hot master receiving 100tx/s... what is mirroring the commit log / write ahead log to the DR master?

Citus Data extension can help you scale horizontally.

https://github.com/citusdata/citus

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

BEST PERFORMANCE ORIENTED RELEASE EVER !

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

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

It is run on volunteer work by companies and individuals. Many of the companies are database consultancy companies, but some like NT&T are end users.

The PostgreSQL project is not run on monetary donations.

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

Historically 5 to 6 months, unfortunately.

I think google cloud sql JUST got postgres 11, so it is even longer over in GCE land ;(

But far better than Google Cloud SQL, which never got PG 10 and only just got PG 11…last week.

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

So about 6 months.

AWS does have a preview available, so you can try it for non production workloads and report problems if any. That should speed up the GA release as well.

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

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.

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.

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).

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.

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

That very much depends on the CTE. The release notes say this:

> CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query.

So if you have a CTE with data modifications (e.g. no DELETE/INSERT/UPDATE), then that will not be inlined.

The problem the inlining is trying to solve is that CTEs block optimizations. Consider an example like this:

    CREATE TABLE t (id SERIAL PRIMARY KEY, ...);

    WITH x AS (SELECT * FROM t)
    SELECT * FROM x WHERE id = 1000;

Without the inlining, the WITH serves as "optimization fence" which means PostgreSQL won't be able to use the index on the ID column. The database essentially evaluates the CTE, stashes the results somewhere (in a work_mem buffer / temp file) and then queries this intermediate result.

There are historical reasons why it was originally implemented like this, but the trouble is, people often don't realize this and use it just as a nice way to name subqueries. So they treat

    WITH x AS (SELECT * FROM t)
    SELECT * FROM x WHERE id = 1000;

as semantically the same as

    SELECT * FROM  (SELECT * FROM t) foo WHERE id = 1000;

but it was executed quite differently.

So for people using CTEs like this, the automatic inlining by default is likely a huge improvement. There may be cases of regression, but that's why it's possible to enforce materialization by using MATERIALIZED.

> What is the impact of the inline CTEs?

It depends greatly on the size of the data and the nature of the query inside the CTE. Sometimes it'll make no difference at all, of course. Sometimes it could mean the difference between scanning an entire table and being able to perform index seeks. As the result is materialised in earlier versions this could mean a fair chunk of write IO as well as read IO if the result is too large to just keep in RAM. Even worse for recursive CTEs the process could be repeated at every level of the recursive depth.

Of course the query planner will still need to do that for bad queries or queries against structures that need better indexing, and there may be many situations out there where a better query/schema wasn't created because before v12 it would make no difference anyway so some rewriting is needed to take advantage of the new optimisation potential.

If the innermost CTE contains filters that significantly limit the data and there are sufficient indexes to support that filtering, or if the data is small to start with, then this change will likely make little or no difference.

Generated columns look awesome.

https://www.postgresql.org/docs/12/ddl-generated-columns.htm...

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

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)

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

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.

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.

loved the improvements! More power to the pg team!

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

Today's news is about PostgreSQL 12 GA release. The news you may have seen recently was about few Beta releases in last few months and an RC release last week.

PostgreSQL has followed the current schedule of yearly major releases for about 10 year.

That doesn't add up. I've been using PG for over 15 years and it was around before that.

Before v10, even the minor version number increase was a major release (i.e. 9.1 -> 9.2).

This interpretation makes sense to me (starting from 6)

Not sure what doesn't add up? While PostgreSQL is certainly around longer than 10 years, the release schedule was somewhat random until ~10 years ago. Since then the project sticks to a yearly schedule, with one major release per year, usually in September/October.

12 - 6, over 15 years makes me think the major should be 21. But counting minor numbers makes the cadence work as another comment states

Before roughly 10 years ago there was no formal release schedule but in practice it was roughly every 1 to 1.5 years, so people decided to formalize the process and do strictly yearly releases.

The release was scheduled and planned for quite some time.

The releases may seem more major than they did in the past because PostgreSQL dropped one part of its version number with version 10.

11 is to 12 roughly as 9.5 is to 9.6, not as 9.0 is to 10.0

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/

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.

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

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.)

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.

That was never true for major version upgrades:

e.g.

9.4.x -> 9.5.x

9.5.x -> 9.6.x

9.6.x -> 10.x (they changed versioning since 10.x)

10.x -> 11.x

11.x -> 12.x

and so on

You couldn't just drop new version and assume it will work, the database needed to be converted to new format, pg_upgrade tool was created for this operation. By default it generates a new database in new folder. There's also a flag that uses hard link for files that didn't change between versions (it usually only takes a minute or less), but with the caveat that if upgrade is not successful and you run the new version of the database, you'll have to use backup to restore old version (since the new version might modify the files).

There's also pg_dump/pg_restore, but this is the slowest way.

Reply on news.ycombinator.com