Tuesday, 17 July 2018

Postgres 9 and AWS Aurora Postgres Table Partitioning

At the time of writing the highest version of Postgres available on AWS Aurora is 9.6. This means declarative partitioning in Postgres 10 (see here) is unavailable.  Partitioning on Postgres aurora must be done the manual way. Below is how partitioning can be implemented in Postgres Aurora or Postgres 9.6.

The guide is adapted from postgres docs here. This approach uses table inheritance and insert trigger function

1. Create Base table 

First create the table which will be partitioned. This table will hold no data, but only define the schema


CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

2. Create partition tables

Each partition table inherits from parent, we also specify checks, this are important to ensure data not matching partition does not get inserted in a partition table. Also it used by the query planner in combination with constraint_exclusion.
-- ================== Partition 1 ============================
CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

-- create index for the partition table
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);

-- ================== Partition 2 ============================
CREATE TABLE measurement_y2006m03 (
  CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);

CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);


-- ================== Partition 3 ============================
CREATE TABLE measurement_y2006m04 (
  CHECK ( logdate >= DATE '2006-04-01' AND logdate < DATE '2006-05-01' )
) INHERITS (measurement);

CREATE INDEX measurement_y2006m04_logdate ON measurement_y2006m04 (logdate);

3. Insert routing 

Define the insert routing function on parent table. This redirects all inserts, done on the main table to appropriate partition table

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
  RETURNS TRIGGER AS $$
  BEGIN
      -- Insert into partition based on date range, NEW = new record to be inserted
      IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN
          INSERT INTO measurement_y2006m02 VALUES (NEW.*);

      ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN
          INSERT INTO measurement_y2006m03 VALUES (NEW.*);

      ELSIF ( NEW.logdate >= DATE '2008-04-01' AND NEW.logdate < DATE '2008-05-01' ) THEN
          INSERT INTO measurement_y2008m01 VALUES (NEW.*);

      ELSE
          RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
      END IF;

      RETURN NULL;
  END;
  $$
  LANGUAGE plpgsql;

  -- attach trigger to measurement table before insert
  CREATE TRIGGER measurement_insert_trigger
  BEFORE INSERT ON measurement
  FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();


Usage

Insert a row

INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES(1, '2006-03-10',0,0)


Check row is inserted in the correct partition
Select * from measurement_y2006m03;

Making select queries partition aware

This tells the query planner, to use the CHECK constraints defined on the table, to only look into appropriate partition.

SET constraint_exclusion = on;

SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-03-01';


Use Explain to verify query planner path

SET constraint_exclusion = on;

EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-03-01';

Removing a partition, completely including the data

 DROP TABLE measurement_y2006m02;


Note: Make sure to to update the trigger function, to remove the if statement redirecting inserts to this table

Removing partition from from partition set, but still have access to the data

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

Note: Make sure to to update the trigger function, to remove the if statement redirecting inserts to this table

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. You know how cool it is to make money at home? chic slot casino Here's a cool site. I sit here all day. Here are cool slots, slot machines, moreover, gambling.

    ReplyDelete
  3. Once connected with the information sources, the information researcher can without much of a stretch utilize the scratch pad to take advantage of dealing with intensity of the group utilizing top in class support for Spark.ExcelR Data Science Courses

    ReplyDelete
  4. Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. Mason Jacob

    ReplyDelete