Skip to main content

DBLab Forge

Postgres experiments you can check

DBLab Forge runs controlled Postgres experiments on real cloud servers: same-server comparisons, predictions with falsifiers written before the first row when a study makes them, the full data, and a brief with every number traced to its source.

Closed beta, available to PostgresAI customers.

Talk to usSee example studies

What a Forge study looks like

A question about Postgres behavior becomes a study with a written design. Opinions and quick benchmarks are where it starts, not where it ends.

  1. Predictions first

    When a study makes predictions, each one is written down with a falsifier before the first row is inserted, and scored afterwards: held, not met, or falsified. The UUID study had fourteen.

  2. Real servers, same-server comparisons

    Experiments run on cloud servers, not laptops. Two servers of the same type are not the same server, so ratios between the variants are taken on one server.

  3. Runs reported per server

    Seven runs in the UUID study, one run per cell; three restores and nine cold runs per step in the dump/restore study. Results are reported per server, so the spread between servers stays visible.

  4. Review before trust

    Results are reviewed before they are trusted. In the UUID study, sixteen independent reviews, mostly by AI models, were posted on the study record with responses.

  5. Full data and a brief

    In the UUID study, counters were sampled every 10 seconds: WAL position, pg_stat_io, pg_stat_wal, buffer residency, wait events. The brief gives every number with its source table, the scored predictions, the corrections, and the prior work.

A wrong result, caught by review

In the UUIDv4 vs UUIDv7 study below, the first run put each key on its own server, and int8 inserted about 12% faster than UUIDv7. A plausible result: the key is half the size. But the review step compared the servers first. The WAL fsync latency measured during the run was 313 µs on the int8 server and 347 µs on the UUIDv7 server: the int8 server most likely had a faster disk.

The next run put UUIDv7 on the int8 server: over the same 71M rows it inserted 23,458 rows/s, against 23,131 for int8 in the first run. The keys insert at about the same rate. Details in the post.

Example studies

Two recent posts on the PostgresAI blog are based on Forge studies. Each was discussed on Postgres.FM.

UUIDv4 vs UUIDv7: what a random primary key costs

Same table, same insert stream, three primary keys on Postgres 18: UUIDv4, UUIDv7 and int8. Seven runs on cloud servers, about 2.1 billion UUID rows in total, fourteen hypotheses with falsifiers.

0.62–0.69×
UUIDv4 insert rate
vs an ordered key, same server, 100M rows
1.5–1.9×
UUIDv4 WAL per row
vs an ordered key, same server, 90M–100M rows
+33%
UUIDv4 primary key size
vs UUIDv7, 100M rows
Inserts per second as the table grows from 0 to 100M rows, run 6: on the small tier int8 ends at 23.5k, UUIDv7 at 21.7k and UUIDv4 at 13.9k, with UUIDv4 falling from about 26M rows; on the mid tier UUIDv7 ends at 20.9k and UUIDv4 at 18.3kInserts per second as the table grows from 0 to 100M rows, run 6: on the small tier int8 ends at 23.5k, UUIDv7 at 21.7k and UUIDv4 at 13.9k, with UUIDv4 falling from about 26M rows; on the mid tier UUIDv7 ends at 20.9k and UUIDv4 at 18.3k

Runs took one day; the whole study, with design and reviews, about three days.

Read the postPostgres.FM episode 194

Dump/restore done. Now what?

After pg_dump and pg_restore, by default the planner statistics and the visibility map are missing. One query over a 250-million-row table with a 31 GiB heap, on a host with 16 GiB of RAM, cold cache, three restores.

32.6 s
right after pg_restore
Hash Join over a Seq Scan
6.1 s
after ANALYZE
24,983 heap fetches
23 ms
after VACUUM ANALYZE
0 heap fetches
On Postgres 18.6, one query takes a median 32.6 s cold after a logical restore alone, 6.1 s after ANALYZE (Index Only Scan with 24,983 heap fetches), and 23 ms after VACUUM ANALYZE (0 heap fetches)On Postgres 18.6, one query takes a median 32.6 s cold after a logical restore alone, 6.1 s after ANALYZE (Index Only Scan with 24,983 heap fetches), and 23 ms after VACUUM ANALYZE (0 heap fetches)

Medians of 9 cold runs, Postgres 18.6; also tested on 19rc1.

Read the postPostgres.FM episode 193

Watch the discussions

Kirk Wolak and Nikolay Samokhvalov discuss both studies on Postgres.FM.

Postgres.FM episode 194: UUIDv4 vs UUIDv7
Postgres.FM episode 193: Dump/restore done. Now what?

Who it is for

Teams facing a Postgres decision that is expensive to get wrong. Forge is the third generation of the experiment harness PostgresAI has built since 2018. The first two generations ran experiments in our consulting work, many of them public in tests-and-benchmarks. A few:

Every study reports its limits. Your tables, row sizes, load and hardware shift the ratios, so the answer to your question comes from an experiment designed for your case.

Closed beta for PostgresAI customers

Tell us the question you need answered, and we will say whether a Forge study fits it.

Talk to us