Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I want to parition my huge table to solve a performance issue, but the attibute on which I want to parition my table resides in another table that is related by FK.

Here are my tables:

Article (Table1)
 id
 submit_date
 ...

Concept (Table2)
 id
 article_id (FK)
 ...

So, I want to parition Concept table based on submit_date attribute from the article table (like split it by months). Is it possible with PostgreSQL?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Sure. You can partition on whatever field(s) or complex queries you want - The INSERT is handled by a trigger, and the SELECT by table inheritance.

In your case the trigger for the Concept table would need to do an appropriate query on submit_date to determine which partition to insert into. If you already have data in the Concept table you will need to do a little work to partition the existing data:

  1. MAKE A BACKUP
    If you proceed without making a backup and lose your data you will be mocked.
  2. Create the sub-tables for each partition.
  3. Make your database quiescent (either disconnect all clients or at a minimum lock the main table).
  4. INSERT the data into each partition
    (INSERT INTO Concept_0001 SELECT * FROM Concept WHERE …)
  5. DROP the original table and re-create it to inherit from the partitons.

Have you read the Postgres documentation on partitioning tables and table inheritence? Doing so is pretty much a prerequisite for attempting any kind of table partitioning in Postgres...

share|improve this answer
    
Thank you. Yes, I have read these docs, but they talk only about paritioning based on the direct table attributes. And you're talking about the trigger, but I don't know how to even create inherited table. The problem is that I already have data in both tables :) –  dragoon Mar 13 '12 at 16:08
    
@dragoon Please read the documentation, don't just skim it. The documenation explains in excruciating detail how to create inherited tables - something too complex to go into here. It is not a huge inductive leap to figure out how to partition on external attributes (It's a trigger. You should be intimately familiar with writing triggers before attempting partitioning), nor is it hard to figure out what you need to do to partition an already existing table, but I will add details on that to my answer for you. –  voretaq7 Mar 13 '12 at 16:54

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.