Detailed explanation of PostgreSQL table partitioning and inheritance

PostgreSQL: Table partitioning and inheritance

Introduction: When data torrent meets the wisdom of structured storage

Driven by the wave of digitalization, the total global data volume is growing at a rate that doubles every two years. Faced with such data torrents, traditional relational database management systems (RDBMSs) are facing unprecedented challenges. According to statistics from DB-Engines, PostgreSQL has become the fourth largest popular database system in the world in 2023, and its powerful scalability and flexibility make it one of the first choice for processing massive data.

In this context,Table partition（Table Partitioning)andTable inheritance（Table Inheritance) As the core technical means of PostgreSQL to deal with big data processing, it is playing an increasingly important role. Imagine a scenario where an e-commerce platform adds a million-level record every day, and it will reach an astonishing 1 billion row scale in three years. If you use traditional single table storage at this time, even with index support, a simple range query may require several minutes to respond. This is exactlyTable partitioning technologyThe moment of showing off your skills - by physically dividing the data into different subtables, the query performance can be improved by dozens of times.

The partition evolution history of PostgreSQL itself is a history of technological evolution: simulate partitioning from early inheritance tables (Version 8.1), to the native declarative partition (Version 10), and then to partition pruning optimization (Version 11) and hash partition support (Version 14), each step embodies the community's deep understanding of big data processing. andTable inheritance mechanismAs a unique object relationship feature of PostgreSQL, it not only provides underlying support for partition implementation, but also opens up new possibilities for more complex data model design.

This article will conduct in-depth analysisPostgreSQLThe implementation mechanism of table partitioning and inheritance, combined with the latest version (Version 16) feature evolution, through a large number of production-level code examples, it reveals how to design efficient partitioning schemes, optimize partition query performance, and cleverly use inheritance features to build flexible data models. Whether you are designingTBThe architect of a data warehouse or an optimized transaction system of tens of millions of yuanDBA, this article will provide you with solutions that can be implemented directly.

1. Design principles of partition tables: the cornerstone of building an efficient data architecture

1.1 The Golden Triangle of Partition Strategy

When designing partition tables, you must balanceQuery mode、Data distributionandMaintenance costThese three key dimensions. According to Google's SRE experience, excellent partition design should meet:

• Query locality: 80% of queries should hit a single partition
• Equalized distribution: The data volume difference between each partition shall not exceed 20%
• Lifecycle Management: Old partition archives do not affect active data

