'Indirect' clustering?

Поиск
Список
Период
Сортировка
От Isak Hansen
Тема 'Indirect' clustering?
Дата
Msg-id 6b9e1eb20612211600v74674967if50b4c56e3dd892@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Ben
Дата:
Сообщение: Re: Partitioning Vs. Split Databases - performance?
Следующее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: Partitioning Vs. Split Databases - performance?