Feature Request Casdoor Transaction Mode DB Connections And Separate DB Endpoints For SELECT Queries

Hey guys! Today, we're diving deep into a crucial feature request that could significantly boost Casdoor's performance and scalability, especially in production environments like Kubernetes. We're talking about transaction mode database connections and separate DB endpoints for SELECT queries. This is a game-changer for those of you running Casdoor with PostgreSQL, particularly in setups leveraging read replicas for optimized performance. Let's break it down and see why this matters and how it can make your life easier.

The Problem: Read/Write Splitting Challenges in Casdoor

Understanding the Current Database Access Pattern

The core of the issue lies in Casdoor's current approach to database interactions. In many Kubernetes deployments, especially those using PostgreSQL with solutions like CNPG (Cloud Native PostgreSQL), the goal is to optimize performance by routing SELECT queries to a read-only replica. This replica resides in the same availability zone as the Casdoor pod, ensuring low latency and efficient resource utilization. Meanwhile, INSERT, UPDATE, and DELETE queries are directed to the primary node, maintaining data integrity and consistency.

However, Casdoor's existing database access pattern throws a wrench in these plans. Here's why:

• Prepared Statements Across Transactions: The application reuses prepared statements across multiple transactions. While this can be efficient in some scenarios, it becomes a major roadblock when using transaction pooling mode with middleware like Pgcat or Odyssey. The dreaded error message? "pq: prepared statement does not exist." This is because prepared statements are typically tied to a specific connection in transaction pooling, and reusing them across different connections fails.
• Session Pooling Limitations: In session pooling mode, Pgcat faces challenges in reliably routing queries based on their type. This means that DELETE and UPDATE queries might inadvertently be sent to read replicas, leading to "read-only transaction" errors and application instability. This is far from ideal, as it undermines the entire purpose of read/write splitting.
• Lack of Built-in Read/Write Separation: Casdoor currently lacks a built-in mechanism to configure separate connections for reads and writes. This absence forces you to either modify Casdoor's source code or forgo the benefits of read/write splitting at the proxy layer. Neither option is particularly appealing for those seeking a clean, scalable solution.

Why This Matters: The Impact on Scalability and Performance

So, why is this a big deal? Well, without the ability to effectively split reads and writes, Casdoor's scalability and performance can suffer, especially under heavy read loads. Think about scenarios with numerous users, frequent authentication requests, and extensive data retrieval. In these situations, directing all traffic to the primary database can create a bottleneck, leading to increased latency and a less-than-ideal user experience.

Furthermore, integrating Casdoor with high-availability (HA) Postgres setups becomes more complex. HA setups often rely on read replicas to handle read traffic, ensuring that the application remains responsive even if the primary node experiences issues. Without proper read/write splitting, Casdoor cannot fully leverage the benefits of these HA architectures.

In essence, the current limitations hinder Casdoor's ability to scale efficiently in production environments, increase latency for read-heavy workloads, and seamlessly integrate with robust Postgres HA setups. This is where the proposed solution comes into play.

The Proposed Solution: A Path to Enhanced Scalability and Performance

The feature request proposes two key enhancements to address the challenges outlined above:

1. Transaction Pooling Compatibility: Ensuring Prepared Statements Play Nice

The first part of the solution focuses on making Casdoor compatible with transaction pooling. This involves ensuring that prepared statements are created within the scope of each transaction. By doing so, middleware like Pgcat can operate in transaction mode without encountering the "prepared statement does not exist" error. This is a crucial step towards enabling effective read/write splitting with connection pooling.

How This Works

Instead of reusing prepared statements across multiple transactions, the application will create a new prepared statement for each transaction. This ensures that the statement is tied to the specific connection used for that transaction, resolving the compatibility issue with transaction pooling.

The Benefits

• Seamless Integration with Pgcat and Odyssey: This change allows Casdoor to work seamlessly with popular connection pooling solutions like Pgcat and Odyssey in transaction mode.
• Improved Connection Management: Transaction pooling can lead to more efficient connection management, reducing the overhead associated with establishing and tearing down connections.
• Enhanced Scalability: By optimizing connection management and enabling read/write splitting, this enhancement contributes to improved scalability, particularly under heavy loads.

2. Optional Separate Read Connection: Directing SELECT Queries to Read Replicas

The second part of the solution introduces the option to configure a separate connection for read operations. This involves adding a configuration parameter, such as READ_DB_DSN (or similar), that allows you to specify a connection string for a read-only database. With this option, SELECT queries can be routed to a read-only replica, independent from writes, which are directed to the primary database.

How This Works

When configured, Casdoor will use two distinct database connections: one for read operations (using the READ_DB_DSN) and another for write operations (using the existing DB_DSN). This allows for a clear separation of read and write traffic at the application level.

The Benefits

• Optimized Read Performance: By directing SELECT queries to read replicas, this feature significantly improves read performance, reducing latency and freeing up resources on the primary database.
• Enhanced Scalability: Separating read and write traffic allows Casdoor to scale more effectively, particularly in read-heavy scenarios.
• Seamless Integration with Postgres HA Setups: This feature enables Casdoor to seamlessly integrate with Postgres HA setups, fully leveraging the benefits of read replicas for improved availability and performance.
• Simplified Read/Write Splitting: The built-in option for separate read connections simplifies the process of implementing read/write splitting, eliminating the need for complex workarounds or source code modifications.

The Impact: Scaling Casdoor for Production Environments

By implementing these two enhancements, Casdoor can achieve significant improvements in scalability, performance, and integration with production-grade database setups. Let's recap the key benefits:

• Improved Performance: Reduced latency for read-heavy workloads by routing SELECT queries to read replicas.
• Enhanced Scalability: Ability to handle higher traffic volumes and more concurrent users by distributing the load across multiple database nodes.
• Seamless Integration: Better integration with Postgres HA setups and connection pooling solutions like Pgcat and Odyssey.
• Simplified Configuration: Easy-to-use configuration options for read/write splitting, eliminating the need for complex workarounds.
• Optimized Resource Utilization: Efficient use of database resources by directing read traffic to read replicas and write traffic to the primary node.

Conclusion: A Step Forward for Casdoor's Evolution

This feature request represents a significant step forward in Casdoor's evolution as a robust and scalable identity and access management solution. By addressing the challenges of read/write splitting and transaction pooling, Casdoor can better serve the needs of organizations running production workloads in Kubernetes environments and beyond. The proposed enhancements will not only improve performance and scalability but also simplify the integration with popular database technologies and HA setups.

So, what do you guys think? Is this something you'd find valuable in your Casdoor deployments? Let's keep the conversation going and help shape the future of Casdoor!