Bypassing the transaction log

04.2014 / Category: / Tags:

Referring to my previous blog post about the amount of xlog written by PostgreSQL I wanted to clarify what I meant when talking about bypassing the PostgreSQL transaction log.

Normal WAL / xlog writes

Whenever data is changed inside PostgreSQL the change must be written to the xlog before it is written to the underlying table. The reason for that is simple: Imagine you are doing a large INSERT, but the power goes out while you are writing the data to the table. The result would be an uncomplete record somewhere in the middle of the table. Index entries might be missing as well. In short: There would be a serious risk of corruption.

To avoid that, PostgreSQL writes all changes to the xlog to make sure that a table / index / etc. can always be repaired based on the xlog.


However, it is not always necessary to write to the xlog.

Imagine the following scenario:

In this case the transaction will not be seen by others until we commit the thing. We don't have to worry about concurrency in this case. If we commit we can take the COMPLETE new data file - or, we simply throw the freshly created data file away in case the transaction fails. Under any circumstances: There is no situation, which would require the entire content of the table being written to the xlog. This kind of optimization can speed up things dramatically - especially in case of very large transactions.

However, there are more cases in which PostgreSQL can skip the transaction log. Consider this one:

In this case the TRUNCATE does the trick. It locks the table to make sure that nobody else can modify it and as soon as the first new row comes in, PostgreSQL creates a new data file (= new relfilenode). At the end of the transaction we got two choices then: If we can commit safely, we take the COMPLETE new data file. In case of a ROLLBACK we can take the complete old data file.

Of course, this can only be done if you are not using streaming replication (wal_level = minimal).

However, if you got a single-node system bypassing the transaction log is a pretty neat optimization and can speed things up considerably.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram