'Indirect' clustering?

From: "Isak Hansen" <isak(dot)hansen(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: 'Indirect' clustering?
Date: 2006-12-22 00:00:06
Message-ID: 6b9e1eb20612211600v74674967if50b4c56e3dd892@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have a multi-tenant db with a lot of DDL along these lines:

journal_entry (
id serial,
tenant_id integer not null,
entry_date datetime not null,
description varchar(255),
primary key (id),
foreign key (tenant_id) references tenant (id)
);

tx (
id serial,
journal_entry_id integer not null,
amount decimal(16, 4) not null,
...
foreign key (journal_entry_id) references journal_entry (id)
);

Most operations apply to a single tenant, thus clustering on the
tenant id (plus pk?) should make sense.

Problem is that a majority of our tables don't contain a tenant id
(and even as-is I'm sure we violate the basic normal forms with too
many references to tenant scattered around..).

Is it somehow possible to cluster the tx table based on
journal_entry's tenant_id value?

Or should we just add a tenant reference to every table anyway? Could
make our authorization layer a bit simpler/safer if every row
contained the tenant id.

I'm not sure clustering our transaction/ledger table would pay off,
we'll probably have to create some kind of summary table anyway (and
those _will_ be clustered, redundant FK or not..), but this is more of
a general question; we also have several other tables with a fair
amount of data in them where a tenant FK isn't natural..

Thanks in advance,
Isak

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2006-12-22 00:11:10 Re: Partitioning Vs. Split Databases - performance?
Previous Message Ben 2006-12-21 23:53:17 Re: Partitioning Vs. Split Databases - performance?