Declarative table partitioning in PostgreSQL 16, specifically RANGE partitioning on a time-series column, accelerates historical queries by enabling efficient partition pruning, allowing the database to scan only relevant data segments. This significantly reduces I/O and improves query performance for time-bound data.
Follow these steps to implement and leverage declarative partitioning:
1. Define the Parent Table: Create the main table with PARTITION BY RANGE on your timestamp column. This table will not store data directly; it acts as a logical container. The partition key column must be part of the primary key.
```sql
CREATE TABLE sensor_readings (
reading_id BIGSERIAL NOT NULL,
device_id INT NOT NULL,
reading_time TIMESTAMPTZ NOT NULL,
temperature NUMERIC(5,2),
humidity NUMERIC(5,2),
PRIMARY KEY (reading_id, reading_time)
) PARTITION BY RANGE (reading_time);
```
2. Create Child Partitions: Define individual partitions for specific time ranges. Monthly or yearly partitions are common for time-series data. Ensure ranges are non-overlapping.
```sql
CREATE TABLE sensor_readings_2023_01 PARTITION OF sensor_readings
FOR VALUES FROM ('2023-01-01 00:00:00+00') TO ('2023-02-01 00:00:00+00');
CREATE TABLE sensor_readings_2023_02 PARTITION OF sensor_readings
FOR VALUES FROM ('2023-02-01 00:00:00+00') TO ('2023-03-01 00:00:00+00');
```
3. Index Child Partitions: Create indexes on the child tables. The partition key column (reading_time) should always be indexed, often alongside other frequently queried columns like device_id. The primary key on the parent table automatically creates unique indexes on child tables, but additional indexes for common query patterns are beneficial.
```sql
CREATE INDEX idx_sensor_readings_2023_01_time_device ON sensor_readings_2023_01 (reading_time, device_id);
CREATE INDEX idx_sensor_readings_2023_02_time_device ON sensor_readings_2023_02 (reading_time, device_id);
```
4. Manage New Partitions: Proactively create new partitions before data arrives for that period. Automate this process using tools like pg_partman or custom scripts. A DEFAULT partition can catch data outside defined ranges, but its heavy use can hinder pruning.
```sql
-- Example for a new month
CREATE TABLE sensor_readings_2024_01 PARTITION OF sensor_readings
FOR VALUES FROM ('2024-01-01 00:00:00+00') TO ('2024-02-01 00:00:00+00');
CREATE INDEX idx_sensor_readings_2024_01_time_device ON sensor_readings_2024_01 (reading_time, device_id);
```
5. Verify Partition Pruning: Use EXPLAIN (ANALYZE, VERBOSE, BUFFERS) to confirm PostgreSQL scans only necessary partitions. Look for "Partition Pruning: ... eliminated ..." in the output.
```sql
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT device_id, AVG(temperature)
FROM sensor_readings
WHERE reading_time >= '2023-01-15 00:00:00+00' AND reading_time < '2023-01-16 00:00:00+00'
AND device_id = 123
GROUP BY device_id;
```
The EXPLAIN output should show only sensor_readings_2023_01 being scanned, demonstrating effective pruning.
Consider this comparison for clarity:
| Feature | Without Partitioning | With Declarative Partitioning |
| :---------------------- | :---------------------------- | :---------------------------- |
| Data Scanned | Entire table | Only relevant partitions |
| Index Size | Single, large index | Smaller, per-partition indexes|
| VACUUM/ANALYZE Cost | High for large tables | Lower, per-partition |
| Data Retention | Manual DELETE (slow) | DETACH / DROP partition (fast)|