Implementing Transparent Table Partitioning in PostgreSQL: Strategies, Code Samples, and Performance Gains

Introduction

Database administrators must ensure both stability and performance of PostgreSQL services; when a table grows large, partitioning becomes an effective solution that is transparent to applications.

What Is Table Partitioning?

Partitioning splits a large table into smaller child tables based on a partition key, physically separating data files while keeping the parent table interface unchanged for most queries.

Why Use Partitioning?

Higher availability – isolates failures to individual partitions.

Improved query performance – reduces the amount of data scanned.

Lower maintenance cost – enables easy archiving via ATTACH/DETACH.

PostgreSQL Partitioning Methods

1. Inheritance‑Based Partitioning

Implemented with triggers or rules; the example creates a parent table test_trigger_part and automatically generates daily child tables.

postgres=# create table test_trigger_part(id serial, flag text, location text, create_time timestamp with time zone);  -- create parent table
DO $$
DECLARE base text; sqlstring text; i int;
BEGIN
base := 'create table test_trigger_part_%s(check(create_time >= ''%s'' and create_time < ''%s'')) inherits (test_trigger_part)';
FOR i IN 0..9 LOOP
sqlstring := format(base, to_char('2021-05-01'::date + (i || ' day')::interval, 'YYYYMMDD'), '2021-05-01'::date + (i || ' day')::interval, '2021-05-01'::date + (i + 1 || ' day')::interval);
EXECUTE sqlstring;
END LOOP;
END $$ language plpgsql;

2. Declarative Partitioning

Supported from PostgreSQL 10 (list, range) and PostgreSQL 11 (hash). It simplifies management and offers better performance.

2.1 List Partition

create table test_list(id serial, flag text, location text, create_time timestamptz) partition by list(flag);
DO $$
DECLARE base text; sqlstring text; i int;
BEGIN
base := 'create table test_list_%s partition of test_list for values in (''%s'')';
FOR i IN 0..9 LOOP
sqlstring := format(base, 'flag' || i, 'flag' || i);
EXECUTE sqlstring;
END LOOP;
END $$ language plpgsql;

2.2 Range Partition

create table test_range(id serial, flag text, location text, create_time timestamptz) partition by range(create_time);
DO $$
DECLARE base text; sqlstring text; i int;
BEGIN
base := 'create table test_range_%s partition of test_range for values from (''%s'') to (''%s'')';
FOR i IN 0..9 LOOP
sqlstring := format(base, to_char('2021-05-01'::timestamptz + (i || ' day')::interval, 'YYYYMMDD'), '2021-05-01'::timestamptz + (i || ' day')::interval, '2021-05-01'::timestamptz + (i + 1 || ' day')::interval);
EXECUTE sqlstring;
END LOOP;
END $$ language plpgsql;

2.3 Hash Partition

create table test_hash(id serial, flag text, location text, create_time timestamptz) partition by hash(location);
DO $$
DECLARE base text; sqlstring text; i int;
BEGIN
base := 'create table test_hash_%s partition of test_hash for values with (modulus 10, remainder %s)';
FOR i IN 0..9 LOOP
sqlstring := format(base, i, i);
EXECUTE sqlstring;
END LOOP;
END $$ language plpgsql;

Maintenance Commands

Inheritance‑based partitions use ALTER TABLE ... INHERIT/NO INHERIT . Declarative partitions use ALTER TABLE ... ATTACH/DETACH PARTITION .

ALTER TABLE child_table INHERIT parent_table;
ALTER TABLE child_table NO INHERIT parent_table;
ALTER TABLE partition_name ATTACH PARTITION parent_table FOR VALUES ...;
ALTER TABLE partition_name DETACH PARTITION;

Version Differences

PostgreSQL has supported partitioning since 8.1; declarative partitioning arrived in 10, with major performance improvements in 11, 12, and logical replication support in 13.

Qunar Practical Experience

Qunar migrated large tables (e.g., hotel data, monitoring data) to partitioned tables, achieving up to 20× speed‑up for simple count queries. Example query plans before and after partitioning are shown, highlighting reduced execution time.

-- Before partitioning (≈28 s)
EXPLAIN ANALYZE SELECT count(1) FROM xds_entity_index_old;
-- After partitioning (≈1.4 s)
EXPLAIN ANALYZE SELECT count(1) FROM xds_entity_index;

References

PostgreSQL official documentation, feature matrix, blog posts, and community articles are listed.

Recruitment Notice

Qunar is hiring for various technical positions; interested candidates are encouraged to contact the company.