notes about proper use of SERIAL/BIGSERIAL for determinism and no gaps

[jchappelow]

This is a discussion for notes about how to properly use SERIAL or BIGSERIAL as a primary key with determinism.

From https://www.reddit.com/r/Supabase/comments/13ffzq3/comment/jjwsq6g/

CREATE SEQUENCE IF NOT EXISTS <sequence_name>
    INCREMENT BY 1 
    NO MAXVALUE
    START WITH 1
    CYCLE
    OWNED BY <table.column_name>; 

-- Whenever the sequence is called, it will increment. 
-- If multiple tables are using the same sequence, it will 
-- lead to some tables having sudden jumps in values. 
-- That is why it may be useful to include an OWNED BY clause.

CREATE TABLE my_table (
    id BIGSERIAL PRIMARY KEY DEFAULT NEXTVAL('<sequence_name>'), 
    name VARCHAR(100) 
);

-- BIGSERIAL is an alias for non-negative BIGINT. 
-- It can reach values up to 9223372036854775807

Part of bigger slack discussion about UUID and the viability of integer serials for a table primary key: https://kwilteam.slack.com/archives/C05RZNFKNJD/p1716564733454109