wonder about WAL....

drop table big;
    s char(1000)
) WITH (fillfactor = 10);
insert into big(s) values('foo');
select pg_current_wal_lsn() ;
 explain( analyze, costs off, verbose, timing off, buffers,settings, wal,timing off, summary)
delete from big;
select pg_current_wal_lsn() ;


                      QUERY PLAN
 Delete on public.big (actual rows=0 loops=1)
   Buffers: shared hit=2 dirtied=1
   WAL: records=1 fpi=1 bytes=1119
   ->  Seq Scan on public.big (actual rows=1 loops=1)
         Output: ctid
         Buffers: shared hit=1 dirtied=1
 Settings: search_path = 'public'
 Query Identifier: 6906021239059634709
   Buffers: shared hit=2
 Planning Time: 0.155 ms
 Execution Time: 0.206 ms
(12 rows)
    Parameter    | Value
 wal_compression | off
 full_page_writes| on

page meta:

select * from page_header(get_raw_page('big',0));

    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid
 1/D2FB7F90 |        0 |     0 |    28 |  7160 |    8192 |     8192 |       4 |         0
(1 row)

2 lsn position do subtract operation return 1160.


  1. fpi = 1 means dumped 8192 Bytes to WAL?
  2. What does WAL: records=1 fpi=1 bytes=1119 1119 bytes mean?

related: https://wiki.postgresql.org/wiki/Full_page_writes

The idea is to switch to "physical" logging for the first modification of each page after a checkpoint, which is a fancy way of saying that we dump the whole 8KB page into the transaction log instead of describing how to change it.

1 Answer 1


The INSERT was the first transaction since the last checkpoint that modified that 8kB page (since the table is new), so PostgreSQL wrote the whole page to disk. That'd the meaning of FPI: Full Page Image.

The page is 8kB in size, but it has a hole in the middle. That hole is not copied to WAL, so the actual number of bytes is smaller.

  • "When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint. " So after a checkpoint. dump the entire content to wal and flush the whole page(8192) from memory(buffer cache) to disk?
    – jian
    May 26 at 2:34
  • No. The meaningful part of the page is written to WAL immediately, and the page itself is written to disk during the next checkpoint (unless the background writer does it earlier than that). May 26 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.