On Wed, 31 Aug 2016 17:33:18 +0200 Nicolas Grilly <nicolas@xxxxxxxxxxxxxxxx> 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@xxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
