Column order in multi column primary key

From: "Craig Boucher" <craig(at)wesvic(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Column order in multi column primary key
Date: 2016-08-08 17:47:28
Message-ID: 0c9001d1f19c$ee793c90$cb6bb5b0$@wesvic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

PG 9.5

I'm in the process of converting our application from Sql Server to
Postgresql. I'm taking advantage of this process to make some database
design changes.

Our database contains data for many customers and I have a customer_id
column in every customer related table. One of the changes I made was to
change all of the tables that use an auto incrementing number as the primary
key to also include a customer_id in the pk. I also changed all of the
foreign keys to include both the id number column and the customer_id. I
made this change so when inserting a child record, I don't have to look up
each parent record to verify that it is owned by that customer. The
database handles this check for me now through foreign key constraints. My
question about multi column primary keys is should I have the customer_id
column first (which will have many repeated rows) and then the auto
incrementing id field (which will most likely be unique in the table), or
should it be there other way around. Will the pk index perform better one
way or the other or will it no matter?

The number of customers are in the hundreds and the number of child records
in some of the tables can be in the millions.

Thanks,

Craig

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alexander Farber 2016-08-08 18:12:47 select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0
Previous Message Adrian Klaver 2016-08-08 17:25:21 Re: Corrupted Data ?