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,000Client 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?
Notes:
- 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.
Best Answer
I have 3 solutions for you:
Direct reference of sequence and using concat
One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here: https://stackoverflow.com/a/17925601/4206293
Using a UUID
Another possible solution is, if you don't need you node-id in the primary-key field, you can use the uuid-ossp extension which provides the type uuid and the functions to generate uuids: http://www.postgresql.org/docs/9.4/static/uuid-ossp.html
Use a trigger for these solutions
For both solutions: you can use a trigger to set the primary key.
Example:
If you now insert a value in the table
you get your primary key '1001'
Using MINVALUE and MAXVALUE
Another way is you use the MINVALUE and MAXVALUE of SEQUENCE to define an numeric space: