Writing while reading in postgresql: scandals, intrigues, investigations

I already talked about monitoring postgresql requests , at that moment it seemed to me that I completely figured out how postgresql works with various server resources.

With constant work with statistics on postgres requests, we began to notice some anomalies. I am helpful to understand, at the same time once again admired the clarity of the source code postgres)

Under the cut is a short story about the unobvious behavior of postgresql.

SELECT "smudge" pages

That is, SELECT causes a modification of some records that postgres will write to disk.

I’ll start with a brief explanation of the MVCC mechanism used by postgres to ensure transactional integrity.

All changes in the database occur during transactions, each transaction has a txid (int32) identification number.

Postgres operates data tables in the form of so-called tuple (tuple). Tuple carries in itself both the data of a specific line in the table, and the metadata associated with this data:

Picture: www.interdb.jp

xmin is the transaction number that created this tuple
xmax is the transaction number that marked this tuple as deleted

• If we do an INSERT into a table, it creates a new tuple (xmin = txid)
• DELETE - marks the stupid that fit the condition as deleted (xmax = txid)
• UPDATE makes conditional DELETE + INSERT.

When we perform a SELECT, in addition to directly searching and retrieving data from a table, it also does visibility check.

Very simply, some transaction number txid1 "sees" this tuple if the conditions are met:

xmin < txid1 < xmax 

But changes in tuples occur immediately, and the transaction can be performed for a long time, so during the visibility check it is necessary to make sure whether the transactions with the numbers xmin, xmax were completed and, if so, with what status. The postgres stores the current state of each transaction in the CLOG (commit log).

Since checking the status of a large number of transactions in the CLOG is quite expensive in terms of resources, the developers decided to "cache" this information directly in the header of the plugin. That is, when some SELECT sees, for example, that xmin is complete, it saves it to the so-called hint bits - a structure on top of the infomask, in which the xmin and xmax transaction states are written.

How is the change in tupples when reading, we figured out, it remains to remember what “pages” are and why they are “dirty”)

The fact is that working with data in memory and on the disk is almost always more efficient in large blocks. Such a block in the postgress is a “page”, it contains a certain number of tuples and meta information about them. When we modify at least one page stub, all of it is marked as "dirty", that is, different as it is on the disk, and must be synchronized. Moreover, changes are almost always recorded in WAL in order to be able to restore the integrity of the data after the abnormal termination of the database process.

SELECT can cause synchronous write to disk

As you know, all work with the data in pg is done via buffer cache, if the necessary data is not there, the postgres will read it from the disk (using OS page cache) and put it in the cache.

At the same time, if there is no space in the cache, then the least requested page is pushed out of it. Finally, if the page for the expulsion candidate is dirty, it should be written to disk at the same point in time.

FrozenTransactionId

At the beginning of the article, I mentioned that the transaction counter in the 32-bit postgrece, that is, it is reset every ~ 2 billion transactions.

So that the visibility check does not turn into a pumpkin when the transaction counter is reset, there is a special process - wraparound vacuum.

Prior to version 9.4, this process replaced xmin with tupl with the special value FrozenTransactionId = 2. The transaction with this number was considered older than any other transaction. C 9.4 simply puts the flag in tupl that xmin is "frozen", and xmin itself remains unchanged.

For quite attentive: there is a special constant BootstrapTransactionId = 1, which is also older than all other transactions)

Total

Most of the cases of "strange" (according to the narrow-minded opinion) postgres behavior are caused by performance optimization.

While picking with postgres I found a wonderful book "The Internals of PostgreSQL" , I recommend to anyone who has not met before.