PostgreSQL 13 (2020-09-24 => 2025-11-??)

• https://pgpedia.info/postgresql-versions/postgresql-13.html

• https://www.postgresql.org/about/featurematrix/

• https://www.postgresql.org/docs/13/release-13.html

• https://docs.postgresql.fr/13/

• https://www.postgresql.org/about/news/postgresql-13-released-2077/

• https://www.postgresql.org/about/press/presskit13/fr/

• https://modern-sql.com/feature

• https://fediverse.org/MarkusWinand

• https://www.2ndquadrant.com/en/blog/category/postgresql/postgresql-13/

• https://www.youtube.com/watch?v=8Op-We-XoNc (Videos Dalibo)

Migration to Version 13

A dump/restore using pg_dumpall or use of pg_upgrade or logical replication is required for those wishing to migrate data from any previous release.

Overview

PostgreSQL 13 contains many new features and enhancements, including:

• Space savings and performance gains from de-duplication of B-tree index entries

• Improved performance for queries that use aggregates or partitioned tables

• Better query planning when using extended statistics

• Parallelized vacuuming of indexes

• Incremental sorting

The above items and other new features of PostgreSQL 13 are explained in more detail in the sections below.

Articles

PostgreSQL 13 s’accompagne d’améliorations significatives de son système d’indexation

See also

• https://postgresql.developpez.com/actu/309119/PostgreSQL-13-s-accompagne-d-ameliorations-significatives-de-son-systeme-d-indexation-d-optimisations-pour-l-administration-quotidienne-et-de-plus-de-commodites-pour-les-developpeurs-d-applications/

PostgreSQL 13 (released September 2020) by 2ndQuadrant

• https://www.2ndquadrant.com/en/postgresql/2ndquadrants-passion-postgresql/#pg13

• https://www.2ndquadrant.com/en/blog/new-features-in-postgresql-13-full-webinar-video/

• https://www.2ndquadrant.com/en/blog/category/postgresql/postgresql-13/

Mettre à jour PostgreSQL pour améliorer les performances

• http://pgphil.ovh/migration_performance_12_13_01.php

Changes

Below you will find a detailed account of the changes between PostgreSQL 13 and the previous major release.

Partitioning

• Allow pruning of partitions to happen in more cases (Yuzuko Hosoya, Amit Langote, Álvaro Herrera)

• Allow partitionwise joins to happen in more cases (Ashutosh Bapat, Etsuro Fujita, Amit Langote, Tom Lane)

• For example, partitionwise joins can now happen between partitioned tables even when their partition bounds do not match exactly.

• Support row-level BEFORE triggers on partitioned tables (Álvaro Herrera)

• However, such a trigger is not allowed to change which partition is the destination.

• Allow partitioned tables to be logically replicated via publications (Amit Langote)

• Previously, partitions had to be replicated individually. Now a partitioned table can be published explicitly, causing all its partitions to be published automatically. Addition/removal of a partition causes it to be likewise added to or removed from the publication. The CREATE PUBLICATION option publish_via_partition_root controls whether changes to partitions are published as their own changes or their parent’s.

• Allow logical replication into partitioned tables on subscribers (Amit Langote)

• Previously, subscribers could only receive rows into non-partitioned tables.

• Allow whole-row variables (that is, table.*) to be used in partitioning expressions (Amit Langote)

Autovacuum

• https://www.cybertec-postgresql.com/en/postgresql-autovacuum-insert-only-tables/

Most people know that autovacuum is necessary to get rid of dead tuples.

These dead tuples are a side effect of PostgreSQL’s MVCC implementation.

So many people will be confused when they read that from PostgreSQL v13 on, commit b07642dbc adds support for autovacuuming insert-only tables (also known as “append-only tables”).

This article explains the reasons behind that and gives some advice on how to best use the new feature. It will also explain how to achieve similar benefits in older PostgreSQL releases.

Note that all that I say here about insert-only tables also applies to insert-mostly tables, which are tables that receive only few updates and deletes.

Allow invisible PROMPT2 in psql

• https://www.depesz.com/2019/11/26/waiting-for-postgresql-13-allow-invisible-prompt2-in-psql/

