Thread

Re: Sequence Access Methods, round two

Andrei Lepikhov <lepihov@gmail.com> — 2026-05-19T13:11:36Z

On 18/05/2026 20:42, Andrei Lepikhov wrote:
> On 18/05/2026 00:43, Michael Paquier wrote:
> Ok. So let me just leave the idea of avoiding unnecessary cache lookups here.
Here is my benchmark results.

Test script:

DROP SEQUENCE IF EXISTS abc;
CREATE UNLOGGED SEQUENCE abc USING seqlocal;
-- Warm up
SELECT count(nextval('abc')) FROM generate_series(1, 1E6) \watch i=0 c=100
\timing on
\o result.txt
-- Benchmark
SELECT count(nextval('abc')) FROM generate_series(1, 1E6) \watch i=0 c=100

To identify thermal impact I averaged and compared results per 20 iterations in
a pack:

| Window      | Baseline | v29          |
| ----------- | -------- | ------------ |
| iter 1-20   | 236.92   | 235.28       |
| iter 21-40  | 235.77   | 236.17       |
| iter 41-60  | 238.53   | 234.64       |
| iter 61-80  | 236.62   | 234.35       |
| iter 81-100 | 237.13   | 235.24       |

This test doesn't include insertion machinery itself, so no cold cache measures
or sequence lock contention.

pgbench results on the UNLOGGED table insertion shows the following:

| Test                           | Baseline   | v29          | Δ      |
| INSERT pgbench (mean tps)      | 24,997     | 25,195       | +0.79% |

My oldish Intel-based MacBook is probably not sensitive enough to detect the
overhead. So, you can recheck the result using scripts [1] to restore the exact
test.

[1] https://github.com/danolivo/conf/tree/main/2026e-SeqAM

-- 
regards, Andrei Lepikhov,
pgEdge