On Wed, 31 Aug 2016 17:33:18 +0200
Nicolas Grilly <nicolas@vocationcity.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
hasit 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@yahoo.es>