Configuring PostgreSQL for Paperclip in production is a task you cannot postpone. When 50 AI agents simultaneously write action logs, call LLMs, and update task statuses, the database begins to degrade within a week. Without proper configuration, SELECT latency grows to 5 seconds, and a primary failure leads to data loss from the last minutes. We configure PostgreSQL to withstand such load without failures: latency < 100 ms at 95th percentile, automatic failover in 30 seconds, point-in-time recovery to any second.
What problems does proper configuration solve?
Degradation of agent log queries. Without composite indexes on agent_id and created_at, date filtering becomes a full scan. We create partial indexes for active tasks — this speeds up selection 10x compared to full scan.
Rapid table growth. The log table can easily grow by hundreds of gigabytes per month. We use RANGE partitioning by date: old partitions are automatically archived and deleted, while queries for fresh data stay fast. This reduces footprint by 40% and speeds up backups.
Data loss during failure. We configure streaming replication with Patroni for automatic failover. If the primary goes down, a replica takes over in 30 seconds. WAL archiving to S3 via pgBackRest provides point-in-time recovery to any second — saving up to 50% on downtime.
How we do it: stack and case study
For a typical project — Paperclip with 50 AI agents — we use PostgreSQL 16 on Ubuntu 22.04. Configuration:
shared_buffers = 25% RAM work_mem = 64MB maintenance_work_mem = 1GB effective_cache_size = 75% RAM wal_buffers = 64MB random_page_cost = 1.1 We create indexes only after analyzing real workload via pg_stat_statements. Partitions are daily with a 90-day retention. Replication is synchronous to one replica, Patroni controls the cluster.
Case study: A client had SELECT latency up to 5 seconds after a month of operation. After our tuning, p99 dropped to 80 ms. Infrastructure costs decreased by 30% due to efficient resource utilization.
Process of work
- Schema and load analysis — collect metrics, study Paperclip queries.
- Configuration design — select shared_buffers, work_mem, plan partitions and indexes.
- Implementation — apply changes, configure Patroni and replication.
- Testing — load testing with synthetic agents, check failover.
- Production deployment — phased rollout, monitoring for the first day.
What's included in the work
- PostgreSQL configuration (parameters, indexes, partitions).
- Replication setup (Streaming + Patroni) or a fault-tolerant cluster.
- Backup configuration (pgBackRest + S3 with PITR).
- Monitoring (Prometheus + Grafana + alerts).
- Documentation and operational recommendations.
Timelines and metrics
The work takes 3 to 7 days depending on schema complexity. Our team has years of PostgreSQL experience (over 30 projects with AI loads). We guarantee that latency will not exceed 100 ms at the 95th percentile. Get a consultation for your project — we'll assess it in one day.
How to properly configure PostgreSQL for Paperclip?
The main rule — do not copy default settings. Start with shared_buffers = 25% RAM, then fine-tune based on real workload. Be sure to enable pg_stat_statements for statistics collection — without it you're blind. Use the official Patroni documentation as a foundation, but adapt it to Paperclip.
What to do under high load?
Connect PgBouncer for connection pooling — Paperclip can open hundreds of connections. Increase work_mem for complex queries (RAG, embedding search). If load is peak, temporarily spin up additional read replicas. For comparison: without PgBouncer, max_connections hits the limit already with 50 agents; with it, we easily handle 200 agents.
Monitoring and alerts
| Metric | Threshold | Action |
|---|---|---|
| cache hit ratio | <99% | Increase shared_buffers |
| replication lag | >1 s | Check network |
| long running queries | >5 s | Optimize query |
| index bloat | >20% | Reindex |
We set up Grafana alerts on all critical metrics. Notifications go to Telegram — response within 10 minutes.
Comparison: manual tuning vs automated approach
| Parameter | Manual tuning | Our approach |
|---|---|---|
| Execution time | 5–10 days | 3–7 days |
| Latency guarantee | No | <100 ms (95%) |
| Failover | Manual | Automatic (30 s) |
| PITR recovery | Rarely | Down to second |
Typical mistakes
- Skipping partitioning — after 3 months of logs, even simple queries become slow.
- Not configuring PgBouncer — Paperclip creates a connection pool, but without a pooler, max_connections quickly hits the limit.
- Ignoring WAL archiving — without it, PITR is impossible, data loss on failure is inevitable.
If you need a consultation on your configuration, contact us — we'll assess your project in one day. Write to us — we'll help you get the most out of PostgreSQL.