Postgresso #10 (59)

PostgreSQL 16.1

– as well as PostgreSQL 15.5, 14.10, 13.13, 12.17 and 11.22 (this version is the last one that will be supported in the 11 line). The update addresses 3 security issues:

  • CVE-2023-5868: showing memory contents in function calls with aggregation (memory disclosure in aggregate function calls) – can occur when a type is passed as an argument unknown;

  • CVE-2023-5869: rewriting the buffer from its integer overflow when modifying arrays (buffer overrun from integer overflow in array modification) – insufficient overflow checks allow authenticated users to both read and write to memory to execute malicious code;

  • CVE-2023-5870: role pg_signal_backend can send signals to some superuser processes (role pg_cancel_backend can signal certain superuser processes) – but according to the documentation it shouldn’t; a role could, for example, send signals to a process that starts logical replication.

In addition, 55 bugs were eliminated.

PostgreSQL 17: Part 2 or Commitfest 2023-09

If the news about the 16th version does not pump adrenaline into someone’s blood, then here is another review article Pavel Luzanov, a little trip to the future (A about the July commitfest in the previous article in this series: 2023-07). Paul addresses 18 points. For example:

As always, there are many examples. Eat translation to English. Now let’s move on to conferences; information has accumulated on them.

Conferences, meetups and round tables

HighLoad++ 2023

It will take place on November 27-28 in Skolkovo. There are not many reports related to Postgres, but they are interesting:

Myths and realities of multimaster architecture in the PostgreSQL relational DBMSMikhail Zhilin, Pavel Konotopov,Postgres Professional. There will be not only about multimaster Postgres Pro, but also about various approaches and technologies, including EDB Postgres, pgEdge And Bucardo.

There will be a report like this:

Compute/Storage separation in GreenplumAndrey Borodin, Yandex Cloud. We’re talking about Yezzey – open GreenplumDB extension, which allows you to transfer a table to S3, but at the same time preserve the native data format. With this approach, the performance of many queries is similar to the performance of queries against tables on local SSD drives. This technology is the next step in the direction of a cloud-based analytical DBMS for Greenplumwhile all code is available in open source.

New theme and new aaS:

MaaS – monitoring as a serviceValentin Lebedev, Anna ZhurbenkoGazprombank.

There will be a report on a completely unexpected topic:

Translator from a language that cannot be spoken or writtenAlexander Kapitanov, SberDevices. I wouldn’t spoil the intrigue here, but Postgresso is not that genre. That’s why:

Find out why Russian sign language (RSL) are not just gestures, but a powerful tool for conveying abstract concepts.

There will be an exhibition at the conference, and a stand at it BiHA — fault-tolerant technology integrated into the Postgres Pro Enterprise core with minimal administrator participation. At PGConf.Russia, the BiHA stand was very popular, we’ll see how it will be here; PostgreSQL will also be introduced Shardman — distributed relational DBMS with horizontal scaling.

As for BiHA, there was a special meeting on this topic on October 31:

PGMeetup: BiHA Failover Cluster

Demonstration took place, senior technical consultant Postgres Professional Andrey Zabelin spoke about the solution and answered questions. Recording available at PGMeetup website.

PGConf.SPb 2023

Recordings of speeches at this conference have been published. It took place on September 25. Video available conference participants in their personal account.

Among the topics of the reports: features of PostgreSQL, migration, data backup, fault tolerance, machine learning, b-tree indexes, fencing, hardening, query rescheduling, tables, accounts, fuzzing research, review of solutions AQO 2.0, PPEM, pgCodeKeeper, VTB- Debesium. Photo report of the conference.

PostGIS Day 2023

Online free one-day conference. Will take place on November 16th. It usually goes away the next day after GIS Day. The PostGIS world is special, it is not just a subset of the post-GIS world. And there are a lot of unfamiliar names. But there are also friends:

Enabling Mobility Data in Multiple Computing Environments – report Esteban Zimani (Esteban Zimanyi, Université libre de Bruxelles). I remember him from a report on PgConf.Russia 2019: MobilityDB: PostgreSQL extension for mobile data management – there are PDF and video buttons.

And, of course, Paul Ramsey:

PostGIS Performance Tips – Paul Ramsey, Crunchy Data

He will talk about the most common PostGIS problem – data irregularity and ways to combat this problem. Including those not from the geo-world. If you can make assumptions about at least some patterns in query parameters, this can help a lot.

In the meantime, here’s a link to Paul’s article: Random Geometry Generation with PostGIS.

Regina Obi appeared in our reviews. She’s going to surprise you with this:

PostGIS Surprise Extensions – Regina Obe (Paragon Corporation).

Collective author Autonomy Group / Ford Motor will present a report:

