Time-based table partitioning using pg partman

Installing and setting up pg_partman

git clone https://github.com/keithf4/pg_partman
cd pg_partman
make install
make NO_BGW=1 install
create database test-partman;
\c test-partman;
create schema partman;
create extension pg_partman schema partman;

Creating a database table

create table users(
id bigserial primary key,
first_name varchar not null,
last_name varchar,
email varchar not null,
created_at timestamp without time zone not null,
updated_at timestamp without time zone not null
);

Creating a partition set

Setting the retention period

UPDATE partman.part_config 
SET retention = '5 days', retention_keep_table=false
WHERE parent_table='public.users';

Running maintenance

select partman.run_maintenance(
p_parent_table := 'public.users',
p_debug := TRUE);

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store