# PostgreSQL Partitioning using inheritance

**URL:** <https://forum.inductiveautomation.com/t/postgresql-partitioning-using-inheritance/7816>\
**Category:** Archived Topics\
**Created:** [January 11, 2014, 4:10am UTC](https://forum.inductiveautomation.com/t/postgresql-partitioning-using-inheritance/7816 "2014-01-11T04:10:59Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![malleblas](https://avatars.discourse-cdn.com/v4/letter/m/ea666f/32.png) [@malleblas](https://forum.inductiveautomation.com/u/malleblas)\
**Post date:** [January 11, 2014, 4:10am UTC](https://forum.inductiveautomation.com/t/postgresql-partitioning-using-inheritance/7816/1 "2014-01-11T04:10:59Z")

</div>

Hi,

I decided to try on my development machine, to set up partitioning using PostgreSQL’s internal inheritance feature.

Essentially you create a parent table, and from that you can inherit all the columns. The benefit is that you can query the parent table and it will automatically go down into the inherited tables based on rules and what not, all the while benefiting from the query planner and optimisations it may apply.

[color=#FF0000]Disclaimer: I do not offer support or plan to support this. I am merely sharing what work I have done just playing around this afternoon.[/color]

[color=#00FF00]Code is free for anyone to use, modify, redistribute at a commercial, academic or personal level.[/color]

My setup is:

- Ignition 7.6.4 (b2013112117) with developer mode license
- Windows 7 SP1
- PostgreSQL 9.3.2, compiled by Visual C++ build 1600, 64-bit
- Java version “1.7.0\_45” / Java™ SE Runtime Environment (build 1.7.0\_45-b18) / Java HotSpot™ 64-Bit Server VM (build 24.45-b08, mixed mode)
- JDBC41 Postgresql Driver, Version 9.3-1100

This is the main data table. Remember to turn off partitioning in Ignition Gateway configuration.

`CREATE TABLE sqlth_1_data
(
  tagid integer NOT NULL,
  intvalue bigint,
  floatvalue double precision,
  stringvalue character varying(255),
  datevalue timestamp without time zone,
  dataintegrity integer,
  t_stamp bigint NOT NULL,
  CONSTRAINT sqlth_1_data_pkey PRIMARY KEY (tagid, t_stamp)
)
WITH (
  OIDS=FALSE
);`

And this is the function which does the partitioning. It is currently hard coded for per month partitions.

[code]CREATE OR REPLACE FUNCTION trg\_sqlth\_data\_partition()  
RETURNS trigger AS  
$BODY$DECLARE  
\_newdate timestamp with time zone;  
\_tablename text;  
\_startdate text;  
\_enddate text;  
BEGIN

\_newdate := to\_timestamp(cast(NEW.t\_stamp as double precision) / 1000) at time zone ‘00:00’;  
_tablename := 'sqlth\_data_’ || to\_char(\_newdate, ‘YYYY\_MM’);

PERFORM 1  
FROM pg\_catalog.pg\_class c  
JOIN pg\_catalog.pg\_namespace n ON n.oid = c.relnamespace  
WHERE c.relkind = ‘r’  
AND c.relname = \_tablename  
AND n.nspname = ‘public’;

IF NOT FOUND THEN  
\_startdate := cast(extract(epoch from date\_trunc(‘month’, (\_newdate))) as bigint) \* 1000;  
\_enddate := cast(extract(epoch from date\_trunc(‘month’, (\_newdate + INTERVAL ‘1 month’))) as bigint) \* 1000;

```
EXECUTE 'CREATE TABLE public.' || quote_ident(_tablename)
|| ' (PRIMARY KEY (tagid, t_stamp),'
|| ' CHECK ((t_stamp >= ' || quote_literal(_startdate)
|| ' AND t_stamp < ' || quote_literal(_enddate)
|| '))) INHERITS (public.sqlth_1_data)';

```

END IF;

EXECUTE ‘INSERT INTO public.’ || quote\_ident(\_tablename) || ’ SELECT ($1).\*’  
USING NEW;  
RETURN NULL;

END;$BODY$  
LANGUAGE plpgsql VOLATILE  
COST 100;[/code]

Last but not least is the trigger definition itself.

`CREATE TRIGGER insbef
  BEFORE INSERT
  ON sqlth_1_data
  FOR EACH ROW
  EXECUTE PROCEDURE trg_sqlth_data_partition();`

---

<div class="post-metadata">

**Author:** ![malleblas](https://avatars.discourse-cdn.com/v4/letter/m/ea666f/32.png) [@malleblas](https://forum.inductiveautomation.com/u/malleblas)\
**Post date:** [February 4, 2014, 4:44am UTC](https://forum.inductiveautomation.com/t/postgresql-partitioning-using-inheritance/7816/2 "2014-02-04T04:44:30Z")

</div>

[github.com/keithf4/pg\_partman](https://github.com/keithf4/pg_partman)

Checking this out now, will report back in time.