Get best of the week

Postgresso 20


Life goes on. We continue to acquaint you with the most interesting PostgreSQL news

main news


Feature Freeze
PostgreSQL version 13 functionality is frozen. Now only improvements and bug fixes. The list of new, contrary to many expectations, is quite extensive. Read our article
How Much New in The Devil's Dozen , where there are many examples. But also in the section of the Postgresso Articles there are links to articles devoted to individual features.

Releases


Postgres Pro Enterprise 12.2.1

This version combines the innovations of PostgreSQL 12 and the features of the Enterprise branch. Multimaster
extension : as in 11.x, and in other versions of Enterprise, it is recommended to use it in a 2 + 1 configuration when one of the referee nodes. More details in the documentation . And now you can check the consistency of data on the nodes of the cluster using the function . In CFS, you can now choose compression algorithms. Zstd (default), zlib and pglz are supported, but other algorithms can be added. Another shock feature of Enterprise is the PTRACK mechanism, which is necessary for the efficient operation of our application pg_probackupmtm.check_query()



, - was thoroughly redesigned and received a new external API. In order for backups already created with PTRACK in pg_probackup to work in Postgres Pro Enterprise 12.x, you need to update pg_probackup to version 2.2.6 or higher and configure PTRACK to copy again.

An important patch for B-tree index deduplication (by Anastasia Lubennikova, Postgres Professional) in PostgreSQL 13, but its functionality is already in Enterprise 12.2.1. Moreover, the exclusion of duplicates can be disabled for the created indexes using the deduplicate_itemsCREATE INDEX command parameter . This is here .

A few more innovations briefly:


Left and the Postgres Pro 12.2.1 of Standard . Since the differences from PostgreSQL in Standard are not as radical as in Postgres Pro Enterprise, we simply refer to the changelog .

pgAdmin 4 version 4.20

This release adds Collation, FTS Configuration, FTS Dictionary, FTS Parser, FTS Template, Domain, Domain Constraints, and Types for Schema Diff .

Read about the release here , and you can download from here .

WAL-G v0.2.15

In the new version of Yandex backup, you can reduce the lag of the replica by commands catchup-pushandcatchup-fetchcreating an incremental backup from a given LSN with a lagging replica. Support has been added for Libsodium encryption, Retrying for AWS.

repmgr 5.1 The

backup from 2ndQuadrant has been enriched with significant improvements: now the requirement that the user repmgr be a superuser of the database has been removed from wherever possible. An ordinary database user can copy his database, and a user with the REPLICATION role will specify the connections and slots (if necessary). But some operations still require an option -S | --superuser. Download from here .

pgBackRest 2.25 The

main news of this release is LZ4-compression. Added useful option: --dry-runfor the commandexpire: you can see which backups / archives will be deleted with this command without deleting anything. You can limit the size of the backup to the value reported at the beginning of copying, relying on playing the WAL if the file swells.

check_pgbackrest 1.8

A new version of the BackRest monitoring utility has also been released (it is developed not by CrunchyData, like pgBackRest itself, but by Dalibo). An option appeared --list-archives: print a list of all archived WAL segments.

pgmetrics v1.9

There is a collection of metrics with AWS RDS and Aurora , a collection of definitions of indexes, types and number of backends, version and environment checks before calling functions. And you can SET ROLE before you start collecting metrics as an option --role. Release on github .

pg_show_plans

The extension, which Hans-Jürgen Schönig of Cybertec offers to try, allows you to see simultaneously all instant plans that are currently active - while they are still being executed. He explains their work on his own blog and on the company's website . And not only him. pg_show_plans was even already discussed on Tuesdays at Samokhvalov (see our section Webinars ). By the way, the pg_query_state extension , which is available in Postgres Pro Standard / Enterprise, is an interesting alternative. But vanilla PostgreSQL will have to attach a patch.

Joe 0.6.2 / 1/0 We

repent: about the appearance of this utility with the chat bot interfacewe wrote two lines in the past and the year before last Postgresso, although it deserves more. This DBA bot, as the developers from Postgres.ai call it, is a tool for testing database changes without endangering the industrial base, but without long copying to the test environment (terabyte databases are copied in seconds). In addition, this test resource is shared: a dozen developers can work simultaneously. Details Anatoly Stansler said in a report on Highload ++, we recommend that you look.
In versions 0.6.2 and 0.6.1 only bug fixes, but interesting things appeared in 0.6.0: working with hypothetical indexes (with the HypoPG extension) Compound teams starting with are working with hypothetical indices hypo. For example: hypo reset- kill all hypothetical indexes. The new team plan gives the plan without fulfilling the request.

Pyrseas 0.9.1

This version of the extension-utility for describing the database schema in YAML / JSON, reconciling database schemas with a given one differs from previous versions mainly by PostgreSQL 12 support. Here is a page on PGXN .

