Postgres 11 release notes

Hello Bruce,

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Here is some, for things I know about:

Add major scripting features to pgbench (Fabien Coelho)

I assume that you are refering to "bc7fa0c1". I do not think that anything
qualifies as "major".

I would rather describe it as "Add support for NULL and boolean, as well
as assorted operators and functions, to pgbench expressions". Or something
in better English.

Add \if macro support to pgbench (Fabien Coelho)

I would remove the word "macro", as it is not a pre-compilation feature,
it is just a somehow simple standard conditional.

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics.

I'm planning to try to help with the later.

--
Fabien.

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup of
B-tree indexes when possible
c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc Add explicit cast from scalar jsonb to
all numeric and bool types.

Pls, edit:
Add functionjson(b)_to_tsvector to create usable text search queries matching
JSON/JSONB values (Dmitry Dolgov)
to
Add function json(b)_to_tsvector to create usable vectors to search in json(b)
(Dmitry Dolgov)
or somehow else. Your wording is about query but actually that functions are
about how to create tsvector from json(b)

Thank you!

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

On 05/11/2018 11:08 AM, Bruce Momjian wrote:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Very superficial things:

Add predicate locking for Hash, GiST, and GIN indexes

s/likelyhood/likelihood/

Add extension jsonb_plpython

There are two such entries; one looks like it should be plperl.

Improve the speed of aggregate computations

This entry is missing a bullet.

-Chap

On Fri, May 11, 2018 at 06:29:39PM +0200, Fabien COELHO wrote:

Hello Bruce,

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Here is some, for things I know about:

Add major scripting features to pgbench (Fabien Coelho)

I assume that you are refering to "bc7fa0c1". I do not think that anything
qualifies as "major".

I would rather describe it as "Add support for NULL and boolean, as well as
assorted operators and functions, to pgbench expressions". Or something in
better English.

OK, new wording is:

Add pgbench expressions support for NULLs, booleans, and some
functions and operators (Fabien Coelho)

Add \if macro support to pgbench (Fabien Coelho)

I would remove the word "macro", as it is not a pre-compilation feature, it
is just a somehow simple standard conditional.

New wording is:

Add \if conditional support to pgbench (Fabien Coelho)

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics.

I'm planning to try to help with the later.

Great, thanks.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 07:49:50PM +0300, Teodor Sigaev wrote:

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc Add explicit cast from scalar jsonb
to all numeric and bool types.

Pls, edit:
Add functionjson(b)_to_tsvector to create usable text search queries
matching JSON/JSONB values (Dmitry Dolgov)
to
Add function json(b)_to_tsvector to create usable vectors to search in
json(b) (Dmitry Dolgov)
or somehow else. Your wording is about query but actually that functions are
about how to create tsvector from json(b)

OK, how is this text?

Add function <function>json(b)_to_tsvector()</function> to create
text search query for matching <type>JSON</type>/<type>JSONB
</type>values (Dmitry Dolgov)

I have made this change but can adjust it some more.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 01:40:54PM -0400, Chapman Flack wrote:

On 05/11/2018 11:08 AM, Bruce Momjian wrote:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Very superficial things:

Add predicate locking for Hash, GiST, and GIN indexes

s/likelyhood/likelihood/

Add extension jsonb_plpython

There are two such entries; one looks like it should be plperl.

Improve the speed of aggregate computations

This entry is missing a bullet.

Great, all fixed, and the URL contents are updated with this and
previous suggestions, plus more markup.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On 2018-05-11 14:44:06 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 07:49:50PM +0300, Teodor Sigaev wrote:

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

It's a *huge* performance problem in larger installations
currently. When you have a multi-TB relation and correspondingly large
relation, the VM allows to make the heap cleanups cheap, but then the
index scan takes just about forever. I know at least one large PG user
that moved off postgres because of it. This won't solve all of those
concerns, but it definitely is crucial to know for such users.

People would notice by vacuums of large relations not taking forever
anymore. And the behaviour change would be to a) upgrade b) tune the
associated reloption/GUC.

Greetings,

Andres Freund

On Fri, May 11, 2018 at 11:50:51AM -0700, Andres Freund wrote:

On 2018-05-11 14:44:06 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 07:49:50PM +0300, Teodor Sigaev wrote:

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

It's a *huge* performance problem in larger installations
currently. When you have a multi-TB relation and correspondingly large
relation, the VM allows to make the heap cleanups cheap, but then the
index scan takes just about forever. I know at least one large PG user
that moved off postgres because of it. This won't solve all of those
concerns, but it definitely is crucial to know for such users.

