9 ms·

PostgreSQL 12

373 points by craigkerstiens 7y ago

truth_seeker 7y ago
BEST PERFORMANCE ORIENTED RELEASE EVER !
jfbaro 7y ago
Congratulations to everyone involved in this release. Many important and needed improvements.
diminish 7y ago
Why a major release soon after another one? Fast release cycles?
- amitlan 7y ago
  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.
- jeltz 7y ago
  PostgreSQL has followed the current schedule of yearly major releases for about 10 year.
  - edoceo 7y ago
    That doesn't add up. I've been using PG for over 15 years and it was around before that.
    - pgaddict 7y ago
      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.
      - edoceo 7y ago
        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
    - vetinari 7y ago
      Before v10, even the minor version number increase was a major release (i.e. 9.1 -> 9.2).
      - edoceo 7y ago
        This interpretation makes sense to me (starting from 6)
    - jeltz 7y ago
      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.
- ellisv 7y ago
  The release was scheduled and planned for quite some time.
- chousuke 7y ago
  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
- throw0101a 7y ago
  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 7y ago
    According to the release notes, dump/restore is not necessary to get to 12 — pg_upgrade works.
    - throw0101a 7y ago
      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 7y ago
        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.
      - takeda 7y ago
        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.
eloff 7y ago
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 7y ago
  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 7y ago
    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-REINDEX-CONCURRENTLY https://www.postgresql.org/docs/12/sql-reindex.html#SQL-REIN...
- darksaints 7y ago
  > 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.
  - tyfon 7y ago
    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 :)
goatinaboat 7y ago
Further JSON improvements make MongoDB even less relevant than ever https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH https://www.postgresql.org/docs/12/functions-json.html#FUNCT...
- beardedman 7y ago
  Less relevant than ever? Do you not think that MDB's API is part of what makes it so fantastic?
  - mantap 7y ago
    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.
    - dominotw 7y ago
      > 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.
      - goatinaboat 7y ago
        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.
        nicolaslem 7y ago
        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.
Dowwie 7y ago
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 7y ago
  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 7y ago
    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 7y ago
      That's a good suggestion. I try to gather some numbers and post to the mailing list.
- pgaddict 7y ago
  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.
javagram 7y ago
How long will it typically take AWS to add this as an option for RDS?
- jfbaro 7y ago
  Historically 5 to 6 months, unfortunately.
  - brianwawok 7y ago
    I think google cloud sql JUST got postgres 11, so it is even longer over in GCE land ;(
  - amarshall 7y ago
    But far better than Google Cloud SQL, which never got PG 10 and only just got PG 11…last week.
- agildehaus 7y ago
  PostgreSQL 11 was released 2018-10-18 and became available in RDS generally 2019-03-13. So about 6 months.
  - sudhirj 7y ago
    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.
  - philliphaydon 7y ago
    That was mostly because they held off releasing it until some bugs were resolved. Otherwise it probably would have come sooner.
    - agildehaus 7y ago
      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.
netcraft 7y ago
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 7y ago
  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 7y ago
loved the improvements! More power to the pg team!
faizshah 7y ago
> 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 7y ago
  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.
- ioltas 7y ago
  Here are some slides from a presentation of Andres Freud on the matter: https://anarazel.de/talks/2018-10-25-pgconfeu-pluggable-storage/pluggable.pdf 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.
throw0101a 7y ago
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 7y ago
  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 7y ago
    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 7y ago
      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.
      - 7y ago
sytse 7y ago
I love the partitioning improvements in PostgreSQL 12 https://www.2ndquadrant.com/en/blog/partitioning-enhancements-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-sharding-in-postgresql-and-how-it-relates-to-mongodbs/ 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 https://momjian.us/main/writings/pgsql/sharding.pdf
- foou 7y ago
  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 7y ago
    Thanks for the tip.
- craigg 7y ago
  We've been considering which minimum version requirement of PostgreSQL at GitLab here: https://gitlab.com/gitlab-org/gitlab/issues/31454 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 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 https://gitlab.com/gitlab-org/gitlab/issues/31454 and ci_builds https://gitlab.com/gitlab-org/gitlab/issues/30441 https://gitlab.com/gitlab-org/gitlab/issues/30441
gigatexal 7y ago
With so much to love in PG 12 I can't wait to see what they do in PG 13.
- ainar-g 7y ago
  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 7y ago
    I’m all about the single keyword and it being standard compliant.
devmunchies 7y ago
unrelated, but how is PG monetized? do they run completely on donations?
- jakobegger 7y ago
  Most of the core team work for companies that offer PostgreSQL consulting services.
- jeltz 7y ago
  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.
jeffdavis 7y ago
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.html#GUC-PASSWORD-ENCRYPTION https://www.postgresql.org/docs/12/runtime-config-connection... [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
etxm 7y ago
Generated columns look awesome. https://www.postgresql.org/docs/12/ddl-generated-columns.html https://www.postgresql.org/docs/12/ddl-generated-columns.htm...
jakobegger 7y ago
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 7y ago
  Thanks for all your hard work!

PostgreSQL 12

truth_seeker 7y ago

BEST PERFORMANCE ORIENTED RELEASE EVER !

jfbaro 7y ago

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

diminish 7y ago

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

amitlan 7y ago
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.
jeltz 7y ago
PostgreSQL has followed the current schedule of yearly major releases for about 10 year.
- edoceo 7y ago
  That doesn't add up. I've been using PG for over 15 years and it was around before that.
  - pgaddict 7y ago
    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.
    - edoceo 7y ago
      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
  - vetinari 7y ago
    Before v10, even the minor version number increase was a major release (i.e. 9.1 -> 9.2).
    - edoceo 7y ago
      This interpretation makes sense to me (starting from 6)
  - jeltz 7y ago
    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.
ellisv 7y ago
The release was scheduled and planned for quite some time.
chousuke 7y ago
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
throw0101a 7y ago
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 7y ago
  According to the release notes, dump/restore is not necessary to get to 12 — pg_upgrade works.
  - throw0101a 7y ago
    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 7y ago
      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.
    - takeda 7y ago
      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.

eloff 7y ago

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 7y ago
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 7y ago
  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-REINDEX-CONCURRENTLY https://www.postgresql.org/docs/12/sql-reindex.html#SQL-REIN...
darksaints 7y ago
> 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.
- tyfon 7y ago
  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 :)

