3

I need to create a partitioned table with 4 rotating partitions for 4 days, and every day truncate the oldest partition.

I know how to create table partitions on postgres, but I can't understand how to arrange daily rotation of several partitions.

I'm on postgresql 9.6, Amazon RDS instance which means no pg_cron to be able to run daily truncating.

I could write a python script and schedule it in cron on a side micro instance, but I'd preper to use internal postgres tools only.

1
  • Are you using trigger to rotate table ?
    – Luan Huynh
    May 21 '18 at 14:22
2

First approach is creating partition every day which name matches date:

DROP TABLE IF EXISTS log_table;

CREATE TABLE log_table (
    logid         SERIAL,
    logdate         date DEFAULT Now()
) PARTITION BY RANGE (logdate);


CREATE TABLE log_table_2020_05_10 PARTITION OF log_table FOR VALUES FROM ('2020-05-10') TO ('2020-05-11');
CREATE TABLE log_table_2020_05_11 PARTITION OF log_table FOR VALUES FROM ('2020-05-11') TO ('2020-05-12');
CREATE TABLE log_table_2020_05_12 PARTITION OF log_table FOR VALUES FROM ('2020-05-12') TO ('2020-05-13');
CREATE TABLE log_table_2020_05_13 PARTITION OF log_table FOR VALUES FROM ('2020-05-13') TO ('2020-05-14');
CREATE TABLE log_table_2020_05_14 PARTITION OF log_table FOR VALUES FROM ('2020-05-14') TO ('2020-05-15');

And then every day you will have to drop the oldest partition and create the new one:

DROP TABLE log_table_2020_05_10;
CREATE TABLE log_table_2020_05_15 PARTITION OF log_table FOR VALUES FROM ('2020-05-15') TO ('2020-05-16');

The second approach with always-the-same-name of partitions will looks like this:

-- initial tables
DROP TABLE IF EXISTS log_table;

CREATE TABLE log_table (
    logid         SERIAL,
    logdate         date DEFAULT Now()
) PARTITION BY RANGE (logdate);

CREATE TABLE log_table_4 PARTITION OF log_table FOR VALUES FROM ('2020-05-10') TO ('2020-05-11');
CREATE TABLE log_table_3 PARTITION OF log_table FOR VALUES FROM ('2020-05-11') TO ('2020-05-12');
CREATE TABLE log_table_2 PARTITION OF log_table FOR VALUES FROM ('2020-05-12') TO ('2020-05-13');
CREATE TABLE log_table_1 PARTITION OF log_table FOR VALUES FROM ('2020-05-13') TO ('2020-05-14');
CREATE TABLE log_table_current PARTITION OF log_table FOR VALUES FROM ('2020-05-14') TO ('2020-05-15');

And then every day you will schedule something like:

-- drop the oldest partition
DROP TABLE log_table_4;

-- rename others
ALTER TABLE log_table_3 RENAME TO log_table_4;
ALTER TABLE log_table_2 RENAME TO log_table_3;
ALTER TABLE log_table_1 RENAME TO log_table_2;
ALTER TABLE log_table_current RENAME TO log_table_1;

-- create new one
CREATE TABLE log_table_current PARTITION OF log_table FOR VALUES FROM (date_trunc('day', Now())) TO (date_trunc('day', Now())+INTERVAL '1 day');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.