People would notice by vacuums of large relations not taking forever
anymore. And the behaviour change would be to a) upgrade b) tune the
associated reloption/GUC.

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

You can see why I was hesitant to include it, based on this text, but I
am happy to add it.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Hi,

Quick notes:

<para>
Add Just-In-Time (<acronym>JIT</acronym>) compilation of plans
run the by the executor
(Andres Freund)
</para>
</listitem>

It's currently not yet compilation of entire plans, but only parts. I
think that's a relevant distinction because I'd like to add that as a
feature to v12 ;). How about just adding 'parts of' or such? I'd also
rephrase the plan and 'run by the executor a bit'. How about:

Add Just-In-Time (<acronym>JIT</acronym>) compilation of parts of
queries, to accelerate their execution.

<listitem>
<!--
2018-03-20 [5b2526c83] Add configure infrastructure (- -with-llvm) to enable LLV
-->

<para>
Add configure flag <option>--with-llvm</option> to test for
<acronym>LLVM</acronym> support (Andres Freund)
</para>
</listitem>

<listitem>
<!--
2018-03-20 [6869b4f25] Add C++ support to configure.
-->

<para>
Have configure check for the availability of a C++ compiler
(Andres Freund)
</para>
</listitem>

<listitem>

I wonder if we shouldn't omit those, considering them part of the JIT
entry? Not quite sure what our policy there is.

Greetings,

Andres Freund

Bruce Momjian wrote:

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

Make vacuum cheaper by avoiding scans of btree indexes when not
necessary
?

Why "manual vacuum"? It's a problem with vacuums invoked from
autovacuum too.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Andres Freund wrote:

<para>
Add configure flag <option>--with-llvm</option> to test for
<acronym>LLVM</acronym> support (Andres Freund)
</para>
<para>
Have configure check for the availability of a C++ compiler
(Andres Freund)
</para>

I wonder if we shouldn't omit those, considering them part of the JIT
entry? Not quite sure what our policy there is.

I don't see why users would be interested in these items at all, so +1
for omitting them.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018-05-11 14:59:04 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 11:50:51AM -0700, Andres Freund wrote:

On 2018-05-11 14:44:06 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 07:49:50PM +0300, Teodor Sigaev wrote:

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

It's a *huge* performance problem in larger installations
currently. When you have a multi-TB relation and correspondingly large
relation, the VM allows to make the heap cleanups cheap, but then the
index scan takes just about forever. I know at least one large PG user
that moved off postgres because of it. This won't solve all of those
concerns, but it definitely is crucial to know for such users.

People would notice by vacuums of large relations not taking forever
anymore. And the behaviour change would be to a) upgrade b) tune the
associated reloption/GUC.

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

I don't think the table being 'append-only' is necessary? Nor does it
have to be a manual vacuum. And 'needless index scan' sounds less than
it is/was, namely a full scan of the index. Perhaps something like:

Allow vacuum to skip doing a full scan of btree indexes after VACUUM,
if not necessary.

or something like that?

You can see why I was hesitant to include it, based on this text, but I
am happy to add it.

I can't. Even if the above were accurate it'd be relevant information.

Greetings,

Andres Freund

On Fri, May 11, 2018 at 04:00:58PM -0300, Alvaro Herrera wrote:

Bruce Momjian wrote:

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

Make vacuum cheaper by avoiding scans of btree indexes when not
necessary
?

Why "manual vacuum"? It's a problem with vacuums invoked from
autovacuum too.

Uh, from the commit it says:

When workload on particular table is append-only, then autovacuum
isn't intended to touch this table. However, user may run vacuum
manually in order to fill visibility map and get benefits of
--------
index-only scans. Then ambulkdelete wouldn't be called for
indexes of such table (because no heap tuples were deleted), only
---------------------------
amvacuumcleanup would be called In this case, amvacuumcleanup
would perform full index scan for two objectives: put recyclable
pages into free space map and update index statistics.

Why would autovacuum run on a table with no expired index entries?

Personally, I think the fact that autovacuum doesn't run on suvch tables
and therefore doesn't automatically do index-only scans is a problem. I
tried to fix it years ago, but failed and gave up.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 11:59:12AM -0700, Andres Freund wrote:

Hi,

Quick notes:

<para>
Add Just-In-Time (<acronym>JIT</acronym>) compilation of plans
run the by the executor
(Andres Freund)
</para>
</listitem>

It's currently not yet compilation of entire plans, but only parts. I
think that's a relevant distinction because I'd like to add that as a
feature to v12 ;). How about just adding 'parts of' or such? I'd also
rephrase the plan and 'run by the executor a bit'. How about:

