Is it safe to rely on auto increments even after restoring data?

Question

I plan of using auto increments as a primary key on one of my table.

I need to store data related to each row in an other storage system and I plan to use each primary key id as the key for the data.

Is it guaranteed that even after the restoration of a pg_dump, each row will still have the same id?

Answer 1

A quick overview:

• PostgreSQL does not have AUTO INCREMENTS. There is no special type.
• In order to set this up PostgreSQL uses the standard IDENTITY COLUMNS which associates a SEQUENCE with the int type.

Let's create a table,

CREATE TABLE foo (
  foo_id int PRIMARY KEY,
  bar    int GENERATED BY DEFAULT AS IDENTITY
);

When you pg_dump that table with an IDENTITY COLUMN you'll see this,

CREATE TABLE public.foo (
    foo_id integer NOT NULL,
    bar integer
);

That's because the type is simply int type. Now, when you load you'll see right after the table this,

ALTER TABLE public.foo ALTER COLUMN foo_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.foo_foo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

This tells the pg_restore the state associated with the sequence the IDENTITY COLUMN was using.

# \d foo
                             Table "public.foo"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 foo_id | integer |           | not null | generated by default as identity
 bar    | integer |           |          |

Then the data is loaded and when it's loaded all of the values of that sequence for each row are specified in the load -- they maintain the old values from the dump.*

Note that the PostgreSQL implementation of an IDENTITY COLUMN which is more less sugar for SEQUENCE does not ensure a gaplessness.

Footnotes

• I've left off the permission stuff for brevity.
• *I've removed all of the stuff about serial, bigserial and what serial was and how it worked because moving forward it should not be a thing. PostgreSQL users should be using IDENTITY COLUMNS in all circumstances.