Subject: Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write

From: 陈宗志 <[email-masked]>
Date: Fri, 27 Feb 2026 19:43:34 +0800

Hi wenhui,

Here are the latest benchmark results for the Double Write Buffer (DWB)
proposal. In this round of testing, I have included the two-phase
checkpoint batch fsync optimization and evaluated the impact of
wal_compression (lz4) on both FPW and DWB.

Test Environment:
- PostgreSQL: 19devel (with DWB patch applied)
- Hardware: Linux 5.10, x86_64
- Configuration:
  * shared_buffers = 1GB
  * max_wal_size = 32MB (to stress checkpoint frequency)
  * wal_compression = lz4
  * double_write_buffer_size = 128MB (for DWB mode)
- Workload: sysbench 1.1.0, 10 tables x 1,000,000 rows (~2.3GB dataset)
- Method: 16 threads, 60 seconds per run, each mode tested
  independently (only one instance running at a time to eliminate
  I/O contention).

Three modes compared:
- FPW: io_torn_pages_protection = full_pages (current default)
- DWB: io_torn_pages_protection = double_writes
- OFF: io_torn_pages_protection = off (no protection, baseline)

Results with wal_compression = lz4
----------------------------------
1. oltp_write_only (pure write transactions: UPDATE + DELETE + INSERT)

Mode         TPS      vs FPW    vs OFF
----      ------      ------    ------
FPW       13,772           -    -64.3%
DWB       20,660      +50.0%    -46.5%
OFF       38,588     +180.2%         -

2. oltp_update_non_index (single UPDATE per transaction)

Mode         TPS      vs FPW    vs OFF
----      ------      ------    ------
FPW       59,427           -    -57.5%
DWB      104,328      +75.6%    -25.4%
OFF      139,870     +135.4%         -

3. oltp_read_write (mixed: 70% reads + 30% writes)

Mode         TPS      vs FPW    vs OFF
----      ------      ------    ------
FPW        6,232           -     -9.0%
DWB        4,408      -29.3%    -35.6%
OFF        6,845       +9.8%         -

Results without wal_compression (for comparison)
------------------------------------------------
Workload                 FPW      DWB      DWB vs FPW
--------              ------   ------      ----------
oltp_write_only        9,651   22,111         +129.1%
oltp_update_non_index 48,624   98,356         +102.3%
oltp_read_write        5,414    5,275           -2.6%

Key Observations:

1. Write-heavy workloads: DWB outperforms FPW by +50% to +76% even
   with lz4 compression enabled. Without lz4, the advantage grows
   to +102% to +129% because uncompressed full-page images cause
   severe WAL bloat.

2. lz4 compression significantly helps FPW: For oltp_write_only, lz4
   boosts FPW from 9,651 to 13,772 TPS (+43%), while DWB sees minimal
   change (22,111 -> 20,660). This is expected -- lz4 compresses the
   8KB full-page images that FPW writes to WAL, but DWB doesn't
   generate FPIs at all, so lz4 has little effect on DWB's WAL volume.

3. Read-heavy mixed workloads: DWB shows a regression (-29%) in
   oltp_read_write with lz4. This workload is 70% reads with only 4
   write operations per transaction, so FPW overhead is minimal.
   Meanwhile, DWB incurs additional I/O overhead from writing pages
   to the double write buffer file, which outweighs the WAL savings
   in this scenario.

4. Batch fsync optimization is critical for DWB: The two-phase
   checkpoint approach (batch all DWB writes in Phase 1 -> single
   fsync -> data file writes in Phase 2) reduces checkpoint DWB
   fsyncs from millions to ~hundreds. For example, in
   oltp_write_only: 1,157,729 DWB page writes -> only 148 fsyncs.

Summary:

DWB provides substantial performance benefits for write-intensive
workloads with frequent checkpoints, which is the scenario where FPW
overhead is most pronounced. The advantage is most significant without
WAL compression (+100~130%), and remains strong (+50~76%) even with
lz4 enabled. For read-dominated mixed workloads, DWB currently shows
overhead that needs further optimization (reducing non-checkpoint
DWB fsync costs).

Regards,
Baotiao

---

From: wenhui qiu <[email-masked]>
Date: Fri, 27 Feb 2026 20:38:41 +0800

Hi
This test is completely meaningless. Just as you wouldn't set
innodb_redo_log_capacity=Minimum Value, innodb_max_dirty_pages_pct=Minimum
Value
You used an extreme example to prove the double write.Why didn't you
compare using best practices?

Thank

---

From: 陈宗志 <[email-masked]>
Date: Sat, 28 Feb 2026 03:11:01 +0800

Hi Tony,

> Personally believe that the Double Write is very smart for MySQL InnoDB,
> but not a good ideal for Postgres, currently, WAL is the best solution
> for Postgres,
> maybe the next generation log system for Postgres could use OrioleDB's
> storage engine.

Just to clarify from a technical perspective, both MySQL and PostgreSQL
use Write-Ahead Logging (WAL) as their fundamental transaction logging
mechanism, so there is no difference in that regard.

The comparison here is specifically between Full-Page Writes (FPW) and
the Double Write Buffer (DWB). Neither of these concepts conflicts with
or replaces the core WAL design. Instead, both are simply different
techniques implemented to solve the exact same issue: preventing torn
pages during a crash.

My proposal is aimed at discussing the performance tradeoffs and
implementation details between these two specific torn-page protection
mechanisms, rather than replacing WAL itself.

Regards,
Baotiao

---

From: 陈宗志 <[email-masked]>
Date: Sat, 28 Feb 2026 03:15:36 +0800

Hi,

> innodb_redo_log_capacity=Minimum Value,
> innodb_max_dirty_pages_pct=Minimum Value

I wouldn't be so quick to dismiss these results. The configuration
was deliberately chosen to trigger more frequent checkpoints. As I
mentioned in my initial email, more frequent checkpoints strictly bound
the amount of WAL that needs to be replayed, resulting in much faster
crash recovery.

The entire ARIES paper heavily emphasizes optimizing crash recovery
time in database design. Minimizing recovery time is a fundamental
database capability, and we shouldn't rely solely on High Availability
(HA) switchovers to mask or solve crash recovery problems.

Actually, I have always felt that PostgreSQL's minimum limit of 30s
for `checkpoint_timeout` is a bit too restrictive. Ideally, the system
should allow for even higher frequency checkpoints. Setting it to a
lower value, such as 10s, could achieve the exact same effect of
strictly bounding recovery time. This test simulates an environment
where a very aggressive RTO (Recovery Time Objective) is required,
which is a highly practical scenario, not just an extreme edge case.

Regards,
Baotiao

---

From: Tony ZHU <[email-masked]>
Date: Sat, 28 Feb 2026 08:44:52 +0800

Hi

ZongZhi, Thanks for feedback.

I have read prior thread on this email, and know your settings, when did 
the testing, checkpoint_timeout set to 30s is too small, it's not a 
reasonable setting ,

the value of checkpoint_timeout is determined by shared_buffers and the 
workload, in real product environment, we usually set it to 30min or 
longer, even 1 hours, when the setting is correct, FPW will not be a 
problem or issue.

other issue is introduced by double write that is the recovery procedure 
and replications, it is not a small project.

if you really focus on the write or read latency, I would like to advice 
you to take a look for OrioleDB storage engine, I believe that's the 
correct direction. it is more efficient reads and writes, resolving many 
known overheads and issues in PostgreSQL

Regards

Tony