Add Just-In-Time (<acronym>JIT</acronym>) compilation of parts of
queries, to accelerate their execution.

OK, new text:

Add Just-In-Time (<acronym>JIT</acronym>) compilation of some
parts of query plans to improve execution speed (Andres Freund)

<listitem>
<!--
2018-03-20 [5b2526c83] Add configure infrastructure (- -with-llvm) to enable LLV
-->

<para>
Add configure flag <option>--with-llvm</option> to test for
<acronym>LLVM</acronym> support (Andres Freund)
</para>
</listitem>

<listitem>
<!--
2018-03-20 [6869b4f25] Add C++ support to configure.
-->

<para>
Have configure check for the availability of a C++ compiler
(Andres Freund)
</para>
</listitem>

<listitem>

I wonder if we shouldn't omit those, considering them part of the JIT
entry? Not quite sure what our policy there is.

OK, removed.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 12:04 PM, Andres Freund <andres@anarazel.de> wrote:

I don't think the table being 'append-only' is necessary? Nor does it
have to be a manual vacuum. And 'needless index scan' sounds less than
it is/was, namely a full scan of the index. Perhaps something like:

Allow vacuum to skip doing a full scan of btree indexes after VACUUM,
if not necessary.

or something like that?

I suggest "Allow vacuuming to avoid full index scans for indexes when
there are no dead tuples found in a table. Where necessary, the
behavior can be adjusted via the new configuration parameter
vacuum_cleanup_index_scale_factor."

Also:

* "Allow indexes to be built in parallel" should specify that it only
works for B-Tree index builds.

* Suggest replacement sort item be phrased as: "Remove the
configuration parameter replacement_sort_tuples. <newline> The
replacement selection sort algorithm is no longer used."

--
Peter Geoghegan

857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

Hm, it could be something like:
Add possibility to miss scan indexes on append-only table, it decreases
vacuum time on such tables.

c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc Add explicit cast from scalar jsonb
to all numeric and bool types.

Pls, edit:
Add functionjson(b)_to_tsvector to create usable text search queries
matching JSON/JSONB values (Dmitry Dolgov)
to
Add function json(b)_to_tsvector to create usable vectors to search in
json(b) (Dmitry Dolgov)
or somehow else. Your wording is about query but actually that functions are
about how to create tsvector from json(b)

OK, how is this text?

Add function <function>json(b)_to_tsvector()</function> to create
text search query for matching <type>JSON</type>/<type>JSONB
</type>values (Dmitry Dolgov)

Not query - *_to_tsvector functions produce a tsvector. So:

Add function <function>json(b)_to_tsvector()</function> to use
customizable full text search over json(b) columns.

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

On 2018-05-11 15:11:31 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 04:00:58PM -0300, Alvaro Herrera wrote:

Bruce Momjian wrote:

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

Make vacuum cheaper by avoiding scans of btree indexes when not
necessary
?

Why "manual vacuum"? It's a problem with vacuums invoked from
autovacuum too.

Uh, from the commit it says:

When workload on particular table is append-only, then autovacuum
isn't intended to touch this table. However, user may run vacuum
manually in order to fill visibility map and get benefits of
--------
index-only scans. Then ambulkdelete wouldn't be called for
indexes of such table (because no heap tuples were deleted), only
---------------------------
amvacuumcleanup would be called In this case, amvacuumcleanup
would perform full index scan for two objectives: put recyclable
pages into free space map and update index statistics.

Why would autovacuum run on a table with no expired index entries?

Anti-Wraparound is one case. One where it's really painful to take
forever on lots of unchanged tables. Lots of hot updates another.

Btw, is it just me, or do the commit and docs confuse say stalled when
stale is intended?

Personally, I think the fact that autovacuum doesn't run on suvch tables
and therefore doesn't automatically do index-only scans is a problem. I
tried to fix it years ago, but failed and gave up.

I'm really unhappy about that too.

Greetings,

Andres Freund

On Fri, May 11, 2018 at 12:22:08PM -0700, Andres Freund wrote:

Btw, is it just me, or do the commit and docs confuse say stalled when
stale is intended?

Should be fixed since yesterday's 8e12f4a250d250a89153da2eb9b91c31bb80c483 ?

Justin

On Fri, May 11, 2018 at 12:17 PM, Peter Geoghegan <pg@bowt.ie> wrote:

* Suggest replacement sort item be phrased as: "Remove the
configuration parameter replacement_sort_tuples. <newline> The
replacement selection sort algorithm is no longer used."

Also, it should be moved to "Migration to Version 11", since the only
issue here is compatibility with older versions.

--
Peter Geoghegan