SQL *

Domain-specific languageused in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system

Articles Authors Companies

kaze_no_saga 30 December 2022 at 15:07

PostgreSQL 16: Part 3 or CommitFest 2022-11

Postgres Professional corporate blog PostgreSQL *SQL *

Translation

We continue to follow the news of the upcoming PostgreSQL 16. The third CommitFest concluded in early December. Let's look at the results.

If you missed the previous CommitFests, check out our reviews: 2022-07, 2022-09.

Here are the patches I want to talk about:

meson: a new source code build system
Documentation: a new chapter on transaction processing
psql: \d+ indicates foreign partitions in a partitioned table
psql: extended query protocol support
Predicate locks on materialized views
Tracking last scan time of indexes and tables
pg_buffercache: a new function pg_buffercache_summary
walsender displays the database name in the process status
Reducing the WAL overhead of freezing tuples
Reduced power consumption when idle
postgres_fdw: batch mode for COPY
Modernizing the GUC infrastructure
Hash index build optimization
MAINTAIN ― a new privilege for table maintenance
SET ROLE: better role change management
Support for file inclusion directives in pg_hba.conf and pg_ident.conf
Regular expressions support in pg_hba.conf

711

kaze_no_saga 16 November 2022 at 09:01

PostgreSQL 16: Part 2 or CommitFest 2022-09

Postgres Professional corporate blog PostgreSQL *SQL *

Translation

It's official! PostgreSQL 15 is out, and the community is abuzz discussing all the new features of the fresh release.

Meanwhile, the October CommitFest for PostgreSQL 16 had come and gone, with its own notable additions to the code.

If you missed the July CommitFest, our previous article will get you up to speed in no time.

Here are the patches I want to talk about:

SYSTEM_USER function
Frozen pages/tuples information in autovacuum's server log
pg_stat_get_backend_idset returns the actual backend ID
Improved performance of ORDER BY / DISTINCT aggregates
Faster bulk-loading into partitioned tables
Optimized lookups in snapshots
Bidirectional logical replication
pg_auth_members: pg_auth_members: role membership granting management
pg_auth_members: role membership and privilege inheritance
pg_receivewal and pg_recvlogical can now handle SIGTERM

704

kaze_no_saga 8 November 2022 at 15:14

Queries in PostgreSQL. Nested Loop

Postgres Professional corporate blog PostgreSQL *SQL *

Translation

So far we've discussed query execution stages, statistics, and the two basic data access methods: Sequential scan and Index scan.

The next item on the list is join methods. This article will remind you what logical join types are out there, and then discuss one of three physical join methods, the Nested loop join. Additionally, we will check out the row memoization feature introduced in PostgreSQL 14.

921

kaze_no_saga 1 November 2022 at 15:32

PostgreSQL 16: Part 1 or CommitFest 2022-07

Postgres Professional corporate blog PostgreSQL *SQL *

Translation

August was a special month in PostgreSQL release cycle as the first CommitFest for the 16th PostgreSQL release was held.

Let's compile the server and check out the cool new stuff!

549

kaze_no_saga 1 November 2022 at 09:35

Queries in PostgreSQL. Sort and merge

Postgres Professional corporate blog PostgreSQL *SQL *

Translation

In the previous articles, we have covered query execution stages, statistics, sequential and index scan, and two of the three join methods: nested loop and hash join.

This last article of the series will cover the merge algorithm and sorting. I will also demonstrate how the three join methods compare against each other.

748

kaze_no_saga 17 August 2022 at 09:51

Queries in PostgreSQL. Hashing

Postgres Professional corporate blog PostgreSQL *SQL *

Translation

Queries in PostgreSQL. Hashing

So far we have covered query execution stages, statistics, sequential and index scan, and have moved on to joins.

2.3K

kaze_no_saga 24 May 2022 at 08:11

Queries in PostgreSQL. Index scan

Postgres Professional corporate blog PostgreSQL *SQL *

Translation

Queries in PostgreSQL. Index scan

In previous articles we discussed query execution stages and statistics. Last time, I started on data access methods, namely Sequential scan. Today we will cover Index Scan.

IvanSGlazunov 1 April 2022 at 21:12

Math introduction to Deep Theory

SQL *Mathematics *Data Engineering *

In this article, we would like to compare the core mathematical bases of the two most popular theories and associative theory.

Calculating deep

1.9K

kaze_no_saga 28 March 2022 at 17:41

Queries in PostgreSQL. Sequential Scan

Postgres Professional corporate blog PostgreSQL *SQL *

Translation

Queries in PostgreSQL. Sequential scan

In previous articles we discussed how the system plans a query execution and how it collects statistics to select the best plan. The following articles, starting with this one, will focus on what a plan actually is, what it consists of, and how it is executed.

In this article, I will demonstrate how the planner calculates execution costs. I will also discuss access methods and how they affect these costs, and use the sequential scan method as an illustration. Lastly, I will talk about parallel execution in PostgreSQL, how it works, and when to use it.

I will use several seemingly complicated math formulas later in the article. You don't have to memorize any of them to get to the bottom of how the planner works; they are merely there to show where I get my numbers from.

1.7K

kaze_no_saga 23 February 2022 at 16:00

Queries in PostgreSQL. Statistics

Postgres Professional corporate blog PostgreSQL *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 3 February 2022 at 13:48

Queries in PostgreSQL. Query execution stages

Postgres Professional corporate blog PostgreSQL *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.

3.2K

erogov 17 June 2020 at 13:53

Locks in PostgreSQL: 4. Locks in memory

Postgres Professional corporate blog PostgreSQL *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.

12K

erogov 29 May 2020 at 19:34

Locks in PostgreSQL: 3. Other locks

Postgres Professional corporate blog PostgreSQL *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.

6.6K

erogov 20 May 2020 at 22:38

Locks in PostgreSQL: 2. Row-level locks

Postgres Professional corporate blog PostgreSQL *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.

10K

erogov 14 May 2020 at 14:32

Locks in PostgreSQL: 1. Relation-level locks

Postgres Professional corporate blog PostgreSQL *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.

11K

erogov 16 April 2020 at 15:05

WAL in PostgreSQL: 4. Setup and Tuning

Postgres Professional corporate blog PostgreSQL *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 3 April 2020 at 14:37

WAL in PostgreSQL: 3. Checkpoint

Postgres Professional corporate blog PostgreSQL *SQL *

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.

5.5K

khatrivijay 28 March 2020 at 20:24

SQL Server & Concurrency Control

SQL *

Tutorial

What is a Transaction?

The standard definition of Transaction state that “Every Query batch that runs in a SQL server is a Transaction.”, this means any query you run on a SQL server will be considered as a Transaction it could either be a simple SELECT query or any UPDATE or ALTER query.

If you run a query without mentioning the BEGIN TRAN keyword then it would be considered as an Implicit transition.

If you run a query which starts with BEGIN TRAN and ends with COMMIT or ROLLBACK then it would be considered as Explicit Transaction.

2.1K

erogov 26 March 2020 at 17:50

WAL in PostgreSQL: 2. Write-Ahead Log

Postgres Professional corporate blog PostgreSQL *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.

5.6K

erogov 10 March 2020 at 14:12

WAL in PostgreSQL: 1. Buffer Cache

Postgres Professional corporate blog PostgreSQL *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.

2 3