0

I have table location in postgres database with more then 50.000.000+ rows, and i decide to do partition! Table parent have columns id,place and i want to do partition onplace column, with php and mysql i get all distinct places(about 300) and foreach 

CREATE TABLE child_place_name (CHECK (place=place_name))INHERITS(location)

and after that in each child table

 INSERT INTO child_place_name SELECT * FROM location WHERE place=place_name

and that works perfectly! After i delete rows from parent class with 

DELETE FROM location WHERE tableoid=('location'::regclass)::oid;

i that affected all rows is table! Then i try to do query and a get times and realize that now is time for query 3 or more times longer then before. I also have problem that my affect on speed: first i can't set primary key on id column in child tables, but i set index key on place(also index is set on place column in parent table), and also i can't set unique key on id and place columns i got error multiple parameters in not allowed(or something like that)

All i want is select side of table i don't need rules or triggers to set when i insert in parent table,cause that is another problem,only i want to know what is wrong with this approach!Maybe 300+ tables is to much?

squareCircle
  • 192
  • 1
  • 13
  • postgres partitions work on rules - you cant avoid it. what did you want to do with `DELETE FROM location WHERE tableoid=('location'::regclass)::oid;`?.. postgres does not support global PK on partitioned tables like oracle. – Vao Tsun Feb 17 '17 at 20:41
  • Is need rule for SELECT statement? This is my first time to do partititon – squareCircle Feb 18 '17 at 17:57

0 Answers0