Case Studies in Extending PostGIS Capabilities, where, oddly enough, it will be about puzzles, and about hypergraphs, as well as about hyperboloids and ellipsoids. Extensions, which we will talk about, will help you work with such objects.

The conference is widely supported by Crunchy Data, they and notify the world about this interesting event.

PGConf.EU 2023

The 13th European Conference will take place on December 12-15 in Prague. Published schedule. Will open Simon Riggs (Simon Riggs, EDB) report The Next 20 Years of PostgreSQL. Tickets are available purchase on the conference website. You can also buy tickets for training sessions.

The program is interesting, there are many familiar names, intriguing titles of reports. Two performances Ilya Kosmodemyansky (Ilya Kosmodemiansky, Data Egret) – How to become a PostgreSQL DBA in a day And Linux IO internals for PostgreSQL administrators in 2023.

Multi-threaded PostgreSQL? – asks Heikki Linnakangas (Heikki Linnakangas, Neon). Making your patch more committable – this will help Melanie Plaigman (Melanie Plageman, Microsoft).

PGDay UK 2023

PGDay UK 2023 is a small one-day conference. It took place on September 12 in London. There were 7 reports + opening and closing Dave Page (Dave Page, EDB). They said, for example, that Embeddings, not embedded – Postgres in the age of AIAlistair Turner (Alastair Turner, VMWare), about BRIN improvements and new opclassesTomas Wondra (Tomas Vondra, EDB).

Citus Con 2023

It took place on April 18-19. One of the nice things about this event is that they make the reports publicly available. For example:

Here’s the playlist with all 37 reports. Moreover: according to reports there is management – to navigate them.

By the way, here New Citus Technical README for distributed PostgreSQL

Ender Kalachi (I don’t know how to pronounce it, he’s from Turkey – Önder Kalaci, Citus Data) reportsthat a detailed and deep-digging technical documentation (including mechanisms and optimizations), showing how Citus turned Postgres into a distributed database. To summarize, I would like to note: it seems to me that the company is behaving well.

FOSDEM PGDay 2024

Magnus Hagander (Magnus Hagander, Redpill Linpro) announced that FOSDEM PGDay 2024 will take place on February 2nd. Applications accepted. The website has some detailsbut there is no schedule yet.

By the way, on the right margin of Magnus’s blog there is list of conferenceswhich he visited. Only in 2023 this is 9 conferences! At some of them he made presentations – all about the elephant: A look at the Elephants Trunk.

PGConf.dev 2024

She’s the same PostgreSQL Development Conference 2024. Will take place May 28-31 in Vancouver, Canada. Applications for reports accepted until January 15, 2024. The organizers are interested in topics: future Postgres features, recently committed innovations, strengthening the community. We are waiting for feedback from new contributors and from venerable contributors with many years of history. The level of reports is technical from intermediate to advanced. But there will also be reports on how to start contributing to Postgres. Registration has not yet opened, but it will appear Here. Soon.

It seems that we did not provide a link to this conversation that took place in June:

The Future of the Postgres Ecosystem Roundtable.

Prominent representatives discuss this topic for more than an hour:

  • Heiki Linnakangas (Heikki Linnakangas, co-founder Neon);

  • Denis Lussier (Denis Lussier, co-founder and technical director PGEdge);

  • Kevin Jernigan (Kevin Jernigan, Product Lead at Google Cloud);

  • Ry Walker (Ry Walker, co-founder CoreDB);

  • Karthik Ranganathan (Karthik Ranganathan, co-founder and tech director Yugabyte).

Find IT

Funny name, a small miracle of naming. This is a Novosibirsk forum for HR people and their search targets. Students come and are told about work opportunities in their companies Sber, Vkantakte, Skoltech and others. Among the DBMS specialists, Postgres Professional participated, talked about their (that is, our) educational programs and internships, played 30 elephants and 420 books “Postgres: first acquaintance”. Participated in the forum Daria Risukhina, Alena Skalkina, Alexander Fatin, Denis Frolov And Vadim Laktyushin.

Domestic software: the path to technological leadership

At the conference ARPP In Ekaterinburg in a closed session Where to go with Microsoft Deputy General Manager of Postgres Professional spoke Ivan Panchenko. He opened with his report Part I. and spoke about domestic software development models, the role of the community in the development of system software, and the conditions for creating a full-fledged domestic software vendor. He was followed by Rustam Rustamov (RED SOFT), Vyacheslav Kadomsky (STC IT ROSA), Dmitry Eliseev (GC Applite) and Anton Ten (CommuniGate Systems). Speaker presentations can be downloaded.

Heisenbug

Didn’t know this word. It turns out that this is a bug + Heisenberg uncertainty = an error, such that either it is there or not. Well, a conference on testing, but this “testing conference not only for testers“. This year it took place on October 10-11 online and October 15-16 offline in St. Petersburg.