pgFormatter 4.3

Added two options, fixed bugs:

  • -r | --redshift for AWS Redshift keywords
  • -N | --numberingAdds a view prefix to each line /* Statement # n */;
  • fixed formatting inside the sentence with WITH;
  • fixed script formatting when working with psql;
  • In addition, several built-in functions have been added to the list of PostgreSQL functions.

pg_timeout 0.0.1

And one more extension: with it you can set the inactivity timeout for the session by defining it in two GUC:

  • pg_timeout.naptime: how many seconds the background process sleeps between inaction checks (10 seconds by default)
  • pg_timeout.idle_session_timeout: how many seconds a session is allowed to live in idle (60 seconds by default).

The corresponding notifications will appear in the log. The release number speaks for itself.

pg_logqueryid

And this extension includes logging of identifiers of requests in pg_stat_statementswhen it is included auto_explain.

Psycopg 2.8.5

This Python connector now supports the AIX platform.

pg_timetable 2.0

This scheduler (scheduler) are added control possibilities: You can mark tasks as @reboot, @every, or @after.

pgBadger 11.2

In the popular log analyzer Gilles Darold added :

  • support and auto-recognition of the AWS Redshift log format;
  • support for the new pgbouncer 1.11 log format;
  • zstd and lz4 compression support;
  • you can run the utility like this: pgbadger -I -O "/out-dir/data" --noreport /var/log/postgresql*.logor
  • as follows: pgbadger -I -l "/out-dir/data/LAST_PARSED" -H "/out-dir/reports" /out-dir/data/2020/02/19/*.bin.


PAF v2.3.0

The version of this very popular fault-tolerant solution from ClusterLabs, based on Pacemaker + Corosync , is primarily distinguished by PostgreSQL 12 support. It will not work with versions older than Pacemaker 1.1.13 + corosync 2.x.

pgagroal 0.5.1 The
new version of the puller can be downloaded from here: sources , RPM .

The clouds


Amazon RDS Now Supports PostgreSQL 12

The announcement says that along with the release you will receive (among others) updated extensions:
PostGIS 3.0,
PGAudit 1.4,
wal2json 2.1,
PLV8 2.3.14,
Orafce 3.8,
pg_repack 1.4.5,
pg_hint_plan 1.3.5.22 a77c,
pglogical 2.3.0.

How to Authenticate Postgres Users Against Amazon AWS Cognito

Unable to find a satisfactory Cognito authentication answer for PostgreSQL, Ernst-Georg Schmid as ergo wrote the cognito_PAM.py script to facilitate authentication of PostgreSQL and pgbouncer users.

Articles


Let's start with articles on the fierce spite of the day. But these two articles are interesting not only by the current agenda, they are informative in the technical sense.

Charting the spread of COVID-19 using data

The Timescale blog will show you step-by- step how to write queries to build virus distribution maps. The author took the data from the Johns Hopkins University github , uploaded it to TimescaleDB , and used Grafana for visualization on the map .

Intersecting GPS-Tracks to identify infected individuals

An article on Cybertec with many pictures and listings. Window functions are used to detect infected contacts, and QGIS is used to visualize the paths of movement of virus owners.

Create and Visualize Machine Learning Models Using 2UDA

2ndQuadrant (this company is unusually active in these difficult times: webinars and articles are coming in stream) in this article offers to try your own set of analytics applications, which also include a machine learning package. 2UDA integrates into the Orange environment and communicates with PostgreSQL up to its latest version - 12.2. This time they are taught how to work with Random Forest . In previous articles of the series we taught SVM (aka Method of Support Vectors) and look for nearest neighbors (KNN).

And here is their series on the development of PostgreSQL for Windows : parts 3 2 1 .

Comparing MongoDB vs Postgres over JOINs

We should start not with the title of the article , but with the authors: Michael Stonebraker (Michael Stonebraker, the same one) and lvaro Hernández . Alvaro, however, is also “the same” - he has repeatedly spoken at conferences in Moscow: for example, his report on StackGres at PGConf.Russia 2020. Well, the topic of comparing NoSQL with Postgres has not lost its relevance.

10 Things I Hate About Postgres

Another Cold Phasecontrast soul Postgres vs Other DBMS. Some of the comments of Rick Branson (Rick Branson) are very tough. For example: process scalability is getting better and better with every major release. But in the end there is a tight performance limit due to the architecture of Postgres compared to the same MySQL with its one thread per connection .

Parallel Query Inside Function

Abdul Yadi analyzes what is happening inside the SQL and PL / pgSQL functions. He found that parallel query execution, working in SQL queries without functions, was disabled when executing SQL and PL / pgSQL functions. And it shows a trick how to enable parallelism in PL / pgSQL.

Monitoring the query planner

Julien Rouhaud writesin my little house I am talking about monitoring the Planner.

Waiting for PostgreSQL 13 - Allow pg_stat_statements to track planning statistics
Hubert "Depeche" Lubashevsky writes about this patch, and how he tested it on pgbench and how you can run into long query planning (the plan is not reused). By the way, our review also has examples with pg_stat_statements .

PostgreSQL Person of the Week: Michael Paquier

Michelle Pacquier (he was born and studied in France, and the latter works in Japan), many familiar with the hackers mailing list , began to work with version 8.3. Answers the question about his favorite extension: pg_stat_statement! (its exclamation mark), and from the features of the latest versions selects TableAM .

But the choice of person weeks Aylarov Battison (Ilaria Battiston) was unpredictable: it is 22-year-old student, Italian, Data Engineering studies at the Technical University of Munich. What did she deserve the title - read .

We write in PostgreSQL on the sublight: 1 host, 1 day, 1TB

Kirill Borovikov akaKilor(company "Tensor") writes about how to make writing to the database more efficient by properly organizing data flows. Spoiler: start with sectioning. The author touched this topic in the previous article , but then in theory, and now on his own experience.

PostgreSQL's 'Related Projects'

Andreas Scherbaum recalls that postgresql.org has a new page on PostgreSQL related projects such as pgweb, pglister, PostgreSQL Build Farm and others.

A Beginners Guide to Basic Indexing in Postgres

Valid for beginners . For those who continue, we recall a series of articles Egor Rogov erogovabout indices (however, we also have a little one , she’s the first acquaintance , where, of course, you can read about indices).

Useful Queries for Postgres Index Maintenance

Ibrar Ahmed from Percona in a short article gives the basics of monitoring indexes using typical queries on catalog tables.

The health of indexes in PostgreSQL through the eyes of a Java developer

Ivan Vakhrushev (Yandex) wrote the pg-index-health library , and in this article he talks about how to use his scripts [UPD: the author updated the links in the article] . Ivan admits that he had a great purge , inspired by the reportOleg Bartunov and Alexander Korotkov. All the necessary SQL scripts can be found at the links in the article and in the repository .

Home education


"Total Recall" or # sidimdoma usefully

on this page are now collected links to all courses of the department of education Postgres Professional.
But courses can also be viewed as Youtube playlists .

Webinars and Mitapas


Postgres Tuesdays
They are conducted by Nikolay Samokhvalov (Postgres.ai) and Ilya Kosmodemyansky (DataEgret).

# 16 : with the participation of Maxim Boguk and Viktor Yegorov (both DataEgret). It was discussed: how to ensure that the application runs smoothly even with an unexpected load jump of 5 times.

# 15 . Topics discussed: PostgreSQL is the Linux of databases ; Once again about GiST; some great improvements in PostgreSQL 13 are in pg_stat_statements and EXPLAIN; Macro-analysis: Planning stats in pg_stat_statements; Micro-analysis: WAL stats in EXPLAIN; pg_show_plans from Cybertec.
# 14 : Developer's mistakes when working with Postgres. Guest: Pavel Bisset.

# 13 : An example of working with JSON in Postgres (+ CTE, LATERAL, intervals, GiST index).

2nd Quadrant's upcoming

Date / Timestamp types in PostgreSQL webinars

April 22, Andrew Dunstan.

KNN Indexing in PostgreSQL

April 29, Kirk Roybal.

Registration and a list on the page of their webinars .

Conferences


PGCon 2020

will pass on May 26-29 online.

From the materials of past conferences, for example:

Easy And Correct High Availability Postgres with Kubernetes

Postgres Open uploads video from Postgres Open conferences that conducts (conducted and, hopefully, will be held after de-isolation) community in the USA. In this report from Postgres Open 2019, Steven Pousty of Crunchy Data talks about the highly available PostgreSQL from container unpacking to practical steps.

PGConf.Russia

Keep in mind that most of the video, not to mention the slides, is available on the conference website .

Highload ++

Reports, apparently, more convenient to watch on youtube channel conferences .

Ethics News


The Code of Conduct Committee 2019 Annual Report

March 17 Postgresista Codex Committee reported on the work done. Of the widely known in the circles of Russian postgresists you can see Ilya Kosmodemyansky. Fortunately, it follows from the report that so far no progressive user has been banned.

Slave - everything. We

remind you that slaves were finally abolished throughout the Postgres documentation: Slave became Standby in replication. The partitioning master become root , and the slave - leaf .


Subscribe to the postgresso channel !

Send ideas and suggestions to the mail: news_channel@postgrespro.ru
Previous issues: # 19 , # 18 , # 17 , # 16 , # 15 , # 14 , # 13 , # 12 , # 11 (special) , # 10 , # 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1

More articles:


All Articles