Table partition with foreign keys

When to partition:

Many a times I have seen people saying oh the table is not so big in size and does not need partitioning. Let me clarify a little bit here. Its never about the size of a table but the number of distinct rows in a table when making a decision on when to partition. The main reason we want to partition a table is to increase the query performance that might be taking a long time.

First things first lets not rush into conclusions and step back and think about the queries itself. Can the query be tuned to perform better? That is the question you should ask yourself and check the explain plan to probably add an index? Increase work_mem? Or just rearrange the joins or even remove some joins that are not necessary(I will write another post about how to tune queries soon). Once you are convinced about the fact that the query cannot be improved further then its time to make a decision to partition the table. The way I do this is to pick up every query that is referencing the table and check the best way moving forward. For eg: if your application queries are running off of id column and you make a partition on the date column. You are in for bigger problems and the performance will degrade. Find the column on which you would want the partition to be based on and then move ahead with the testing and implementation. Personally I would create the partition on a test box and run some serious tests using pgbench. That would give you information if its worth partitioning the table or not.

The process: 

I started working on table partitioning on postgres and thought this might come in handy when we are dealing with foreign keys that are not recognized when dealing with partitions. The standard method of partition will give us information on how to create and manage partitions but when it comes to foreign keys we have to go an extra mile to make sure foreign keys are maintained when implementing partition.

I have an example below with two tables that I want to partition (accounts and billing). Billing table has a foreign key on the billing_id column referencing accounts_id as below:

billing:

Table "public.billing"
Column | Type | Modifiers
--------------+---------+-----------
billing_id | integer | not null
billing_type | boolean |
dt_billing | date |
Indexes:
"billing_pkey" PRIMARY KEY, btree (billing_id)
Foreign-key constraints:
"bill_id_fk" FOREIGN KEY (billing_id) REFERENCES accounts(account_id) ON UPDATE RESTRICT ON DELETE RESTRICT

accounts:

Table "public.accounts"
Column | Type | Modifiers
--------------+------------------------+-----------
account_id | integer | not null
account_name | character varying(100) |
dt_account | date |
Indexes:
"accounts_pkey" PRIMARY KEY, btree (account_id)
Referenced by:
TABLE "billing" CONSTRAINT "bill_id_fk" FOREIGN KEY (billing_id) REFERENCES accounts(account_id) ON UPDATE RESTRICT ON DELETE RESTRICT

Lets start by setting up partition on both of these tables with the date field.

NOTE: I have created a shadow table for accounts to keep track of the foreign keys. The reason for this is that foreign keys can be created from one partitioned table to another table, or to a specific partition, but not to the top level of a partitioned table because the parent table has no data. In many cases you are not going to partition using that foreign key column and then table 2 (billing) can’t see the respective column in table1 (accounts). For partitions to work with foreign keys I simply created a shadow table with the account_id column and reference that table for all the child billing tables. Also, in order to maintain this shadow table I am creating a rule that will update the shadow table when the primary accounts table is updated. You can call it a hack but it works well.  If you don’t care about the foreign keys then just drop them and follow steps without creating shadow table and rule.

-- Creating accounts parent table

