# Resource Limits and Recommendations
## Overview
This document outlines tested resource limits, PostgreSQL configuration recommendations, and capacity planning guidelines for pg_tviews deployments.
## Tested Limits
Based on benchmark testing and production experience:
| TVIEW Size | 10M rows | 5M rows | Beyond 5M, consider partitioning |
| Dependency Depth | 10 levels | 5 levels | Hard limit: 10, soft limit: 5 for performance |
| Cascade Width | 50 TVIEWs | 20 TVIEWs | One base table → N TVIEWs |
| JSONB Data Size | 10 MB | 1 MB | Per-row JSONB document size |
| Concurrent Refresh | 100 sessions | 50 sessions | Parallel cascade updates |
| Batch Size | 100K rows | 10K rows | Single UPDATE affecting TVIEWs |
## PostgreSQL Configuration Recommendations
### Small Deployments (<100K rows per TVIEW)
```sql
-- postgresql.conf
work_mem = 64MB
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 128MB
```
### Medium Deployments (100K-1M rows)
```sql
work_mem = 128MB
shared_buffers = 1GB
effective_cache_size = 4GB
maintenance_work_mem = 512MB
max_parallel_workers_per_gather = 4
```
### Large Deployments (>1M rows)
```sql
work_mem = 256MB
shared_buffers = 4GB
effective_cache_size = 16GB
maintenance_work_mem = 2GB
max_parallel_workers_per_gather = 8
random_page_cost = 1.1 -- For SSD storage
```
## Capacity Planning
### Estimating TVIEW Size
```sql
-- Current size
-- Trinity pattern: tv_your_entity has pk_your_entity (int), id (UUID), data (JSONB)
SELECT pg_size_pretty(pg_relation_size('tv_your_entity'));
-- With indexes (includes pk_your_entity primary key, id index)
SELECT pg_size_pretty(pg_total_relation_size('tv_your_entity'));
-- Projection: If base table has 1M rows
-- Estimated TVIEW size ≈ base_table_size × 1.5 to 2.0
-- (due to JSONB denormalization + UUID storage)
```
### Estimating Cascade Performance
```
Single-row cascade time = 5-8ms (with jsonb_delta)
Batch cascade (N rows) ≈ 5ms + (N × 0.5ms)
Example: 1000 rows ≈ 505ms
```
## Scaling Recommendations
### Horizontal Scaling
- **Read Replicas**: Use read replicas for TVIEW queries, primary handles writes
- **Connection Pooling**: Implement PgBouncer for connection management
- **Load Balancing**: Distribute read queries across replicas
### Vertical Scaling
- **More RAM**: Larger shared_buffers, work_mem for better performance
- **More CPU**: Increase max_parallel_workers for concurrent operations
- **Faster Storage**: Reduce random_page_cost for SSD deployments
### Partitioning
A TVIEW's table is a plain table pg_tviews creates and maintains; it cannot be
partitioned. Base tables can be: a write to a partition is mapped to the keys of
the TVIEWs that read its partitioned table.
## Performance
Cost depends on the definition and the host: measure yours with
`test/sql/real_benchmark` ([README](../../test/sql/real_benchmark/README.md)). On
the reference host (PostgreSQL 18, product catalogue of 10K products and 50K
reviews, 2026-10), a single-row write refreshing its TVIEW row takes about 1.7 ms
with or without `jsonb_delta`, and an update of 100 rows about 36 ms. A cascade
costs one row refresh per parent row reached.
## Memory Requirements
### Per-Connection Memory
```
Base memory per connection: 2-5MB
work_mem per sort/hash: Configured value
JSONB processing: 2-10MB for large documents
Cascade state: 1-5MB per active cascade
```
### Total Memory Calculation
```sql
-- Estimate total memory usage
SELECT
(SELECT count(*) FROM pg_stat_activity WHERE state = 'active') * 5 as base_mb,
current_setting('work_mem')::int / 1024 as work_mem_mb,
(SELECT count(*) FROM tviews.pg_tview_meta) * 2 as cascade_overhead_mb
FROM pg_settings
WHERE name = 'work_mem';
```
## Monitoring Thresholds
### Alert Levels
- **Queue Size**: Warning > 100, Critical > 1000
- **Update Latency**: Warning > 1s, Critical > 5s
- **Memory Usage**: Warning > 70%, Critical > 85%
- **Error Rate**: Warning > 1%, Critical > 5%
- **Cascade Depth**: Warning > 3, Critical > 5
### Health Check Queries
```sql
-- Quick health assessment
SELECT
CASE WHEN unhealthy > 0 THEN 'WARNING' ELSE 'OK' END as tview_status,
CASE WHEN memory_usage > 0.8 THEN 'WARNING' ELSE 'OK' END as memory_status,
CASE WHEN error_count > 0 THEN 'ERROR' ELSE 'OK' END as error_status
FROM (
SELECT
(SELECT count(*) FROM pg_tviews_health_check() WHERE status <> 'OK') as unhealthy,
(SELECT sum(total_bytes) / (SELECT setting::bigint * 1024 * 1024
FROM pg_settings WHERE name = 'shared_buffers')
FROM pg_backend_memory_contexts) as memory_usage,
(SELECT count(*) FROM pg_log WHERE message LIKE '%ERROR%' AND log_time > now() - interval '1 hour') as error_count
) checks;
```
## Backup and Recovery
### Backup Strategy
```bash
# Metadata-only backup (fast, small)
pg_dump -t tviews.pg_tview_meta -t tviews.pg_tview_helpers > tview_metadata.sql
# Full database backup
pg_dump dbname > full_backup.sql
# Incremental WAL shipping
# Configure for PITR recovery
```
### Recovery Time Objectives
- **RTO (Recovery Time Objective)**: < 15 minutes for metadata, < 4 hours for full data
- **RPO (Recovery Point Objective)**: < 5 minutes data loss acceptable
- **Backup Frequency**: Metadata hourly, full database daily
## Troubleshooting High Resource Usage
### CPU Usage Issues
```sql
-- Identify CPU-intensive queries
SELECT
pg_stat_activity.pid,
pg_stat_activity.query,
pg_stat_activity.state,
extract(epoch from (now() - pg_stat_activity.query_start)) as duration_seconds
FROM pg_stat_activity
WHERE pg_stat_activity.state = 'active'
ORDER BY duration_seconds DESC
LIMIT 10;
```
### Memory Issues
```sql
-- Check memory usage by connection
SELECT
pg_stat_activity.pid,
pg_stat_activity.usename,
pg_stat_activity.client_addr,
pg_size_pretty(pg_backend_memory_contexts.total_bytes) as memory_used,
pg_backend_memory_contexts.name as context_name
FROM pg_stat_activity
JOIN LATERAL pg_backend_memory_contexts ON true
ORDER BY pg_backend_memory_contexts.total_bytes DESC;
```
### Disk I/O Issues
```sql
-- Monitor I/O statistics
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_tup_upd DESC;
```
## See Also
- [Performance Tuning](../operations/performance-tuning.md) - Detailed optimization guides
- [Monitoring Guide](../operations/monitoring.md) - Health check and metrics
- [Troubleshooting Guide](../operations/troubleshooting.md) - Production procedures