in a database with many companies, segregating them using
almost all access comes at the company level, that is, limited to a single schema. some data-warehouse and accounting functions need to scan all schema.
for this problem, per @horse_with_no_name's most excellent suggestion, using the schema themselves as partitions. for example
create table global.usr( usr_id int primary key, // these will be unique across all partitions name varchar ); create table cmp1.usr( usr_id int primary key, ) inherits( global.usr ); create table cmp2.usr( ....
if we populate
insert into cmp1 usr values( 11, 'eleven' ); insert into cmp1 usr values( 12, 'twelve' ); insert into cmp2 usr values( 21, 'twenty-one' ); insert into cmp2 usr values( 22, 'twenty-two' ); insert into cmp3 usr values( 31, 'thirty-one' ); insert into cmp3 usr values( 32, 'thirty-two' );
this works beautifully at the schema level:
set search_path = 'global'; select count(1) from t; => 6 set search_path = 'cmp1, global'; select count(1) from t; => 2 set search_path = 'cmp1, cmp2, global'; select count(1) from t; => 4 set search_path = 'global, cmp1'; select count(1) from t; => 6
the question is whether there is any way to optimize at the global level. for example, would it be possible to create an index on global.usr_id that would span all partitions?