PostgreSQL *

Object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance

Articles Posts News Authors

kaze_no_saga Feb 23 2022 at 16:00

Queries in PostgreSQL. Statistics

18 min

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

In the last article we reviewed the stages of query execution. Before we move on to plan node operations (data access and join methods), let's discuss the bread and butter of the cost optimizer: statistics.

Dive in to learn what types of statistics PostgreSQL collects when planning queries, and how they improve query cost assessment and execution times.

kaze_no_saga Feb 3 2022 at 13:48

Queries in PostgreSQL. Query execution stages

15 min

5.4K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

Hello! I'm kicking off another article series about the internals of PostgreSQL. This one will focus on query planning and execution mechanics.

In the first article we will split the query execution process into stages and discuss what exactly happens at each stage.

eugeneovsyannikov Feb 11 2021 at 10:40

Just for Fun: PVS-Studio Team Came Up With Monitoring Quality of Some Open Source Projects

5 min

1.2K

PVS-Studio corporate blogOpen source*PostgreSQL*C++*

Static code analysis is a crucial component of all modern projects. Its proper application is even more important. We decided to set up a regular check of some open source projects to see the effect of the analyzer's frequent running. We use the PVS-Studio analyzer to check projects. As for viewing the outcome, the choice fell on SonarQube. As a result, our subscribers will learn about new interesting bugs in the newly written code. We hope you'll have fun.

anatolii_kabanov Dec 8 2020 at 12:41

Development of “YaRyadom” (“I’mNear”) application under the control of Vk Mini Apps. Part 1 .Net Core

8 min

997

.NET*API*C#*PostgreSQL*

Translation

Application is developed in order to help people find their peers who share similar interests and to be able to spend some time doing what you like. The project is currently on the stage of beta-testing in the social network “VKontakte”. Right now I am in the process of fixing bugs and adding everything that is missing. I felt like I could use a bit of destruction and decided to write a little about the development. While I was writing, I decided to divide the text into different parts. Here we are going to pay more attention to backend nuances which I faced, and to everything that a user does not see.

floitet Nov 19 2020 at 10:27

Patroni cluster (with Zookeeper) in a docker swarm on a local machine

20 min

11K

PostgreSQL*Development Management*DevOps*

Tutorial

There probably is no way one who stores some crucial data (and well, in particular, using SQL databases) can possibly dodge from thoughts of building some kind of safe cluster, distant guardian to protect consistency and availability at all times. Even if the main server with your precious database gets knocked out deadly - the show must go on, right? This basically means the database must still be available and data be up-to-date with the one on the failed server.

As you might have noticed, there are dozens of ways to go and Patroni is just one of them. There is plenty of articles providing a more or less detailed comparison of the options available, so I assume I'm free to skip the part of luring you into Patroni's side. Let's start off from the point where among others you are already leaning towards Patroni and are willing to try that out in a more or less real-case setup.

I am not a DevOps engineer originally so when the need for the high-availability cluster arose and I went on I would catch every single bump on the road. Hope this tutorial will help you out to get the job done with ease! If you don't want any more explanations, jump right in. Otherwise, you might want to read some more notes on the setup I went on with.

erogov Jun 17 2020 at 13:53

Locks in PostgreSQL: 4. Locks in memory

10 min

15K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

To remind you, we've already talked about relation-level locks, row-level locks, locks on other objects (including predicate locks) and interrelationships of different types of locks.

The following discussion of locks in RAM finishes this series of articles. We will consider spinlocks, lightweight locks and buffer pins, as well as events monitoring tools and sampling.

erogov May 29 2020 at 19:34

Locks in PostgreSQL: 3. Other locks

14 min

8.6K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

We've already discussed some object-level locks (specifically, relation-level locks), as well as row-level locks with their connection to object-level locks and also explored wait queues, which are not always fair.

We have a hodgepodge this time. We'll start with deadlocks (actually, I planned to discuss them last time, but that article was excessively long in itself), then briefly review object-level locks left and finally discuss predicate locks.

Deadlocks

When using locks, we can confront a deadlock. It occurs when one transaction tries to acquire a resource that is already in use by another transaction, while the second transaction tries to acquire a resource that is in use by the first. The figure on the left below illustrates this: solid-line arrows indicate acquired resources, while dashed-line arrows show attempts to acquire a resource that is already in use.

