1

I propose using a single sequence for the bigint identity columns of most or all tables. (Essentially, OID, except 64-bit.)

  1. This helps prevent security problems for a multi-tenant application that joins tables incorrectly.

  2. While a uuid could be used for this, bigint is half that size, helping save index space.

My understanding is that sequence generation is very fast in PostgreSQL.

The only downside I can see is widening the type of tables could get by with 4 byte integer IDs, but thanks to PostgreSQL upsert behavior (generating a new value whether or not it's needed), I rarely use int IDs anyway.

Is there an issue with approach? I haven't found anyone advocating for it, but I consider the join safeguard to be quite nice.

1
  • @McNets, the title is right, but they seem to have weird setup. Oct 24 '20 at 2:09