-- Typical time range partition design example
CREATE TABLE sensor_data (
    device_id BIGINT NOT NULL,
    record_time TIMESTAMPTZ NOT NULL,
    temperature NUMERIC(5,2),
    humidity NUMERIC(5,2)
PARTITION BY RANGE (record_time);
CREATE TABLE sensor_data_2023 PARTITION OF sensor_data
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sensor_data_2024 PARTITION OF sensor_data
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

1.2 Art of Partition Key Selection

Evaluation is required when selecting partition keys:

• Cardinality distribution: Avoid creating too many small partitions (>1000 partitions will degrade performance)
• Query predicate: The most commonly used field in the WHERE clause
• Data age: Natural attenuation characteristics of the time field

-- Example of using composite partition keys（PG14+）
CREATE TABLE customer_orders (
    region VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    amount NUMERIC(10,2)
PARTITION BY LIST (region), RANGE (order_date);
-- Create a subpartition
CREATE TABLE orders_asia_2023 PARTITION OF customer_orders
    FOR VALUES IN ('asia') 
    PARTITION BY RANGE (order_date);

1.3 Best practices for partition maintenance

• Automatic partition creation: Use trigger or pg_partman extension
• Partition Archive:useALTER TABLE ... DETACH PARTITION
• Statistical Information Management: Configure a separate configurationautovacuumparameter

-- Partition maintenance operation example
-- Archive old partition
ALTER TABLE sensor_data DETACH PARTITION sensor_data_2022;
-- Merge partitions（PG12+）
ALTER TABLE sensor_data 
    MERGE PARTITIONS sensor_data_202301, sensor_data_202302 
    INTO sensor_data_2023_q1;

2. Range partition, list partition and hash partition: The power of the trident

2.1 Range Partition: The King of Time Series Data

Range partition(Range Partitioning) is particularly suitable for data types with natural order, such as timestamps, self-increment IDs, etc. In IoT scenarios, the design of partition by hour can improve query performance by 40 times.

-- Automatic partition creation every hour（usepg_partman）
SELECT partman.create_parent(
    'public.sensor_logs',
    'log_time',
    'native',
    'hourly',
    p_premake := 24
);

2.2 List partition: elegant segmentation of discrete values

List partition(List Partitioning) is suitable for data with clear classification, such as region, status code, etc. A certain e-commerce platform reduces the regional report query speed from 15 seconds to 0.3 seconds through the region list partition.

-- Multi-level list partition design
CREATE TABLE sales (
    region VARCHAR(20),
    country VARCHAR(20),
    sale_date DATE,
    amount NUMERIC
) PARTITION BY LIST (region);
CREATE TABLE sales_europe PARTITION OF sales
    FOR VALUES IN ('western_europe', 'eastern_europe')
    PARTITION BY LIST (country);

2.3 Hash partitioning: The art of uniform distribution

Hash partition(Hash Partitioning) was introduced from PG11 and the data is evenly distributed to multiple partitions through a hashing algorithm. A social platform uses hash partitions to spread user tables into 128 partitions, and the concurrent query throughput is increased by 8 times.

-- Hash partition example（PG14+Supports custom modulus）
CREATE TABLE user_sessions (
    user_id BIGINT,
    session_data JSONB
) PARTITION BY HASH (user_id) 
WITH (MODULUS 4, REMAINDER 0); 
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

3. Query optimization of partition tables: keys that break through performance bottlenecks

3.1 In-depth analysis of the execution plan

passEXPLAIN (ANALYZE, BUFFERS)Observe whether the query is triggeredPartition trimming(Partition Pruning). The optimizer will automatically trim in the following scenarios:

• Static conditions：WHERE partition_key = constant
• Dynamic Conditions：WHERE partition_key = $1(Requires to be turned onenable_partition_pruning）
• Range query：BETWEENOperator matching time range

-- View partition trimming effect（PG16Newpartition pruninghint）
EXPLAIN (ANALYZE)
SELECT * FROM sensor_data 
WHERE record_time BETWEEN '2024-03-01' AND '2024-03-02';
-- Key snippets of output results
Append  (cost=0.00..48.95 rows=12 width=48)
  ->  Seq Scan on sensor_data_20240301  (cost=0.00..24.12 rows=6 width=48)
  ->  Seq Scan on sensor_data_20240302  (cost=0.00..24.12 rows=6 width=48)

3.2 Parallel query acceleration strategy

By adjustingmax_parallel_workers_per_gatherParameter implementationParallel scanning across partitions. On a 32-core server, parallel query speeds for 100 partitions can reach 15 times that of a single thread.

-- Set the parallelism（PG16Support partition-level parallelism control）
ALTER TABLE sensor_data 
    SET (parallel_workers = 8);
-- View the parallel execution plan
EXPLAIN (ANALYZE)
SELECT AVG(temperature) FROM sensor_data 
WHERE record_time > now() - interval '1 week';

3.3 Index Strategy Essence

useHierarchical index architecture：

• Global index: Create indexes in parent table (auto propagate to all partitions)
• Local index: Create a dedicated index in a specific partition
• Conditional index: Partial index for hotspot partitions

-- Global index example（PG11+Automatically create subpartition indexes）
CREATE INDEX idx_record_time ON sensor_data (record_time);
-- Partition local index optimization
CREATE INDEX idx_asia_2024_sales ON sales_asia_2024 (product_id) 
WHERE quantity > 1000;

3.4 Statistical information maintenance

passpg_stat_user_tablesMonitor partition statistics and configure independent statistical strategies for large partitions:

-- Configure partition automatic cleaning parameters
ALTER TABLE sensor_data_2024 SET (
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_scale_factor = 0.02
);
-- Manually collect statistics（PG14+Supports parallel analysis of subpartitions）
ANALYZE VERBOSE sensor_data;

3.5 Common Performance Traps

• Cross-partition aggregation：SUM()Operations may trigger full table scans
• Foreign key constraints: The parent table cannot define a cross-partition foreign key (need to be set separately in the child partition)
• JOIN order: When JOIN of large tables, you need to ensure that the partition table is used as the driver table.

4. Table inheritance and polymorphic association: an object relationship model beyond partitions

4.1 Analysis of the principle of inheritance mechanism

PostgreSQLTable inheritance(Table Inheritance) Implementation using object relationship model:

• Father and son table structure: The child table automatically contains all columns of the parent table
• Query spread: Parent table query automatically contains all child table data
• Constraint overlay：CHECKConstraints form logical filtering conditions

-- Create an inheritance hierarchy（Classic Case：Device type inheritance）
CREATE TABLE devices (
    id SERIAL PRIMARY KEY,
    name TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE sensors (
    accuracy DECIMAL(5,2)
) INHERITS (devices);
CREATE TABLE actuators (
    max_force NUMERIC
) INHERITS (devices);

4.2 Polymorphic association implementation solution

Implemented by inheritancePolymorphic association(Polymorphic Associations), solve the problem of entity type extension:

-- Event log polymorphic model
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    target_type VARCHAR(32),
    target_id BIGINT,
    event_time TIMESTAMPTZ
);
CREATE TABLE temperature_events (
    sensor_id BIGINT REFERENCES sensors(id),
    temperature NUMERIC(5,2)
) INHERITS (events);
-- Query all device events（Automatically include subtable data）
SELECT e.* FROM events e WHERE target_type = 'sensor';

4.3 Comparison of inheritance and partition

4.4 Advanced application scenarios

Versioned data storage: Implement data version snapshots through inheritance

CREATE TABLE contracts_v1 (LIKE contracts);
CREATE TABLE contracts_v2 (payment_terms TEXT) INHERITS (contracts_v1);

Multi-tenant isolation: Each tenant subtable independent permission control

CREATE TABLE tenant_a.orders () INHERITS ();
GRANT SELECT ON tenant_a.orders TO role_a;

Real-time archive system: Use rules system to realize automatic data migration

CREATE RULE archive_orders AS 
ON INSERT TO orders WHERE order_date < '2020-01-01'
DO INSTEAD INSERT INTO orders_archive VALUES (NEW.*);

4.5 Inheritance query optimization

ONLY keywords: Restrict query to scan only specified tables

SELECT * FROM ONLY devices; -- Not included in subtable data

Constraint exclusion:passconstraint_exclusionParameter control

SET constraint_exclusion = on;
EXPLAIN SELECT * FROM devices WHERE id BETWEEN 1000 AND 2000;

5. Frontier Development: PG16 Partition Enhanced Features

5.1 Asynchronous partition pruning

PG16 introduces background work process implementationAsynchronous partition pruning, stripping the pruning time from the query main path:

-- Enable asynchronous pruning（Added parameters）
SET enable_async_partition_pruning = on;
-- Monitor the trimming progress
SELECT * FROM pg_stat_async_partition_pruning;

5.2 Partition-level permission control

Implement fine-grained permission management:

GRANT SELECT ON TABLE sales_2024 TO analyst_role;
REVOKE DELETE ON TABLE sales_archive FROM api_user;

5.3 Hybrid partitioning strategy

supportMulti-level combined partition(For example: LIST first and then HASH):

CREATE TABLE genomic_data (
    lab_id INT,
    sample_date DATE,
    dna_data BYTEA
PARTITION BY LIST (lab_id), HASH (sample_date);
CREATE TABLE lab_nyc PARTITION OF genomic_data
    FOR VALUES IN (1)
    PARTITION BY HASH (sample_date);

References

1. PostgreSQL 16 Official Documentation - Table Partitioning
2. 《PostgreSQL 14 High Performance》Chapter 9 - Partitioning Strategies
3. AWS Technical Whitepaper - Best Practices for Partitioning on Aurora PostgreSQL
4. Microsoft Azure Architecture Center - Designing Scalable Partitioning Schemes
5. Uber Engineering Blog - PostgreSQL Partitioning at Scale
6. Citus Data - Sharding vs Partitioning Benchmark 2023
7. PostgreSQL pg_partman Extension - GitHub Repository

This is the end of this article about detailed explanation of PostgreSQL table partitioning and inheritance. For more related PostgreSQL table partitioning and inheritance content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!