To visualize a deadlock, it is convenient to build the wait-for graph. To do this, we remove specific resources, leave only transactions and indicate which transaction waits for which other. If a graph contains a cycle (from a vertex, we can get to itself in a walk along arrows), this is a deadlock.

erogov May 20 2020 at 22:38

Locks in PostgreSQL: 2. Row-level locks

14 min

13K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

Last time, we discussed object-level locks and in particular relation-level locks. In this article, we will see how row-level locks are organized in PostgreSQL and how they are used together with object-level locks. We will also talk of wait queues and of those who jumps the queue.

Row-level locks

Organization

Let's recall a few weighty conclusions of the previous article.

A lock must be available somewhere in the shared memory of the server.
The higher granularity of locks, the lower the contention among concurrent processes.
On the other hand, the higher the granularity, the more of the memory is occupied by locks.

There is no doubt that we want a change of one row not block other rows of the same table. But we cannot afford to have its own lock for each row either.

There are different approaches to solving this problem. Some database management systems apply escalation of locks: if the number of row-level locks gets too high, they are replaced with one, more general lock (for example: a page-level or an entire table-level).

As we will see later, PostgreSQL also applies this technique, but only for predicate locks. The situation with row-level locks is different.

erogov May 14 2020 at 14:32

Locks in PostgreSQL: 1. Relation-level locks

13 min

18K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

The previous two series of articles covered isolation and multiversion concurrency control and logging.

In this series, we will discuss locks.

This series will consist of four articles:

Relation-level locks (this article).
Row-level locks.
Locks on other objects and predicate locks.
Locks in RAM.

The material of all the articles is based on training courses on administration that Pavel pluzanov and I are creating (mostly in Russian, although one course is available in English), but does not repeat them verbatim and is intended for careful reading and self-experimenting.

Many thanks to Elena Indrupskaya for the translation of these articles into English.

General information on locks

PostgreSQL has a wide variety of techniques that serve to lock something (or are at least called so). Therefore, I will first explain in the most general terms why locks are needed at all, what kinds of them are available and how they differ from one another. Then we will figure out what of this variety is used in PostgreSQL and only after that we will start discussing different kinds of locks in detail.

Igor_Le May 10 2020 at 01:37

Parallelism in PostgreSQL: treatment of trees and conscience

10 min

3.6K

Postgres Professional corporate blogPostgreSQL*

Database scaling is a continually coming future. DBMS get improved and better scaled on hardware platforms, while the hardware platforms themselves increase the performance, number of cores, and memory — Achilles is trying to catch up with the turtle, but has not caught up yet. The database scaling challenge manifests itself in all its magnitude.

Postgres Professional had to face the scaling problem not only theoretically, but also in practice: through their customers. Even more than once. It's one of these real-life cases that this article
will discuss.

Many thanks to Elena Indrupskaya for the translation. Russian version is here.

Igor_Le May 5 2020 at 19:17

JSONPath in PostgreSQL: committing patches and selecting apartments

10 min

27K

Postgres Professional corporate blogPostgreSQL*

This article was written in Russian in 2019 after the PostgreSQL 12 feature freeze, and it is still up-to-date. Unfortunately other patches of the SQL/JSON will not get even into version 13.

Many thanks to Elena Indrupskaya for the translation.

JSONPath

All that relates to JSON(B) is relevant and of high demand in the world and in Russia, and it is one of the key development areas in Postgres Professional. The jsonb type, as well as functions and operators to manipulate JSON/JSONB, appeared as early as in PostgreSQL 9.4. They were developed by the team lead by Oleg Bartunov.

The SQL/2016 standard provides for JSON usage: the standard mentions JSONPath — a set of functionalities to address data inside JSON; JSONTABLE — capabilities for conversion of JSON to usual database tables; a large family of functions and operators. Although JSON has long been supported in Postgres, in 2017 Oleg Bartunov with his colleagues started their work to support the standard. Of all described in the standard, only one patch, but a critical one, got into version 12; it is JSONPath, which we will, therefore, describe here.

Igor_Le May 1 2020 at 15:22

What is Baked in the Baker's Dozen?

31 min

1.7K

Postgres Professional corporate blogPostgreSQL*

