PostgreSQL auto-increment increases on each update

Question

Every time I do an INSERT or UPSERT (ON CONFLICT UPDATE), the increments column on each table increments by the number of updates that came before it.

For instance, if I have this table:

id int4
title text
description text
updated_at timestamp
created_at timestamp

And then run these queries:

INSERT INTO notifications (title, description) VALUES ('something', 'whatever'); // Generates increments ID=1

UPDATE notifications title='something else' WHERE id = 1; // Repeat this query 20 times with different values.

INSERT INTO notifications (title, description) VALUES ('something more', 'whatever again'); // Generates increments ID=22

This is a pretty big issue. The script we are running processes 100,000+ notifications every day. This can create gaps between each insert on the order of 10,000, so we might start off with 100 rows but by the time we reach 1,000 rows we have an auto-incremented primary key ID value over 100000 for that last row.

We will quickly run out of auto-increment values on our tables if this continues.

Is our PostgreSQL server misconfigured? Using Postgres 9.5.3.

I'm using Eloquent Schema Builder (e.g. $table->increments('id')) to create the table and I don't know if that has something to do with it.

Answer 1

A sequence will be incremented whenever an insertion is attempted regardless of its success. A simple update (as in your example) will not increment it but an insert on conflict update will since the insert is tried before the update.

One solution is to change the id to bigint. Another is not to use a sequence and manage it yourself. And another is to do a manual upsert:

with s as (
    select id
    from notifications
    where title = 'something'
), i as (
    insert into notifications (title, description)
    select 'something', 'whatever'
    where not exists (select 1 from s)
)
update notifications
set title = 'something else'
where id = (select id from s)

This supposes title is unique.

Answer 2

You can reset auto increment column to max inserted value by run this command before insert command:

SELECT setval('notifications_id_seq', MAX(id)) FROM notifications;