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
