Re: Clustered index to preserve data locality in a multitenant application?

On Wed, 31 Aug 2016 17:33:18 +0200
Nicolas Grilly <nicolas(at)vocationcity(dot)com> wrote:

> Eduardo Morras wrote:
>
>
> > Check BRIN indexs, they are "designed for handling very large
> > tables in which certain columns have some natural correlation with
> > their physical location within the table", I think they fit your
> > needs.
>
>
> Yes, a BRIN index on the tenant ID would be very useful if the rows
> in the heap were naturally sorted by the tenant ID, but they are not.
> They are naturally sorted by their order of insertion, which is
> completely unrelated. The first step in solving this is to find a way
> to keep rows belonging to the same tenant close to each other. The
> second step could be to use a BRIN index.

Then you can make multiple column partial indexes:

CREATE INDEX CONCURRENTLY tenant_01_idx ON big_tenant_table (the_columns_with_data_you_need, tenant_id) WHERE tenant_id = 1;
CREATE INDEX CONCURRENTLY tenant_02_idx ON big_tenant_table (the_columns_with_data_you_need, tenant_id) WHERE tenant_id = 2;

This way each index has the data for a tenant, is updated only when the data for that tenant is updated and each index has it own files and you can reindex to clean index content and debloat.

REINDEX INDEX tenant_01_idx;

Or grouping them if there are too much indexes:
CREATE INDEX CONCURRENTLY tenant_01_idx ON big_tenant_table (the_columns_with_data_you_need, tenant_id) WHERE tenant_id <= 300;
CREATE INDEX CONCURRENTLY tenant_02_idx ON big_tenant_table (the_columns_with_data_you_need, tenant_id) WHERE tenant_id > 300 AND tenant_id <= 600;

--- ---
Eduardo Morras <emorrasg(at)yahoo(dot)es>