I want to stress the importance of not using

  id int SERIAL

If you are on a somewhat recent version of postgres, please do yourself a favor and use:

  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

An "identity column", the part here:

https://hakibenita.com/postgresql-unknown-features#prevent-s...

You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so you run into massive headaches if you try to move things around.

Identity columns don't, and avoid the issue altogether.

https://www.2ndquadrant.com/en/blog/postgresql-10-identity-c...

netcraft

I'll stop short of giving a recommendation or using the word "should", but ill give encouragement to consider using uuid's for keys. I have used them in several systems and have never had any issues with them, and they solve so many issues. The ability to generate a key on the client or on the server or in the database is great for one. And the fact that keys are unique not only in a table but in the system (or many systems) is a huge advantage.

gavinray

Yeah I recognize the arguments for UUID keys:

  - Avoids people being able to just iterate through records, or to discover roughly how many records of a thing you have
  - Allows you to generate the key before the row is saved

I think I default to auto-increment ID's due to:

  - Familiarity bias
  - They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they might be created)
  - Easier to read (when you have less than +1,000,000 rows in a table)

I agree and think you're right in that UUID's are probably a better default.

Though you can never find a "definitive" guide/rule online or in the docs unfortunately.

spiffytech

> They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they might be created)

UUIDv7 (currently a draft spec[0]) are IDs that can be sorted in the chronological order they were created

In the meantime, ulid[1] and ksuid[2] are popular time-sortable ID schemes, both previously discussed on HN[3]

[0] https://datatracker.ietf.org/doc/html/draft-peabody-dispatch...

[1] https://github.com/ulid/spec

[2] https://github.com/segmentio/ksuid

[3] ulid discussion: https://news.ycombinator.com/item?id=18768909

UUIDv7 discusison: https://news.ycombinator.com/item?id=28088213