Skip to content
This project is part of pgEdge Labs and is under active development. APIs and features may change without notice.

Performance

This document gives the measured cost of covering a table, and explains how to reduce that cost. Every figure here comes from test/bench.sh, which you can run on your own hardware.

How the figures were measured

The figures come from PostgreSQL 17.11 with four concurrent clients, taking the best of three runs from a rebuilt fixture each time, inside Docker on a laptop. The update workloads run against 200,000-row tables; the insert workload starts from an empty table.

Throughput figures vary. Repeated runs on the same machine moved by several percentage points, and two runs of identical code differed by about two points, so the ranges below are honest and single-point precision would not be. The storage and write-ahead log figures come from a single statement with no concurrency, so those are stable.

Reproduce every figure on your own hardware:

./test/bench.sh 17 20 3

The arguments are the PostgreSQL version, the seconds per run, and the number of repetitions.

Throughput

The following table shows the observed cost of coverage across repeated runs:

Workload Cost of coverage
UPDATE, narrow row 32 to 41 percent
UPDATE, wide row with TOAST 25 to 31 percent
INSERT, narrow row 11 to 12 percent
UPDATE that changes nothing About zero

An UPDATE is the worst case, because pgVolvra captures both a before and an after value. An INSERT captures one. Reads cost nothing, and an uncovered table costs nothing.

The cost is well short of the threefold figure that "every write becomes three writes" suggests, because the extra work is one local insert into an append-only table rather than three round trips.

Storage

The following table shows the disk cost per captured change, measured on a freshly built table with 50,000 updates in each capture mode:

Capture mode Row shape Bytes per change History against table
changed Narrow, about 60 bytes 298 1.82 times
changed Wide, 4 KB with TOAST 291 0.93 times
full Narrow, about 60 bytes 420 2.57 times
full Wide, 4 KB with TOAST 544 1.73 times

Read the wide-row figures skeptically. The benchmark fills the wide column with repeated MD5 text, which compresses very well. A wide row of incompressible data, such as an encrypted blob or an image, costs considerably more per change.

Write-ahead log

pgVolvra's own inserts are logged, so coverage increases WAL volume, which sets replication bandwidth and backup size. The following table shows WAL generated by 50,000 updates in a single statement:

Row shape Uncovered Covered Extra per change
Narrow 15.1 MB 44.3 MB 614 bytes
Wide with TOAST 22.1 MB 51.3 MB 614 bytes

Coverage roughly doubles to trebles WAL volume for an update-heavy workload. The extra WAL per change exceeds the stored bytes per change, because WAL logs the index updates as well, and change_log carries four indexes.

Size replication capacity from the WAL figure rather than from the disk figure.

Reducing the cost

pgVolvra applies two optimizations by default. The following list describes each one:

  • pgVolvra records nothing for an UPDATE that changed no values, which takes that workload from a full capture to roughly zero cost.
  • pgVolvra stores only the columns whose values changed, rather than two copies of the row.

Choosing a capture mode

Storing only the delta is a trade rather than a free win. Computing the delta means comparing every column, and for a TOASTed value that means decompressing the value.

Measured side by side within a single run, the throughput difference between the two modes was within the noise on both row shapes, while the storage difference was large and consistent: changed stored 29 percent fewer bytes per change on a narrow row and 46 percent fewer on a wide row.

The changed mode is therefore the default. Disk grows without bound and eventually forces a choice between retention and history, while the throughput difference is a few points either way.

Because the trade flips with row width, the mode is settable per table:

SELECT volvra.set_capture_mode('sessions', 'full');
SELECT volvra.set_capture_mode('documents', 'changed');

Choose full for a narrow, write-heavy table where you would rather pay disk than processor time, or where an audit regime requires the complete before image of every write.

Choosing which tables to cover

Cost is proportional to rows changed rather than to table size, which makes coverage a per-table decision:

  • A 500 million row table with 200 writes a day costs almost nothing to cover.
  • A high-volume append-only event table is where 30 percent hurts, and is also the table you would least want to undo.

Cover the tables where a wrong statement is expensive. Leave event and telemetry tables uncovered.

What is not measured

pgVolvra publishes throughput, disk, and WAL. The following costs are not measured and should not be assumed to be free:

  • The autovacuum load that history growth adds.
  • Index bloat on change_log over long periods.
  • The effect of coverage on replication lag under sustained load.

Next Steps