The following blog post by Connor Mcdonald prompted me to write this note.
https://connor-mcdonald.com/2021/08/06/we-killed-sequence-contention/
When you create a sequence in Oracle you have to be very careful about the Cache size. Many Oracle DBAs leave the sequence cache size at the default of 20, which leads to contention on very busy sequences. From Oracle version 19.10 on Oracle's internal code will check the rate at which a sequence nextval is being called and ignore the cache size defined, using larger and larger values to bump the sequence highwater in the updates to the seq$ table.
In PostgreSQL the default value of the sequence cache size is 1. I highly recommend not to increase the value. In PostgreSQL the caching is per session which means that if you set the value to 10, session A starts with 1, session B starts with 11, session C starts with 21, and then some. You might think that when you insert a lot of data in a session, big cache size may boost performance, which is true in Oracle. However, my experimentation below contradicts that in PostgreSQL.
In my short experience with PostgreSQL I have never gone through performance degradation using sequences, which was somewhat odd to me because I had experienced a lot of performance problems with sequences in Oracle. One of the common wait events you encounter in Oracle is "enq: SQ - contention". So I decided to do some comparison experiments.
The following is the performance test scripts and their results.
--Oracle Version 12c
CREATE SEQUENCE CATEGORIES_SEQUENCE CACHE 20;
drop table t purge;
create table t (x int);
begin
for i in 1 .. 100000 loop
insert into t values (categories_sequence.nextval);
end loop;
commit;
end;
/
Elapsed: 00:00:11.40
--PostgreSQL version 13
CREATE SEQUENCE events_pk_seq;
You can see the database image of a sequence by querying the view pg_sequence.
SELECT schemaname, sequencename, sequenceowner, start_value, min_value
, max_value, increment_by, cycle, cache_size, last_value
FROM pg_sequences;
analdb=# do $$
analdb$# begin
analdb$# for i in 1 .. 100000 loop --11.40 sec in Oracle
analdb$# insert into t values (nextval('events_pk_seq'));
analdb$# end loop;
analdb$# --commit;
analdb$# end;
analdb$# $$
analdb-# ;
Time: 1820.758 ms (00:01.821)
I increased the cache size to 10000.
ALTER SEQUENCE events_pk_seq CACHE 10000;
analdb=# do $$
analdb$# begin
analdb$# for i in 1 .. 100000 loop --11.40 sec in Oracle
analdb$# insert into t values (nextval('events_pk_seq'));
analdb$# end loop;
analdb$# --commit;
analdb$# end;
analdb$# $$
analdb-# ;
DO
Time: 1630.835 ms (00:01.631)
As you can see, the performance benefit of increasing the cache size is negligible.
Wrap Up
The sequence performance of PostgreSQL is better than that of Oracle. The chances are rare that you have sequence contentions in PostgreSQL. You had better leave the sequence cache size the default value of 1.
Addendum 1
Even though the value of cache size is 1, a transaction that requests a new value from the sequence may leave a gap in the values committed to a column. In the rare cases that you really need a gapless series of values, a sequence is not the right solution for you.
Addendum 2
While executing plpgsql above I monitored pg_stat_activity. I was not able to see any wait events.
Addendum 3
Oracle's solution of bumping the sequence highwater has a side effect of increasing the size of the gap. I don't know why PostgreSQL sequence is more performant than Oracle sequence. My guess is :
In PostgreSQL a sequence is a "special table" with a single row. Since sequence operations should be fast and are never rolled back, PostgreSQL can be more efficient by just modifying the single row of a sequence in place whenever its values change.
