PostgreSQL partition existing table

postgres_partition_on_existing_table.sql

select integration_type, count(*) from integration_logs group by integration_type order by 2 desc;
select time_stamp from integration_logs order by time_stamp asc limit 1;
select time_stamp from integration_logs order by time_stamp desc limit 1;

insert into integration_logs (id, time_stamp, utc_time_stamp, integration_data, post_data_list, status, idoc_number, document_number, log_type, integration_data_type, integration_type, integration_message_type, message, ref_code, request_data, update_logs, "user", ref_number, re_execute)
select id, time_stamp, utc_time_stamp, integration_data, post_data_list, status, idoc_number, document_number, log_type, integration_data_type, integration_type, integration_message_type, message, ref_code, request_data, update_logs, "user", ref_number, re_execute
from integration_logs_old where time_stamp between '2020-01-01' and '2021-06-01';

select * from integration_logs_202102 limit 1000;
select * from integration_logs_202201 limit 100;
select * from integration_logs where id='f853d413-27d1-43ed-a549-eb0b54648218';
select count(*) from integration_logs;
select count(*) from integration_logs_old;

select count(*) from integration_logs where integration_type='Order';
select count(*) from integration_logs where integration_type='TransportOrder';
select count(*) from integration_logs where integration_type='Shipment';
select count(*) from integration_logs where integration_type='Condition';
select count(*) from integration_logs where integration_type='Order';
select * from information_schema.columns where table_name='integration_logs';
select * from pg_get_keywords();


begin;
alter table integration_logs rename to integration_logs_old;
create table integration_logs
(
    id                       text not null,
    time_stamp               timestamp not null,
    utc_time_stamp           timestamp not null,
    integration_data         jsonb,
    post_data_list           jsonb,
    status                   integer   not null,
    idoc_number              text,
    document_number          text,
    log_type                 text,
    integration_data_type    text,
    integration_type         text,
    integration_message_type text,
    message                  text,
    ref_code                 text,
    request_data             jsonb,
    update_logs              jsonb,
    "user"                   jsonb,
    ref_number               text,
    re_execute               boolean   not null,
    primary key (id, time_stamp)
) partition by range (time_stamp);
alter table integration_logs owner to postgres;
--alter table integration_logs_old add constraint integration_logs_old check(time_stamp >='2021-01-01' and time_stamp<'2021-06-01');
--alter table integration_logs attach partition integration_logs_old for values from ('2021-01-01') to ('2021-06-01');

drop index idx_integration_type;
--drop index idx_time_stamp;
create index idx_time_stamp on integration_logs (time_stamp);
create index idx_integration_type on integration_logs (integration_type);
--drop index idx_ref_number;
create index idx_document_number on integration_logs (document_number);
create index idx_idoc_number on integration_logs (idoc_number);

create table integration_logs_202101 partition of integration_logs for values from ('2021-01-01') to ('2021-06-01');
create table integration_logs_202102 partition of integration_logs for values from ('2021-06-01') to ('2022-01-01');
create table integration_logs_202201 partition of integration_logs for values from ('2022-01-01') to ('2022-06-01');
-- create table integration_logs_202202 partition of integration_logs for values from ('2022-06-01') to ('2022-01-01');
-- create table integration_logs_202301 partition of integration_logs for values from ('2023-01-01') to ('2023-06-01');
-- create table integration_logs_202302 partition of integration_logs for values from ('2023-06-01') to ('2023-01-01');

create table integration_logs_202601 partition of integration_logs for values from ('2026-01-01') to ('2026-06-01');
create table integration_logs_202602 partition of integration_logs for values from ('2026-06-02') to ('2026-12-31');
create table integration_logs_202701 partition of integration_logs for values from ('2027-01-01') to ('2027-06-01');
create table integration_logs_202702 partition of integration_logs for values from ('2027-06-02') to ('2027-12-31');
create table integration_logs_202801 partition of integration_logs for values from ('2028-01-01') to ('2028-06-01');
create table integration_logs_202802 partition of integration_logs for values from ('2028-06-02') to ('2028-12-31');
create table integration_logs_202901 partition of integration_logs for values from ('2029-01-01') to ('2029-06-01');
create table integration_logs_202902 partition of integration_logs for values from ('2029-06-02') to ('2029-12-31');
create table integration_logs_203001 partition of integration_logs for values from ('2030-01-01') to ('2030-06-01');
create table integration_logs_203002 partition of integration_logs for values from ('2030-06-02') to ('2030-12-31');

commit;
end;



-------------------------

select nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(c.oid)) AS "total_size"
from pg_class c left join pg_namespace n on (n.oid = c.relnamespace)
where nspname not in ('pg_catalog', 'information_schema')
and c.relkind <> 'i' and nspname !~ '^pg_toast' order by pg_total_relation_size(c.oid) desc;