The videos are posted, but: if you have a ticket, log in to watch the video. But PDFs are available to everyone. I know that on the topic of Postgres on the 15th he spoke Mikhail Zhilin, Postgres Professional with a report Examples of atypical scenarios during PostgreSQL load testing and 16th Andrey Zubkov, Postgres Professional with a report pg_profile – PostgreSQL strategic monitoring utility. Prikhodko Ivan, Ozon Tech talked about Scenario- vs Hit-Based-approachesalthough the full title promises Load testing for cats. But there really are a lot of cat photos in the PDF.

The quantum structure of the world: from the 2022 Nobel Prize to experiencing reality inside a quantum computer – that was the name of the report Alexey Semikhatov from FIAN. Lots of incomprehensible words, no PDF. AND How we tested uploading from Greenplum to KafkaEkaterina Frolova from Tinkoff (PDF available). Published photo.

Oleg Bartunov promoted open source and Postgres is quite active these days.

Took part in the round table Sublodka Java Crew: “Comparison of NoSQL and SQL databases using specific examples”: pros and cons of SQL/NoSQL, cases for each approach. On Linkmeup Podcast He told about Open Source (there is also youtube version). The influence of open source software on digitalization in Russia, its dependence on funding, ways of developing open source software. The Ontiko team interviewed him: we talked about leadership in the industry, the development of the Postgres community in Russia and plans for the future.

Webinar “PostgreSQL Overview: Your Path to a Powerful, Flexible and Reliable Database”

At a joint meeting with “First Beat” Mark Rivkin And Andrey Zabelin talked about the capabilities, technical features and functionality of PostgreSQL.

How to analyze pgpro_pwr diagnostic reports – new training video. All training videos from Postgres Professional Here.

Time is money

In a tiny note Monitoring the user – two types of timestamp the author, NNikolay, writes: a long time ago, one database specialist (one of those bearded and already gray-haired) told me that timestamps are the most difficult topic in databases.

The most/not the most, but definitely not easy. One might even say that this is not a purely technical question, but almost a choice of philosophy. And juicy questions arise, like what about events in the future?.

Temporal types in PostgreSQL and their use

Author, Ivan Frolkov (aka @plumqqz, Postgres Professional) writes:

Many times I had to deal with the fact that due to the difference in time zones, reports for a month or even a day did not converge. Problems like these arise from careless date and time handling that can be avoided.

Ivan talks not only about TIMESTAMP, TIMESTAMPTZ and functions for working with them, but also a little about unixtime. And he also gives advice on how to Java-developer Interesting comments have already appeared, and apparently there will be a lot of them. Since I know Ivan as an experienced developer with a lot of practice, it’s worth listening.

A lot has been written on this topic, including on Habré, and on November 9 an article appeared with a decisive and undemocratic title:

Why you should stop using timestamp in PostgreSQL

Article status in the sandbox, but the mixed comments instantly exceeded 100. Both Oracle and Java were hooked. And it’s even being discussed slapstick with the participation of a krudoslap. The author of the article is someone dmserebr – apparently Dmitry Serebryannikov/Serebryakovthis is his first article on Habré that started in such a stormy way.

Working with Money in Postgres

Elizabeth Christensen (Elizabeth Christensen, Crunchy Data) begins the article with a reference to temporary types:

It would be great if in Postgres everything with money was the same as with time (this is a link to Elizabeth’s article Working with Time in Postgres): saved the canonical version in the database, but will work all over the world. Alas. This is different.

There is a link to the corresponding tutorialwhere you can communicate with the Postgres server through the browser.

But not money, not time, but branching versions – in Git style:

Versioning data in Postgres? Testing a git like approach

Samuel Bodine (Samuel Bodin, Specfy) shows how to build such a branching structure on top of Postgres yourself. Describes 4 ways to implement DELETE. At the same time, about how versions are stored in Git:

If you rewrote a file 999 times, then 999 of this file will be in the object storage – that’s why cloning repositories takes so long.

The article has a lot of pictures, explanations – what and why, lists the pros and cons, alternative ways to solve the problem. And the task was to make a convenient platform – Specfy for developers.

At the same time, Samuel admits that he did not particularly study alternative solutions. The first one that comes to mind is Postgres.ai. Everything there is initially well thought out, logical and effective. Samuel also refers to alternatives:

  1. Branching – Neon (Open Source) – cloning based on the copy-on-write principle;

  2. Dolt www.dolthub.com (Open Source) – supposedly the first and only SQL DBMS with version control; it also says that this is the child of Git and MySQL, and not Postgres its father;

  3. extension temporal_tables (pgxn.org);

  4. extension linz/postgresql-tableversion.

