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
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? |