goatinaboat 7y ago

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

beardedman 7y ago
Less relevant than ever? Do you not think that MDB's API is part of what makes it so fantastic?
- mantap 7y ago
  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.
  - dominotw 7y ago
    > 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.
    - goatinaboat 7y ago
      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.
      - nicolaslem 7y ago
        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.

Dowwie 7y ago

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 7y ago
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 7y ago
  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 7y ago
    That's a good suggestion. I try to gather some numbers and post to the mailing list.
pgaddict 7y ago
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.

javagram 7y ago

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

jfbaro 7y ago
Historically 5 to 6 months, unfortunately.
- brianwawok 7y ago
  I think google cloud sql JUST got postgres 11, so it is even longer over in GCE land ;(
- amarshall 7y ago
  But far better than Google Cloud SQL, which never got PG 10 and only just got PG 11…last week.
agildehaus 7y ago
PostgreSQL 11 was released 2018-10-18 and became available in RDS generally 2019-03-13. So about 6 months.
- sudhirj 7y ago
  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.
- philliphaydon 7y ago
  That was mostly because they held off releasing it until some bugs were resolved. Otherwise it probably would have come sooner.
  - agildehaus 7y ago
    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.

netcraft 7y ago

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 7y ago
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 7y ago

loved the improvements! More power to the pg team!

faizshah 7y ago

> 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 7y ago
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.
ioltas 7y ago
Here are some slides from a presentation of Andres Freud on the matter: https://anarazel.de/talks/2018-10-25-pgconfeu-pluggable-storage/pluggable.pdf 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.

throw0101a 7y ago

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

sytse 7y ago

I love the partitioning improvements in PostgreSQL 12 https://www.2ndquadrant.com/en/blog/partitioning-enhancements-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-sharding-in-postgresql-and-how-it-relates-to-mongodbs/ 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 https://momjian.us/main/writings/pgsql/sharding.pdf

foou 7y ago
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 7y ago
  Thanks for the tip.
craigg 7y ago
We've been considering which minimum version requirement of PostgreSQL at GitLab here: https://gitlab.com/gitlab-org/gitlab/issues/31454 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 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 https://gitlab.com/gitlab-org/gitlab/issues/31454 and ci_builds https://gitlab.com/gitlab-org/gitlab/issues/30441 https://gitlab.com/gitlab-org/gitlab/issues/30441

gigatexal 7y ago

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

ainar-g 7y ago
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 7y ago
  I’m all about the single keyword and it being standard compliant.

devmunchies 7y ago

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

jakobegger 7y ago
Most of the core team work for companies that offer PostgreSQL consulting services.
jeltz 7y ago
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.

jeffdavis 7y ago

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.html#GUC-PASSWORD-ENCRYPTION https://www.postgresql.org/docs/12/runtime-config-connection... [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

etxm 7y ago

Generated columns look awesome. https://www.postgresql.org/docs/12/ddl-generated-columns.html https://www.postgresql.org/docs/12/ddl-generated-columns.htm...

jakobegger 7y ago

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 7y ago
Thanks for all your hard work!