Automatically add partitions to PostgreSQL tables


Author: Letu
Pseudonym: lottu
Personal profile: at present, I am engaged in the design and development of database architecture in a game company, and have led the company to go to “O” related work

PostgreSQL introduces the feature of “partition” table, which liberates the tedious and inefficient implementation of partition table by using “table inheritance” + “trigger”. When adding partitions, SQL is executed manually.
Purpose of demonstration: use Python to automatically add partitions to PostgreSQL tables.
Python version: Python 3+
pip3 install psycopg2

1、 Configure data sources

database.ini Files: recording database connection parameters



2、 Config script File: the following config() function reads database.ini File and returns the connection parameters. The config() function is located in the In the file

from configparser import ConfigParser
def config(section ,filename='database.ini'):
    # create a parser
    parser = ConfigParser()
    # read config file
    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))
    return db

3、 Create subtable script

pg_ add_ partition_ File: where create_ The table function is to create a sub table SQL. Where parameters

Parameter name meaning
db Point to database
table Main table
sub_table The name of the child table you are about to create
start_date Start value of range boundary
end_date End value of range boundary

import psycopg2
from config import config

#example: create table tbl_game_android_step_log_2021_07 PARTITION OF tbl_game_android_step_log FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
def create_table(db, table, sub_table, start_date, end_date):
    """ create subtable in the PostgreSQL database"""
    command = "create table {0} PARTITION OF {1} FOR VALUES FROM ('{2[0]}') TO ('{2[1]}');".format(sub_table, table, (start_date, end_date)) 
    conn = None
        # read the connection parameters
        params = config(section = db)
        # connect to the PostgreSQL server
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        # create table one by one
        # close communication with the PostgreSQL database server
        # commit the changes
    except (Exception, psycopg2.DatabaseError) as error:
        if conn is not None:

4、 Executive document : main file; generate partition table by executing main.

import datetime
from datetime import date
from dateutil.relativedelta import *
from pg_add_partition_table import create_table

#Get the 1st day of the next month
def get_next_month_first_day(d):
    return date(d.year + (d.month == 12), d.month == 12 or d.month + 1 , 1)
def create_sub_table(db, table):
    # Get current date
    d1 =
    # Get next month's date
    d2 = d1 + relativedelta(months=+1)
    # Get the 1st day of the next month;As the starting value of the partitioned table
    start_date = get_next_month_first_day(d1)
    # Gets the 1st of the next two months as the end value of the partitioned table
    end_date = get_next_month_first_day(d2)
    # get sub table name
    getmonth =  datetime.datetime.strftime(d2, '%Y_%m')
    sub_table = table + '_' + getmonth
    create_table(db, table, sub_table, start_date, end_date)
if __name__ == '__main__':
    create_sub_table('test', 'tbl_game_android_step_log');

The above example is a separate TABLE tbl_ game_ android_ step_ Log; create partition; if more than one table; use for statement to process
Multi table operation

for table in ['tbl_game_android_step_log', 'tbl_game_android_game_log','tbl_game_android_pay_log']:
        create_sub_table('test', table);

Before the presentation:

adsas=> select * from  pg_partition_tree('tbl_game_android_step_log');
               relid               |        parentrelid        | isleaf | level 
 tbl_game_android_step_log         |                           | f      |     0
 tbl_game_android_step_log_2020_12 | tbl_game_android_step_log | t      |     1
(2 rows)

After the presentation:

adsas=> select * from  pg_partition_tree('tbl_game_android_step_log');
               relid               |        parentrelid        | isleaf | level 
 tbl_game_android_step_log         |                           | f      |     0
 tbl_game_android_step_log_2020_12 | tbl_game_android_step_log | t      |     1
 tbl_game_android_step_log_2021_01 | tbl_game_android_step_log | t      |     1

Partition key: RANGE (visit_time)
Partitions: tbl_game_android_step_log_2020_12 FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00'),
            tbl_game_android_step_log_2021_01 FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2021-02-01 00:00:00')

5、 Join timing task

For more wonderful content, please pay attention to the following platforms and websites:

Official account of China PostgreSQL branch (technical articles and technical activities):
PostgreSQL branch of Open Source Software Alliance

China PostgreSQL technical Q & a community:

Official website of China PostgreSQL branch:

Recommended Today

Introduce regular expressions in ruby in detail

A regular expression is a special sequence of characters that matches or finds other strings or collections of strings by using patterns with special syntax. grammar A regular expression is literally a pattern between slashes or any separator after% R, as follows: ? 1 2 3 4 5 6 7 8 9 10 11 12 […]