[Timebox] fix: performance degradation of flexible back-end with many workspaces

[magrinj]

Scope & Context

The flexible backend was officially released on November 24th. With the production database accumulating an increasing number of workspaces, we've observed performance degradation directly proportional to the number of workspaces. This issue appears to be associated with pg_graphql and our current method of utilization.

Current behavior

Currently, we assign a specific data source for each workspace. This approach disrupts the caching mechanism of pg_graphql, leading to frequent regeneration of the GraphQL schema. pg_graphql iterates over all database schemas during the generation of the GraphQL schema. Since we create a separate database schema for each workspace, this significantly slows down pg_graphql.

Expected behavior

The optimal solution would involve utilizing a single data source for all workspaces, resorting to a new data source only when querying foreign objects (a functionality that is not yet implemented). Further investigation into the internal workings of pg_graphql is necessary, possibly leading to an improved method for generating and versioning the GraphQL schema based on the database schema.

Technical inputs

1. Data Source Optimization: Explore consolidating the multiple data sources into a singular, centralized source. This change aims to reduce the load on pg_graphql by minimizing schema generation processes.
2. Schema Caching Strategy: Investigate alternative caching strategies within pg_graphql. This could involve implementing a more efficient caching mechanism that doesn't require schema regeneration for each workspace.
3. Performance Profiling: Conduct thorough performance profiling to pinpoint specific bottlenecks associated with pg_graphql when handling multiple schemas. Tools like EXPLAIN ANALYZE in PostgreSQL can maybe be useful for this analysis.
4. Code Review and Refactoring: Review the current implementation code for pg_graphql integration. Look for any inefficient practices or potential improvements that could be contributing to the performance degradation.

[charlesBochet]

In scope for this ticket: #2190

[charlesBochet]

Scope of this ticket:

• understand the issue and have an idea of how to solve it
• take a decision on datasources by workspace