Digest news from the world of PostgreSQL. Issue number 10
We continue to introduce you to the most interesting news on PostgreSQL.
PostgreSQL 11 Beta 4
In this release, they fixed the bugs revealed after the release of Beta 3. Including:
- now disabled by default is
- names in
constraints must be unique.
- removed a memory leak when accessing XMLTABLE
- fixed errors in stored procedures
- finalized the partitioning, including the selection of sections at the time of execution (runtime partition pruning)
Read more here .
In this release, several dozen fixes related to WAL, libpq, VACUUM and FREEZE, GIN indices, query parallelization, OpenSSL. Here is a list of them .
Postgres Pro Enterprise 10.5.2.
In this version there are the following innovations in relation to Postgres Pro Enterprise 10.5.1, they concern
pgbenchnow supports composite commands;
- Using the parameter,
--latency-limityou can now limit the time allotted for the repetition of transactions. If using this parameter value
--max-tries=0, transactions can be repeated an unlimited number of times until the time specified by the parameter has expired
- when calculating the number of processed transactions and the speed of execution (TPS), skipped and unsuccessful transactions are no longer taken into account.
Recall that during the time between our releases, Postgres Pro Enterprise 10.5.1 was released. . There are significant changes, you can read about them here .
2ndQuadrant announced the release of the release, which can be downloaded here .
In the vanilla version based on PostgreSQL 10, the following improvements were picked up:
- declarative partitioning;
- improved query concurrency;
- performance improved overall;
- improved monitoring and administration.
From the version based on PostgreSQL 9.6 taken:
- parallel execution of sequential scanning, JOINs and aggregations;
- during VACUUM FREEZE, pages are not scanned without the need;
- significantly improved performance, especially in the case of multi-socket servers;
- full-text search is now able to do a phrasal search.
TimescaleDB 1.0.0 rc1 The
release candidate of this DBMS, which is based on PostgreSQL, has appeared, has been improved for storing and processing time series (
time-series), made as an extension to PostgreSQL. With a modest release order number , the DBMS already has clients like Bloomberg and Cray , millions of daunloods .
I came PostGIS 2.5.0
Approximately one month after the beta was released and the official release. List of changes here
Official ChangeLog here , download the weights from here .
in the announced 2ndQuadrant-th version there are improvements for more convenient work. Details are here , and you can download it here
pgAdmin 4 v 3.3.
In this release there are some useful improvements:
- viewing geometric shapes;
- Added new options in
backup/restorePostgreSQL 11 and new options in
Packages are available for Windows, macOS, for Python Wheel, Docker, there are also tarballs. RPM and DEB will appear a bit later on postgresql.org
new version of the monitoring utility, written by Alexey Lesovsky (Data Egret) , has been rewritten to
Go. Now it
pgCentercan save statistics in files, take into account background processes, filter by regular expressions. The version is accompanied by an article where the innovations are described in detail. There is a demo. Download here .
In the new version of the tools for monitoring and managing Postgres, which is offered in Dalibo , there were notifications (
alerting). Download here.
This is a major release, in addition to fixing bugs, there are new, significant features:
- pgbouncer file format support
- support for all formats
auto_explain format (text, xml, json and yaml);
%q placeholder в log_line_prefix;
- Added a format
jsonlogin the extension by Michel Paquier, with the option -f jsonlog pgbadger can parse the log;
Added a lot of options.
Source codes here .
This is a set of
bashscripts for working with
PITRbackup copies in PostgreSQL. In the new version, which can be found on the Dalibo githaba , it can, for example, save the configuration file and saved commands inside the backup itself.
Depesz (i.e. Hubert Lyubashevsky) announces -a exit
pg_terminator. This monitoring utility can clean problem queries and / or connections.
- PostgreSQL 11 hash partitioning support
- export of the default section;
- support for stored procedure objects
As well as some other improvements for less painful exports and more meaningful debugging.
View all changes on the githaba ;
New training courses from Postgres Professional “SQL language” and “Basics of database technology” started on September 10
From September 10 to October 19, 2018, two new training courses are held with the support of Postgres Professional
- SQL language (read by SIBGU associate professor E.P. Morgunov );
- The basics of database technology (read by Professor B. A. Novikov of SPSU ).
You can apply for the course “Basics of Database Technologies” by writing to the email address:,
firstname.lastname@example.org the full name of the letter in the letter.
Enterprise-Grade PostgreSQL: Built on Open Source Tools
At the Percona webinar, everyone is invited on October 10th. Register here . Topics will be discussed:
- how to secure a database cluster;
- high availability;
- How to choose a backup strategy and what tools to use;
- PostgreSQL scaling with pullers and load balancers
- utilities / modules (extensions) for every day work of DBA, detailed logging;
- real-time monitoring and analysis.
Using Postgres to watch Star Wars!
In this video report, Will Leinweber tells and shows how to make a vintage cartoon using DBMS.
Preventing SQL Injections
Dimitri Fontaine 's short, intelligible article explains how to protect PostgreSQL from SQL injections. Not an ad-free
Pythonadapter Securing PostgreSQL as an Enterprise-Grade Environment Percona employees describe the process of configuring PostgreSQL security in enterprise information systems. We are talking about
- logging and auditing
- tracking bugs
PostgreSQL Backup Strategy for an Enterprise-Grade Environment
The second article in this perkonovskaya series talks about enterprise-level backup. Both articles do not detail the problem too much, but this one is quite laconic.
USE, RED, PgBouncer, its settings and monitoring
Great article from okmeter with many examples and illustrations.
PostgreSQL 11 and Just In Time Compilation of Queries
This article from Citus Data tells about the TPC-H Q1 benchmark.compared to the Pricing Summary Report in the real world. Benchmark is a request with a large number of units. It shows how
JITPostgreSQL 11 speeds up queries.
Setting up Streaming Replication in PostgreSQL
Avinash Vallarapu (Avinash Vallarapu) blog Percona tells you how to prepare
slavefor streaming replication.
Upgrading to PostgreSQL 11 with Logical Replication
Peter Yazentraut (Peter Eisentraut) on the 2ndQuadrant blog discusses the pros and cons of upgrading PostgreSQL installation: using
pg_upgrade, or using logical replication (in the kernel). It then details how to set up logical replication.
Parallelism in PostgreSQL: not spherical, not a horse, not in a vacuum
An article based on a real case from one of Postgres Professional 's customers . About diagnostics and patches, including block queue managers
Parallel PostGIS and PgSQL 11
In his blog, Paul Ramsey (Paul Ramsey) shares PostGIS 2.5 tests on PostgreSQL 11, which show that some progress has been made in parallelizing.
Partitioning Improvements in PostgreSQL 11
Alvaro Herrera (Alvaro Herrera) reviews11th version patches related to partitioning.
Be careful with CTE in PostgreSQL
This article warns against mindlessly using CTE: a sharp drop in performance can be a side effect.
Better PostgreSQL testing with Python: announcing pytest-pgsql and pgmock
Users, employees of Clover Health present 2 utilities:
Conferences and meetings
Materials from the meeting #RuPostgres - videos, presentations, quiz analysis and photo report
Mitap was organized by Avito , the materials are posted here . Reports:
- Stas Kelvich (Postgres Professional) spoke about distributed transactions and time travel.
- Konstantin Evteev (Avito) made a presentation on scaling the application on PostgreSQL in Avito and shared tips and tricks.
- Mikhail Tyurin has prepared a report on logical replication and PostgreSQL transaction isolation levels.
- Sergey Khandrikov (Yandex) told the audience about how OZO works, asynchronous type-safe header-only PostgreSQL client library for C ++ 17.
PostgresConf South Africa 2018
will be held in Johannesburg on October 9th.
PostgreSQL Conference Europe 2018
will be held October 23-26 in Lisbon.
will be held November 12-13 in Krasnoyarsk. Conference topics:
- Experience using PostgreSQL in various areas
- Experience in the implementation of the transfer to PostgreSQL information systems that work with other DBMS
- Experience in developing system tools for PostgreSQL
- Highly loaded projects based on PostgreSQL
- Using PostgreSQL in web projects
- Big Data and PostgreSQL
- PostgreSQL Administration
- Using PostgreSQL in teaching in universities, colleges and schools.
is expected December 10-12 in Tokyo,
Subscribe to the postgresso channel !
Send your ideas and wishes to email: email@example.com
Previous issues: # 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1