Skip to content

TimescaleDB Overview

TimescaleDB is enabled in the PRS production environment to provide optimal performance for time-series data and long-term data growth with zero data deletion policy.

What is TimescaleDB?

TimescaleDB is a PostgreSQL extension that transforms PostgreSQL into a high-performance time-series database. It provides:

  • Faster time-based queries - Optimized for timestamp-based data
  • Automatic data compression - Reduces storage requirements for older data
  • Better performance - Optimized for large datasets with time-series patterns
  • Production-grade reliability - Zero data loss with comprehensive backup strategies

Benefits for PRS

Performance Improvements

  • 10-100x faster time-based queries on large tables
  • Significant reduction in query time for large table scans
  • Much faster aggregations over time periods
  • Optimized analytics queries for reporting

Storage Efficiency

  • 70-90% storage reduction for compressed data
  • Automatic compression for older data
  • Zero data loss - all data remains fully queryable
  • Cost efficiency - significant storage cost savings over years

Production Features

  • Zero data deletion policy - All data preserved indefinitely
  • Lossless compression for storage optimization
  • Multiple backup formats for maximum recovery options
  • Comprehensive backup strategy with retention policies

Architecture in PRS

Hypertables Created

The following tables are converted to hypertables for time-series optimization:

Table Category Tables Chunk Interval Performance Benefit
High-Volume Logs audit_logs, notifications, force_close_logs 1 week Faster audit queries
User Interactions comments, notes, histories 1 week - 1 month Optimized user activity tracking
Business Data requisitions, purchase_orders, delivery_receipts 1 month Core business data optimization
Transaction Data delivery_receipt_items, rs_payment_requests 1 month Transaction data performance

Compression Strategy

Data is automatically compressed based on business requirements:

Data Type Compress After Storage Savings Rationale
High-Volume Logs 30 days 70-90% Frequent access for recent data only
User Interactions 90 days 60-80% Active collaboration period
Business Data 6 months 50-70% Financial compliance requirements
Reference Data 1 year 40-60% Long-term reference needs

Integration with PRS

Database Schema

TimescaleDB is fully compatible with the existing PRS database schema. All existing: - Sequelize models work without changes - Database relationships are preserved - Foreign key constraints remain intact - Indexes and queries continue to function

Migration Process

The TimescaleDB setup is integrated into the standard deployment process: 1. Automatic detection - System detects TimescaleDB availability 2. Extension enablement - TimescaleDB extension is enabled 3. Hypertable creation - Tables are converted via Sequelize migration 4. Compression setup - Compression policies are automatically configured

Zero Downtime

The TimescaleDB integration is designed for: - Zero application changes required - Backward compatibility maintained - Gradual performance improvements as data accumulates - No breaking changes to existing functionality

Expected Performance Impact

Query Performance

SQL
1
2
3
4
5
6
7
8
9
-- Before TimescaleDB (slow on large tables)
SELECT COUNT(*) FROM audit_logs
WHERE created_at >= NOW() - INTERVAL '30 days';
-- Scans entire table

-- After TimescaleDB (fast with chunk exclusion)
SELECT COUNT(*) FROM audit_logs
WHERE created_at >= NOW() - INTERVAL '30 days';
-- Uses only relevant chunks, much faster!

Storage Growth Projections

With compression enabled, the system can handle:

Timeframe Uncompressed Growth Compressed Growth Storage Savings
Year 1 ~24-48 GB ~7-14 GB 70-80%
Year 2 ~48-96 GB ~14-28 GB 70-80%
Year 3+ ~72+ GB ~21+ GB 70-80%

Management and Monitoring

Available Commands

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Check TimescaleDB status
./scripts/deploy-ec2.sh timescaledb-status

# Create backup
./scripts/deploy-ec2.sh timescaledb-backup

# Run maintenance
./scripts/deploy-ec2.sh timescaledb-maintenance full-maintenance

# Health monitoring
./scripts/timescaledb-alerts.sh

Automated Maintenance

The system includes automated maintenance for: - Compression policies - Automatic data compression - Performance optimization - Regular VACUUM and ANALYZE - Health monitoring - Continuous system health checks - Backup procedures - Automated backup creation

Documentation Structure

The TimescaleDB documentation is organized into:

  1. Production Setup - Complete setup guide
  2. Long-Term Maintenance - Maintenance strategies
  3. Monitoring and Alerting - Health monitoring
  4. Backup and Recovery - Data protection
  5. Performance Optimization - Query optimization
  6. Troubleshooting - Common issues and solutions

Getting Started

For developers working with the PRS system:

  1. No changes required - Existing code continues to work
  2. Monitor performance - Observe query improvements over time
  3. Use time-based queries - Leverage TimescaleDB optimizations
  4. Review compression - Understand data lifecycle management

TimescaleDB provides the foundation for PRS to handle years of data growth while maintaining optimal performance and storage efficiency!