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

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

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

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

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

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';

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

Step 3 — Write fast-path

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:

SELECT call_id, transcript
FROM call_logs
WHERE tenant_id = $1
  AND started_at >= now() - interval '7 days'  -- prunes!
  AND started_at <  now()
ORDER BY started_at DESC LIMIT 100;

EXPLAIN should show only the relevant partitions in the plan.

Step 5 — Detach old partitions before drop

ALTER TABLE call_logs DETACH PARTITION call_logs_2026w05 CONCURRENTLY;
COPY call_logs_2026w05 TO PROGRAM 'aws s3 cp - s3://archive/call_logs_2026w05.csv';
DROP TABLE call_logs_2026w05;

CONCURRENTLY (PG14+) avoids the access-exclusive lock.

Step 6 — Monitor partition health

SELECT n.nspname, c.relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
       (SELECT count(*) FROM pg_inherits WHERE inhparent = 'call_logs'::regclass) AS partitions
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname LIKE 'call_logs_%'
ORDER BY c.relname DESC LIMIT 10;

Pitfalls

• Casting the partition key in WHERE — WHERE date_trunc('day', started_at) = ... disables pruning. Use raw >= / <.
• No premake — late inserts at midnight UTC fail with "no partition for value". Premake at least 2.
• Indexes on root only — child indexes must be created via the root or pg_partman.
• Forgetting DETACH CONCURRENTLY — drops take an access-exclusive lock and freeze writes.

CallSphere production note

Explore a live demo and compare current plans to find the right fit for your business.

FAQ

Q: Daily, weekly, or monthly partitions? Daily for >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
• AWS — pg_partman partition management
• Stormatics — Improving Postgres performance with partitioning
• TechBuddies — Time-series partitioning with retention