Skip to content

bitdivine/pg_day_partitions

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

4 Commits
 
 
 
 
 
 

Repository files navigation

Postgres SQL: Simple timeseries in postgres.

You have a massive table with records going back for years. It has become unmanageable. Even deleting old data is slow and inconvenient. What do you do?

Postgres inheritance provides a transparent way of splitting a massive table into many smaller tables, one for each day. Queries don't have to be changed - postgres will transparently run a select query on all the small tables and glue the results together:

-- Create a parent table:
create table ht
( my_daynum	int
, score		int
, size		int
, domain	text
, country	text
, browser	text
, hack_type	int
);

-- Create small tables for UNIX daynums 16791 and 16792:
create table ht_16791 () inherits (ht);
create table ht_16792 () inherits (ht);

-- Insert some data fro those days:
copy ht_16791 from '/tmp/ht_16791.csv' csv;
copy ht_16792 from '/tmp/ht_16792.csv' csv;

-- Querying the parent table gives you data in the small tables:
select * from ht limit 20;

-- Data too old to be interesting?  Delete that day:
drop table ht_16791;

However postgres does NOT manage insertions transparently. Not out of the box. That is what these functions are for:

CREATE TRIGGER ht_insert_trigger
    BEFORE INSERT ON ht
    FOR EACH ROW EXECUTE PROCEDURE daytable_insert_trigger('my_daynum');
insert into ht values (19,10,3,'loopy','NZ','Panda',96758769);
\dt
select * from ht;

This will automagically create the small table, if necessary, and insert the data into the appropriate small table.

About

Functions for creating tables partitioned by day.

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published