1

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.
  • 1
    Well, it pretty much depends on the queries you use to access the data, but I would not expect any performance hit. I think the only place where you can see some difference is the index of the PK itself, inserting new rows will happen all over the place, not just one end (well, this is a somewhat naive interpretation, I admit). I hope you devised a solution to avoid collision and overflow on these IDs ;) – dezso Jan 29 '15 at 16:15
  • Since you are only reserving 3 digits for use by each client, it is hard to see how the data size can get much bigger anyway. – jjanes Jan 30 '15 at 20:37
  • @jjanes It it just a simplified example, real running number will be much bigger and sufficient. – Lee Chee Kiam Jan 31 '15 at 0:54
  • Performance is a game of details. Simplified examples don't work very well, unless you simplify exactly the right thing in the right way. How far can you go without overflowing int? Or are you using bigint, or numeric, or varchar/text which just hold a string of digits? Most important, how many clients do you anticipate having? – jjanes Feb 2 '15 at 19:19
  • @jjanes Noted. I will use bigint. Number of my clients is from range 200 to 3000. My main concern is will I get the same SQL Server clustered PK problem in PG 9.3? – Lee Chee Kiam Feb 3 '15 at 3:10
1

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:

-- table
CREATE TABLE test(
   id character varying(10) NOT NULL,
  "name" character varying,
   CONSTRAINT idx_pk PRIMARY KEY (id)
);

-- seqence
CREATE SEQUENCE test_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Create Function
CREATE OR REPLACE FUNCTION insert_trigger() 
RETURNS TRIGGER    
AS $$
BEGIN
   NEW.id := '100' || nextval('test_seq')::TEXT;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- add Trigger
CREATE TRIGGER insert_table_trigger
BEFORE INSERT ON pkTable
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

If you now insert a value in the table

INSERT INTO test (name) VALUES ('test text');

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:

CREATE SEQUENCE node100_seq
  INCREMENT 1
  MINVALUE 100000000000
  MAXVALUE 100999999999
  START 100000000000
  CACHE 1;

CREATE SEQUENCE node200_seq
  INCREMENT 1
  MINVALUE 200000000000
  MAXVALUE 200999999999
  START 200000000000
  CACHE 1;
  • 2
    +1 on the UUID, but don't use a trigger. A function as column default is simpler, faster and safer. Also, you might want to mention bigint as intermediary option between int and uuid. – Erwin Brandstetter Jan 29 '15 at 19:54
  • Thanks for your suggestions. However, my concern is about performance of inserting non-sequential primary key at my centralized database. Do you have any idea? – Lee Chee Kiam Jan 29 '15 at 23:46
  • @CKLee have you seen my comment above? – dezso Jan 30 '15 at 9:09
  • 2
    Your IDs do not like random at all. To repeat myself, I'd expect a difference only in adding the new IDs to the index - page splits and reordering would happen more often than in a sequential PK. But this is what happens with an average non-PK index anyway, so it must not be that terrible. (In SQL Server, for example, when the table is clustered along the PK, the picture would be very different.) Which part of the suggestion do you mean, by the way? UUIDs are not better in this regard. – dezso Jan 30 '15 at 13:03
  • 1
    Sequentialness matters a lot less with SSDs too...some of the old best practices behavior with CI keys is out the window there. – JNK Jan 30 '15 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.