Time-based table partitioning using pg partman

  • Time-based table partitioning
  • Serial id based partitioning. Follow below sections to do time-based table partitioning. I will be using the postgres database to describe the example

Installing and setting up pg_partman

  • run following commands to install pg_partman
git clone https://github.com/keithf4/pg_partman
cd pg_partman
make install
make NO_BGW=1 install
  • create schema and extension for partman by logging into the postgreSQL database. I am using PostgreSQL interactive terminal to run following commands
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

  • p_parent_table is the name of the parent table, here users table is under the public schema. This argument is of type text.
  • p_control is the name of the column in the parent table. Partitioning is based on this column. As partman provides time/serial id based partitioning so data type of this column in the table must of time or integer type. This argument is of type text.
  • p_type is about the type of partitioning. Type of partitioning can of native or partman type. In this example, I have used the partman type. This argument is of type text.
  • p_interval is the time interval of each partition. In this example, I used ‘daily’ interval. so the child table will be created for each date. This argument is of type text.
  • p_automatic_maintenance is used to set the automatic maintenance when run_maintenance() is called. Maintenance is about the creation of new child tables and deleting older child tables based on retention period config. This argument is of type text.
  • p_debug turns on the printing the additional debugging information in the console. This argument is of type boolean

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);

--

--

--

Product Engineer at Gojek.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

The Industry of the Future

OOP: Classes and instance atributes

October Newsletter | Incorporating Web Monetization to LikeCoin

How to make a cryptocurrency Telegram bot with Rust and Teloxide

Cracking the DBMS Round

How to write my first Java Program

Azure Functions using Visual Studio

How to test Github Actions locally

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
Asheet Bhaskar

Asheet Bhaskar

Product Engineer at Gojek.

More from Medium

Understanding Frame Rates Vs Shutter Speed

Installing SQL Server and Microsoft SQL Server Management Studio on Windows Server 2019 VM.

Running containers in Openshift with custom SELinux type

How to fix “VBoxManage: error: Code E_ACCESSDENIED” or “Error checking TLS connection”