Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

in a database with many companies, segregating them using schema and search_path

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?

share|improve this question

migrated from stackoverflow.com Jan 8 '12 at 21:45

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 4 down vote accepted

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?

Sadly the answer is "No" - global indexes are not currently supported

share|improve this answer
    
I can understand why - it's a bitch of a problem - but because of the usefulness of pg's partitioned tables, would be dynamite to have –  cc young Jan 14 '12 at 4:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.