3

When designing a multi-tenant database for use in a SaaS system, in an architecture where all tenants share the same tables, what is the best-practice for the type and value of the field that is suppose to uniquely represent a specific row?

Sure, the table might have a single auto-incremented id/sequence but, in that case a tenant could theoretically know the volume use of the SaaS system for other tenants and might disclose some additional meta-information. For example let's say that each tenant has it's own set of registered users (same user table). tenant 1 registers a user, and the user is visible to this tenant by id X. tenant 2 registered 100 users. tenant 1 registers another user, this time the id of the new first tenant's user is X+100. tenant 1 now knows that 100 users registered on the other tenant.

One option is some kind of guid; but it's an overkill in my opinion.

My better guess is that the primary key should be composed out of the record's tenant-id AND some internally sequenced number (i.e. a sequential id field where each tenant has it's own sequence of ascending id's).

THE QUESTION(S): 1) How should this internal-sequencing implemented in a PgSQL Database? 2) If it isn't the way to go, What is the best practice to do this in a relational database in general, and how should be implemented on PgSQL in particular.

Thanks.

Edit: Additional question: 3) If the right way IS a double-fielded primary key, how all of this should work and displayed to a user on the application (php) to a super-tenant? Do I need to pass each time I want to address a specific record, 2 values for the key, or, should an additional sequence-unique index should be created just for super-tenants?

Edit2 due to question on hold: I'm looking for alternatives (other than the one's i'v mentioned) and it's pros and cons, in general. And specifically I was asking how do I implemented such multi-sequence in PgSQL.

HLL
  • 169
  • 10
  • Composite primary key would be strongly preferred as far as I'm concerned, unless you're stuck with using an idiot ORM like Rails that doesn't support them. – Craig Ringer Apr 03 '14 at 12:09
  • I'm looking for alternatives (other than the one's i'v mentioned) and it's pros and cons, in general. And specifically I was asking how do I implemented such `multi-suqance` in PgSQL. – HLL Apr 03 '14 at 22:33
  • @CraigRinger Do u have any reference regarding how can I populate(in what value) the id field in the composed key? – HLL Apr 03 '14 at 22:34
  • Seems like there is no 'good' way to do it... only hackish one using a table and manual implementation of multi-sequance, related/suggestions could be found here: http://stackoverflow.com/questions/4672629/postgresql-company-id-based-sequence http://stackoverflow.com/questions/3591298/how-to-create-a-postgresql-partitioned-sequence – HLL Apr 04 '14 at 14:11
  • Just found another alternative, is using partitioning based on tenant id + ROW_NUMBER() is safe? http://stackoverflow.com/questions/6821871/postgresql-sequence-based-on-another-column?rq=1 – HLL Apr 04 '14 at 14:18
  • The latter would not work if I intend to delete records from the db sometime (as it uses the offset of the row in the window's result set) – HLL Apr 07 '14 at 22:48
  • 1
    Take a look at Daniel Verité's "pseudo_encrypt" function on the postgresql wiki. I think it is what you want. – Craig Ringer Apr 07 '14 at 23:22

0 Answers0