Postgresql: Partition table by hour

Question

I have a system that inserts several million records over the course of 24 hours. Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 minutes). Applications need to access small timespans of data very quickly (under a second). Partitioning the table by hour seemed like the best thing to do.

I made a sample / test setup to make sure the partitioning would work correctly. Here's my main table:

create table test_table(
    test_table_id bigserial primary key,
    "timestamp" timestamp without time zone default now(),
    did_pass boolean not null
);
create index timestamp_idx on test_table("timestamp");

And then created a separate schema to hold the partition tables:

create schema test_table_parition;

And created the hourly partition tables. There's a total of 24 partition tables.

create table test_table_partition.h0(
    check((extract(hour from "timestamp")) >= 0 and (extract(hour from "timestamp")) < 1)
)inherits (test_table);
create index timestamp_h0_idx on test_table_partition.h0("timestamp");

create table test_table_partition.h1(
    check((extract(hour from "timestamp")) >= 1 and (extract(hour from "timestamp")) < 2)
)inherits (test_table);
create index timestamp_h1_idx on test_table_partition.h1("timestamp");
.
.
.
create table test_table_partition.h23(
    check((extract(hour from "timestamp")) >= 23 and (extract(hour from "timestamp")) < 24)
)inherits (test_table);
create index timestamp_h23_idx on test_table_partition.h23("timestamp");

Created the insert trigger

 create or replace function test_table_insert_function() 
 returns trigger as $$
 declare 
 currentHour smallint;
 begin
 currentHour := extract(hour from now());
 if ( currentHour = 0 ) then insert into test_table_partition.h0 values(NEW.*);
 elseif ( currentHour = 1 ) then insert into test_table_partition.h1 values(NEW.*);
 .
 .
 .
 elseif ( currentHour = 23 ) then insert into test_table_partition.h23 values(NEW.*);
 end if;
 return null;
 end;
 $$
 language  plpgsql;

 create trigger insert_test_table_trigger
 after insert on test_table for each row
 Execute procedure test_table_insert_function();

Inserted some dummy data. The data showed up in the main table and in the partition table (as expected)

insert into test_table(did_pass)
values(false),(false), (true), (true);

And then got the execution plan for a simple test query.

explain select *
from test_table
where "timestamp" > '2015-02-20 11:00:30.152';

The query execution plan is going after the main table and all 24 of the partition tables. From my understanding of partitioning, the execution plan should only be going after the partition tables with an hour value greater than or equal to 11.

How can I partition tables based on hour?

UPDATE

I was playing around with it a bit more today, and I discovered that running a query directly against the hour value of the table will produce the expected query plan.

For example:

explain select *
from test_table
where extract(hour from "timestamp") >= 11
and extract(hour from "timestamp") <13;

will deliver the expected execution plan:

"Append  (cost=0.00..50.54 rows=11 width=17)"
"  ->  Seq Scan on test_table  (cost=0.00..4.46 rows=1 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"
"  ->  Seq Scan on h11  (cost=0.00..1.48 rows=1 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"
"  ->  Seq Scan on h12  (cost=0.00..44.60 rows=9 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"

I think I need to change how the table partitioning is being setup.

Answer 1

For partition pruning to work, it is important that your partition key is simple and part of the where clause of your queries. In your example you can simplify to

check((extract(hour from "timestamp")) = 0  # equality
check((extract(hour from "timestamp")) = 1  # etc.
check((extract(hour from "timestamp")) = 23

At this time it would be a nice use case for timescaleDB which does a lot of this all automatically for you.