Understanding WAL nomenclature

There are some PostgreSQL functions that reports an information such as 68A/16E1DA8. But what does it mean? The documentation states that it is a transaction log location. Let me try to explain it in details.

What is WAL? WAL is an abbreviation of Write-Ahead Log. That is how PostgreSQL named the transaction log (also known as REDO log). Every modern database system has one. It is basically a history of operations that was executed by a DBMS. It improves performance (you only need to flush one file per transaction) and guarantees ACID properties (if the DBMS was in an inconsistent state -- because of software crash, for example -- it reapplies all transactions that are committed but whose changes are not in the database files). PostgreSQL introduced the transaction log in version 7.1 (2001). It opted to implement only REDO log (there is no UNDO log on PostgreSQL).

The transaction log files are stored in $PGDATA/pg_xlog directory. They are named as 000000020000070A0000008E. They are all hexadecimal digits (0123456789ABCDEF). The first 8 digits identifies the timeline, the following 8 digits identifies the (logical) xlog file and the last ones represents the (physical) xlog file (aka segment). The physical files in pg_xlog directory are not actually the xlog files; PostgreSQL calls it segments. By default, a logical xlog file has 255 segments (16 MB each, 4080 MB in total) So in our example, the 0000070A and 0000008E are the logical xlog file and segment file, respectively. When segment file 000000FE is filled up the logical xlog file is incremented by 1 (i.e. 0000070B) and the segment file sequence starts up again (i.e. 00000000) -- 000000020000070B00000000.

What means 68A/16E1DA8? 68A is the (logical) xlog file and 16E1DA8 is the offset inside the logical xlog file. It means that the offset goes from 00000000 to FF000000 (4278190080 bytes = 4080 MB). When it reaches FF000000, it means we are in the last segment (aka physical xlog file) and the xlog file number will be incremented by 1 (i.e. 68B) and the offset will be restarted (00000000).

PostgreSQL provides a set of functions [1] [2] that deals with the transaction log location. For example, converting transaction log location to segment file name:

postgres=# select pg_xlogfile_name('68A/16E1DA8');
     pg_xlogfile_name  
--------------------------
 000000020000068A00000001
(1 row)

Converting transaction log location to segment file name and the offset inside that segment.

postgres=# select * from pg_xlogfile_name_offset('68A/16E1DA8');
        file_name         | file_offset
--------------------------+-------------
 000000020000068A00000001 |     7216552
(1 row)


If I want to know which segment file the PostgreSQL server is writing the transaction log now:

postgres=# select pg_xlogfile_name(pg_current_xlog_location());
     pg_xlogfile_name  
--------------------------
 000000020000074B000000E4
(1 row)


If you have a replication scenario, you can obtain the receive and replay lags. It is a simple hexadecimal math: master transaction log location minus replica transaction log location.

In the replica:

postgres=# select pg_last_xlog_receive_location();
 pg_last_xlog_receive_location
-------------------------------
 74B/E4D1C628
(1 row)

postgres=# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location
------------------------------
 74B/E4D1C628
(1 row)

In the master:

postgres=# select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 74B/E4D3B070
(1 row)

If they report the same xlog file (74B), it is a matter of E4D3B070 - E4D1C628 = 1EA48 (125512 bytes). It means that the replica is roughly 123 kB behind the master. Receive and replay locations aren't the same all the time; if the replica can't keep up with writing receive location > replay location.

What about different xlog files? Some tools use the following formula:

(FFFFFFFF * xlog_master + offset_master) - (FFFFFFFF * xlog_replica + offset_replica)

It's wrong ¹. Why? Because the offset goes up to FF000000 (and not FFFFFFFF). It means that the above formula reports 16 MB times the difference between xlog numbers when the xlog numbers aren't the same. The correct formula is:

(FF000000 * xlog_master + offset_master) - (FF000000 * xlog_replica + offset_replica)

Let's suppose the master and replica transaction location are 67E/AFE198 and 67D/FECFA308, respectively. The correct lag is 14696080 bytes and not 31473295 bytes (the difference between results is 16 MB!).


¹ I will send patches to fix check_postgres and Munin. If you know another tool that is using the wrong formula, let me know or even better, the tool's author. FYI, both tools were fixed in a few hours. Thanks, Greg and Magnus!

PGBR 2011 is over

PGBR 2011 is over. It was a challenge to organize an event after 2 years and with a budget 50% higher than the last conference. Yes, we did it! It was an event organized by the community to the community. Thanks to our Kahunas (Fabio Telles and Flavio Gurgel) and our sponsors.

This year we had participants from another countries (Argentina, Peru, and USA). We are planning a better support from website and registration next year (sorry Joe). Also, some foreign speakers: Alvaro (Chile), Bruce Momjian (USA), Dave Page (UK), Greg Smith (USA), Jaime Casanova (Ecuador), and Koichi Suzuki (Japan).

I don't have the definitive numbers yet but we had roughly 250 participants from the 5 regions of Brazil. I talked to a lot of participants that bring to me interesting success stories from their companies. One of the many interested success cases I heard of was from Locaweb (hosting company) that migrated its DNS infrastructure from M$ database to PostgreSQL.

We had three rooms, 29 sessions (4 sessions from Timbira) and a lot of new speakers. In the first day I was at room 1 because I was coordinating the sessions along the day. I had the opportunity to listen Alvaro and Greg talking about locks and benchmarks, respectively. Also, I was in the round table about PostgreSQL service opportunities in Brazil. In the second day, I attended to Jaime's talk (repmgr) and Dickson's (extensions). At this year, I decided to submit a newbie tutorial that answers the frequently asked questions in the PostgreSQL mailing lists. Title is Everything you want to know about PostgreSQL but were afraid to ask. It was a challenge to keep the audience awake (1 hour and a half) after the lunch but I think I got it. Oh, I lost the Dojo (because it conflicted with my session). :( The last session I attended was that of Lucio. After that the awards. For the first time, brazilian community has decided to reward prominent members of the community. We had 5 categories (I was nominated in 4) and got one. Thanks for reconnaissance! The last session was the always-funny-session: lightning talks. 8 LTs with speak^H^H^H^H^Hstand-up comedians who entertained the participants (@lhcezar will stop working with databases and will start a career as singer).

Thumbs up to: local (hotel), coffee-break and beer (200 liters).

Hope to see you again next year!