Create PostgreSQL Table Partitioning (Part 1) - Comment Page: 1

PostgreSQL supports partitioning via table inheritance. So the partitioning is made in such a way that every child table inherits single parent table. Parent table is empty and it exists just to describe the whole data set. PostgreSQL partitioning can be implemented in range partitioning or list partitioning. Range partitioning can be done for example by ID ranges (like 0-100 000, 100 001-200 000, 200 001-300 000...) or Date ranges (like 2009-11-01 - 2009-11-30, 2009-12-01 - 2009-12-31...). List partitioning can be done for example by list of cities (like New York, Los Angeles, Chicago, Houston, Philadelphia...) or list of categories (like Programming, Home, Food...). It's...

8 comments on “Create PostgreSQL Table Partitioning (Part 1) - Comment Page: 1

    1. Great, very clear article.

      Question: will the INSERT trigger be enough, or do I also need to create similar UPDATE and DELETE triggers?


      • Hi Erick,

        Glad to hear that you like this article.

        You don’t need UPDATE and DELETE triggers, INSERT trigger is enough. You could test it easily, just create test tables, INSERT, UPDATE and DELETE rows. And you can of course use EXPLAIN to see what is happening. :)

    2. for this query i have problem if im update with enter grade out of range will error
      example im insert
      INSERT INTO impressions_by_day(
      advertiser_id, “day”, impressions)
      VALUES (1234, ‘2009-01-02′, 756);

      and im update

      UPDATE impressions_by_day
      SET advertiser_id=1234, day=’2008-01-02′, impressions=756
      WHERE day=’2009-01-02’;

      then error
      ERROR: new row for relation “impressions_by_day_y2009m1ms2” violates check constraint “impressions_by_day_y2009m1ms2_day_check”
      SQL state: 23514

      except im upgrade with range day ( NEW.DAY >= DATE ‘2009-01-01’ AND NEW.DAY < DATE '2009-03-01' )

      how to solution with this case


    3. Excellent article…Thumbs up!!
      Thanks a lot…

    4. Hi , I tried partition scripts and could see the insert on master table goes to the right child table whereas the delete operation is not happening.Please look in to this issue

      • Hi dmhr,

        What PostgreSQL version you use?

    5. @didik prasetyo … a have xtly the same problem … did you find any solutions !?

      • Hi Nikolay,

        Could you post more info about your update/insert command and your trigger function(s)?

        This simple example is mainly storing and querying data, like logging something. If you have just insert trigger, then update fails, when you try to update row outside of your table check range.

        If we check didik example:

        First insert:

        INSERT INTO impressions_by_day(advertiser_id, “day”, impressions)
        VALUES (1234, ‘2009-01-02′, 756);

        Then update:

        UPDATE impressions_by_day
        SET advertiser_id=1234, day=’2008-01-02′, impressions=756
        WHERE day=’2009-01-02′;

        Now your update is out of range ( NEW.DAY >= DATE ‘2009-01-01′ AND NEW.DAY < DATE '2009-03-01' ). So just update command won't move that row to another table (from impressions_by_day_y2009m1ms2 table to impressions_by_day_y2008m1ms2 table). You have to create another trigger function to handle update situations, where updated rows are inserted right place and old rows are deleted from old table.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.