Performance and Scale
Optimizing FERIN registers for speed and scale: database tuning, caching strategies, query patterns, and capacity planning.
Performance Fundamentals
Register performance depends on three factors: database efficiency, application architecture, and caching strategy. Optimize all three for best results.
Database
Schema design, indexing, queries
High ImpactApplication
Code efficiency, connection pooling
Medium ImpactCaching
Read replicas, CDN, application cache
High ImpactDatabase Optimization
Schema Design Principles
- Normalize for integrity: Keep concepts, items, and versions in separate tables
- Denormalize for reads: Add computed columns for common queries
- Partition large tables: Partition by date or status for tables over 10M rows
- Use appropriate types: Don't store JSON in text columns; use native JSON types
Indexing Strategy
Proper indexing is the single most important performance optimization:
| Query Pattern | Recommended Index | Notes |
|---|---|---|
| Lookup by identifier | UNIQUE INDEX on identifier | Always needed |
| Filter by status | INDEX on (valid, published) | Composite for common combinations |
| Date range queries | INDEX on date_added, date_modified | Supports pagination by date |
| Full-text search | FULLTEXT or GIN index | Database-specific |
| Concept hierarchy | INDEX on parent_id, CTE optimization | Consider closure tables |
| JSON content queries | GIN index on JSON column | For PostgreSQL |
Query Optimization
Good: Paginated with Index
SELECT * FROM items
WHERE status = 'valid'
ORDER BY date_added DESC
LIMIT 20 OFFSET 0;Uses index on (status, date_added)
Bad: Unfiltered Large Result
SELECT * FROM items
ORDER BY date_added DESC;Scans entire table, sorts in memory
Good: Cursor-Based Pagination
SELECT * FROM items
WHERE date_added < :cursor
ORDER BY date_added DESC
LIMIT 20;Uses index, consistent performance
Connection Pooling
Database connections are expensive. Use connection pooling:
- Pool size: 10-20 connections per application instance
- Timeout: Set connection timeout (30-60 seconds)
- Validation: Test connections before use
- External pooler: Consider PgBouncer for PostgreSQL
Caching Strategy
Cache Layers
CDN
- Static assets (JS, CSS, images)
- Published item content (immutable)
- API responses for public data
TTL: 1 hour - 1 day
Application Cache
- Concept hierarchies
- Domain value sets
- Register metadata
TTL: 5-15 minutes
Database Cache
- Query result cache
- Item lookup cache
- Count/aggregation cache
TTL: 1-5 minutes
Cache Invalidation
Cache invalidation is hard. Strategies:
- Time-based: Simple but may serve stale data
- Event-based: Invalidate on write (more complex)
- Version-based: Include version in cache key
- Tag-based: Group related entries for bulk invalidation
What to Cache
| Data Type | Cache? | Strategy |
|---|---|---|
| Concept hierarchies | Yes | Cache fully, invalidate on any change |
| Domain values | Yes | Cache fully, rare invalidation |
| Published items | Yes | Cache by version, long TTL |
| Draft items | No | Too frequently updated |
| Search results | Partial | Short TTL, common queries only |
| Proposal queues | No | Must be real-time |
Scaling Patterns
Vertical vs Horizontal
Vertical Scaling
Increase resources on single server
- Simpler to implement
- Limited ceiling
- Higher cost per unit
- No code changes needed
Best for: Small-medium registers (up to 10M items)
Horizontal Scaling
Add more servers
- More complex architecture
- Near-infinite ceiling
- Better cost efficiency at scale
- Requires application changes
Best for: Large registers (10M+ items)
Read Scaling
Most register workloads are read-heavy (90%+ reads). Scale reads:
- Add read replicas: Direct read queries to replicas
- Implement caching: Reduce database load
- Use CDN: Serve static content at edge
- Separate search: Use Elasticsearch/Solr for search
Write Scaling
Write scaling is harder. Options:
- Queue writes: Async processing for non-critical writes
- Batch operations: Combine multiple writes
- Sharding: Partition data across databases
Sharding Complexity: Sharding adds significant complexity. Only consider it when you've exhausted other options and have 100M+ items or very high write volume.
Benchmarks
Expected performance on modest hardware (4 vCPU, 16GB RAM, SSD):
Read Performance
| Operation | Target | Notes |
|---|---|---|
| Single item lookup (cached) | < 5ms | p99 latency |
| Single item lookup (uncached) | < 20ms | p99 latency |
| Paginated list (20 items) | < 50ms | p99 latency |
| Search query | < 100ms | p99 latency |
| Concept hierarchy traversal | < 30ms | 5 levels, cached |
Write Performance
| Operation | Target | Notes |
|---|---|---|
| Create item | < 50ms | Single item, indexed |
| Update item | < 30ms | Single field change |
| Bulk import | 1000 items/sec | Bypassing governance |
| Proposal submission | < 100ms | With validation |
Throughput
| Scale | Reads/sec | Writes/sec | Architecture |
|---|---|---|---|
| Small | 1,000 | 100 | Single server |
| Medium | 10,000 | 500 | Primary + 2 replicas |
| Large | 100,000 | 2,000 | Cluster with caching |
Performance Checklist
Database
- ☐ Indexes on all lookup fields
- ☐ Query plans reviewed
- ☐ Connection pooling enabled
- ☐ Slow query logging enabled
Application
- ☐ N+1 queries eliminated
- ☐ Pagination on all lists
- ☐ Response compression enabled
- ☐ Timeout handling in place
Caching
- ☐ Static assets on CDN
- ☐ Application cache for hot data
- ☐ Cache invalidation tested
- ☐ Cache hit ratio monitored
Monitoring
- ☐ Latency percentiles tracked
- ☐ Throughput monitored
- ☐ Error rate alerts
- ☐ Regular load testing