---
title: "Handling Very Large Tables in Postgres Using Partitioning"
description: "Very large Postgres tables containing application events or log-like data can degrade query performance, slow index updates, and make vacuum disproportionately long. The article explains table partitioning through inheritance, using a master table, child partitions, CHECK constraints, and a trigger that routes inserts according to created_at; for an events workload with 10 million daily inserts and short-lived data, it uses daily partitions. It then introduces pg_partman, whose create_parent command configures time-based partitioning and whose run_maintenance function creates new partitions and removes old ones according to retention settings. Partitioning can improve performance and simplify deletion of expired data, but it should follow indexing and usage analysis, especially when queries cover a small time range and older data has little value."
---

# Handling Very Large Tables in Postgres Using Partitioning

[Heroku](https://yomu.fyi/company/heroku) · Rimas Silkaitis · Sep 13, 2016

**Type:** Tutorial

## Summary

Very large Postgres tables containing application events or log-like data can degrade query performance, slow index updates, and make vacuum disproportionately long. The article explains table partitioning through inheritance, using a master table, child partitions, CHECK constraints, and a trigger that routes inserts according to created\_at; for an events workload with 10 million daily inserts and short-lived data, it uses daily partitions. It then introduces pg\_partman, whose create\_parent command configures time-based partitioning and whose run\_maintenance function creates new partitions and removes old ones according to retention settings. Partitioning can improve performance and simplify deletion of expired data, but it should follow indexing and usage analysis, especially when queries cover a small time range and older data has little value.

## Context

Tables that grow to hundreds of gigabytes or terabytes can experience degraded query performance, slower index updates, and lengthy vacuum operations. The described events data is queried for only a few days, receives 10 million inserts per day, and has little value after a short retention period.

## Approach / What changed

The setup uses Postgres table inheritance, daily child partitions keyed by created\_at, CHECK constraints, and an insert trigger for manual routing. It then replaces that operationally fragile process with pg\_partman, using create\_parent for time-based partitioning and run\_maintenance on a regular schedule to create and remove partitions according to retention settings.

## Takeaways

- Postgres queries against a parent table include its descendant tables by default, while individual child partitions can also be queried directly.
- Manual partitioning requires maintaining child-table CHECK constraints, insert-routing triggers, future partitions, and procedures for removing old data.
- pg\_partman can configure daily time-based partitions with create\_parent and automate partition creation and retention cleanup through run\_maintenance.

**Tags:** [Databases](https://yomu.fyi/topic/databases), [pg\_partman](https://yomu.fyi/topic/pg-partman), [Postgres](https://yomu.fyi/topic/postgres)

- Source: [Heroku](https://www.heroku.com/blog/handling-very-large-tables-in-postgres-using-partitioning)
- Source URL: https://www.heroku.com/blog/handling-very-large-tables-in-postgres-using-partitioning
- Ingested by Yomu: 2026-09-09T14:24:55.974Z

[Read original post](https://www.heroku.com/blog/handling-very-large-tables-in-postgres-using-partitioning)