Future proof Primary key design in postgresql
I've always used either auto_generated or Sequences in the past for my primary keys. With the current system I'm working on, it is possible to end up splitting data that was never a requirement in the past. Knowing that I might need to split the data in the future, is there an advantage to using a PC UUID rather than an embedded database sequence? If so, is there a design pattern that can safely generate relatively short keys (say 6 characters instead of the usual long e6709870-5cbc-11df-a08a-0800200c9a66)? 36 ^ 6 keys per table are more than enough for any table I could imagine.
I will be using keys in URLs, so brevity is important.
a source to share
There is no pattern to reduce the 128-bit UUID to 6 characters as information is lost. Almost all databases implement a surrogate key strategy called incremental keys. Postgres and Informix have serials, MySql auto_increment, and Oracle offers sequence generators. In your case, I think it would be safe to use integer ids.
See this article: Choosing a Primary Key: Natural or Surrogate? to discuss methods availabe
a source to share
I'm not sure what type of section you are planning ( this? ), But I don't understand why the design of the primary key should be changed? Even if the old partitioned tables are "live" (that is, you can insert rows into any partitioned table), there is no problem sharing sequencing across multiple tables.
a source to share