PostgreSQL *

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

ArticlesPostsNewsAuthors

kotsev96 Feb 5 2023 at 18:19

Database selection cheat sheet: SQL or NoSQL?

9 min

5.3K

PostgreSQL * Java * SQL * NoSQL * Go *

This is a series of articles dedicated to the optimal choice between different systems on a real project or an architectural interview.

This topic seemed relevant to me because such tasks can be encountered both at work and at an interview for System Design Interview and you will have to choose between these two types of DBMS. I plunged into this issue and will tell you what and how. What is better in each case, what are the advantages and disadvantages of these systems and which one to choose, I will show with several examples at the end of the article.

SQL or NoSQL?

kaze_no_saga Dec 30 2022 at 12:07

PostgreSQL 16: Part 3 or CommitFest 2022-11

10 min

1.4K

Postgres Professional corporate blogPostgreSQL * 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

kaze_no_saga Nov 16 2022 at 06:01

PostgreSQL 16: Part 2 or CommitFest 2022-09

13 min

1.9K

Postgres Professional corporate blogPostgreSQL * 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

kaze_no_saga Nov 8 2022 at 12:14

Queries in PostgreSQL. Nested Loop

17 min

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

kaze_no_saga Nov 1 2022 at 12:32

PostgreSQL 16: Part 1 or CommitFest 2022-07

7 min

1.1K

Postgres Professional corporate blogSQL * PostgreSQL *

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!

kaze_no_saga Nov 1 2022 at 06:35

Queries in PostgreSQL. Sort and merge

19 min

2.2K

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

kaze_no_saga Aug 17 2022 at 06:51

Queries in PostgreSQL. Hashing

18 min

3.5K

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

kaze_no_saga May 24 2022 at 05:11

Queries in PostgreSQL. Index scan

18 min

5.4K

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

kaze_no_saga Mar 28 2022 at 14:41

Queries in PostgreSQL. Sequential Scan

15 min

2.6K

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

kaze_no_saga Feb 23 2022 at 13:00

Queries in PostgreSQL. Statistics

18 min

6.3K

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 10:48

Queries in PostgreSQL. Query execution stages

15 min

5.6K

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 07:40

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

5 min

1.2K

PVS-Studio corporate blogC++ * PostgreSQL * Open source *

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 09:41

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

8 min

PostgreSQL * C# * API * .NET *

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 07:27

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

20 min

12K

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 10:53

Locks in PostgreSQL: 4. Locks in memory

10 min

16K

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 16:34

Locks in PostgreSQL: 3. Other locks

14 min

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 19:38

Locks in PostgreSQL: 2. Row-level locks

14 min

14K

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 11:32

Locks in PostgreSQL: 1. Relation-level locks

13 min

19K

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 9 2020 at 22:37

Parallelism in PostgreSQL: treatment of trees and conscience

10 min

3.7K

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 16:17

JSONPath in PostgreSQL: committing patches and selecting apartments

10 min

28K

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.

1 2

4 5