Implementing PostgreSQL Range Partitioning for Large Time‑Series Tables

Desired Outcome

Divide a massive time‑series table into manageable monthly child tables so that queries filter on the timestamp column are automatically routed to the relevant partitions. This reduces scan size, speeds up vacuum and analyze, and makes archiving or deleting old data trivial.

Prerequisites

• PostgreSQL 10 or newer (declarative partitioning). Prefer 15+ for global indexes if you need unique constraints across partitions.
• Sufficient privileges: CREATE TABLE and ALTER TABLE on the parent, and DROP on child tables.
• Understanding of your time‑series granularity: choose monthly or weekly partitions based on query patterns and table size.
• Optional: a maintenance job that runs VACUUM ANALYZE on the parent to keep statistics up‑to‑date.

Focused Procedure

1. Create the parent table with a range partition key

   CREATE TABLE logs (
       id          BIGSERIAL PRIMARY KEY,
       log_ts      TIMESTAMPTZ NOT NULL,
       level       TEXT,
       message     TEXT
   ) PARTITION BY RANGE (log_ts);
   

   All columns are inherited by child tables. Indexes defined on the parent will be inherited automatically.

2. Define the first monthly partition

   CREATE TABLE logs_2026_01 PARTITION OF logs
       FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-02-01 00:00:00+00');
   

   Repeat for each month you need to pre‑populate. Use a naming convention that makes the range obvious.

3. Add an index on the partition key for faster pruning

   CREATE INDEX ON logs (log_ts);
   

   This index is inherited by all partitions. PostgreSQL 15+ can create a global index if you need a unique constraint across partitions.

4. Insert test data and verify routing

   INSERT INTO logs (log_ts, level, message)
   VALUES ('2026-01-15 12:34:56+00', 'INFO', 'Test log');
   
   SELECT * FROM logs WHERE log_ts = '2026-01-15 12:34:56+00';
   

   Run SELECT * FROM logs_2026_01 WHERE log_ts = '2026-01-15 12:34:56+00'; to confirm the row lives in the correct child table.

5. Check automatic partition pruning

   EXPLAIN (ANALYZE, BUFFERS) 
   SELECT * FROM logs WHERE log_ts BETWEEN '2026-01-01' AND '2026-01-31';
   

   The planner should list only logs_2026_01 in the plan. If you see all partitions, the WHERE clause is missing the partition key or the key is not indexed.

6. Adding a new partition for an upcoming month

   CREATE TABLE logs_2026_02 PARTITION OF logs
       FOR VALUES FROM ('2026-02-01 00:00:00+00') TO ('2026-03-01 00:00:00+00');
   

   Always ensure the range does not overlap existing partitions and that the name is unique.

7. Removing old data – drop or detach

   • Drop – permanently delete the partition and its data:

     DROP TABLE logs_2025_12;
     

   • Detach – keep the table for later use but remove it from the partition hierarchy:

     ALTER TABLE logs DETACH PARTITION logs_2025_12;
     

Expected Checks

• Verify child table existence:

  SELECT relname FROM pg_class WHERE relname LIKE 'logs_%' AND relkind='r';
  

• Count rows before and after drop/detach:

  SELECT COUNT(*) FROM logs_2025_12;
  SELECT COUNT(*) FROM logs;
  

• Monitor I/O per partition:

  SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname LIKE 'logs_%';
  

• Confirm partition pruning with EXPLAIN (ANALYZE, BUFFERS) on a query that spans multiple months.

Recovery Options

• If a partition was dropped accidentally, restore it from backup or recreate it and re‑insert the data if you have a dump.
• If you need to re‑partition the table (e.g., change granularity), create a new parent table, copy data, and drop the old one. Use ALTER TABLE ... ATTACH PARTITION to move existing child tables into the new structure.
• For unique constraints that span partitions, PostgreSQL 15+ offers global indexes. If you are on an older version, enforce uniqueness by adding a trigger that checks across all partitions or by using a distributed unique key.

Limitations & Practical Tips

• Foreign key constraints cannot reference a partitioned table that spans multiple child tables; they must point to a single partition or be omitted.
• Triggers defined on the parent do not fire automatically on partitions; create them on each child if needed.
• Too many partitions (e.g., daily for a multi‑year dataset) can degrade planner performance; test with EXPLAIN ANALYZE before scaling.
• Indexes on the parent are inherited, but unique constraints require per‑partition indexes unless using PostgreSQL 15+ global indexes.
• Always test partition creation on a staging environment before applying to production, especially when adding overlapping ranges.

Conclusion

Declarative range partitioning in PostgreSQL gives you a clean, efficient way to manage large time‑series tables. By partitioning on the timestamp column, you gain automatic pruning, easier maintenance, and simple data lifecycle controls. Follow the steps above, verify with EXPLAIN and statistics queries, and choose a granularity that balances performance with administrative overhead.