Re: PostgreSQL 16 release announcement draft - Mailing list pgsql-hackers

On 2023-Aug-24, Jonathan S. Katz wrote:

> ### Performance Improvements
> 
> PostgreSQL 16 improves the performance of existing PostgreSQL functionality
> through new query planner optimizations. In this latest release, the query
> planner can parallelize  `FULL` and `RIGHT` joins, generate better optimized
> plans for queries that use aggregate functions (e.g. `count`) with a `DISTINCT`
> or `ORDER BY` clause, utilize incremental sorts for `SELECT DISTINCT` queries,
> and optimize window function executions so they execute more efficiently.

"optimize window function executions so that they execute blah" sounds
redundant and strange. Maybe just "optimize execution of window
functions" is sufficient?  Also, using "e.g." there looks somewhat out
of place; maybe "(such as `count`)" is a good replacement?

> It also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to
> identify rows not present in a joined table.

Wait.  Are you saying we didn't have those already?  Looking at
release-16.sgml I think this refers to commit 16dc2703c541, which means
this made them more efficient rather than invented them.


> This release includes improvements for bulk loading using `COPY` in both single
> and concurrent operations, with tests showing up to a 300% performance
> improvement in some cases. PostgreSQL adds support for load balancing in clients

PostgreSQL 16

> that use `libpq`, and improvements to vacuum strategy that reduce the necessity
> of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
> using `SIMD` in both x86 and ARM architectures, resulting in performance gains
> when processing ASCII and JSON strings, and performing array and subtransaction
> searches.
> 
> ### Logical replication 
> 
> Logical replication lets PostgreSQL users stream data to other PostgreSQL

"L.R. in PostgreSQL lets users"?

> instances or subscribers that can interpret the PostgreSQL logical replication
> protocol. In PostgreSQL 16, users can perform logical decoding from a standby

s/decoding/replication/ ? (It seems odd to use "decoding" when the
previous sentence used "replication")

> instance, meaning a standby can publish logical changes to other servers. This
> provides developers with new workload distribution options – for example, using
> a standby rather than the busier primary to logically replicate changes to
> downstream systems.
> 
> Additionally, there are several performance improvements in PostgreSQL 16 to
> logical replication. Subscribers can now apply large transactions using parallel
> workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree

"a primary key", no caps.

> indexes instead of sequential scans to find rows. Under certain conditions,
> users can also speed up initial table synchronization using the binary format.
> 
> There are several access control improvements to logical replication in
> PostgreSQL 16, including the new predefined role pg_create_subscription, which
> grants users the ability to create a new logical subscription. Finally, this
> release begins adding support for bidirectional logical replication, introducing
> functionality to replicate data between two tables from different publishers.

"to create a new logical subscription" -> "to create new logical subscriptions"

> ### Developer Experience
> 
> PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
> constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
> `IS JSON`. This release also introduces the ability to use underscores for
> thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
> as `0x1538`, `0o12470`, and `0b1010100111000`.
> 
> Developers using PostgreSQL 16 will also benefit from the addition of multiple
> commands to `psql` client protocol, including the `\bind` command, which allows
> users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
> to substitute the variables. 

This paragraph sounds a bit suspicious.  What do you mean with "multiple
commands to psql client protocol"?  Also, I think "to execute parameterized
queries" should be "to prepare parameterized queries", and later "then
use \bind to execute the query substituting the variables".



> ### Monitoring
> 
> A key aspect of tuning the performance of database workloads is understanding
> the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
> how you can analyze this data with the new pg_stat_io view, which tracks key I/O
> statistics such as shared_buffer hits and I/O latency.

Hmm, I think what pg_stat_io gives you is data which wasn't available
previously at all.  Maybe do something like "Pg 16 introduces
pg_stat_io, a new source of key I/O metrics that can be used for more
fine grained something something".

> Additionally, this release adds a new field to the `pg_stat_all_tables` view 
> that records a timestamp representing when a table or index was last scanned.
> PostgreSQL also makes auto_explain more readable by logging values passed into

PostgreSQL 16

> parameterized statements, and improves accuracy of pg_stat_activity's
> normalization algorithm.

I think jian already mentioned that this refers to pg_stat_statement
query fingerprinting.  I know that the query_id also appears in
pg_stat_activity, but that is much newer, and it's not permanent there
like in pss.  Maybe it should be "of the query fingerprinting algorithm
used by pg_stat_statement and pg_stat_activity".

> ## Images and Logos
> 
> Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
> trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).

Isn't this just the "PostgreSQL Community Association", no Canada?



-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Ellos andaban todos desnudos como su madre los parió, y también las mujeres,
aunque no vi más que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)