Due to requirement to support distributed environment, each of my clients has been assigned unique node id, it appends the node id with running number as primary key and stores at local database. E.g.
Client A node id = 200, first row in a table will have primary key 200,000
Client B node id = 100, first row in a table will have primary key 100,000
These records then replicate to centralized database. Since primary key at centralized database is not in sequence, will it cause any serious performance issue when data size getting bigger?
Possible sequence of inserting new data at centralized database:
200,000 100,000 100,001 200,001 300,000 100,002
This may cause a big performance in SQL Server with table is clustered along the PK. However, will this happens in PostgreSQL 9.3?
- I can't use composite keys as it does not play well at my presentation layers.
- The 3 digits running number is just a simplified example, real running number will be much bigger and sufficient.