begin;
alter table integration_logs rename to integration_logs_old;
create table integration_logs
(
    id                       text not null,
    time_stamp               timestamp not null,
    utc_time_stamp           timestamp not null,
    integration_data         jsonb,
    post_data_list           jsonb,
    status                   integer   not null,
    idoc_number              text,
    document_number          text,
    log_type                 text,
    integration_data_type    text,
    integration_type         text,
    integration_message_type text,
    message                  text,
    ref_code                 text,
    request_data             jsonb,
    update_logs              jsonb,
    "user"                   jsonb,
    ref_number               text,
    re_execute               boolean   not null,
    primary key (id, time_stamp)
) partition by range (time_stamp);
alter table integration_logs owner to postgres;

create index idx_time_stamp on integration_logs (time_stamp);
create index idx_integration_type on integration_logs (integration_type);
create index idx_document_number on integration_logs (document_number);
create index idx_idoc_number on integration_logs (idoc_number);

create table integration_logs_202101 partition of integration_logs for values from ('2021-01-01') to ('2021-06-01');
create table integration_logs_202102 partition of integration_logs for values from ('2021-06-01') to ('2022-01-01');
create table integration_logs_202201 partition of integration_logs for values from ('2022-01-01') to ('2022-06-01');
create table integration_logs_202202 partition of integration_logs for values from ('2022-06-01') to ('2023-01-01');
create table integration_logs_202301 partition of integration_logs for values from ('2023-01-01') to ('2023-06-01');
create table integration_logs_202302 partition of integration_logs for values from ('2023-06-01') to ('2024-01-01');
create table integration_logs_202401 partition of integration_logs for values from ('2024-01-01') to ('2024-06-01');
create table integration_logs_202402 partition of integration_logs for values from ('2024-06-01') to ('2025-01-01');
create table integration_logs_202501 partition of integration_logs for values from ('2025-01-01') to ('2025-06-01');
create table integration_logs_202502 partition of integration_logs for values from ('2025-06-01') to ('2026-01-01');
create table integration_logs_202601 partition of integration_logs for values from ('2026-01-01') to ('2026-06-01');
create table integration_logs_202602 partition of integration_logs for values from ('2026-06-01') to ('2027-01-01');
create table integration_logs_202701 partition of integration_logs for values from ('2027-01-01') to ('2027-06-01');
create table integration_logs_202702 partition of integration_logs for values from ('2027-06-01') to ('2028-01-01');
create table integration_logs_202801 partition of integration_logs for values from ('2028-01-01') to ('2028-06-01');
create table integration_logs_202802 partition of integration_logs for values from ('2028-06-01') to ('2029-01-01');
create table integration_logs_202901 partition of integration_logs for values from ('2029-01-01') to ('2029-06-01');
create table integration_logs_202902 partition of integration_logs for values from ('2029-06-01') to ('2030-01-01');
create table integration_logs_203001 partition of integration_logs for values from ('2030-01-01') to ('2030-06-01');
create table integration_logs_203002 partition of integration_logs for values from ('2030-06-01') to ('2031-01-01');
create table integration_logs_203101 partition of integration_logs for values from ('2031-01-01') to ('2031-06-01');
create table integration_logs_203102 partition of integration_logs for values from ('2031-06-01') to ('2032-01-01');
create table integration_logs_203201 partition of integration_logs for values from ('2032-01-01') to ('2032-06-01');
create table integration_logs_203202 partition of integration_logs for values from ('2032-06-01') to ('2033-01-01');
create table integration_logs_203301 partition of integration_logs for values from ('2033-01-01') to ('2033-06-01');
create table integration_logs_203302 partition of integration_logs for values from ('2033-06-01') to ('2034-01-01');
create table integration_logs_203401 partition of integration_logs for values from ('2034-01-01') to ('2034-06-01');
create table integration_logs_203402 partition of integration_logs for values from ('2034-06-01') to ('2035-01-01');
create table integration_logs_203501 partition of integration_logs for values from ('2035-01-01') to ('2035-06-01');
create table integration_logs_203502 partition of integration_logs for values from ('2035-06-01') to ('2036-01-01');
commit;
end;

insert into integration_logs (id, time_stamp, utc_time_stamp, integration_data, post_data_list, status, idoc_number, document_number, log_type, integration_data_type, integration_type, integration_message_type, message, ref_code, request_data, update_logs, "user", ref_number, re_execute)
select id, time_stamp, utc_time_stamp, integration_data, post_data_list, status, idoc_number, document_number, log_type, integration_data_type, integration_type, integration_message_type, message, ref_code, request_data, update_logs, "user", ref_number, re_execute
from integration_logs where time_stamp between '2020-01-01' and '2021-06-01';