Relational Service
The Relational Service is a central and critical database service that provides relational data storage for multiple Lakehousecat platform services. This service manages all relational data including user configurations, data source models, application configurations, group memberships, and supports other services requiring relational persistence such as Operations Service and reporting systems.
Overview
The Relational Service functions as the primary relational database backbone for the Lakehousecat platform, storing and managing structured data that requires ACID compliance and relational integrity. As a central service supporting multiple platform components, it requires careful configuration management and scaling considerations to ensure data consistency and platform stability.
Administrator Access Required
Only users with Administrator privileges can access and configure the Relational Service. Navigate to Admin Workspace > Settings > Services > Relational Service.
Critical Central Database Service
The Relational Service is extremely central to platform operations. Configuration changes and deployments should be handled with extreme care. Contact Lakehousecat Support for scaling needs or configuration uncertainties.
Core Functionality
Relational Data Management
- Structured Data Storage: Storage of structured relational data with ACID compliance
- Data Integrity: Maintenance of referential integrity and data consistency
- Transaction Management: Support for complex transactions and rollback capabilities
- Query Processing: Efficient processing of complex relational queries and joins
Multi-Service Data Persistence
The Relational Service provides data storage for multiple platform components:
User Management Data
- User Configurations: User profiles, preferences, and configuration settings
- Authentication Data: User authentication information and session data
- Permission Data: User permissions, roles, and access control information
- Group Memberships: User group assignments and organizational structure
Application Data
- Data Source Models: Metadata and configuration for data source connections
- Application Configurations: Platform configuration settings and parameters
- Service Configurations: Configuration data for various platform services
- Workflow Definitions: Process and workflow configuration data
Operational Data
- Operations Service Data: Operational task data, schedules, and execution history
- Reporting Data: Report definitions, templates, and execution metadata
- Audit Logs: System audit trails and activity logging
- System Metadata: Platform metadata and system configuration information
Database Operations
- Read/Write Operations: High-performance read and write operations
- Backup and Recovery: Automated backup and point-in-time recovery capabilities
- Data Migration: Tools and processes for data migration and schema updates
- Performance Optimization: Query optimization and database performance tuning
Service Architecture Modes
The Relational Service supports two deployment architectures:
Standalone Mode (Default)
Configuration: Single primary database instance Characteristics:
- Single Node: Single database instance handling all operations
- Simplified Management: Easier configuration and maintenance
- Primary Services: All database operations handled by primary instance
- Resource Efficiency: Lower resource overhead for smaller deployments
Use Cases:
- Development and testing environments
- Small to medium deployments with moderate load
- Scenarios where high availability is less critical
- Cost-optimized deployments with basic requirements
Replication Mode (High Availability)
Configuration: Primary-Reader replication architecture Characteristics:
- Write Separation: Primary node handles all write operations
- Read Scaling: Multiple reader nodes distribute read load
- Horizontal Scaling: Scalable read capacity through additional reader nodes
- Load Distribution: Intelligent load distribution between primary and readers
Use Cases:
- Production environments with high availability requirements
- Large-scale deployments with high read loads
- Mission-critical applications requiring database redundancy
- Performance-optimized deployments with read-heavy workloads
Default Configuration
Standalone Mode Configuration
| Setting | Default Value |
|---|---|
| Architecture | Standalone |
| Primary Services | Enabled |
| CPU Request | 250m |
| Memory Request | 2Gi |
| CPU Limit | 500m |
| Memory Limit | 4Gi |
Replication Mode Configuration
Primary Node (Writer)
| Setting | Default Value |
|---|---|
| Role | Writer Node |
| CPU Request | 250m |
| Memory Request | 2Gi |
| CPU Limit | 500m |
| Memory Limit | 4Gi |
Reader Nodes (Horizontally Scalable)
| Setting | Default Value |
|---|---|
| Role | Reader Node |
| Replica Count | Configurable |
| CPU Request | 250m |
| Memory Request | 1Gi |
| CPU Limit | 500m |
| Memory Limit | 2Gi |
Reader Node Memory Configuration
Reader nodes are configured with 1Gi memory request and 2Gi memory limit, optimized for read-heavy operations and query caching while maintaining resource efficiency.
Replication Architecture Benefits
Write-Read Separation
Primary Node Responsibilities
- Write Operations: Handles all INSERT, UPDATE, DELETE operations
- Schema Changes: Manages database schema modifications
- Transaction Coordination: Coordinates complex transactions and rollbacks
- Replication Management: Manages replication to reader nodes
Reader Node Responsibilities
- Read Operations: Handles SELECT queries and read-only operations
- Query Optimization: Optimized for read performance and caching
- Load Distribution: Distributes read load across multiple instances
- Failover Support: Provides read capacity during primary maintenance
Horizontal Scaling Capabilities
Reader Node Scaling Strategy
# Reader node scaling based on read load requirements
Light Read Load: 1-2 reader nodes
Medium Read Load: 2-4 reader nodes
Heavy Read Load: 4-6 reader nodes
Enterprise Scale: 6+ reader nodes
Load Distribution Benefits
- Read Performance: Improved read performance through load distribution
- Primary Offloading: Reduces load on primary node for better write performance
- Query Optimization: Specialized optimization for different query patterns
- Geographic Distribution: Support for geographically distributed read replicas
Critical Service Management
Central Service Importance
The Relational Service's central role requires special attention:
Platform Dependencies
- User Management: All user-related data and configurations
- Service Configurations: Configuration data for multiple platform services
- Operational Data: Critical operational and workflow data
- Application State: Platform application state and metadata
Data Criticality
- Data Loss Prevention: Critical importance of preventing data loss
- Consistency Requirements: Strong consistency requirements across services
- Availability Requirements: High availability needs for platform operations
- Recovery Planning: Comprehensive disaster recovery and backup strategies
Support Consultation Requirements
Mandatory Support Consultation
Due to the critical nature of the Relational Service, contact Lakehousecat Support in the following scenarios:
Scaling Requirements
- Performance Issues: Database performance degradation or bottlenecks
- Capacity Planning: Planning for increased database capacity needs
- Architecture Changes: Switching between Standalone and Replication modes
- Reader Node Scaling: Determining optimal reader node configurations
Configuration Changes
- Resource Modifications: Changes to CPU or memory allocations
- Replication Setup: Implementing or modifying replication configurations
- Performance Tuning: Database performance optimization requirements
- Schema Changes: Major schema modifications or optimizations
Operational Concerns
- High Availability: Implementing high availability and disaster recovery
- Data Migration: Planning and executing data migration operations
- Backup Strategy: Optimizing backup and recovery strategies
- Monitoring Setup: Implementing comprehensive database monitoring
Performance Optimization
Database Performance Enhancement
Query Optimization
- Index Management: Optimized indexing strategies for frequently accessed data
- Query Planning: Advanced query planning and execution optimization
- Connection Pooling: Efficient database connection pooling and management
- Cache Management: Intelligent caching of frequently accessed data
Resource Management
- Memory Allocation: Optimized memory allocation for database operations
- CPU Utilization: Efficient CPU utilization for query processing
- I/O Optimization: Optimized disk I/O for database operations
- Network Optimization: Optimized network communication for replication
Replication Performance
Primary Node Optimization
- Write Performance: Optimized write performance and transaction processing
- Replication Efficiency: Efficient replication to reader nodes
- Resource Allocation: Optimal resource allocation for primary operations
- Monitoring: Comprehensive monitoring of primary node performance
Reader Node Optimization
- Read Performance: Optimized read performance and query execution
- Cache Efficiency: Efficient caching strategies for read operations
- Load Balancing: Intelligent load balancing across reader nodes
- Synchronization: Efficient synchronization with primary node
Monitoring and Metrics
Database Performance Indicators
Core Database Metrics
- Query Performance: Average query execution times and performance patterns
- Transaction Throughput: Number of transactions processed per second
- Connection Usage: Database connection pool utilization and efficiency
- Resource Utilization: CPU, memory, and I/O utilization patterns
Replication Metrics
- Replication Lag: Lag between primary and reader node synchronization
- Read Distribution: Distribution of read queries across reader nodes
- Primary Load: Load distribution between write and read operations
- Failover Performance: Failover timing and recovery performance
Data Integrity Metrics
- Data Consistency: Consistency checks across primary and reader nodes
- Backup Success: Backup operation success rates and completion times
- Recovery Testing: Regular disaster recovery testing and validation
- Audit Compliance: Audit trail completeness and compliance metrics
Comprehensive Monitoring Commands
# Check Relational Service status and configuration
kubectl get pods -l app=relational-service
kubectl get statefulset relational-service
# Monitor database resource utilization
kubectl top pods -l app=relational-service
# Check primary database health and performance
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT version();"
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT * FROM pg_stat_activity;"
# Monitor replication status (if in replication mode)
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT * FROM pg_stat_replication;"
# Check reader node synchronization
kubectl exec -it <reader-pod> -- psql -U admin -d lakehouse -c "SELECT pg_last_wal_replay_lsn();"
# Monitor database connections and performance
kubectl logs -l app=relational-service --tail=200 | grep -E "(connection|query|slow)"
# Check database configuration and settings
kubectl get configmap -l app=relational-service
kubectl get secret -l app=relational-service
# Monitor backup and recovery operations
kubectl logs -l component=backup --tail=100
Relational Service Performance Dashboard
Implement comprehensive monitoring dashboards:
- Database Performance: Real-time monitoring of query performance and resource usage
- Replication Health: Monitoring of primary-reader replication status and performance
- Data Integrity: Monitoring of data consistency and backup operations
- Connection Management: Analysis of connection pool usage and optimization
- Service Dependencies: Monitoring of services dependent on relational data
Troubleshooting
Common Relational Service Issues
Database Performance Degradation
Symptoms:
- Slow query response times
- High database CPU or memory utilization
- Connection pool exhaustion
Diagnostic Steps:
- Analyze query performance and execution plans
- Check database resource utilization patterns
- Review connection pool configuration and usage
- Examine database logs for errors and warnings
Solutions:
- Contact Support: Consult Lakehousecat support for performance optimization
- Analyze and optimize slow queries and database schema
- Consider scaling to replication mode for read load distribution
- Review and optimize database configuration parameters
Replication Issues
Symptoms:
- High replication lag between primary and readers
- Inconsistent data across database nodes
- Reader node synchronization failures
Diagnostic Steps:
- Monitor replication lag and synchronization status
- Check network connectivity between primary and reader nodes
- Analyze replication logs and error patterns
- Review resource utilization on replication nodes
Solutions:
- Contact Support: Consult support for replication optimization
- Optimize network configuration for replication traffic
- Review and adjust replication configuration parameters
- Consider resource scaling for replication performance
High Availability Concerns
Symptoms:
- Single point of failure in standalone mode
- Insufficient disaster recovery capabilities
- Data backup and recovery issues
Diagnostic Steps:
- Assess current high availability configuration
- Review backup and recovery procedures and testing
- Analyze disaster recovery requirements and capabilities
- Check failover mechanisms and procedures
Solutions:
- Contact Support: Consult support for high availability planning
- Consider migration to replication mode for improved availability
- Implement comprehensive backup and disaster recovery procedures
- Regular testing of failover and recovery procedures
Advanced Database Troubleshooting
Database Performance Analysis
# Analyze database query performance
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT query, mean_time, calls FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;"
# Check database locks and blocking queries
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT * FROM pg_locks WHERE NOT granted;"
# Monitor database connection patterns
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"
# Check database size and growth patterns
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT pg_size_pretty(pg_database_size('lakehouse'));"
Replication Health Analysis
# Check replication slot status
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT * FROM pg_replication_slots;"
# Monitor WAL generation and archiving
kubectl exec -it <primary-pod> -- psql -U admin -d lakehouse -c "SELECT * FROM pg_stat_archiver;"
# Check reader node lag and synchronization
for reader in $(kubectl get pods -l role=reader -o name); do
echo "=== $reader ==="
kubectl exec -it $reader -- psql -U admin -d lakehouse -c "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));"
done
Security and Data Protection
Database Security Measures
- Access Control: Role-based access control for database operations
- Authentication: Secure authentication for database connections
- Encryption: Data encryption at rest and in transit
- Audit Logging: Comprehensive audit logging of database operations
Data Protection Strategies
- Backup Automation: Automated regular backups with retention policies
- Point-in-Time Recovery: Capability for point-in-time recovery operations
- Data Integrity: Regular data integrity checks and validation
- Disaster Recovery: Comprehensive disaster recovery planning and testing
Compliance and Governance
- Data Governance: Governance policies for relational data management
- Privacy Protection: Protection of sensitive user and application data
- Regulatory Compliance: Compliance with data protection regulations
- Change Management: Systematic change management for database schemas
Integration Architecture
Platform Integration Points
Service Data Integration
- User Management Service: User data, profiles, and authentication information
- Operations Service: Operational data, tasks, and workflow information
- Reporting Service: Report definitions, templates, and execution metadata
- Configuration Management: Application and service configuration data
API Integration
- Database APIs: RESTful APIs for database operations and management
- Connection Management: Connection pooling and management APIs
- Backup APIs: Backup and recovery management interfaces
- Monitoring APIs: Database monitoring and metrics interfaces
Relational Service Data Flow
Best Practices
Configuration Management
- Support Consultation: Always consult Lakehousecat support for configuration changes
- Change Documentation: Comprehensive documentation of all database changes
- Testing Procedures: Thorough testing of configuration changes in non-production
- Rollback Planning: Detailed rollback procedures for all configuration changes
Operational Excellence
- Monitoring: Comprehensive monitoring of database performance and health
- Backup Validation: Regular validation of backup integrity and recovery procedures
- Capacity Planning: Proactive capacity planning based on data growth patterns
- Security Reviews: Regular security reviews and access control audits
Data Management Excellence
- Schema Management: Systematic schema change management and version control
- Performance Optimization: Regular performance analysis and optimization
- Data Lifecycle: Implementation of data lifecycle management policies
- Quality Assurance: Regular data quality checks and validation procedures
Critical Service Management Guidelines
- The Relational Service is central to all platform operations - treat with extreme care
- Always contact Lakehousecat Support before making scaling or configuration changes
- Implement comprehensive backup and recovery procedures before any modifications
- Test all changes thoroughly in non-production environments first
- Consider replication mode for production environments to ensure high availability
- Monitor database performance continuously to identify issues proactively
- Plan for disaster recovery and test recovery procedures regularly