Postgresql – in postgres, can one optimize a table across partitions

postgresql

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?

Best Answer

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

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

PostgreSQL – Good Use Cases for Schemas

The development process has falsified the premiss of the question, i.e. each of the 2 derivations of metric1 have become inputs to derivations at several metric levels.

If this had not been the case, I'm now inclined to implement the original scenario via alternate views that duplicate query logic while being based on one or the other derivation of metric1. This seems from my pov a simpler approach, in that it avoids an additional schema and instead adopts a view-naming convention where view names denote the particular metric1 derivation.

Best Answer

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

PostgreSQL – Good Use Cases for Schemas

Related Question