GovTool Performance Analysis and Optimization

Overview

GovTool has experienced performance issues primarily due to complex SQL queries, missing database indexes, and inefficient query logic. This document outlines the current state of critical endpoints, past and ongoing optimizations, and future improvements aimed at reducing load times.

DRep Directory

Data and Fetching Logic

• Endpoint: GET /drep/list
• SQL File: govtool/backend/list-drep.sql
• Usage: Core DRep directory endpoint, also used on the dashboard for delegation lookups.
• Challenges Faced:
  • Complex queries with multiple nested joins, duplicated joins, and CTEs.
  • Overuse of window functions like ROW_NUMBER().
  • Missing indexes leading to slow lookups.
• Optimizations Implemented:
  • Refactored query to reduce redundant joins.
  • Removed unnecessary window functions where possible.
• Impact: Improved query execution time and overall responsiveness of the DRep directory.

Governance Actions

Data and Fetching Logic

• Endpoint: GET /proposal/list
• SQL File: govtool/backend/sql/list-proposals.sql
• Usage: Core endpoint for displaying governance actions.
• Challenges Faced:
  • No direct SQL performance issues.
  • Issues arose from incorrect filtering, parsing, and state management rather than database inefficiencies.
• Optimizations Implemented:
  • Fixed parsing of governance action details.
  • Improved filtering logic for votes.
• Impact: Resolved inconsistencies in governance action rendering.

Dashboard Page

Data and Fetching Logic

• Endpoints:
  • GET /ada-holder/get-voting-power/{stakeKey}
  • GET /drep/get-voting-power/{drepId}
  • GET /drep/info/{drepId}
  • GET /network/metrics
• Usage: Provides voting power data and network metrics for dashboard visualization.
• Challenges Faced:
  • The get-voting-power query initially depended on utxo_view, which lacked proper indexing.
  • GET /network/metrics had duplicated logic and excessive window functions (TODO: Still needs to be optimized).
• Optimizations Implemented:
  • Stopped using utxo_view and instead relied on the drep_distr table.
• Impact:
  • Faster voting power retrieval.

Identified Performance Bottlenecks

1. Metadata Validation Service Slowness

   • Issue: Each DRep and governance action is validated against CIP-108 and CIP-119, requiring multiple POST requests.
   • Impact: Causes significant delays as frontend calls validation for every item.
   • Potential Solution: Optimization or redesign of the validation process to reduce the number of external requests.

2. Inefficient Transaction Status Checking

   • Endpoint: GET /transaction/status/{transactionId}
   • Issue: Due to the lack of sockets or queue-based updates, this endpoint is re-executed every 20 seconds.
   • Potential Solution: Implementing WebSockets or a job queue to minimize unnecessary re-execution.

Future Optimizations

Improved Testing Strategy

• Establish query performance benchmarks before and after optimizations.
• Use real-time profiling tools to detect slow queries dynamically.
• Expand automated testing coverage for API endpoints.

Considerations for Further Improvements

• Review frontend data fetching strategies to minimize redundant API calls.
• Improve monitor of backend performance to detect and mitigate memory leaks or bottlenecks.
• Implement better login and diagnostics for identifying slow queries and inefficient processes.
• Further refine window function usage.
• Refine existing SQL queries against known bottlenecks to further optimize performance.
• Refine indexes and locks of databases to find reasons and understand the logic behind some of the most complex tables.
• Improve utilization of server side compression

Conclusion

GovTool has historically suffered from slow load times, primarily due to inefficient SQL queries and frontend validation overhead. By optimizing queries, refining indexing strategies, and reworking validation mechanisms, we aim to improve the platform’s responsiveness significantly. Continued monitoring and incremental optimizations will further enhance performance and scalability.