PostgreSQL 12 Beta 1 press release draft

Hi!

On Wed, May 22, 2019 at 6:40 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:

Attached is a draft of the PG12 Beta 1 press release that is going out
this Thursday. The primary goals of this release announcement are to
introduce new features, enhancements, and changes that are available in
PG12, as well as encourage our users to test and provide feedback to
help ensure the stability of the release.

Great work! Thank you for your efforts.

Speaking of feedback, please provide me with your feedback on the
technical correctness of this announcement so I can incorporate changes
prior to the release.

I suggest renaming "Most-common Value Statistics" to "Multicolumn
Most-common Value Statistics". Looking on current title one may think
we didn't support MCV statistics at all, but we did support
single-column case for a long time.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

For CTEs, is forcing inlining the example we want to give, rather than the
example of forcing materialization given?

According to the docs, virtual generated columns aren't yet supported. I'm
pretty sure the docs are right. Do we still want to mention it?

Otherwise it looks good to me.

On Tue, May 21, 2019 at 11:39 PM Jonathan S. Katz <jkatz@postgresql.org>
wrote:

On 2019-05-22 05:39, Jonathan S. Katz wrote:

Speaking of feedback, please provide me with your feedback on the
technical correctness of this announcement so I can incorporate changes
prior to the release.

Here are a few changes.

Main change: generated columns exist only in the STORED variety. VIRTUAL
will hopefully later be added.

thanks,

Erik Rijkers

Find some corrections inline.

On Tue, May 21, 2019 at 11:39:38PM -0400, Jonathan S. Katz wrote:

PostgreSQL 12 Beta 1 Released
=============================

The PostgreSQL Global Development Group announces that the first beta release of
PostgreSQL 12 is now available for download. This release contains previews of
all features that will be available in the final release of PostgreSQL 12,
though some details of the release could change before then.

In the spirit of the open source PostgreSQL community, we strongly encourage you
to test the new features of PostgreSQL 12 in your database systems to help us
eliminate any bugs or other issues that may exist. While we do not advise for
you to run PostgreSQL 12 Beta 1 in your production environments, we encourage
you to find ways to run your typical application workloads against this beta
release.

Your testing and feedback will help the community ensure that the PostgreSQL 12
release upholds our standards of providing a stable, reliable release of the
world's most advanced open source relational database.

PostgreSQL 12 Features Highlights
---------------------------------

### Indexing Performance, Functionality, and Management

PostgreSQL 12 improves the overall performance of the standard B-tree indexes
with improvements to the overall space management of these indexes as well.
These improvements also provide an overall reduction of bloating when using
B-tree for specific use cases, in addition to a performance gain.

Additionally, PostgreSQL 12 adds the ability to rebuild indexes concurrently,
which lets you perform a [`REINDEX`](https://www.postgresql.org/docs/devel/sql-reindex.html) operation

without blocking any writes to the index. The inclusion of this feature should
help with length index rebuilds that could cause potential downtime evens when

events

administration a PostgreSQL database in a production environment.

PostgreSQL 12 extends the abilities of several of the specialized indexing
mechanisms. The ability to create covering indexes, i.e. the `INCLUDE` clause
that was introduced in PostgreSQL 11, have now been added to GiST indexes.

has now

SP-GiST indexes now support the ability to perform K-nearest neighbor (K-NN)
queries for data types that support the distance (`<->`) operation.

The amount of write-ahead log (WAL) overhead generated when creating a GiST,
GIN, or SP-GiST index is also significantly reduced in PostgreSQL 12, which
provides several benefits to the overall disk utilization of a PostgreSQL
cluster as well as using features such as continuous archiving and streaming
replication.

### Inlined WITH queries (Common table expressions)

Common table expressions (aka `WITH` queries) can now be automatically inlined
in a query if they are a) not recursive, b) do not have any side-effects and

I think "are" should be rearranged:
a) are not recursive

c) are only referenced once in a later part of a query. These removes a known
"optimization fence" that has existed since the introduction of the `WITH`
clause in PostgreSQL 8.4

You can force a `WITH` query to be inlined using the `NOT MATERIALIZED` clause,
e.g.