On April 8, PostgreSQL feature freeze took place, so only features committed earlier will get into version PostgreSQL 13. Probably, this version can hardly be considered revolutionary, since it has no conceptual changes. Some of critical patches were late to get into it, such as Table and Functions for the JSON/SQL standard, which had been desirable to be part of PostgreSQL 12, along with the JSONPath patch; plug-in warehouses did not appear either — only the interface is being finalized. The list of improvements is still impressive. We prepared a pretty complete overview of the patches included in the Baker's Dozen.

erogov Apr 16 2020 at 15:05

WAL in PostgreSQL: 4. Setup and Tuning

17 min

9.3K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

So, we got acquainted with the structure of the buffer cache and in this context concluded that if all the RAM contents got lost due to failure, the write-ahead log (WAL) was required to recover. The size of the necessary WAL files and the recovery time are limited thanks to the checkpoint performed from time to time.

In the previous articles we already reviewed quite a few important settings that anyway relate to WAL. In this article (being the last in this series) we will discuss problems of WAL setup that are unaddressed yet: WAL levels and their purpose, as well as the reliability and performance of write-ahead logging.

WAL levels

The main WAL task is to ensure recovery after a failure. But once we have to maintain the log anyway, we can also adapt it to other tasks by adding some more information to it. There are several logging levels. The wal_level parameter specifies the level, and each next level includes everything that gets into WAL of the preceding level plus something new.

erogov Apr 3 2020 at 14:37

WAL in PostgreSQL: 3. Checkpoint

11 min

6.7K

Postgres Professional corporate blogSQL*PostgreSQL*

Translation

We already got acquainted with the structure of the buffer cache — one of the main objects of the shared memory — and concluded that to recover after failure when all the RAM contents get lost, the write-ahead log (WAL) must be maintained.

The problem yet unaddressed, where we left off last time, is that we are unaware of where to start playing back WAL records during the recovery. To begin from the beginning, as the King from Lewis Caroll's Alice advised, is not an option: it is impossible to keep all the WAL records from the server start — this is potentially both a huge memory size and equally huge duration of the recovery. We need such a point that is gradually moving forward and that we can start the recovery at (and safely remove all the previous WAL records, accordingly). And this is the checkpoint, to be discussed below.

Checkpoint

What features must the checkpoint have? We must be sure that all the WAL records starting with the checkpoint will be applied to the pages flushed to disk. If it were not the case, during recovery, we could read from disk a version of the page that is too old, apply the WAL record to it and by doing so, irreversibly hurt the data.

erogov Mar 26 2020 at 17:50

WAL in PostgreSQL: 2. Write-Ahead Log

8 min

7.3K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

Last time we got acquainted with the structure of an important component of the shared memory — the buffer cache. A risk of losing information from RAM is the main reason why we need techniques to recover data after failure. Now we will discuss these techniques.

The log

Sadly, there's no such thing as miracles: to survive the loss of information in RAM, everything needed must be duly saved to disk (or other nonvolatile media).

Therefore, the following was done. Along with changing data, the log of these changes is maintained. When we change something on a page in the buffer cache, we create a record of this change in the log. The record contains the minimum information sufficient to redo the change if the need arises.

For this to work, the log record must obligatory get to disk before the changed page gets there. And this explains the name: write-ahead log (WAL).

In case of failure, the data on disk appear to be inconsistent: some pages were written earlier, and others later. But WAL remains, which we can read and redo the operations that were performed before the failure but their result was late to reach the disk.

erogov Mar 10 2020 at 14:12

WAL in PostgreSQL: 1. Buffer Cache

13 min

7.5K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

The previous series addressed isolation and multiversion concurrency control, and now we start a new series: on write-ahead logging. To remind you, the material is based on training courses on administration that Pavel pluzanov and I are creating (mostly in Russian, although one course is available in English), but does not repeat them verbatim and is intended for careful reading and self-experimenting.

This series will consist of four parts:

Buffer cache (this article).
Write-ahead log — how it is structured and used to recover the data.
Checkpoint and background writer — why we need them and how we set them up.
WAL setup and tuning — levels and problems solved, reliability, and performance.

Many thanks to Elena Indrupskaya for the translation of these articles into English.

Why do we need write-ahead logging?

