I'm seeking a primary key for my relational database (Postgres) tables that:
- Can be generated on the client and has a low chance of collisions
- Can support fast (and possibly clustered) indexes.
- Will play well with federation, sharding, and migration scenarios.
- Will not leak data about my system internals when used as a public object id (for example in an API).
At first, I thought of using an internal auto-increment integer primary key, and a public client-generated GUID surrogate key. But there are issues with this approach.
After further thought, I'm thinking an integer timestamp primary key (with millisecond resolution) generated on the client, will meet all these requirements well.
Are there possible problems with this approach?