Write-Ahead Log

Logically WAL can be think of as a continuous stream of records. Each record is identified by its number, which is called the LSN (Log Sequence Number). LSN is a 64-bit number representing the offset of the record from the very start of the log.

Current position in the log can be viewed by pg_current_wal_lsn function (pg_current_xlog_location before version 10):

α=> SELECT pg_current_wal_lsn();

 pg_current_wal_lsn 
--------------------
 0/4808A000
(1 row)

Position is shown as two 32-bit numbers delimited by slash.

Let's save the current position in a psql variable:

α=> SELECT pg_current_wal_lsn() AS pos1 \gset

Now we are going to perform some operations and then look at the position change.

α=> CREATE TABLE t(n integer);

CREATE TABLE

α=> INSERT INTO t SELECT gen.id FROM generate_series(1,1000) AS gen(id);

INSERT 0 1000

α=> SELECT pg_current_wal_lsn();

 pg_current_wal_lsn 
--------------------
 0/480B2E4C
(1 row)

α=> SELECT pg_current_wal_lsn() AS pos2 \gset

It is not convenient to compare absolute numbers, but fortunately we can see the difference in bytes:

α=> SELECT :'pos2'::pg_lsn - :'pos1'::pg_lsn;

 ?column? 
----------
   167500
(1 row)

Physically the log in stored in a separate directory in 16 MB files (which are often called segments). Starting from version 10 we can look at them not only in the operating system (PGDATA/pg_wal), but also at the database level:

α=> SELECT * FROM pg_ls_waldir() ORDER BY name;

           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000048 | 16777216 | 2019-02-27 15:37:49+03
 000000010000000000000049 | 16777216 | 2019-02-27 15:19:20+03
 00000001000000000000004A | 16777216 | 2019-02-27 15:19:33+03
 00000001000000000000004B | 16777216 | 2019-02-27 15:19:38+03
 00000001000000000000004C | 16777216 | 2019-02-27 15:19:39+03
(5 rows)

PostgreSQL will automatically delete files which are no longer required for recovery.

The end of demonstration.