PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Sequence jump with CACHE option

I have 2 db sequences and 2 tables:

CREATE SEQUENCE products_seq
MINVALUE 1
MAXVALUE 9999999999999999
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE reviews_seq
MINVALUE 1
MAXVALUE 9999999999999999
START WITH 1
INCREMENT BY 1
CACHE 10;
CREATE TABLE IF NOT EXISTS products (
                      id BIGINT PRIMARY KEY default nextval('products_seq'),
  //other fields
CREATE TABLE IF NOT EXISTS reviews (
                         id BIGINT PRIMARY KEY default nextval('reviews_seq'),
//other fields

When I populate table via db script it populates correctly in db

INSERT INTO reviews (title, rating, description, product_id) VALUES
                 ('Great Product', 4.5, 'I love this product!', 1),
                 ('Disappointing', 2.0, 'Not what I expected.', 2),
//other records, 13 in common

But when I run SELECT nextval('reviews_seq'); I see that next val for id is 21. I understand that this command increment sequence value every time, but why it starts from 21? This happens only for sequence with cache (in this case cache is 10). Works ok with no cache (next id=14 with SELECT nextval('reviews_seq');).

Tags:

2

Answers


  1. 0

    Instead of calling the sequence for each insert, N next values are put in a cache that remains in memory for the duration of the session. The sequence counter is increased by this same N value, so 1 or 10 in your example.

    Since you have inserted 13 rows in a single session, the sequence has been increased by 10+10, so the next session calling it will get 21.

    See the doc notes about concurrent sessions and "last value"

    Login or Signup to reply.
  2. 0

    The option CACHE, quoting the manual:

    … specifies how many sequence numbers are to be preallocated and
    stored in memory for faster access. The minimum value is 1 (only one
    value can be generated at a time, i.e., no cache), and this is also
    the default.

    So the sequence allocates 10 numbers at a time. While you fetch the next number within the same session, you get the next number (faster). But a sequence never "takes back" numbers. That’s their nature! So the rest is wasted at the end of the session.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search