---
title: "Partitioned Postgres for AI Call Logs: Time-Range at Scale (2026)"
description: "AI call logs grow 100GB+ per month per tenant. Native Postgres range partitioning + pg_partman keeps queries fast and retention painless. A working schema, retention job, and pruning checklist."
canonical: https://callsphere.ai/blog/vw7h-partitioned-postgres-ai-call-logs-2026
category: "AI Infrastructure"
tags: ["Postgres", "Partitioning", "pg_partman", "Call Logs", "Scale"]
author: "CallSphere Team"
published: 2026-03-27T00:00:00.000Z
updated: 2026-05-07T22:22:41.299Z
---

# Partitioned Postgres for AI Call Logs: Time-Range at Scale (2026)

> AI call logs grow 100GB+ per month per tenant. Native Postgres range partitioning + pg_partman keeps queries fast and retention painless. A working schema, retention job, and pruning checklist.

> **TL;DR** — A flat `call_logs` table dies around 200M rows. Range partitioning by `created_at` keeps inserts fast, queries pruned, and retention a single `DROP`. pg_partman automates the whole lifecycle.

## What you'll build

A weekly-partitioned `call_logs` table with automatic future-partition creation and 90-day retention, capable of >10k inserts/sec while keeping point-in-time queries under 50ms.

## Schema

```sql
CREATE TABLE call_logs (
  id BIGSERIAL,
  tenant_id UUID NOT NULL,
  call_id UUID NOT NULL,
  started_at TIMESTAMPTZ NOT NULL,
  ended_at TIMESTAMPTZ,
  duration_sec INT,
  transcript TEXT,
  metadata JSONB DEFAULT '{}',
  PRIMARY KEY (id, started_at)
) PARTITION BY RANGE (started_at);

-- Initial weekly partitions
CREATE TABLE call_logs_2026w19 PARTITION OF call_logs
  FOR VALUES FROM ('2026-05-04') TO ('2026-05-11');
CREATE TABLE call_logs_2026w20 PARTITION OF call_logs
  FOR VALUES FROM ('2026-05-11') TO ('2026-05-18');

CREATE INDEX ON call_logs (tenant_id, started_at DESC);
```

## Architecture

```mermaid
flowchart LR
  W[App writes] --> ROOT[call_logs partitioned root]
  ROOT --> P1[2026w18]
  ROOT --> P2[2026w19]
  ROOT --> P3[2026w20]
  P1 --> COLD[Detach + S3 archive at 90d]
  P2 --> HOT[Hot reads]
  P3 --> WRITES[Active writes]
  PM[pg_partman cron] --> ROOT
```

## Step 1 — Install pg_partman

```sql
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
  p_parent_table => 'public.call_logs',
  p_control => 'started_at',
  p_type => 'native',
  p_interval => 'weekly',
  p_premake => 4
);
```

`p_premake = 4` keeps 4 weekly partitions ready ahead of `now()` so inserts never miss a partition.

## Step 2 — Schedule maintenance

```sql
UPDATE partman.part_config SET
  retention = '90 days',
  retention_keep_table = false,
  retention_keep_index = false,
  optimize_constraint = 30
WHERE parent_table = 'public.call_logs';
```

```bash
# Cron every 15 min
psql -d app -c "CALL partman.run_maintenance_proc();"
```

## Step 3 — Write fast-path

```ts
await prisma.$executeRaw`
  INSERT INTO call_logs (tenant_id, call_id, started_at, ended_at, duration_sec, transcript)
  VALUES (${tenantId}::uuid, ${callId}::uuid,
          ${startedAt}, ${endedAt}, ${duration}, ${transcript})
`;
```

Postgres routes the row to the correct partition automatically.

## Step 4 — Read with pruning

Always pin the partition key:

```sql
SELECT call_id, transcript
FROM call_logs
WHERE tenant_id = $1
  AND started_at >= now() - interval '7 days'  -- prunes!
  AND started_at = / 100M rows/day, weekly for the typical 1–10M/day, monthly for archives.

**Q: What's the partition limit?**
Postgres handles thousands gracefully; >10k starts hurting plan time.

**Q: Can I have a global unique constraint?**
Only if it includes the partition key. Otherwise enforce uniqueness in app or via deferred trigger.

**Q: Does logical replication work with partitions?**
Yes — use `publish_via_partition_root` so the subscriber sees a single logical stream.

**Q: pg_partman vs hand-rolled?**
pg_partman every time. Hand-rolled cron jobs forget to premake at exactly the wrong moment.

## Sources

- [Tiger Data — When to consider Postgres partitioning](https://www.tigerdata.com/learn/when-to-consider-postgres-partitioning)
- [AWS — pg_partman partition management](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.html)
- [Stormatics — Improving Postgres performance with partitioning](https://stormatics.tech/blogs/improving-postgresql-performance-with-partitioning)
- [TechBuddies — Time-series partitioning with retention](https://www.techbuddies.io/2026/03/07/how-to-build-postgresql-time-series-partitioning-with-automatic-retention/)

---

Source: https://callsphere.ai/blog/vw7h-partitioned-postgres-ai-call-logs-2026