1

I wanted to implement partitioning via inheritance in Postgres. I implemented the below steps by referring to the Postgres article :-

  1. Created a master table called "test_table"
CREATE TABLE kirana_customer.test_table
(
    col1 bigint NOT NULL DEFAULT nextval('kirana_customer."testTable_col1_seq"'::regclass),
    col2 bigint NOT NULL,
    col3 integer,
    CONSTRAINT "testTable_pkey" PRIMARY KEY (col1)
)
  1. Created the child/inherited table
CREATE TABLE kirana_customer.test_table_1
(
    -- Inherited from table kirana_customer.test_table: col1 bigint NOT NULL DEFAULT nextval('kirana_customer."testTable_col1_seq"'::regclass),
    -- Inherited from table kirana_customer.test_table: col2 bigint NOT NULL,
    -- Inherited from table kirana_customer.test_table: col3 integer,
    CONSTRAINT check_col3 CHECK (col3 = 1)
)
    INHERITS (kirana_customer.test_table)
  1. Attached a "BEFORE INSERT" trigger to the master table for inserting data based on column "col3" to the correct partition table
DECLARE

    v_col3 bigint;
BEGIN
    v_col3 := NEW.col3;
    
    
    EXECUTE 'INSERT INTO kirana_customer.test_table_'||v_col3||' VALUES ($1.*)' USING NEW;

RETURN NULL;
END;

After completing all these steps i am able to insert my entries into the correct partition , but while analysing the select statements i found that Postgres is scanning all the partitions

explain select * from  kirana_customer.test_table where col3 = 1 

This gives the below output

"Append  (cost=0.00..34.42 rows=9 width=20)"
"  ->  Seq Scan on test_table  (cost=0.00..3.12 rows=1 width=20)"
"        Filter: (col3 = 1)"
"  ->  Seq Scan on test_table_1  (cost=0.00..31.25 rows=8 width=20)"
"        Filter: (col3 = 1)"

So, did I miss something? Or is this the way Postgres partitioning works?

5
  • 1
    "I wanted to implement partitioning via inheritance" - why? With Postres 12 you should be using declarative partitioning. Much more efficient and much better integrated into the query optimizer Commented Aug 26, 2021 at 12:52
  • Declarative partition wasn't allowing me to create partition if my partition key ins't a primary key or a composite primary key . I wasn't also able to use foreign keys . So , i chose to use inheritance base partitioning instead Commented Aug 26, 2021 at 13:28
  • You can't use foreign keys with inheritance based partitioning either. And you can't really have a proper primary key across all inherited tables as well. Commented Aug 26, 2021 at 13:29
  • But i still can use a partition key which isn't a PK or a composite PK . I know declarative partitioning is new and quite easy to work with . But , shouldn't inheritance based partitioning work as well ? Commented Aug 26, 2021 at 13:32
  • Inheritance based partitioning always was a poor man's partitioning implementation. Not well integrated into the query optimizer and it never worked as good as it should be. True, you might be able to use a partition key that is not part of the PK - but the PK itself isn't really a (global) PK to begin with. Commented Aug 26, 2021 at 13:36

2 Answers 2

3

You can't really draw conclusions with a sample size of 1. And you only have one child table.

There is no constraint that the root table cannot contain rows where col3=1, so it needs to be scanned. Scanning an empty able is not a big deal, but if you did want to avoid it you could add a constraint:

alter table kirana_customer.test_table add constraint whatever
   check (col3 is null) no inherit; 

Also, your reason for not wanting to use declarative partitioning doesn't make any sense. Maybe you should ask a question with an example about whatever misconception you have about that.

Sign up to request clarification or add additional context in comments.

1 Comment

After adding the constraint , it's not scanning the parent table . Thanks !!!
1

You have to set constraint_exclusion to on or partition for that to work.

1 Comment

constraint_exclusion is already set to partition . It stopped scanning the parent table , once i put a constraint as explained in the previous answer . Anyways , thanks for your help !!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.