Enable BEFORE row-level triggers for partitioned tables

• https://www.depesz.com/2020/03/31/waiting-for-postgresql-13-enable-before-row-level-triggers-for-partitioned-tables/

Allow pg_stat_statements to track planning statistics

• https://www.depesz.com/2020/04/02/waiting-for-postgresql-13-allow-pg_stat_statements-to-track-planning-statistics/

• https://www.postgresql.org/docs/current/pgstatstatements.html

Add the option to report WAL usage in EXPLAIN and auto_explain

See also

• https://www.depesz.com/2020/04/17/waiting-for-postgresql-13-add-the-option-to-report-wal-usage-in-explain-and-auto_explain/

partitioning-improvements-in-postgresql-13

• https://www.highgo.ca/2020/08/08/partitioning-improvements-in-postgresql-13/

The table partitioning feature in PostgreSQL has come a long way after the declarative partitioning syntax added to PostgreSQL 10.

The partitioning feature in PostgreSQL was first added by PG 8.1 by Simon Rigs, it has based on the concept of table inheritance and using constraint exclusion to exclude inherited tables (not needed) from a query scan.

The exclusion constraint will basically do the pruning of tables based on the query filter.

Before declarative partitioning https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f0e44751d7175f was added to PG, it meant that the child partitions, constraints and triggers etc needed to be created manually which can be cumbersome and could lead to errors.

Thanks to the great work from Amit Langote and others, the declarative partitioning feature on PG-10 meant that the user don’t need to create the partitions manually or create the constraints and triggers for routing the rows to the correct partition.

All the artefacts required for setting up partitioning would be done by simply creating the partition table and specify the partitions using a standard syntax, this is was great step forward and the one that makes this feature very user friendly.

This blog is about number of enhancements for partitions added to PG-13.

I am going to list down all the partitioning enhancements in the blog and and will demonstrate some of them with examples.

Unicode normalization in PostgreSQL 13

• https://www.2ndquadrant.com/en/blog/unicode-normalization-in-postgresql-13/

PostgreSQL 13: LIMIT … WITH TIES

• https://www.2ndquadrant.com/en/blog/postgresql-13-limit-with-ties/

Extended Statistics Improvements in Postgres 13

• https://pganalyze.com/blog/postgres13-better-performance-monitoring-usability

Postgres 10 introduced the concept of extended statistics.

Postgres keeps some statistics about the “shape” of your data to ensure it can plan queries efficiently, but the statistics kept by default cannot track things like inter-column dependencies.

Extended statistics were introduced to address that: These are database objects (like indexes) that you create manually with CREATE STATISTICS to give the query planner more information for more specific situations.

These would be expensive for Postgres to determine automatically, but armed with an understanding of the semantics of your schema, you can provide that additional info. Used carefully, this can lead to massive performance improvements.

Postgres 13 brings a number of small but important improvements to extended statistics, including support for using them with OR clauses and in IN/ANY constant lists, allowing consideration of multiple extended statistics objects in planning a query, and support for setting a statistics target for extended statistics:

ALTER STATISTICS table_stx SET STATISTICS 1000;

Like with the regular statistics target, this is a trade-off between additional planning time (and longer ANALYZE runs), versus having more precise plans.

We recommend using this in a targeted manner using EXPLAIN plans to confirm plan changes.

TLS Related Updates in PostgreSQL 13 by Cary Huang

• https://www.highgo.ca/2020/08/21/tls-related-updates-in-postgresql-13/

Introduction

The upcoming major release of PostgreSQL 13 has several important behavioral updates related to the TLS implementation.

These updates may have some to your current PostgreSQL security deployment if you were considering to upgrade to 13 when it officially releases.

Today I would like to do a quick summary of these updates.

Conclusion

PG13 brings many exciting new features and enhancements to PostgreSQL and many of these new changes need to be carefully assessed for potential incompatibility with the previous PG versions.

Today, our focus is mainly on new updates related to TLS, which may have behavioral impacts to the existing deployment and the way existing client and server communicates using TLS.

For the full changes and potential incompatibilities, visit the official change log here