vendredi 30 janvier 2015

Non-sequential Primary Key performance in PostgreSQL 9.3


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?


Notes:



  1. I can't use composite keys as it does not play well at my presentation layers.

  2. The 3 digits running number is just a simplified example, real running number will be much bigger and sufficient.





Aucun commentaire:

Enregistrer un commentaire