CREATE TABLE accounts
(
account_id integer NOT NULL,
account_name character varying(100),
dt_account date,
CONSTRAINT accounts_pkey PRIMARY KEY (account_id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE accounts OWNER TO postgres;

--Creating accounts child tables

CREATE TABLE accounts_2012
(
account_id integer NOT NULL,
account_name character varying(100),
dt_account date,
CONSTRAINT accounts_2012_pkey PRIMARY KEY (account_id ),
CHECK ( EXTRACT(YEAR FROM dt_account) = 2012))
INHERITS (accounts);
ALTER TABLE accounts_2012 OWNER TO postgres;

CREATE TABLE accounts_2011
(
account_id integer NOT NULL,
account_name character varying(100),
dt_account date,
CONSTRAINT accounts_2011_pkey PRIMARY KEY (account_id ),
CHECK ( EXTRACT(YEAR FROM dt_account) = 2011))
INHERITS (accounts);
ALTER TABLE accounts_2011 OWNER TO postgres;

CREATE TABLE accounts_2010
(
account_id integer NOT NULL,
account_name character varying(100),
dt_account date,
CONSTRAINT accounts_2010_pkey PRIMARY KEY (account_id ),
CHECK ( EXTRACT(YEAR FROM dt_account) = 2010))
INHERITS (accounts);
ALTER TABLE accounts_2010 OWNER TO postgres;

--Creating shadow table for primary and foreign keys

CREATE TABLE accounts_shadow
(
account_id integer NOT NULL,
CONSTRAINT accounts_shadow_pkey PRIMARY KEY (account_id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE accounts OWNER TO postgres;

--Creating parent billing table

CREATE TABLE billing
(
billing_id integer NOT NULL,
billing_type boolean,
dt_billing date,
CONSTRAINT billing_pkey PRIMARY KEY (billing_id ),
CONSTRAINT bill_id_fk FOREIGN KEY (billing_id)
REFERENCES accounts_shadow (account_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
OIDS=FALSE
);
ALTER TABLE billing
OWNER TO postgres;

--Creating child billing tables

CREATE TABLE billing_2012
(
billing_id integer NOT NULL,
billing_type boolean,
dt_billing date,
CONSTRAINT billing_2012_pkey PRIMARY KEY (billing_id ),
CONSTRAINT bill_id_2012_fk FOREIGN KEY (billing_id)
REFERENCES accounts_shadow (account_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK ( EXTRACT(YEAR FROM dt_billing) = 2012))
INHERITS (billing);
ALTER TABLE billing OWNER TO postgres;

CREATE TABLE billing_2011
(
billing_id integer NOT NULL,
billing_type boolean,
dt_billing date,
CONSTRAINT billing_2011_pkey PRIMARY KEY (billing_id ),
CONSTRAINT bill_id_2011_fk FOREIGN KEY (billing_id)
REFERENCES accounts_shadow (account_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK ( EXTRACT(YEAR FROM dt_billing) = 2011))
INHERITS (billing);
ALTER TABLE billing OWNER TO postgres;

CREATE TABLE billing_2010
(
billing_id integer NOT NULL,
billing_type boolean,
dt_billing date,
CONSTRAINT billing_2010_pkey PRIMARY KEY (billing_id ),
CONSTRAINT bill_id_2010_fk FOREIGN KEY (billing_id)
REFERENCES accounts_shadow (account_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK ( EXTRACT(YEAR FROM dt_billing) = 2010))
INHERITS (billing);
ALTER TABLE billing OWNER TO postgres;

After creating the tables its time to create the functions and triggers like so:

--Function for billing table

CREATE OR REPLACE FUNCTION billing_trig() RETURNS TRIGGER
AS
$$
BEGIN
IF TG_OP = 'INSERT' THEN
IF EXTRACT (YEAR FROM NEW.dt_billing) = 2012 THEN
INSERT INTO billing_2012 VALUES(NEW.*);
ELSIF EXTRACT (YEAR FROM NEW.dt_billing) = 2011 THEN
INSERT INTO billing_2011 VALUES(NEW.*);
ELSIF EXTRACT (YEAR FROM NEW.dt_billing) = 2010 THEN
INSERT INTO billing_2010 VALUES(NEW.*);
ELSE
RAISE NOTICE 'Wrong Year in NEW.dt_billing Values%', NEW.dt_billing;
END IF;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_billing_tg
BEFORE INSERT ON billing
FOR EACH ROW
EXECUTE PROCEDURE billing_trig();

--Function for accounts table

CREATE OR REPLACE FUNCTION accounts_trig() RETURNS TRIGGER
AS
$$
BEGIN
IF TG_OP = 'INSERT' THEN
IF EXTRACT (YEAR FROM NEW.dt_account) = 2012 THEN
INSERT INTO accounts_2012 VALUES(NEW.*);
ELSIF EXTRACT (YEAR FROM NEW.dt_account) = 2011 THEN
INSERT INTO accounts_2011 VALUES(NEW.*);
ELSIF EXTRACT (YEAR FROM NEW.dt_account) = 2010 THEN
INSERT INTO accounts_2010 VALUES(NEW.*);
ELSE
RAISE NOTICE 'Wrong Year in NEW.dt_account Values%', NEW.dt_account;
END IF;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_account_tg
BEFORE INSERT ON accounts
FOR EACH ROW
EXECUTE PROCEDURE accounts_trig();

Finally lets create a rule on the accounts table to insert new values into the shadow table:

CREATE RULE 'accounts_id_rule' AS ON INSERT
TO accounts
DO INSERT INTO accounts_shadow VALUES
(
NEW.account_id
);
Advertisements

, ,

  1. #1 by Ayoub on December 16, 2015 - 9:35 am

    –Creating shadow table for primary and foreign keys

    CREATE TABLE accounts_shadow
    (
    account_id integer NOT NULL,
    CONSTRAINT accounts_shadow_pkey PRIMARY KEY (account_id )
    )
    WITH (
    OIDS=FALSE
    );
    ALTER TABLE accounts OWNER TO postgres;

    You mean ALTER TABLE accounts_shadow >

    • #2 by Vikram Rai on February 17, 2016 - 12:49 am

      yes sir thats correct. Sorry about that

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: