Scalable sequences in PostgreSQL / YugabyteDB
Tags: yugabytedb, distributed, sql, postgres
Published: 2023-03-14 17:48:10


You may be surprised if you come from Oracle Database but the sequence cache in PostgreSQL is per session. Which means that:
- if you re-connect, you will see a gap. This is not a problem by the way
- if you have new connections, their first use of the sequence may compete on updating a single row

I described this, with YugabyteDB enhancements, in:
{% embed https://www.youtube.com/watch?v=er0eD46SCXA&ab_channel=Yugabyte %}

I was showing a workaround to transparently use 8 sequences when calling `nextval()` with only one name, in order to distribute the updates for the problem exposed above. Here is the code:

```sql
create or replace function nextval(name text) returns bigint as $$
declare
 sequence_to_read text:=name;
begin
 if name like '%\%' escape '\' then
  sequence_to_read:=(name||mod(pg_backend_pid(),8));
 end if;
 raise log 'Reading nextval from %',sequence_to_read;
 return nextval(sequence_to_read::regclass);
end;
$$ language plpgsql;

```

This overrides the `nextval()` function when passing a `text`. The builtin `nextval()` accepts a `reglass` argument, which is the `oid` of the sequences. You usually pass the name of the sequence as `text` but then it does an implicit casting to `regclass`.

Here, when you pass a name without the special pattern (name ending with `%`) it just calls the builtin function. However, with the `%` pattern, I add a random number from `0` to `7` to call one of those sequences. Well... not really random. I take the number from `pg_backend_pid()` because the goal is to hit different sequences when from different sessions but one session can use the same one as it holds a cache for it.

Of course, you can choose a different pattern. The only thing is that I want it to be fast.

To create the 8 sequences, I just generate the DDL and run it from `psql` with `\gexec`:
```sql`
select format('create sequence if not exists %I cache 100 start with %s increment by 8','seq%'||n,n+1) from generate_series(0,7) n;
\gexec
```

This generates the following:
```sql
yugabyte=> select format('create sequence if not exists %I cache 100 start with %s increment by 8','seq%'||n,n+1) from generate_series(0,7) n;
                                   format
-----------------------------------------------------------------------------
 create sequence if not exists "seq%0" cache 100 start with 1 increment by 8
 create sequence if not exists "seq%1" cache 100 start with 2 increment by 8
 create sequence if not exists "seq%2" cache 100 start with 3 increment by 8
 create sequence if not exists "seq%3" cache 100 start with 4 increment by 8
 create sequence if not exists "seq%4" cache 100 start with 5 increment by 8
 create sequence if not exists "seq%5" cache 100 start with 6 increment by 8
 create sequence if not exists "seq%6" cache 100 start with 7 increment by 8
 create sequence if not exists "seq%7" cache 100 start with 8 increment by 8
(8 rows)
```
The all `increment by 8` and `start with` a different number so that their `nextval()` do not overlap. Remember, the goal is still to get a unique number, but avoiding the single row to update when the cache is cold.

You can use that as a normal sequence, the example is in the video. This workaround should not be necessary in future versions of YugabyteDB as we implement [server-side caching](https://github.com/yugabyte/yugabyte-db/issues/15642) and [push down the update logic](https://github.com/yugabyte/yugabyte-db/issues/13123). 

Note that in YugabyteDB, you don't have the same scalability problems that you find in PostgreSQL or Oracle because we don't use B-Tree indexes and Heap Tables. There is no need to partition the sequence like [Scalable Sequences in Oracle Database](https://oracle-base.com/articles/18c/scalable-sequences-18c) because new values are appended to the LSM-Tree (no hot block issue) and you will probably use hash sharding on the generated key, even if range sharding is possible if you goal is to collocate rows inserted together.