Postgres Weekly advises to pay attention to the following temporary tables as an alternative: nearform/temporal_tables.

Speaking of serverless. The regiment arrived:

Nile Database

Its creators write: this is serverless Postgres for SaaS. And that this is the first such base that implements multi-tenant, which is what is needed for SaaS.

JSON(B)

JSON Updates in Postgres 16

Christopher Winslet (Christopher Winslett, Crunchy Data)

There are useful examples of using the IS JSON predicate, functions json_array(), json_arrayagg(), json_object() And json_objectagg(). It demonstrates on generated data a not too intricate design. But enough to show some pretty subtle effects.

He also talks about quite a few keywords that have appeared and some other functions with “add-ons”, for example: json_object_agg_unique_strict() / jsonb_object_agg_unique_strict().

pg_jsonschema 0.2: JSON Schema Validation Extension

This is an extension for validating JSON structures. It works because the structure can be defined in advance through JSON Schema. And then you can run the extension pg_jsonschema for document validation.

PostgreSQL Antipatterns: going through JSON rake

Kirill Borovikov from Tensor, aka kilor, writes about how bad it is when you read from CTE many times and about many other bad things that he found in a bad request. But how typical is he? This raises questions.

In the comments, Kirill also refers to his other article – about some tricks. It’s called sweetly: Lazy Sugar PostgreSQL.

In general, he writes on completely different topics. Some time ago he wrote on the topic:

SQL HowTo: Tree’s nearest common ancestor (LCA)

There, to begin with, Kirill shows how to bypass the branches in a simple way in search of least common ancestor (Lowest (Least) Common Ancestor). But even in a simple way, of course, you can’t do without WITH RECURSIVE. In a more sophisticated way, it demonstrates the capabilities of the SEARCH … FIRST BY … SET … construct introduced in PostgreSQL 14. The comments contain useful constructive criticism.

On the Russian market

Postgres Professional has confirmed the compatibility of Cyber ​​Backup 16 and the Postgres Pro DBMS

On the site Cyberprotecta report that they have received certificates confirming the technological compatibility of Cyber ​​Backup 16 with Postgres Pro Standard and Postgres Pro Enterprise versions 13, 14 and 15 as part of the Postgres Professional Compatibility Verification Program. The 16th version of the SRC added support for PostgreSQL backup plans, including setting their parameters.

More articles and videos

Hierarchical database

The database is hierarchical, but the DBMS is not hierarchical at all – for now it is Oracle 11g, but it will probably be Postgres. But more on that later: this is the 1st part, there will be 2 more. The implementation of the author’s hierarchical idea in Postgres should be in the 3rd part.

The ideology of building an IS is based on a directed graph of concepts (entities) of the subject area – says the author, Torus 1st. The article is strange, but the topic interested many.

To implement a data model in a procedural language (APL) the data type COV (category, object, value) has been introduced. A value of this type contains the category number and object number, and may also contain the value of one of the object’s attributes. <...> Accordingly, tokens for “navigating” through a tree of objects were introduced into the query language (AQL), ​​which has significant similarities with SQL.

Postgres System Columns Explained

25-minute column diving video ctid, xmin And xmax. About how these system columns can help understand transaction behavior. Speaks and types SQL queries in the terminal Hussein Nasser (Hussein Nasser).

Automated index bloat management: How pg_index_watch keeps PostgreSQL indexes lean

Max Boguk (Max Boguk, Data Egret) writes about the tool pg_index_watch, which automatically rebuilds swollen indexes a-la autovacuum after exceeding a certain threshold. And he tells what this threshold is, where it came from.

Some releases

pglift 1.0

Whatever they do at Dalibo, it’s better to be aware: the company has created a lot of useful things. The other day they announced the framework pglift 1.0 for deploying and managing multiple PostgreSQL clusters. Version 1.0 is ready to work in industrial environment and use homogeneous in a uniform manner. They explain these two italic words as follows:

Industrial in the sense that the Postgres core needs external services for backup, monitoring and fault tolerance. A homogeneous in the sense that users usually want to deploy their databases without much hassle and for them to work reliably.

pg_cron 1.6.2

Actually, nothing special happened in this version. But this article talks about what’s new in this popular scheduler (in the sense of a scheduler):

Making PostgreSQL tick: New features in pg_cron

Marco Slot (Marco Slot, Microsoft) reports that version 1.6 added support for PostgreSQL 16, but this is not the most interesting thing. The most important thing appeared back in 1.5 and is the ability to set tasks every few seconds. It was not easy because if some kind of error appears with such frequency, it can lead to very serious consequences. But now pg_cron has already been thoroughly tested in combat conditions, and working in seconds is the most popular need of users.


That’s all for today.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *