0

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?

dragoon
  • 103
  • 2

1 Answers1

0

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...

voretaq7
  • 79,345
  • 17
  • 128
  • 213
  • 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