As a table keeps growing, the amount of data eventually reaches a point where indexes become too large to remain efficient. Once that happens, common operations such as querying, updating, and deleting records can slow down noticeably.
One way to deal with this is to design the large table as a partitioned table. A partitioned table is associated with multiple underlying partitions, and inserted rows are routed into different partitions according to predefined rules. Queries can be executed against a specific partition, or directly against the parent partitioned table.
When PostgreSQL’s built-in partitioning rules are used—for example, partitions divided by time—queries with matching time conditions can avoid scanning unrelated partitions. PostgreSQL only includes the partitions that may contain matching rows, while partitions outside the requested range are skipped. This behavior is known as partition pruning, and it can significantly improve query performance.
PostgreSQL supports two common approaches to partitioning tables: built-in declarative partitioning and inheritance-based partitioning. Declarative partitioning is provided by PostgreSQL itself. It automatically routes data into the correct partition according to the partition definition and benefits from PostgreSQL’s built-in performance optimizations. Inheritance-based partitioning, on the other hand, relies on table inheritance and user-defined triggers. It requires more manual work, but gives more flexibility for customized routing logic.
After a partitioned table and its partitions have been created, inserted data is stored according to the routing rules. For example, logs inserted into a partitioned table named t_logs may be automatically stored in partitions such as t_logs_y2019m02, t_logs_y2019m03, and so on.
Partitioning also makes historical data cleanup and archiving much easier. Without partitioning, exporting old data or deleting records may require operating on millions of rows. With partitioning, the corresponding partition table can be exported directly or dropped as a whole.
Partitioning can greatly improve query speed, but it does not make unrestricted full-table scans disappear. If a query has no range condition and must scan every partition, the result is not very different from scanning one very large table. To actually benefit from partitioning, queries should include reasonable filter conditions that narrow the search scope.
Declarative partitioning
Range partitioning
Range partitioning is suitable for columns that grow linearly, such as time fields or continuously increasing numeric values.
-- 创建分区表
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
-- 创建分区
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
-- 创建索引
CREATE INDEX ON measurement (logdate);
-- 插入数据到分区
INSERT into measurement (city_id, logdate, peaktemp, unitsales) values (230000, '2006-02-01', '23', 92374);
INSERT into measurement (city_id, logdate, peaktemp, unitsales) values (230000, '2006-03-06', '36', 17281);
List partitioning
List partitioning is useful when data needs to be separated by specific values in a column. For example, messages from different systems can be stored in different partitions according to systemid.
-- 加载uuid扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 创建表
CREATE TABLE message (
uuid uuid DEFAULT uuid_generate_v4 (),
nodeid TEXT NOT NULL,
systemid TEXT NOT NULL,
time TEXT NOT NULL,
jdoc jsonb NOT NULL
) partition by list (systemid);
CREATE TABLE message_109001 PARTITION OF message FOR VALUES in ('109001');
CREATE TABLE message_109002 PARTITION OF message FOR VALUES in ('109002');
CREATE TABLE message_109003 PARTITION OF message FOR VALUES in ('109003');
CREATE TABLE message_109004 PARTITION OF message FOR VALUES in ('109004');
CREATE TABLE message_109005 PARTITION OF message FOR VALUES in ('109005');
CREATE TABLE message_109006 PARTITION OF message FOR VALUES in ('109006');
CREATE INDEX ON message (nodeid);
CREATE INDEX ON message (systemid);
CREATE INDEX ON message (time);
-- 插入数据
INSERT into message (nodeid, systemid, time, jdoc) values ('111111', '109004', '2019-06-13 19:05:11', '{"hello": "AA系统"}');
INSERT into message (nodeid, systemid, time, jdoc) values ('222222', '109005', '2019-06-13 19:05:12', '{"hello": "BB系统"}');
INSERT into message (nodeid, systemid, time, jdoc) values ('333333', '109006', '2019-06-13 19:05:13', '{"hello": "CC系统"}');
-- 验证查询
EXPLAIN SELECT * FROM message WHERE systemid = '109005';
PostgreSQL’s built-in partitioning features are already enough for many scenarios. When the built-in model cannot fully match the business rules, partition routing can also be implemented manually with inheritance and triggers.
Inheritance-based partitioning
Inheritance-based partitioning provides more room for custom behavior. The parent table is created first, child tables inherit from it, and constraints define the data range each child table should contain. A trigger then decides where each inserted row should go.
-- SQL 示例
-- 使用继承实现表分区
CREATE TABLE measurement2 (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
-- 创建子表
CREATE TABLE measurement2_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement2);
CREATE TABLE measurement2_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement2);
-- 创建索引
CREATE INDEX measurement2_y2006m02_logdate ON measurement2_y2006m02 (logdate);
CREATE INDEX measurement2_y2006m03_logdate ON measurement2_y2006m03 (logdate);
-- 定义触发器函数
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2006-02-01' AND
NEW.logdate < DATE '2006-03-01' ) THEN
INSERT INTO measurement2_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
NEW.logdate < DATE '2006-04-01' ) THEN
INSERT INTO measurement2_y2006m03 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
-- 创建一个调用该触发器函数的触发器
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement2
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
-- 插入数据到分区
INSERT into measurement2 (city_id, logdate, peaktemp, unitsales) values (100000, '2006-02-07', '23', 1111);
INSERT into measurement2 (city_id, logdate, peaktemp, unitsales) values (100000, '2006-02-07', '21', 0000);
INSERT into measurement2 (city_id, logdate, peaktemp, unitsales) values (100000, '2006-03-07', '36', 2222);
INSERT into measurement2 (city_id, logdate, peaktemp, unitsales) values (100000, '2018-11-11', '36', 4444);
select * from measurement2;
Checking how partitions are searched
Partition pruning can optimize queries on declarative partitioned tables.
The enable_partition_pruning setting controls whether PostgreSQL enables partition pruning for declarative partitioning. When it is disabled, a query may involve all partitions. When it is enabled, PostgreSQL can intelligently remove irrelevant partitions from the query plan.
The difference can be observed by comparing the query plan generated by EXPLAIN.
SET enable_partition_pruning = on; -- off
EXPLAIN SELECT count(*) FROM measurement WHERE logdate > DATE '2006-03-15';
Generating partition SQL scripts
File name: generate_partition_sql_datetime.js
const moment = require("moment")
if (process.argv.length != 5) {
console.log("Usage: node script.js table_name 2019(start) 5(years)")
return;
}
TABLE_NAME = process.argv[2]
START_Y = process.argv[3]
TOTAL_Y = process.argv[4]
LOOP_COUNT = TOTAL_Y * 4
START_STRING = START_Y + "-01-01"
START_YMD = moment(START_STRING)
console.log("-- start --")
from = START_STRING
for (let i=0; i<LOOP_COUNT; i++) {
let tmp_date = START_YMD.add(3, 'months')
let tmp_date_string = tmp_date.format("YYYY-MM-DD")
let to = tmp_date.format("YYYY-MM-DD")
let from_Y = from.split("-")[0]
let from_M = from.split("-")[1]
console.log("CREATE TABLE " + TABLE_NAME + "_y" + from_Y + "m" + from_M + " PARTITION OF " + TABLE_NAME + " FOR VALUES FROM ('" + from + "') TO ('" + to + "');")
from = to
}
console.log("-- end --")
Usage and output:
# 首先安装依赖
npm install moment
This script generates partition creation SQL starting from 2019, using one partition per quarter, for a total duration of five years. It is intended for declarative partitioning.