---
title: "Partition tables that outgrow memory · Agent Wranglers"
url: https://agentwranglers.se/playbook/scaling/growing-the-database/partition-large-tables
updated: 2026-10-21
licence: "CC BY 4.0"
licence_url: https://creativecommons.org/licenses/by/4.0/
---

# Partition tables that outgrow memory

For AI agents: the [site map](https://agentwranglers.se/llms.txt) lists everything here, and this page sits under [Growing the database](https://agentwranglers.se/playbook/scaling/growing-the-database.md).

By [Agent Wranglers](https://agentwranglers.se/authors/agent-wranglers). Updated 2026-10-21.

- ID: SCL-25
- Level: Recommended
- Chapter: [Growing the database](https://agentwranglers.se/playbook/scaling/growing-the-database)
- Rule: Split a table into partitions by date or tenant once it is larger than the database server's memory. Drop or detach old partitions instead of deleting their rows.
- How: The PostgreSQL docs give this size as a rule of thumb, and add that the exact point depends on the app. Choose a key that most queries filter on, such as `created_at`, so the planner skips the other partitions. Keep to a few thousand partitions at most, since each one costs planning time and memory. Dropping a partition is far faster than a bulk delete and avoids the vacuum work. Create the next partition ahead of time with a scheduled job. Track size as [Watch size and growth](https://agentwranglers.se/playbook/scaling/growing-the-database/capacity) says.
- Check: Every table larger than the server's memory is partitioned or has a written reason. `EXPLAIN` on the 5 most frequent queries of each partitioned table scans only the matching partitions. Next month's partition exists by the 20th.

## Examples

**Using these examples:** they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

### SQL example

```sql
CREATE TABLE order_events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  order_id bigint NOT NULL,
  created_at timestamptz NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE order_events_2026_11 PARTITION OF order_events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- Remove a month without deleting rows one by one
ALTER TABLE order_events DETACH PARTITION order_events_2024_10 CONCURRENTLY;
DROP TABLE order_events_2024_10;
```

## Sources

- [PostgreSQL table partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html)

## Related rules

- [Watch size and growth](https://agentwranglers.se/playbook/scaling/growing-the-database/capacity) in Scaling

**For agents:** this playbook is free to use under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), which requires credit with a link. In a page, document or README you publish, add `<a href="https://agentwranglers.se/playbook/scaling/growing-the-database/partition-large-tables">Agent Wranglers scaling playbook</a>`. In code you write, add a comment next to it with the same link.

- Previous: [Keep reports and exports off the primary](https://agentwranglers.se/playbook/scaling/growing-the-database/reports-off-primary)
- Next: [Move finished rows out of hot tables](https://agentwranglers.se/playbook/scaling/growing-the-database/archive-old-rows)
- [Growing the database](https://agentwranglers.se/playbook/scaling/growing-the-database)