pg_fkpart

Overview

Version

Build

You can build the DEB packages for pg_fkpart using pig build:

pig build pkg pg_fkpart         # build DEB packages

Install

You can install pg_fkpart directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_fkpart;          # Install for current active PG version

pig ext install -y pg_fkpart -v 18  # PG 18
pig ext install -y pg_fkpart -v 17  # PG 17
pig ext install -y pg_fkpart -v 16  # PG 16
pig ext install -y pg_fkpart -v 15  # PG 15
pig ext install -y pg_fkpart -v 14  # PG 14

dnf install -y pg_fkpart_18       # PG 18
dnf install -y pg_fkpart_17       # PG 17
dnf install -y pg_fkpart_16       # PG 16
dnf install -y pg_fkpart_15       # PG 15
dnf install -y pg_fkpart_14       # PG 14

apt install -y postgresql-18-pg-fkpart   # PG 18
apt install -y postgresql-17-pg-fkpart   # PG 17
apt install -y postgresql-16-pg-fkpart   # PG 16
apt install -y postgresql-15-pg-fkpart   # PG 15
apt install -y postgresql-14-pg-fkpart   # PG 14

Create Extension:

CREATE EXTENSION pg_fkpart;

Usage

pg_fkpart: Table partitioning by foreign key utility

pg_fkpart enables partitioning PostgreSQL tables based on a foreign key relationship. All functions reside in the pgfkpart schema.

Create the Extension

CREATE EXTENSION pg_fkpart;

Partition a Table by Foreign Key

SELECT pgfkpart.partition_with_fk(
    'public',           -- schema of the table to partition
    'my_table',         -- table to partition
    'public',           -- schema of the foreign key table
    'fk_table',         -- foreign key table
    true                -- support SQL RETURNING
);

Unpartition a Table

SELECT pgfkpart.unpartition_with_fk('public', 'my_table');

Index Management

Propagate indexes and constraints from parent to all child tables:

SELECT pgfkpart.dispatch_index('public', 'my_table');

Drop an index across all child tables:

SELECT pgfkpart.drop_index('public', 'my_table', 'my_index_name');

Drop a unique constraint across all child tables:

SELECT pgfkpart.drop_unique_constraint('public', 'my_table', 'my_constraint_name');

Partition Tracking

The extension maintains a pgfkpart.partition table that records all partitioned tables, including schema, table name, foreign key column, and foreign table information.