```
WITH c AS NOT MATERIALIZED (
SELECT * FROM a WHERE a.x % 4
)
SELECT * FROM c JOIN d ON d.y = a.x;
```

### Partitioning

PostgreSQL 12 improves on the performance of processing tables with thousands

=> improves on the performance WHEN processing tables with thousands

of partitions for operations that only need to use a small number of partitions.
PostgreSQL 12 also provides improvements to the performance of both
using `COPY` with a partitioned table as well as the `ATTACH PARTITION`
operation. Additionally, the ability to use foreign keys to reference

=> Additionally, the ability for foreign keys to reference partitioned

partitioned tables is now allowed in PostgreSQL 12.

### JSON path queries per SQL/JSON specification

PostgreSQL 12 now lets you execute [JSON path queries](https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH)

=> allows execution of

per the SQL/JSON specification in the SQL:2016 standard. Similar to XPath
expressions for XML, JSON path expressions let you evaluate a variety of

arithmetic expressions and functions in addition to comparing values within JSON
documents.

A subset of these expressions can be accelerated with GIN indexes, letting you

=> allowing execution of

execute highly performant lookups across sets of JSON data.

### Collations

PostgreSQL 12 now supports case-insensitive and accent-insensitive collations
for ICU provided collations, also known as "[nondeterministic collations](https://www.postgresql.org/docs/devel/collation.html#COLLATION-NONDETERMINISTIC)&quot;.
When used, these collations can provide convenience for comparisons and sorts,
but can also lead to a performance penalty depending as a collation may need to
make additional checks on a string.

### Most-common Value Statistics

