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

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
  • 80,749