Table Partitioning with Postgres

table_partition.sql

EXPLAIN ANALYZE
select count(*) from ais_trackings;

select * from ais_trackings LIMIT 1;

DELETE from ais_trackings WHERE last_ais_updated_at < '2017-11-15'

DROP TABLE ais_trackings_2017_11_16;

CREATE TABLE ais_trackings_2017_11_14 (
	CHECK (last_ais_updated_at BETWEEN timestamp without time zone '2017-11-14 00:00:00' AND timestamp without time zone '2017-11-14 23:59:59')
) INHERITS(ais_trackings);

CREATE TABLE ais_trackings_2017_11_15 (
	CHECK (last_ais_updated_at BETWEEN timestamp without time zone '2017-11-15 00:00:00' AND timestamp without time zone '2017-11-15 23:59:59')
) INHERITS(ais_trackings);

CREATE TABLE ais_trackings_2017_11_16 (
	CHECK (last_ais_updated_at BETWEEN timestamp without time zone '2017-11-16 00:00:00' AND timestamp without time zone '2017-11-16 23:59:59')
) INHERITS(ais_trackings);

CREATE TABLE ais_trackings_2017_11_17 (
	CHECK (last_ais_updated_at BETWEEN timestamp without time zone '2017-11-17 00:00:00' AND timestamp without time zone '2017-11-17 23:59:59')
) INHERITS(ais_trackings);

SET constraint_exclusion = on

EXPLAIN ANALYZE
SELECT * from ais_trackings WHERE last_ais_updated_at BETWEEN DATE '2017-11-14' AND DATE '2017-11-15'

SELECT COUNT(*) from ONLY ais_trackings;

SELECT * from ais_trackings_2017_11_14;
SELECT * from ais_trackings_2017_11_15;
SELECT * from ais_trackings_2017_11_16;
SELECT * from ais_trackings_2017_11_17;

CREATE OR REPLACE FUNCTION ais_trackings_insert()
RETURNS TRIGGER AS $$
BEGIN
	IF (NEW.last_ais_updated_at BETWEEN timestamp without time zone '2017-11-14 00:00:00' AND timestamp without time zone '2017-11-14 23:59:59') THEN
    	INSERT INTO ais_trackings_2017_11_14 VALUES(NEW.*);
    ELSIF (NEW.last_ais_updated_at BETWEEN timestamp without time zone '2017-11-15 00:00:00' AND timestamp without time zone '2017-11-15 23:59:59') THEN
        INSERT INTO ais_trackings_2017_11_15 VALUES(NEW.*);
    ELSIF (NEW.last_ais_updated_at BETWEEN timestamp without time zone '2017-11-16 00:00:00' AND timestamp without time zone '2017-11-16 23:59:59') THEN
    	INSERT INTO ais_trackings_2017_11_16 VALUES(NEW.*);
    ELSIF (NEW.last_ais_updated_at BETWEEN timestamp without time zone '2017-11-17 00:00:00' AND timestamp without time zone '2017-11-17 23:59:59') THEN
    	INSERT INTO ais_trackings_2017_11_17 VALUES(NEW.*);
    ELSE
    	INSERT INTO ais_trackings VALUES(NEW.*);
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER ais_trackings_insert_trigger
BEFORE INSERT ON ais_trackings
FOR EACH ROW EXECUTE PROCEDURE ais_trackings_insert();