[`CREATE STATISTICS`](https://www.postgresql.org/docs/devel/sql-createstatistics.html),
introduced in PostgreSQL 10 to help collect more complex statistics to improve
query planning, now supports most-common value statistics. This leads to
improved query plans for distributions that are non-uniform.

### Generated Columns

PostgreSQL 12 lets you create [generated columns](https://www.postgresql.org/docs/devel/ddl-generated-columns.html)
that compute their values based on the contents of other columns. This feature
provides two types of generated columns:

- Stored generated columns, which are computed on inserts and updated and are saved on disk
- Virtual generated columns, which are computed only when a column is read as part of a query

### Pluggable Table Storage Interface

PostgreSQL 12 introduces the pluggable table storage interface that allows for
the creation and use of different storage mechanisms for table storage. New
access methods can be added to a PostgreSQL cluster using the [`CREATE ACCESS METHOD`](https://www.postgresql.org/docs/devel/sql-create-access-method.html)
and subsequently added to tables with the new `USING` clause on `CREATE TABLE`.

A table storage interface can be defined by creating a new [table access method](https://www.postgresql.org/docs/devel/tableam.html).

In PostgreSQL 12, the storage interface that is used by default is the `heap`
access method, which is currently the only supported method.

### Page Checksums

The `pg_verify_checkums` command has been renamed to [`pg_checksums`](https://www.postgresql.org/docs/devel/app-pgchecksums.html)
and now supports the ability to enable and disable page checksums across an
PostgreSQL cluster that is offline. Previously, page checksums could only be
enabled during the initialization of a cluster with `initdb`.

### Authentication

GSSAPI now supports client and server-side encryption and can be specified in
the [`pg_hba.conf`](https://www.postgresql.org/docs/devel/auth-pg-hba-conf.html)
file using the `hostgssenc` and `hostnogssenc` record types. PostgreSQL 12 also
allows for LDAP servers to be discovered based on `DNS SRV` records if

=> allows discovery of LDAP servers

PostgreSQL was compiled with OpenLDAP.

Noted Behavior Changes
----------------------

There are several changes introduced in PostgreSQL 12 that can affect the
behavior as well as management of your ongoing operations. A few of these are
noted below; for information about other changes, please review the
"Migrating to Version 12" section of the [release notes](https://www.postgresql.org/docs/devel/release-12.html).

1. The `recovery.conf` configuration file is now merged into the main
`postgresql.conf` file. PostgreSQL will not start if it detects that
`recovery.conf` is present. To put PostgreSQL into a non-primary mode, you can
use the `recovery.signal` and the `standby.signal` files.

You can read more about [archive recovery](https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY) here:

https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY

2. Just-in-Time (JIT) compilation is now enabled by default.

Additional Features
-------------------

Many other new features and improvements have been added to PostgreSQL 12, some
of which may be as or more important to specific users than what is mentioned above. Please see the [Release Notes](https://www.postgresql.org/docs/devel/release-12.html) for a complete list of new and changed features.

Testing for Bugs & Compatibility
--------------------------------

The stability of each PostgreSQL release greatly depends on YOUR, the community,

you

to test the upcoming version with your workloads and testing tools in order to
find bugs and regressions before the release of PostgreSQL 12. As this is a

Remove: "of PostgreSQL 12" ?

Beta, minor changes to database behaviors, feature details, and APIs are still
possible. Your feedback and testing will help determine the final tweaks on the
new features, so please test in the near future. The quality of user testing
helps determine when we can make a final release.

A list of [open issues](https://wiki.postgresql.org/wiki/PostgreSQL_12_Open_Items)
is publicly available in the PostgreSQL wiki. You can
[report bugs](https://www.postgresql.org/account/submitbug/) using this form on
the PostgreSQL website:

https://www.postgresql.org/account/submitbug/

Beta Schedule
-------------

This is the first beta release of version 12. The PostgreSQL Project will
release additional betas as required for testing, followed by one or more
release candidates, until the final release in late 2019. For further
information please see the [Beta Testing](https://www.postgresql.org/developer/beta/) page.

Links
-----

* [Download](https://www.postgresql.org/download/)
* [Beta Testing Information](https://www.postgresql.org/developer/beta/)
* [PostgreSQL 12 Beta Release Notes](https://www.postgresql.org/docs/devel/release-12.html)
* [PostgreSQL 12 Open Issues](https://wiki.postgresql.org/wiki/PostgreSQL_12_Open_Items)
* [Submit a Bug](https://www.postgresql.org/account/submitbug/)

On Wed, May 22, 2019 at 07:05:49AM +0200, Erik Rijkers wrote:

--- 12beta1.md.orig	2019-05-22 06:33:16.286099932 +0200
+++ 12beta1.md	2019-05-22 06:48:24.279966057 +0200
@@ -30,12 +30,12 @@
Additionally, PostgreSQL 12 adds the ability to rebuild indexes concurrently,
which lets you perform a [`REINDEX`](https://www.postgresql.org/docs/devel/sql-reindex.html) operation
without blocking any writes to the index. The inclusion of this feature should
-help with length index rebuilds that could cause potential downtime evens when
-administration a PostgreSQL database in a production environment.
+help with lengthy index rebuilds that could cause potential downtime when
+administrating a PostgreSQL database in a production environment.

Should be "administering"

Common table expressions (aka `WITH` queries) can now be automatically inlined
in a query if they are a) not recursive, b) do not have any side-effects and
-c) are only referenced once in a later part of a query. These removes a known
+c) are only referenced once in a later part of a query. This removes a known

I would remove "known".

On Wed, 22 May 2019 at 15:39, Jonathan S. Katz <jkatz@postgresql.org> wrote:

Speaking of feedback, please provide me with your feedback on the
technical correctness of this announcement so I can incorporate changes
prior to the release.

Seems like a pretty good summary. Thanks for writing that up.

Couple notes from my read through:

help with length index rebuilds that could cause potential downtime evens when
administration a PostgreSQL database in a production environment.

length -> lengthy?
evens -> events? (mentioned by Justin)
administration -> administering? (mentioned by Justin)

PostgreSQL 12 also provides improvements to the performance of both
using `COPY` with a partitioned table as well as the `ATTACH PARTITION`
operation. Additionally, the ability to use foreign keys to reference
partitioned tables is now allowed in PostgreSQL 12.

I'd say nothing has been done to improve the performance of ATTACH
PARTITION. Robert did reduce the lock level required for that
operation, but that should make it any faster.

I think it would be good to write:

PostgreSQL 12 also provides improvements to the performance of both
`INSERT` and `COPY` into a partitioned table. `ATTACH PARTITION` can
now also be performed without blocking concurrent queries on the
partitioned table. Additionally, the ability to use foreign keys to
reference partitioned tables is now allowed in PostgreSQL 12.

### Most-common Value Statistics

I think this might be better titled:

### Most-common Value Extended Statistics

which is slightly different from what Alexander mentioned. I think we
generally try to call them "extended statistics", even if the name of
the command does not quite agree. git grep -i "extended stat" shows
more interesting results than git grep -i "column stat" when done in
the doc directory. Either way, I think it's slightly confusing to
title this the way it is since we already have MCV stats and have had
for a long time.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2019/05/22 15:47, David Rowley wrote:

On Wed, 22 May 2019 at 15:39, Jonathan S. Katz <jkatz@postgresql.org> wrote:

PostgreSQL 12 also provides improvements to the performance of both
using `COPY` with a partitioned table as well as the `ATTACH PARTITION`
operation. Additionally, the ability to use foreign keys to reference
partitioned tables is now allowed in PostgreSQL 12.

I'd say nothing has been done to improve the performance of ATTACH
PARTITION. Robert did reduce the lock level required for that
operation, but that should make it any faster.

Maybe you meant "..., but that shouldn't make it any faster."

Thanks,
Amit

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

Attached is a draft of the PG12 Beta 1 press release that is going out
this Thursday. The primary goals of this release announcement are to
introduce new features, enhancements, and changes that are available in
PG12, as well as encourage our users to test and provide feedback to
help ensure the stability of the release.

Awesome!

### Authentication

GSSAPI now supports client and server-side encryption and can be
specified in the
[`pg_hba.conf`](https://www.postgresql.org/docs/devel/auth-pg-hba-conf.html)
file using the `hostgssenc` and `hostnogssenc` record
types. PostgreSQL 12 also allows for LDAP servers to be discovered
based on `DNS SRV` records if PostgreSQL was compiled with OpenLDAP.

Maybe a better title for this section would be "Authentication /
Encryption" or maybe even "Connection security"? I get that this is a
press release though, so feel free to disregard.

Thanks,
--Robbie

Hi,

### Indexing Performance, Functionality, and Management

PostgreSQL 12 improves the overall performance of the standard B-tree indexes
with improvements to the overall space management of these indexes as well.
These improvements also provide an overall reduction of bloating when using
B-tree for specific use cases, in addition to a performance gain.

I'm not sure everyone will understand bloating as a term? Perhaps just
saying 'also reduce index size when the index is modified frequently' or
such?

### Inlined WITH queries (Common table expressions)

Common table expressions (aka `WITH` queries) can now be automatically inlined
in a query if they are a) not recursive, b) do not have any side-effects and
c) are only referenced once in a later part of a query. These removes a known
"optimization fence" that has existed since the introduction of the `WITH`
clause in PostgreSQL 8.4

You can force a `WITH` query to be inlined using the `NOT MATERIALIZED` clause,
e.g.

```
WITH c AS NOT MATERIALIZED (
SELECT * FROM a WHERE a.x % 4
)
SELECT * FROM c JOIN d ON d.y = a.x;
```

Wouldn't it be more important to reference how they can be *forced* to
be materialized? Because that'll be what users will need. And I think if
we reference NOT MATERIALIZED it also sounds like CTEs will not
automatically inlined ever.

### Pluggable Table Storage Interface

PostgreSQL 12 introduces the pluggable table storage interface that allows for
the creation and use of different storage mechanisms for table storage. New
access methods can be added to a PostgreSQL cluster using the [`CREATE ACCESS METHOD`](https://www.postgresql.org/docs/devel/sql-create-access-method.html)
and subsequently added to tables with the new `USING` clause on `CREATE TABLE`.

A table storage interface can be defined by creating a new [table access method](https://www.postgresql.org/docs/devel/tableam.html).

In PostgreSQL 12, the storage interface that is used by default is the `heap`
access method, which is currently the only supported method.

I think s/which is currently the only supported method/which currently
is the only built-in method/ or such would be good. I don't know what
"supported" would actually mean here.

### Authentication

GSSAPI now supports client and server-side encryption and can be specified in
the [`pg_hba.conf`](https://www.postgresql.org/docs/devel/auth-pg-hba-conf.html)
file using the `hostgssenc` and `hostnogssenc` record types. PostgreSQL 12 also
allows for LDAP servers to be discovered based on `DNS SRV` records if
PostgreSQL was compiled with OpenLDAP.

Is this really accurately categorized under authentication? Because it's
really ongoing encryption, as an alternative to tls?

Noted Behavior Changes
----------------------

There are several changes introduced in PostgreSQL 12 that can affect the
behavior as well as management of your ongoing operations. A few of these are
noted below; for information about other changes, please review the
"Migrating to Version 12" section of the [release notes](https://www.postgresql.org/docs/devel/release-12.html).

1. The `recovery.conf` configuration file is now merged into the main
`postgresql.conf` file. PostgreSQL will not start if it detects that
`recovery.conf` is present. To put PostgreSQL into a non-primary mode, you can
use the `recovery.signal` and the `standby.signal` files.

You can read more about [archive recovery](https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY) here:

https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY

2. Just-in-Time (JIT) compilation is now enabled by default.

I think we should probably list the removal of WITH OIDs.

Greetings,

Andres Freund

On 5/21/19 11:39 PM, Jonathan S. Katz wrote:

Hi,

Attached is a draft of the PG12 Beta 1 press release that is going out
this Thursday. The primary goals of this release announcement are to
introduce new features, enhancements, and changes that are available in
PG12, as well as encourage our users to test and provide feedback to
help ensure the stability of the release.

Speaking of feedback, please provide me with your feedback on the
technical correctness of this announcement so I can incorporate changes
prior to the release.

Thank you everyone for your feedback. I have incorporated most of it
into this latest revision. For your convenience I have also attached a diff.

Please let me know if you have any questions. If you have additional
feedback please provide it before 7am EDT tomorrow.

Thanks!

Jonathan

Hi Jonathan,

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

If you have additional feedback please provide it before 7am EDT
tomorrow.

Thanks for writing this up. Below are some things I noticed when
reading through (disclaimer: I'm not a native speaker).

PostgreSQL 12 extends the abilities of several of the specialized indexing
mechanisms. The ability to create covering indexes, i.e. the `INCLUDE` clause
that was introduced in PostgreSQL 11, havs now been added to GiST indexes.

"havs" should be "has"

The amount of write-ahead log (WAL) overhead generated when creating a GiST,
GIN, or SP-GiST index is also significantly reduced in PostgreSQL 12, which
provides several benefits to the overall disk utilization of a PostgreSQL
cluster as well as using features such as continuous archiving and streaming
replication.

The "using" reads odd to me. I think it would be better either omitted,
or expanded to "when using".

### Partitioning

PostgreSQL 12 improves on the performance when processing tables with thousands
of partitions for operations that only need to use a small number of partitions.

PostgreSQL 12 also provides improvements to the performance of both `INSERT` and
`COPY` into a partitioned table. `ATTACH PARTITION` can now also be performed
without blocking concurrent queries on the partitioned table. Additionally, the
ability to use foreign keys to reference partitioned tables is now allowed in
PostgreSQL 12.

"the ability to use ... is now allowed" doesn't look right. How about
"the ability to use ... is now provided" or "using ... is now allowed"?

### Collations

PostgreSQL 12 now supports case-insensitive and accent-insensitive collations
for ICU provided collations,

"collations for ... collations" doesn't look right. I think either
"comparison for ... collations" or "collation ... for collations" would
be better, but I'm not sure which.

### Generated Columns

PostgreSQL 12 lets you create [generated columns](https://www.postgresql.org/docs/devel/ddl-generated-columns.html)
that compute their values based on the contents of other columns. This feature
provides stored generated columns, which are computed on inserts and updated and
are saved on disk.

Should be "on inserts and updates".

### Pluggable Table Storage Interface

PostgreSQL 12 introduces the pluggable table storage interface that allows for
the creation and use of different storage mechanisms for table storage. New
access methods can be added to a PostgreSQL cluster using the [`CREATE ACCESS METHOD`](https://www.postgresql.org/docs/devel/sql-create-access-method.html)
and subsequently added to tables with the new `USING` clause on `CREATE TABLE`.

Should be either "the CREATE ACCESS METHOD command" or just "CREATE
ACCESS METHOD".

### Page Checksums

The `pg_verify_checkums` command has been renamed to [`pg_checksums`](https://www.postgresql.org/docs/devel/app-pgchecksums.html)
and now supports the ability to enable and disable page checksums across an
PostgreSQL cluster that is offline.

Should be "a PostgreSQL cluster", not "an".

- ilmari
--
"A disappointingly low fraction of the human race is,
at any given time, on fire." - Stig Sandbeck Mathisen

Hi Ilmari,

On 5/22/19 6:52 PM, Dagfinn Ilmari Mannsåker wrote:

Hi Jonathan,

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

If you have additional feedback please provide it before 7am EDT
tomorrow.

Thanks for writing this up. Below are some things I noticed when
reading through (disclaimer: I'm not a native speaker).

Thanks for the fixes + suggestions. I accepted most of them. Attached is
v3 of the patch, along with a diff.

Best,

Jonathan

On Thu, 23 May 2019 at 15:31, Jonathan S. Katz <jkatz@postgresql.org> wrote:

Attached is
v3 of the patch, along with a diff.

Minor details, but this query is not valid:

WITH c AS MATERIALIZED (
SELECT * FROM a WHERE a.x % 4
)
SELECT * FROM c JOIN d ON d.y = a.x;

a.x % 4 is not a boolean clause, and "a" is not in the main query, so
a.x can't be referenced there.

How about:

WITH c AS MATERIALIZED (
SELECT * FROM a WHERE a.x % 4 = 0
)
SELECT * FROM c JOIN d ON d.y = c.x;

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 5/23/19 1:45 AM, David Rowley wrote:

On Thu, 23 May 2019 at 15:31, Jonathan S. Katz <jkatz@postgresql.org> wrote:

Attached is
v3 of the patch, along with a diff.

Minor details, but this query is not valid:

WITH c AS MATERIALIZED (
SELECT * FROM a WHERE a.x % 4
)
SELECT * FROM c JOIN d ON d.y = a.x;

a.x % 4 is not a boolean clause, and "a" is not in the main query, so
a.x can't be referenced there.

...that's the only gotcha I'm actually embarrassed about. Fixed.

Thanks,

Jonathan

On Thu, May 23, 2019 at 1:01 PM Jonathan S. Katz <jkatz@postgresql.org>
wrote:

On 5/23/19 1:45 AM, David Rowley wrote:

On Thu, 23 May 2019 at 15:31, Jonathan S. Katz <jkatz@postgresql.org>

wrote:

Attached is
v3 of the patch, along with a diff.

Minor details, but this query is not valid:

WITH c AS MATERIALIZED (
SELECT * FROM a WHERE a.x % 4
)
SELECT * FROM c JOIN d ON d.y = a.x;

a.x % 4 is not a boolean clause, and "a" is not in the main query, so
a.x can't be referenced there.

...that's the only gotcha I'm actually embarrassed about. Fixed.

The ON d.y = a.x still needs to be changed to ON d.y = c.x

Pantelis

On Thu, May 23, 2019 at 4:36 PM Pantelis Theodosiou <ypercube@gmail.com>
wrote:

On Thu, May 23, 2019 at 1:01 PM Jonathan S. Katz <jkatz@postgresql.org>
wrote:

On 5/23/19 1:45 AM, David Rowley wrote:

On Thu, 23 May 2019 at 15:31, Jonathan S. Katz <jkatz@postgresql.org>

wrote:

Attached is
v3 of the patch, along with a diff.

Minor details, but this query is not valid:

WITH c AS MATERIALIZED (
SELECT * FROM a WHERE a.x % 4
)
SELECT * FROM c JOIN d ON d.y = a.x;

a.x % 4 is not a boolean clause, and "a" is not in the main query, so
a.x can't be referenced there.

...that's the only gotcha I'm actually embarrassed about. Fixed.

The ON d.y = a.x still needs to be changed to ON d.y = c.x

Pantelis

Another minor point in the sentence "... which is currently is ...":

In PostgreSQL 12, the storage interface that is used by default is the

heap access method, which is currently is the only built-in method.

But I forgot the most important. Thank you for the new version and all the
work that has gone into it!