Part of the data that a DBMS works with is stored in RAM and gets written to disk (or other nonvolatile storage) asynchronously, i. e., writes are postponed for some time. The more infrequently this happens the less is the input/output and the faster the system operates.

But what will happen in case of failure, for example, power outage or an error in the code of the DBMS or operating system? All the contents of RAM will be lost, and only data written to disk will survive (disks are not immune to certain failures either, and only a backup copy can help if data on disk are affected). In general, it is possible to organize input/output in such a way that data on disk are always consistent, but this is complicated and not that much efficient (to my knowledge, only Firebird chose this option).

Usually, and specifically in PostgreSQL, data written to disk appear to be inconsistent, and when recovering after failure, special actions are required to restore data consistency. Write-ahead logging (WAL) is just a feature that makes it possible.

erogov Feb 27 2020 at 15:53

On recursive queries

25 min

11K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

This article deals with writing recursive queries. This topic was brought up routinely, but the discussion was usually limited to simple cases related to trees: to descend from a vertex to the leaves and to ascend from a vertex to the root. We will address a more complicated case of an arbitrary graph.

Let's start with recalling the theory (very briefly since all of it is trivial), and then we will discuss what to do if it is unclear how to approach a real-life problem or if it seems to be clear, but the query persistently fails to work fine.

For an exercise, we will use the airlines demo database and try to write a query to find the shortest route from one airport to another.

erogov Feb 10 2020 at 17:17

MVCC in PostgreSQL-8. Freezing

12 min

5.8K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

We started with problems related to isolation, made a digression about low-level data structure, discussed row versions in detail and observed how data snapshots are obtained from row versions.

Then we covered different vacuuming techniques: in-page vacuum (along with HOT updates), vacuum and autovacuum.

Now we've reached the last topic of this series. We will talk on the transaction id wraparound and freezing.

erogov Jan 29 2020 at 18:28

MVCC in PostgreSQL-7. Autovacuum

10 min

2.6K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

To remind you, we started with problems related to isolation, made a digression about low-level data structure, discussed row versions in detail and observed how data snapshots are obtained from row versions.

Then we explored in-page vacuum (and HOT updates) and vacuum. Now we'll look into autovacuum.

Autovacuum

We've already mentioned that normally (i. e., when nothing holds the transaction horizon for a long time) VACUUM usually does its job. The problem is how often to call it.

If we vacuum a changing table too rarely, its size will grow more than desired. Besides, a next vacuum operation may require several passes through indexes if too many changes were done.

If we vacuum the table too often, the server will constantly do maintenance rather than useful work — and this is no good either.

Note that launching VACUUM on schedule by no means resolves the issue because the workload can change with time. If the table starts to change more intensively, it must be vacuumed more often.

Autovacuum is exactly the technique that enables us to launch vacuuming depending on how intensive the table changes are.

erogov Jan 24 2020 at 16:59

MVCC in PostgreSQL-6. Vacuum

13 min

3.6K

Postgres Professional corporate blogPostgreSQL*SQL*

Translation

We started with problems related to isolation, made a digression about low-level data structure, then discussed row versions and observed how data snapshots are obtained from row versions.

Last time we talked about HOT updates and in-page vacuuming, and today we'll proceed to a well-known vacuum vulgaris. Really, so much has already been written about it that I can hardly add anything new, but the beauty of a full picture requires sacrifice. So keep patience.

Vacuum

What does vacuum do?

In-page vacuum works fast, but frees only part of the space. It works within one table page and does not touch indexes.

The basic, «normal» vacuum is done using the VACUUM command, and we will call it just «vacuum» (leaving «autovacuum» for a separate discussion).

So, vacuum processes the entire table. It vacuums away not only dead tuples, but also references to them from all indexes.

Vacuuming is concurrent with other activities in the system. The table and indexes can be used in a regular way both for reads and updates (however, concurrent execution of commands such as CREATE INDEX, ALTER TABLE and some others is impossible).

Only those table pages are looked through where some activities took place. To detect them, the visibility map is used (to remind you, the map tracks those pages that contain pretty old tuples, which are visible in all data snapshots for sure). Only those pages are processed that are not tracked by the visibility map, and the map itself gets updated.

The free space map also gets updated in the process to reflect the extra free space in the pages.